/* 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
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