0

I have the following procedure executing some business logic (looping through a cursor):

PROCEDURE myproc() AS

  CURSOR mycur IS
    SELECT * FROM mytable;

BEGIN

  FOR c IN mycur LOOP

    ...business logic here...
    ...many lines of code...

  END LOOP;

END myproc;

I'd like to have different procedures and execute the same business logic for different cursors (sets of data). For example I'd like to have one procedure myproc_adopters() for:

SELECT * FROM mytable WHERE cutomer_type='Adopters'

and another procedure myproc_others() for others:

SELECT * FROM mytable WHERE customer_type!='Adopters'

So I'd like to have one main procedure mainproc() containing cursor loop and business logic and other procedures calling this main procedure and sending different cursors as parameters. The problem is that it seems that cursor FOR loop does not accept cursor as variable that I can send as procedure call parameter:

PROCEDURE myproc_adopters() AS
  CURSOR mycur IS
    SELECT * FROM mytable WHERE customer_type='Adopters';
BEGIN
  mainproc(mycur);
END myproc_adopters;

PROCEDURE myproc_others() AS
  CURSOR mycur IS
    SELECT * FROM mytable WHERE customer_type!='Adopters';
BEGIN
  mainproc(mycur);
END myproc_others;




PROCEDURE mainproc(mycur IN SYS_REFCURSOR) AS
BEGIN
  FOR c IN mycur LOOP <-- does not accept cursor as variable
  ...
  END LOOP;
END mainproc;

How to send different cursor to the same cursor FOR LOOP?

Improve this question

2 Answers 2

Reset to default
1

Your idea is OK (at least, to me), but - you then have to pass refcursor (as you declared it).

For example:

mainproc:

SQL> create or replace procedure mainproc (mycur in sys_refcursor)
  2  is
  3    l_row emp%rowtype;
  4  begin
  5    loop
  6      fetch mycur into l_row;
  7      exit when mycur%notfound;
  8
  9      dbms_output.put_line(l_row.ename ||' - '|| l_row.job ||' - '|| l_row.sal);
 10    end loop;
 11  end;
 12  /

Procedure created.

This procedure accepts a parameter and - depending on it - opens refcursor with some criteria and then calls mainproc, passing that refcursor:

SQL> create or replace procedure myproc_adopters (par_deptno in emp.deptno%type) is
  2    l_rc sys_refcursor;
  3  begin
  4    if par_deptno = 10 then
  5       open l_rc for select * from emp where job = 'CLERK';
  6    elsif par_deptno = 20 then
  7        open l_rc for select * from emp where sal > 2000;
  8    end if;
  9
 10    mainproc (l_rc);
 11    close l_rc;
 12  end;
 13  /

Procedure created.

Testing:

SQL> set serveroutput on
SQL> exec myproc_adopters(10);
SMITH - CLERK - 840
ADAMS - CLERK - 1100
JAMES - CLERK - 950
MILLER - CLERK - 1300

PL/SQL procedure successfully completed.

SQL>
SQL> exec myproc_adopters(20);
JONES - MANAGER - 2975
BLAKE - MANAGER - 2850
CLARK - MANAGER - 2450
SCOTT - ANALYST - 3000
KING - PRESIDENT - 5000
FORD - ANALYST - 3000

PL/SQL procedure successfully completed.

SQL>
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

At first I tried to avoid explicit opening/closing of cursor. I was thinking about the same solution because I know that CURSOR FOR LOOP expects the CLOSED cursor NOT OPEN one (the cursor loop opens and closes the given cursor internally) while SYS_REFCURSOR is reference to already opened cursor (in memory) and CURSOR FOR LOOP will no accept it. BTW where you close the cursor?
Close it after you used it (see line #11 in MAINPROC) (I just added it; didn't exist in initial code I posted).
Closing cursor in the processing procedure is not very clear behaviour. Because consumer cannot know if its input will or will not be consumed by others. So it would be better to move control logic outside of the processing logic and open/close cursor at the same code level (at the end or inside an error handler of myproc_adopters in this example)
0

There are two other ways you could do this that don't require a refcursor:

Add a parameter to your myproc procedure, and update the cursor to retrieve the rows based on the parameter, e.g.:

PROCEDURE myproc(p_query_type IN VARCHAR2) AS

  CURSOR mycur IS
    SELECT *
    FROM   mytable
    WHERE  (p_query_type IS NULL -- or whatever value you want to indicate all rows, e.g. p_query_type = 'ALL'
            OR (UPPER(p_query_type) = 'ADOPTERS' AND customer_type = 'Adopters')
            OR (UPPER(p_query_type) = 'OTHERS' AND customer_type != 'Adopters'));

BEGIN

  FOR c IN mycur LOOP

    ...business logic here...
    ...many lines of code...

  END LOOP;

END myproc;

Or you could extract the business logic into its own procedure (complete with the requisite number of parameters), and the wrapper procedures call that within the cursor-for-loop, e.g.:

PROCEDURE mainproc (p_param1 mytable.col1%TYPE,
                    p_param2 mytable.col2%TYPE,
                    ....)
BEGIN

   ...business logic here...
   ...many lines of code...

END mainproc;


PROCEDURE myproc_adopters() AS
  CURSOR mycur IS
    SELECT * FROM mytable WHERE customer_type='Adopters';
BEGIN
  FOR c IN mycur LOOP

    mainproc (p_param1 => c.col1,
              p_param2 => c.col2,
              ...)

  END LOOP;
END myproc_adopters;

PROCEDURE myproc_others() AS
  CURSOR mycur IS
    SELECT * FROM mytable WHERE customer_type!='Adopters';
BEGIN
  FOR c IN mycur LOOP

    mainproc (p_param1 => c.col1,
              p_param2 => c.col2,
              ...)

  END LOOP;
END myproc_others;

I hope that these procedures are in a package! If you're going to go down the route of having wrapper procedures but your current procedure isn't in a package, I highly recommend you move it into a package.

My personal preference would be to go with a combination of both options; have the cursor decide what rows to select based on the input parameter, but also encapsulate the business logic in its own procedure, or better yet, break it down into separate procedures to make it more manageable, as one long, spaghetti-like procedure isn't good programming practice (IMO).

Improve this answer

1 Comment

Thanks @Boneist for your response. Your first suggestion where one can choose the scope of records with p_query_type seems most convenient for me.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.