Cursor FOR LOOP with cursor as parameter-CodePudding

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?

CodePudding user response：

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>

CodePudding user response：

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).