PL/SQL common routines
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: PL/SQL common routines

  1. #1
    Join Date
    Jan 2001
    Posts
    63
    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.

  2. #2
    Join Date
    Oct 2000
    Posts
    90
    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.

  3. #3
    Join Date
    Jan 2001
    Posts
    63
    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.

  4. #4
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    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

  5. #5
    Join Date
    Mar 2001
    Location
    Cologne, Germany
    Posts
    24
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width