Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

plsql

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.

like image 765
David Sykes Avatar asked May 17 '12 04:05

David Sykes


People also ask

How do you use a cursor inside a FOR loop?

With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or raises an exception.

Is it possible to open a cursor which is in a package in another procedure a yes b no?

Sure it's possible (although your attempt won't work) - you'd just be passing around the string containing the select statement you want to use to open your ref cursor - but it's not the best design.

Can we pass cursor as parameter in procedure in Oracle?

You can use the PL/SQL type called sys_refcursor - and open a ref cursor and pass that to the procedure as input parameter.


2 Answers

I had a similar problem, where I had two cursors that needed to be processed the same way, so this is how I figured it out.

DECLARE
   --Define our own rowType
   TYPE employeeRowType IS RECORD (
      f_name           VARCHAR2(30),
      l_name            VARCHAR2(30));
   --Define our ref cursor type
   --If we didn't need our own rowType, we could have this: RETURN employees%ROWTYPE
   TYPE empcurtyp IS REF CURSOR RETURN employeeRowType;

   --Processes the cursors
   PROCEDURE process_emp_cv (emp_cv IN empcurtyp) IS
      person employeeRowType;
   BEGIN
      LOOP
         FETCH emp_cv INTO person;
         EXIT WHEN emp_cv%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE('Name = ' || person.f_name ||
                          ' ' || person.l_name);
      END LOOP;
   END;

   --Defines the cursors
   PROCEDURE mainProcedure IS
    emp empcurtyp;
   BEGIN
      OPEN emp FOR SELECT first_name, last_name FROM employees WHERE salary > 50000;
      process_emp_cv(emp);
      CLOSE emp;

      OPEN emp FOR SELECT first_name, last_name FROM kuren WHERE first_name LIKE 'J%';
      process_emp_cv(emp);
      CLOSE emp;
   END;

BEGIN
  mainProcedure;
END;
/

You can also use this if you want to bulk collect your cursors. You just need to change your helper procedure process_emp_cv; the rest can stay the same.

Using BULK COLLECT

 --Processes the cursors
   PROCEDURE process_emp_cv (emp_cv IN empcurtyp) IS
      TYPE t_employeeRowTable IS TABLE OF employeeRowType;
      employeeTable   t_employeeRowTable;
   BEGIN
      LOOP
         FETCH emp_cv BULK COLLECT INTO employeeTable LIMIT 50;
         FOR indx IN 1 .. employeeTable.Count
         LOOP
            DBMS_OUTPUT.PUT_LINE('Name = ' || employeeTable(indx).f_name ||
                          ' ' || employeeTable(indx).l_name);
         END LOOP;
         EXIT WHEN emp_cv%NOTFOUND;
      END LOOP;
   END;
like image 95
plasmaTonic Avatar answered Oct 16 '22 12:10

plasmaTonic


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;
like image 20
Vasily Komarov Avatar answered Oct 16 '22 12:10

Vasily Komarov