Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to orginize you sp advice

764712Sep 9 2010 — edited Sep 10 2010
Hi, all
I'm working on exisiting sp which is inside the package where I need to modify old Select splitting it into 4 depending on the parameters passed, they are quite complicated so I'm thinking how better orginize it for nicer look and easier maintanance. Are there any kind of user define functions or "paragraphs"or "section" or... so I can do something like below and won't need to paste my SELECTs between elseif. Results of my SELECTs go to the cursor (same for all 4) so it also could be concern... And if possible I'd like to keep all code inside the same sp, don't store it somewhere else, moreover it's inside the package.

If param1 = null then
do SELECT1_FUNCTION
elseif param2 = null then
do SELECT2_FUNCTION
elseif param3 = null then
do SELECT3_FUNCTION...

...
...
SELECT1_FUNCTION.
OPEN curs_all for '(Select * from mytable where condition1...)'.

SELECT2_FUNCTION.
OPEN curs_all for '(Select * from mytable where condition2...)'.
...
...

Tx all
Tr
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 8 2010
Added on Sep 9 2010
4 comments
619 views