how to orginize you sp advice
764712Sep 9 2010 — edited Sep 10 2010Hi, 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