Can I pass an explicit cursor to a function/procedure for use in FOR loop?

plsql

Solution

Create a package.

Declare your cursor as package variable.

Use `%rowtype` to set function parameter type.

create or replace package test is
  cursor c is select 1 as one, 2 as two from dual;

  procedure test1;
  function test2(test_record c%ROWTYPE) return number;

end test;


create or replace package body test is
  procedure test1 is    
  begin
    for r in c loop      
      dbms_output.put_line(test2(r));
    end loop;
  end;

  function test2(test_record c%ROWTYPE) return number is
    l_summ number;
  begin
    l_summ := test_record.one + test_record.two;
    return l_summ;
  end;
end test;

Problem

I have a procedure that performs some calculations on all records returned by a cursor. It looks a bit like this: ``` PROCEDURE do_calc(id table.id_column%TYPE) IS CURSOR c IS SELECT col1, col2, col3 FROM table WHERE ...; BEGIN FOR r IN c LOOP -- do some complicated calculations using r.col1, r.col2, r.col3 etc. END LOOP; END; ``` Now I have the case where I need to perform the exact same calculation on a different set of records that come from a different table. However, these have the same "shape" as in the above in example. Is it possible to write a procedure that looks like this: ``` PROCEDURE do_calc2(c some_cursor_type) IS BEGIN FOR r IN c LOOP -- do the calc, knowing we have r.col1, r.col2, r.col3, etc. END LOOP; END; ``` I know about `SYS_REFCURSOR`, but I was wondering if it was possible to use the much more convenient `FOR ... LOOP` syntax and implicit record type.

Original source