Friday, July 29, 2011

ref curso in ref cursor

/* Formatted on 2011/07/30 03:01 (Formatter Plus v4.8.8) */
SET DBMS_OUTPUT.PUT_LINE ON;

DECLARE
   TYPE rctyp IS REF CURSOR; --defining ref cursor

   myrc1    rctyp;                         --instantiating ref cursor
   myrc2    rctyp;
   empno    NUMBER;
   deptno   NUMBER;
   ename    VARCHAR2 (10);
BEGIN
   OPEN myrc1 FOR                                                              --opening ref cursor
      SELECT deptno, CURSOR (SELECT e.empno, e.ename --using keyword CURSOR
                               FROM emp e             
                              WHERE e.deptno = d.deptno)
        FROM dept d;

   FETCH myrc1                                  --fetching refcursor into refcursor
    INTO deptno, myrc2;

   WHILE myrc1%FOUND
   LOOP
      DBMS_OUTPUT.put_line ('deptno = ' || deptno);

      FETCH myrc2   --fetching re cursr to variables
       INTO empno, ename;

      WHILE myrc2%FOUND
      LOOP
         DBMS_OUTPUT.put_line ('empno = ' || empno || ' ename = ' || ename);

         FETCH myrc2
          INTO empno, ename;
      END LOOP;

      FETCH myrc1
       INTO deptno, myrc2;
   END LOOP;

   CLOSE myrc1;
END;
/

source



  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:3027089372477

No comments:

Post a Comment