-
Excuse my COBOL-ese, but what are my options for "performing" common chunks of code in PL/SQL? If I have the same block of code I want to execute several times in a procedure, I know I can do a "goto" and branch to a named routine. My problem is, I need to do this while in a cursor FOR loop, and as soon as I do a "goto" from there, my cursor gets closed so I only process 1 row of the cursor.
How do most people handle this? Do you just write another procedure or function and call it? If so, how do you pass your cursor row to it for processing? Or are there other options I don't know about. THis seems so basic.
Thanks for your input.
-
You could create a function or procedure in your PL/SQL DECLARE section and then you can use it in the loop and not be kicked out.
Hope this clear, if not, say and I could put some more info.
-
Thank you. Do you mean create a package that includes the procedure as well as the common block as a function or procedure? Then I can send that function/procedure cursor rows/variables for processing? Or are you thinking of calling another external function or procedure? If so, I need to know how to pass cursor rows to external functions/procedures.
I appreciate your help very much.
-
I suggest something like :
DECLARE -- main declaring of the PROG
v_1 number;
v_2 table of ...
v_3 varchar2(20);
--
PROCEDURE P_SUBPROC is
Begin
-- do something ...
End;
--=======================
BEGIN -- main program starting point
... do something
v_1 := nnnn
v_2 := zzzzz
P_SUBPROC;
... PL/SQL will return here after P_SUBPROC executing
do anotherthing
v_1 := yyyyy
v_2 := kdkdk
P_SUBPROC;
and so on ...
END; -- main program end here
===> In this example, you can call P_SUBPROC n times, each time it will exec P_SUBPROC (and P_SUBPROC will "see" the current value of v_1, v_2, and the execution will be like a PERFORM : you go to another point, exec something there and return exactly after the starting point.
[]s
Chiappa
-
If you create a package containing the calling as well as the called procedure, you can define global variables, cursors, records in that package, which are accessible to all procedures.
The less dirty way is of course to pass the parameters to called functions/procedures.
Commit;
6502
