PL/SQL (20) 패키지 Package (1)

참조: www.grubee.net/lecture/1075

패키지(Package)는 오라클 데이터베이스에 저장되어 있는 서로 관련있는 PL/SQL 프로시저와 함수들의 집합이다.

패키지는 선언부와 본문 두 부분으로 나누어 진다. 

1. 패키지 선언절 문법

선언부

CREATE [ OR REPLACE ] PACKAGE  패키지명 IS | AS 

[ 변수 선언절 ]

[ 커서 선언절 ]

[ 예외 선언절 ]

[ Procedure 선언절 ]

[ Function 선언절 ]

END 패키지명

- 선언절은 패키지에 포함될 PL/SQL 프로시저, 함수, 커서, 변수, 예약절을 선언한다. 

- 패키지 선언부에서 선언한 모든 요소들은 패키지 전체에 적용된다. 

- 즉, 선언부에서 선언한 변수는 PUBLIC 변수로 사용된다.

2. 패키지 본문 문법 

본문 

CREATE [ OR REPLACE ] PACKAGE BODY 패키지명 IS | AS

[ 변수 선언절 ]

[ 커서 선언절 ]

[ 예외 선언절 ]

[ Procedure 선언절 ]

[ Function 선언절 ]

END 패키지명

- 패키지 본문은 패키지에서 선언된 부분의 실행을 정의한다. 

- 즉 실제 프로시저나 함수의 내용에 해당하는 부분이 온다. 

3. 패키지 예제 

모든 사원의 사원 정보를 가져오는 프로시져 1개 ( 사번, 성명, 아이디, 부서번호, 급여 ) (BASIC..LOOP)

, 모든 사원의 사원 정보를 가져오는 프로시져 1개 ( 사번, 성명, 아이디, 부서번호, 급여 ) (WHILE..LOOP)

, 모든 사원의 급여 정보를 가져오는 프로시저 1개 ( 총급여, 평균급여, 최고급여, 최소급여 )

, 특정 부서의 사원 정보를 가져오는 프로시저 1개 ( 사번, 성명, 아이디, 부서번호, 급여 ) (FOR .. IN... LOOP)

, 특정 부서의 급여 정보를 가져오는 프로시저 1개 ( 총급여, 평균급여, 최고급여, 최소급여 )

총 5개의 프로시저를 만들어 패키지를 구성해 보아라. 

3-1 패키지 선언부 생성 예제 

CREATE OR REPLACE PACKAGE EMP_PKG 

AS 

-- IS 로 해도되지만 본문에서 각각의 프로시저를 정의할 때 

-- 해당 프로시저의 선언문을 IS로 사용하기 때문에 난 AS로 사용한다. 

-- 헷갈리지 않게 하기 위해서 

PROCEDURE ALL_EMP_INFO; -- BASIC .. LOOP

PROCEDURE ALL_EMP_INFO_2; -- WHILE .. LOOP

PROCEDURE ALL_EMP_SAL;

PROCEDURE SPC_DEPT_EMP_INFO (P_DEPT_NO IN NUMBER); -- FOR .. IN .. LOOP

PROCEDURE SPC_DEPT_EMP_SAL (P_DEPT_NO IN NUMBER);

END EMP_PKG;

/ 

-- 패키지만들때는 / 이거 놓치지말자.

3-2 패키지 본문 생성 예제 

CREATE OR REPLACE PACKAGE BODY EMP_PKG

