You post this huge script with no descriptions and no real comments and no schema and expect someone to convert it into optimized PL/SQL it for you? Good luck with that.
But here are a few quick pointers:
- You have SELECTs all over the place. Why? What do you plan on doing with the output in PL/SQL?
- Garbage like this tells me your data model is far beyond my help:
UPDATE AGENTMASTER SET AGENCYCATEGORY_ID = 'EMP'
where (substr(agt_id,1,1) not in ( '0', '8', '9', 'I', 'G', 'R' ) or substr(agt_id,1,2) = '02' )
and substr(agt_id,3,1) in ( '8', '9' )
or substr(agt_id,1,3)='002';
Looking at substrings of values for meaning is a sure sign that your model is in bad, bad shape.
- All your INSERT/UPDATES can be merged into only INSERTS. Whatever modifications you want to do to any inserted columns - do them in the insert! Use DECODE or CASE in the SELECT clause if the INSERT to change the value from what is in the source table to what you want it to be in the target table.
- PL/SQL is not the problem here, and it will not be the solution. Your data-model is screwed to begin with. After that, the SQL in this script is sad and innefficient. Worry about the SQL (since the model is likely set in stone) and collapse it down until you only have INSERTs - then simply throw a BEGIN/END; around it and viola - you have PL/SQL.
And usually, such broad requests get completely ignored. You need to be able to stand on your own somewhat.
Reply With Quote
