PL/SQL (17) 동적 SQL개념 및 예제

1. 동적 SQL

-- 1) PL/SQL 을 실행할 때마다 SQL이 동적으로 변경된다는 의미

-- 2) 프로시저 생성할 때 SELECT문의 INTO절 없이 사용 가능해진다. 

-- 3) 프로시저 생성 시, DDL문이나 DCL문을 포함시킬 수 있다.

-- ( DDL : CREATE, ALTER, DROP, TRUNCATE, RENAME )

-- ( DCL : GRANT, REVOKE )

2. 동적 SQL을 사용하는 방법 2가지

-- 1) EXECUTE IMMEDIATE 절 사용하는 방법 (가장 많이 활용)

- INTO절 없는 SELECT 문장

- DDL 문을 PL/SQL에서 수행

- DML 문을 PL/SQL에서 수행

- ANONYMOUS PL/SQL을 수행

-- 2) DBMS_SQL 패키지를 이용하는 방법

예제 1-1. 부서번호를 입력해서 프로시저를 실행하면 아래와 같이 결과가 출력되게 하라. (동적 SQL 사용 x)

-- 부서번호 : 82

-- 토탈월급 : 

-- 최대월급 : 

-- 최소월급 :

-- 평균월급 : 

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE PR_LS_EMP745

  ( P_DEPT_NO 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;

  

BEGIN

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

  INTO V_DEPT_NO, V_TOT_SAL, V_MAX_SAL, V_MIN_SAL, V_AVG_SAL

  FROM TB_LS_EMP867

  WHERE DEPT_NO = P_DEPT_NO

  GROUP BY DEPT_NO;

  

  DBMS_OUTPUT.PUT_LINE('부서번호: ' || V_DEPT_NO);

  DBMS_OUTPUT.PUT_LINE('토탈월급: ' || V_TOT_SAL);

  DBMS_OUTPUT.PUT_LINE('최대월급: ' || V_MAX_SAL);

  DBMS_OUTPUT.PUT_LINE('최소월급: ' || V_MIN_SAL);

  DBMS_OUTPUT.PUT_LINE('평균월급: ' || V_AVG_SAL);

  

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

END;

/

EXEC PR_LS_EMP745(82);

#############################################################

바인드변수: http://egloos.zum.com/messfilm/v/2166470

#############################################################

예제 1-2. 부서번호를 입력해서 프로시저를 실행하면 아래와 같이 결과가 출력되게 하라. (동적 SQL 사용 O)

☆★☆★☆

CREATE OR REPLACE PROCEDURE PR_LS_EMP810

  ( P_DEPT_NO 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;

    -- 반드시 선언해줘야한다. CLOB 왜 안되는지 알아보기.

    V_STMT VARCHAR2(300);

BEGIN

    -- 동적 SQL Start

    -- :NAME 은 바인드 변수이다. 

    -- 바인드변수는 임의로 설정가능하며 변수명 앞에 콜론을 꼭 넣어줘야한다.

    V_STMT := 

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

      FROM TB_LS_EMP867 

      WHERE DEPT_NO = :BUSEO_NUM

      GROUP BY DEPT_NO';

    -- V_STMT 결과값을 수행하는데 P_DEPT_NO를 BUSEO_NUM으로 사용한다.

    EXECUTE IMMEDIATE V_STMT 

    INTO V_DEPT_NO, V_TOT_SAL, V_MAX_SAL, V_MIN_SAL, V_AVG_SAL

    USING P_DEPT_NO; 

    -- P_DEPT_NO: 바인드변수에 들어갈 변수이다.

    

    DBMS_OUTPUT.PUT_LINE('부서번호: ' || P_DEPT_NO);

    DBMS_OUTPUT.PUT_LINE('토탈월급: ' || V_TOT_SAL);

    DBMS_OUTPUT.PUT_LINE('최대월급: ' || V_MAX_SAL);

    DBMS_OUTPUT.PUT_LINE('최소월급: ' || V_MIN_SAL);

    DBMS_OUTPUT.PUT_LINE('평균월급: ' || V_AVG_SAL);

  

    -- ( 예외처리 ) 에러번호 출력: SQLERRM

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

END;

/

EXEC PR_LS_EMP810(82);