CODEWITHSUNDEEP
oracle-databaseplsql
milcaepsilon
milcaepsilon

Reputation: 272

Is it possible to pass cursors with parameter as a parameter of a procedure?

I've got a lot of similar treatments to do and I want to make a pl/sql script for it. It's easier to understand by looking code rather than explain it so here it is a simplify version :

create or replace package my_test as
  cursor my_cursor(my_filter NUMBER) is select column1, column2 from mytable where column3 = my_filter;
  cursor my_cursor2(my_filter VARCHAR2) is select column1, column2 from mytable where column4 LIKE my_filter;
  -- ...

  procedure test1;
  procedure test2(p_cursor XXX);
  function test3(test_record XXX%ROWTYPE) return number;

end my_test;
/

create or replace package body my_test as
  procedure test1 is    
  begin
    test2(my_cursor(3));
    test2(my_cursor2('foo%'));
    test2(my_cursor(5));
    -- ...
  end test1;

  procedure test2(p_cursor XXX) is
    tmp number;
  begin
    for r in p_cursor loop      
      --some actions 
      tmp := test3(r);
      --some actions
    end loop;
  end test2;

  function test3(test_record XXX%ROWTYPE) return number is
    tmp_sum number;
  begin
    -- ...
    tmp_sum := test_record.column1 + test_record.column2;
    -- ...
    return l_summ;
  end test3;


end my_test;
/

BEGIN
    my_test.test1();
END;
/

I've tried my best but didn't succeed. Maybe someone can help me ? It is possible to achieve such a thing ? What should I put instead of XXX ?

Upvotes: 2

Views: 312

Answers (2)

APC
APC

Reputation: 146349

Define a record type to match the projection of the queries you want to execute. You can use this to define the IN parameter of test3().

Define a ref cursor which returns this record type. You can use this to define the IN parameter of test2().

Instead of cursors define functions which return ref cursors, defined by that cursor type.

So your package looks like this:

create or replace package my_test as

  type t_record is record(
     column1 mytable.column1%type
    ,column2 mytable.column2%type
  );

  type t_cursor is ref cursor return t_record;

  function my_cursor (my_filter NUMBER)   return t_cursor ;
  function my_cursor2(my_filter VARCHAR2) return t_cursor ;

  procedure test1;
  procedure test2(p_cursor t_cursor);
  function  test3(test_record t_record) return number;

end my_test;
/

and the implementation looks like this (with some output to make the demo more intelligible):

create or replace package body my_test as

  function my_cursor(my_filter NUMBER) return t_cursor is 
      rc sys_refcursor;
  begin
    open rc for  select column1, column2 from mytable where column3 = my_filter;
    return rc;
  end my_cursor;

  function my_cursor2(my_filter VARCHAR2)  return t_cursor is 
      rc sys_refcursor;
  begin
    open rc for  select column1, column2 from mytable where column4 LIKE my_filter;
    return rc;
  end my_cursor2;

  procedure test1 is    
  begin
    dbms_output.put_line('test2(my_cursor, 3)');
    test2(my_cursor(3)); 
    dbms_output.put_line('test2(my_cursor2, foo');
    test2(my_cursor2('foo%'));
    dbms_output.put_line('test2(my_cursor,5)');
    test2(my_cursor(5));
    -- ...
  end test1;

  procedure test2(p_cursor t_cursor) is
    tmp number;
    l_rec t_record;
  begin
    loop
      fetch p_cursor into l_rec;
      exit when p_cursor%notfound;
      --some actions 
      tmp := test3(l_rec);
      dbms_output.put_line(l_rec.column1 ||'+'||l_rec.column2||'='||tmp);
      --some actions
    end loop;
    close p_cursor;
  end test2;

  function test3(test_record t_record) return number is
    tmp_sum number;
  begin
    -- ...
    tmp_sum := test_record.column1 + test_record.column2;
    -- ...
    return tmp_sum;
  end test3;

end my_test;
/

Did I say demo? Of course there's a demo on db<>fiddle.

Upvotes: 2

Steven Feuerstein
Steven Feuerstein

Reputation: 1974

Cursor variables are a perfect fit for what you want to do!

Here's a rewrite of a portion of your code illustrating how to use them:

CREATE OR REPLACE PACKAGE my_test
AS
   PROCEDURE test1;

   PROCEDURE test2 (p_cursor IN OUT SYS_REFCURSOR);
END my_test;
/

CREATE OR REPLACE PACKAGE BODY my_test
AS
   FUNCTION my_cursor (my_filter NUMBER)
      RETURN SYS_REFCURSOR
   IS
      l_cursor   SYS_REFCURSOR;
   BEGIN
      OPEN l_cursor FOR
         SELECT column1, column2
           FROM mytable
          WHERE column3 = my_filter;

      RETURN l_cursor;
   END;

   PROCEDURE test1
   IS
   BEGIN
      test2 (my_cursor (3));
      test2 (my_cursor (5));
   END test1;

   PROCEDURE test2 (p_cursor xxx)
   IS
      tmp   NUMBER;
   BEGIN
      LOOP
         FETCH p_cursor INTO tmp;

         EXIT WHEN p_cursor%NOTFOUND;
         --some actions
         NULL;
      END LOOP;

      CLOSE p_cursor;
   END test2;
END my_test;
/

You use OPEN FOR syntax to associate a query (and its result set) with a variable. You can then use the usual operations FETCH, CLOSE, references to %cursor attributes.

AND close the cursor when you are done. :-)

Upvotes: 0

Related Questions