CREATE OR REPLACE PROCEDURE XX_TEST_PROC ( PARA1 VARCHAR2 ,PARA2 NUMBER ) IS CURSOR CUR_A IS SELECT * FROM SCOTT.EMP WHERE JOB = PARA1; CURSOR CUR_B IS SELECT * FROM SCOTT.EMP WHERE DEPTNO = PARA2; REC CUR_A%ROWTYPE; PROCEDURE WORK_PROC IS BEGIN DBMS_OUTPUT.PUT(REC.EMPNO || ','); DBMS_OUTPUT.PUT(REC.ENAME || ','); DBMS_OUTPUT.PUT(REC.JOB || ','); DBMS_OUTPUT.PUT_LINE(REC.DEPTNO); END WORK_PROC; BEGIN DBMS_OUTPUT.PUT_LINE('*** START'); IF PARA1 IS NOT NULL THEN FOR CSR IN CUR_A LOOP REC := CSR; WORK_PROC; END LOOP; ELSIF PARA2 IS NOT NULL THEN FOR CSR IN CUR_B LOOP REC := CSR; WORK_PROC; END LOOP; END IF; DBMS_OUTPUT.PUT_LINE('*** END'); END XX_TEST_PROC; -- 실행 DECLARE BEGIN XX_TEST_PROC('MANAGER',0); XX_TEST_PROC('',20); END ; -- 결과 *** START 7566,JONES,MANAGER,20 7698,BLAKE,MANAGER,30 7782,CLARK,MANAGER,10 *** END *** START 7369,SMITH,CLERK,20 7566,JONES,MANAGER,20 7788,SCOTT,ANALYST,20 7876,ADAMS,CLERK,20 7902,FORD,ANALYST,20 *** END