[오라클DB] PL/SQL 프로시저 반복문 - 0329 2교시

--0329 DB7 2교시 : 프로시저 반복문
 -- FOR ~ LOOP, END LOOP

--부서번호를 받아서 해당 부서사람의 사번, 이름, 급여 정보 출력
SET SERVEROUTPUT ON;

SELECT * FROM EMP;

CREATE OR REPLACE PROCEDURE LOOP_TEST1 (
    P_DEPTNO IN EMP.DEPTNO%TYPE)
IS
    TYPE EMPNO_TABLE IS TABLE OF EMP.EMPNO%TYPE
    INDEX BY BINARY_INTEGER;
    TYPE ENAME_TABLE IS TABLE OF EMP.ENAME%TYPE
    INDEX BY BINARY_INTEGER;
    TYPE SAL_TABLE IS TABLE OF EMP.SAL%TYPE
    INDEX BY BINARY_INTEGER;
    
    EMPNO_ARR EMPNO_TABLE;
    ENAME_ARR ENAME_TABLE;
    SAL_ARR SAL_TABLE;
    
    i BINARY_INTEGER := 0; --반복문에 사용할 변수
BEGIN
    FOR EMP_LIST IN (SELECT EMPNO, ENAME, SAL 
                    FROM EMP
                    WHERE DEPTNO = P_DEPTNO) LOOP 
    i := i + 1;
    EMPNO_ARR(i) := EMP_LIST.EMPNO; --EMPNO_ARR(7369,7499,7521,...)
    ENAME_ARR(i) := EMP_LIST.ENAME;
    SAL_ARR(i) := EMP_LIST.SAL;
    
    END LOOP;
    
    FOR CNT IN 1..i LOOP
        DBMS_OUTPUT.PUT_LINE('사원번호 : ' || EMPNO_ARR(CNT));
        DBMS_OUTPUT.PUT_LINE('사원이름 : ' || ENAME_ARR(CNT));
        DBMS_OUTPUT.PUT_LINE('사원급여 : ' || SAL_ARR(CNT));
        DBMS_OUTPUT.PUT_LINE('----------------------------');
    END LOOP;
END;
/

EXECUT LOOP_TEST1(10);