PL/SQL Example 9: More on Cursor Variables and Parameters

set serveroutput on

DECLARE
   CURSOR c1 IS select empno, ename, dname from emp, dept where emp.deptno = dept.deptno;
   v_c1	c1%rowtype;
PROCEDURE proc1 is
BEGIN
   DBMS_OUTPUT.PUT_LINE('Proc1');
   OPEN c1;
   LOOP FETCH c1 INTO v_c1;
   EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('EMPNO: '||v_c1.empno||' ENAME: '||v_c1.ename);
   END LOOP;
   CLOSE c1;
END proc1;

PROCEDURE proc2 (p1 in number) is
   CURSOR c1 IS select empno, ename, dname from emp, dept 
      where emp.deptno = dept.deptno and emp.empno = p1;
   v_c1	c1%rowtype;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Proc2');
   OPEN c1;
   LOOP FETCH c1 INTO v_c1;
   EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('EMPNO: '||v_c1.empno||' ENAME: '||v_c1.ename||' DNAME: '||v_c1.dname);
   END LOOP;
   CLOSE c1;
END proc2;

BEGIN
   DBMS_OUTPUT.PUT_LINE('MAIN');
   dbms_output.enable(1000000);
   proc1();
   proc2(7900);
END;
/

Output:

MAIN
Proc1
EMPNO: 7369 ENAME: SMITH
EMPNO: 7499 ENAME: ALLEN
EMPNO: 7521 ENAME: WARD
EMPNO: 7566 ENAME: JONES
EMPNO: 7654 ENAME: MARTIN
EMPNO: 7698 ENAME: BLAKE
EMPNO: 7782 ENAME: CLARK
EMPNO: 7788 ENAME: SCOTT
EMPNO: 7839 ENAME: KING
EMPNO: 7844 ENAME: TURNER
EMPNO: 7876 ENAME: ADAMS
EMPNO: 7900 ENAME: JAMES
EMPNO: 7902 ENAME: FORD
EMPNO: 7934 ENAME: MILLER
Proc2
EMPNO: 7900 ENAME: JAMES DNAME: SALES

Data:

SQL> select empno, ename, dname from emp, dept where emp.deptno = dept.deptno;

     EMPNO ENAME                          DNAME
---------- ------------------------------ ------------------------------------------
      7369 SMITH                          RESEARCH
      7499 ALLEN                          SALES
      7521 WARD                           SALES
      7566 JONES                          RESEARCH
      7654 MARTIN                         SALES
      7698 BLAKE                          SALES
      7782 CLARK                          ACCOUNTING
      7788 SCOTT                          RESEARCH
      7839 KING                           ACCOUNTING
      7844 TURNER                         SALES
      7876 ADAMS                          RESEARCH
      7900 JAMES                          SALES
      7902 FORD                           RESEARCH
      7934 MILLER                         ACCOUNTING

14 rows selected.