AS

    PROCEDURE ALL_EMP_INFO  

    IS

        CURSOR EMP_CURSOR 

        IS

        SELECT EMP_NO, EMP_NM, EMP_ID, DEPT_NO, SAL

        FROM TB_LS_EMP867;

        

        -- 변수를 선언한다.

        V_EMP_NO NUMBER(8) := 0;

        V_EMP_NM VARCHAR2(20);

        V_EMP_ID VARCHAR2(30);

        V_DEPT_NO NUMBER(4) := 0;

        V_SAL NUMBER(10) := 0;

      

    BEGIN

      

      -- CURSOR, BASIC LOOP 사용

      OPEN EMP_CURSOR ;

        LOOP

          FETCH EMP_CURSOR INTO V_EMP_NO, V_EMP_NM, V_EMP_ID, V_DEPT_NO, V_SAL;

          EXIT WHEN EMP_CURSOR%NOTFOUND;

          DBMS_OUTPUT.PUT_LINE(V_EMP_NM || '님의 레코드는 다음과 같습니다. : ' 

|| V_DEPT_NO || ' | ' || V_EMP_NM || ' | ' || V_EMP_ID || ' | ' || V_DEPT_NO || ' | ' || V_SAL );

        END LOOP;

      CLOSE EMP_CURSOR ;

      

      EXCEPTION WHEN OTHERS THEN SYS.DBMS_OUTPUT.PUT_LINE ('Error Number:::' || SQLERRM );

      

    END;

    

    PROCEDURE ALL_EMP_INFO_2 

    IS

        CURSOR EMP_CURSOR 

        IS

        SELECT EMP_NO, EMP_NM, EMP_ID, DEPT_NO, SAL

        FROM TB_LS_EMP867;

        

        -- 변수를 선언한다.

        V_EMP_NO NUMBER(8) := 0;

        V_EMP_NM VARCHAR2(20);

        V_EMP_ID VARCHAR2(30);

        V_DEPT_NO NUMBER(4) := 0;

        V_SAL NUMBER(10) := 0;

      

    BEGIN

      

      -- CURSOR, WHILE LOOP

      OPEN EMP_CURSOR;

        FETCH EMP_CURSOR INTO V_EMP_NO, V_EMP_NM, V_EMP_ID, V_DEPT_NO, V_SAL;

        WHILE EMP_CURSOR%FOUND LOOP

          DBMS_OUTPUT.PUT_LINE(V_EMP_NM || '님의 레코드는 다음과 같습니다. : '

 || V_DEPT_NO || ' | ' || V_EMP_NM || ' | ' || V_EMP_ID || ' | ' || V_DEPT_NO || ' | ' || V_SAL );

          FETCH EMP_CURSOR INTO V_EMP_NO, V_EMP_NM, V_EMP_ID, V_DEPT_NO, V_SAL;

        END LOOP;

      CLOSE EMP_CURSOR;

      

      EXCEPTION WHEN OTHERS THEN SYS.DBMS_OUTPUT.PUT_LINE ('Error Number:::' || SQLERRM );

      

    END;

  

    PROCEDURE ALL_EMP_SAL

    IS 

        V_TOT_SAL NUMBER(12) := 0;

        V_MAX_SAL NUMBER(12) := 0;

        V_MIN_SAL NUMBER(12) := 0;

        V_AVG_SAL NUMBER(12) := 0;

        

    BEGIN

        SELECT SUM(SAL), MAX(SAL), MIN(SAL), AVG(SAL) 

        INTO V_TOT_SAL, V_MAX_SAL, V_MIN_SAL, V_AVG_SAL

        FROM TB_LS_EMP867;

        DBMS_OUTPUT.PUT_LINE('총급여: '|| V_TOT_SAL ||'/ 최대급여: '|| V_MAX_SAL||'/ 최소급여: '|| V_MIN_SAL ||'/ 평균급여: '|| V_AVG_SAL);

        

        EXCEPTION WHEN OTHERS THEN SYS.DBMS_OUTPUT.PUT_LINE ('Error Number:::' || SQLERRM );

        

    END;

    

    -- CURSOR, FOR ..IN .. LOOP 사용

    PROCEDURE SPC_DEPT_EMP_INFO(P_DEPT_NO IN NUMBER)

    

    IS

        CURSOR EMP_CURSOR 

        IS

        SELECT EMP_NO, EMP_NM, EMP_ID, DEPT_NO, SAL

        FROM TB_LS_EMP867

        WHERE DEPT_NO = P_DEPT_NO;

    BEGIN 

        

        FOR EMP_RECORD IN  EMP_CURSOR LOOP

          DBMS_OUTPUT.PUT_LINE(EMP_RECORD.EMP_NM || '님의 레코드는 다음과 같습니다. : '

    || EMP_RECORD.DEPT_NO || ' | ' || EMP_RECORD.EMP_ID || ' | ' || EMP_RECORD.DEPT_NO || ' | ' || EMP_RECORD.SAL );

        END LOOP;

        

        EXCEPTION WHEN OTHERS THEN SYS.DBMS_OUTPUT.PUT_LINE ('Error Number:::' || SQLERRM );

        

    END;

    

    -- 동적 SQL 사용

    PROCEDURE SPC_DEPT_EMP_SAL (P_DEPT_NO IN NUMBER)

    IS 

        V_DEPT_NO NUMBER(4) := 0;

        V_TOT_SAL NUMBER(12) := 0;

        V_MAX_SAL NUMBER(12) := 0;

        V_MIN_SAL NUMBER(12) := 0;

        V_AVG_SAL NUMBER(12) := 0;

        V_STMT VARCHAR2(200);

        

    BEGIN 

        

        V_STMT := 'SELECT SUM(SAL), MAX(SAL), MIN(SAL), AVG(SAL) FROM TB_LS_EMP867 WHERE DEPT_NO = :BUSEO_NUM GROUP BY 

DEPT_NO' ;

        EXECUTE IMMEDIATE V_STMT

        INTO V_TOT_SAL, V_MAX_SAL, V_MIN_SAL, V_AVG_SAL

        USING P_DEPT_NO;

        DBMS_OUTPUT.PUT_LINE('총급여: '|| V_TOT_SAL ||'/ 최대급여: '|| V_MAX_SAL||'/ 최소급여: '|| V_MIN_SAL ||'/ 평균급여: '|| V_AVG_SAL);

        

        EXCEPTION WHEN OTHERS THEN SYS.DBMS_OUTPUT.PUT_LINE ('Error Number:::' || SQLERRM );

        

    END;

    -- CURSOR, BASIC LOOP 사용

END EMP_PKG;

3-3 패키지 실행

- 패키지의 실행은 패키지명 (ex : EMP_PKG) 다음에 점(.)을 찍고 프로시저나 함수 명을 적어주면 된다. 

SET SERVEROUTPUT ON;

-- 패키지 실행 

SQL > EXEC EMP_PKG.ALL_EMP_INFO;

SQL > EXEC EMP_PKG.ALL_EMP_INFO_2;

SQL > EXEC EMP_PKG.ALL_EMP_SAL;

SQL > EXEC EMP_PKG.SPC_DEPT_EMP_INFO (P_DEPT_NO IN NUMBER);

SQL > EXEC EMP_PKG.SPC_DEPT_EMP_SAL (P_DEPT_NO IN NUMBER);