Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2005
    Posts
    5

    Question Unanswered: Return a cursor from a function

    Hi,

    I 'm developing a (rather complicated) function in PL/SQL which returns a cursor. I know I can do that by the statement OPEN cursor FOR... However, I want use a FOR loop inside that to open another cursor and pass its records to it. To make myself clear, here is the code, which results in syntax errors:

    CREATE OR REPLACE FUNCTION MYFUNC
    RETURN Types.ref_cursor
    IS
    CURSOR large_customers_cur IS
    ....;

    CURSOR normal_customers_cur IS
    ...;
    large_customer_rec large_customers_cur%ROWTYPE;
    normal_customer_rec normal_customers_cur%ROWTYPE;
    cur Types.ref_cursor;
    BEGIN
    OPEN cur FOR
    FOR large_customer_rec IN large_customers_cur
    LOOP
    SELECT ...
    END LOOP;
    FOR normal_customer_rec IN normal_customers_cur
    LOOP
    SELECT ...
    END LOOP;
    RETURN cur;
    END;



    I hope it's not too much! The two cursors large_customers_cur and normal_customers_cur contain customer ids which I then pass as parameters (each one of them) to the SELECT statement which calls other functions that use these customer ids.

    Thanks in advance for your replies.

    Regards,

    John.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Another approach: instead of a trigger, create a table which will contain data you need to transfer around, such as this:
    Code:
    CREATE OR REPLACE PACKAGE pkg_emps
    IS
       TYPE t_emp_id IS TABLE OF EMP.empno%TYPE
          INDEX BY BINARY_INTEGER;
    
       tab_emps   t_emp_id;
    
       PROCEDURE p_emps;
    
       PROCEDURE print_emps;
    END pkg_emps;
    /
    
    CREATE OR REPLACE PACKAGE BODY pkg_emps
    IS
       PROCEDURE p_emps
       IS
          CURSOR cur_emp_l_20
          IS
             SELECT e.empno
               FROM EMP e
              WHERE e.deptno <= 20;
    
          CURSOR cur_emp_m_20
          IS
             SELECT e.empno
               FROM EMP e
              WHERE e.deptno > 20;
    
          i         NUMBER;
          end_num   NUMBER;
       BEGIN
          FOR cur_r_l20 IN cur_emp_l_20
          LOOP
             tab_emps (cur_emp_l_20%ROWCOUNT) := cur_r_l20.empno;
          END LOOP;
    
          end_num := tab_emps.COUNT;
    
          FOR cur_r_m20 IN cur_emp_m_20
          LOOP
             tab_emps (end_num + cur_emp_m_20%ROWCOUNT) := cur_r_m20.empno;
          END LOOP;
       END p_emps;
    
       PROCEDURE print_emps
       IS
       BEGIN
          p_emps;
    
          FOR i IN tab_emps.FIRST .. tab_emps.LAST
          LOOP
             DBMS_OUTPUT.put_line (tab_emps (i));
          END LOOP;
       END print_emps;
    END pkg_emps;
    /
    
    EXECUTE pkg_emps.print_emps;
    (Just like in your example, procedure 'p_emps' - in two loops - fills the table and another procedure ('print_emps') uses data stored within. Would that be OK for you?)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •