[PL/SQL] 동적 SQL

동적 SQL이 필요한 경우

1. 컴파일 시에 SQL 문장이 확정되지 않은 경우

2. PL/SQL 블록 상에서 DDL문을 실행해야 할 경우

3. PL/SQL 블록 상에서 ALTER SYSTEM/SESSION 명령어를 실행해야 할 경우

동적 SQL을 사용하는 방법

1. NDS(원시 동적 SQL, NATIVE DYNAMIC SQL)

2. DBMS_SQL 시스템 패키지

EXECUTE IMMEDIATE문

EXECUTE IMMEDIATE SQL문_문자열
[ INTO OUT변수1, OUT변수2, ... ]
[ USING [ IN / OUT / INOUT ] 매개변수1, ... ]
...;

- INTO   : SELECT INTO 문에서 INTO 역할

- USING : 바인드 변수. 디폴트 값은 IN으로 생략가능

예제

BEGIN
    EXECUTE IMMEDIATE 'SELECT   EMPLOYEE_ID, EMP_NAME, JOB_ID
                       FROM     EMPLOYEES
                       WHERE    JOB_ID = ''AD_ASST'' ';
END;

DECLARE
    VN_EMP_ID   EMPLOYEES.EMPLOYEE_ID%TYPE;
    VS_EMP_NAME EMPLOYEES.EMP_NAME%TYPE;
    VS_JOB_ID   EMPLOYEES.JOB_ID%TYPE;
BEGIN
    EXECUTE IMMEDIATE 'SELECT   EMPLOYEE_ID, EMP_NAME, JOB_ID
                       FROM     EMPLOYEES
                       WHERE    JOB_ID = ''AD_ASST'' '
                       INTO     VN_EMP_ID, VS_EMP_NAME, VS_JOB_ID;
    
    DBMS_OUTPUT.PUT_LINE( 'EMP_ID : ' || VN_EMP_ID );
    DBMS_OUTPUT.PUT_LINE( 'EMP_NAME : ' || VS_EMP_NAME );
    DBMS_OUTPUT.PUT_LINE( 'JOB_ID : ' || VS_JOB_ID );
END;

위 쿼리는 쿼리문을 변수에 할당해서 실행시킬 수도 있다.

DECLARE
    VN_EMP_ID   EMPLOYEES.EMPLOYEE_ID%TYPE;
    VS_EMP_NAME EMPLOYEES.EMP_NAME%TYPE;
    VS_JOB_ID   EMPLOYEES.JOB_ID%TYPE;
    
    VS_SQL      VARCHAR2(1000);
BEGIN
    VS_SQL  := 'SELECT  EMPLOYEE_ID, EMP_NAME, JOB_ID
                FROM    EMPLOYEES
                WHERE   JOB_ID = ''AD_ASST'' ';
                
    EXECUTE IMMEDIATE VS_SQL INTO VN_EMP_ID, VS_EMP_NAME, VS_JOB_ID;
    
    DBMS_OUTPUT.PUT_LINE( 'EMP_ID : ' || VN_EMP_ID );
    DBMS_OUTPUT.PUT_LINE( 'EMP_NAME : ' || VS_EMP_NAME );
    DBMS_OUTPUT.PUT_LINE( 'JOB_ID : ' || VS_JOB_ID );
END;

DECLARE
    VN_EMP_ID   EMPLOYEES.EMPLOYEE_ID%TYPE;
    VS_EMP_NAME EMPLOYEES.EMP_NAME%TYPE;
    VS_JOB_ID   EMPLOYEES.JOB_ID%TYPE;
    
    VS_SQL      VARCHAR2(1000);
BEGIN
    VS_SQL := 'SELECT   EMPLOYEE_ID, EMP_NAME, JOB_ID
               FROM     EMPLOYEES
               WHERE    JOB_ID = ''SA_REP''
               AND      SALARY < 7000
               AND      MANAGER_ID = 148 ';
    
    EXECUTE IMMEDIATE VS_SQL INTO VN_EMP_ID, VS_EMP_NAME, VS_JOB_ID;
    
    DBMS_OUTPUT.PUT_LINE( 'EMP_ID : ' || VN_EMP_ID );
    DBMS_OUTPUT.PUT_LINE( 'EMP_NAME : ' || VS_EMP_NAME );
    DBMS_OUTPUT.PUT_LINE( 'JOB_ID : ' || VS_JOB_ID );
END;

위 쿼리처럼 조건에 상수를 넣게되면 성능면에서 매우 좋지않다.

왜냐면 실행될 때 상수 값이 달라질 때마다 오라클은 이 문장을 다른 문장으로 인식하기 때문이다.

하지만, 조건의 비교값에 변수를 사용하면 값은 달라지더라도 오라클이 동일한 문장으로 인식하기 때문에 처리 성능이 좋아진다.

이렇게 SQL 구문상에서 조건절에 들어가는 값으로 변수를 사용하는 것을 바인드 변수를 사용한다고 말한다.

DECLARE
    VN_EMP_ID   EMPLOYEES.EMPLOYEE_ID%TYPE;
    VS_EMP_NAME EMPLOYEES.EMP_NAME%TYPE;
    VS_JOB_ID   EMPLOYEES.JOB_ID%TYPE;
    
    VS_SQL      VARCHAR2(1000);
    
    VS_JOB      EMPLOYEES.JOB_ID%TYPE       := 'SA_REP';
    VN_SAL      EMPLOYEES.SALARY%TYPE       := 7000;
    VN_MANAGER  EMPLOYEES.MANAGER_ID%TYPE   := 148;
BEGIN
    VS_SQL  :=  'SELECT EMPLOYEE_ID, EMP_NAME, JOB_ID
                 FROM   EMPLOYEES
                 WHERE  JOB_ID = :A
                 AND    SALARY < :B
                 AND    MANAGER_ID = :C ';
    
    EXECUTE IMMEDIATE VS_SQL INTO VN_EMP_ID, VS_EMP_NAME, VS_JOB_ID
                             USING VS_JOB, VN_SAL, VN_MANAGER;
    
    DBMS_OUTPUT.PUT_LINE( 'EMP_ID : ' || VN_EMP_ID );
    DBMS_OUTPUT.PUT_LINE( 'EMP_NAME : ' || VS_EMP_NAME );
    DBMS_OUTPUT.PUT_LINE( 'JOB_ID : ' || VS_JOB_ID );
END;

바인드 변수를 사용하려면 동적 구문에 :변수명 을 추가한 후 변수를 선언한 순서대로 USING 다음에 해당 변수들을 나열한다.

변수명에는 제한이 없고, 변수의 순서와 타입, 개수를 맞춰주면 된다.

CREATE TABLE CH13_PHYSICIST
(   IDS         NUMBER,
    NAMES       VARCHAR2(50),
    BIRTH_DT    DATE            );

DECLARE
    VN_IDS      CH13_PHYSICIST.IDS%TYPE      := 10;
    VS_NAME     CH13_PHYSICIST.NAMES%TYPE    := 'Albert Einstein';
    VD_DT       CH13_PHYSICIST.BIRTH_DT%TYPE := SYSDATE;
    
    VS_SQL      VARCHAR2(1000);
BEGIN
    VS_SQL  := 'INSERT INTO CH13_PHYSICIST VALUES (:A, :A, :A)';
    
    EXECUTE IMMEDIATE VS_SQL USING VN_IDS, VS_NAME, VD_DT;
    
    COMMIT;
END;
 
SELECT * FROM CH13_PHYSICIST;

DECLARE
    VN_IDS      CH13_PHYSICIST.IDS%TYPE      := 10;
    VS_NAME     CH13_PHYSICIST.NAMES%TYPE    := 'Max Planck';
    VD_DT       CH13_PHYSICIST.BIRTH_DT%TYPE := TO_DATE('1858-04-23', 'YYYY-MM-DD');
    
    VS_SQL      VARCHAR2(1000);
    VN_CNT      NUMBER  :=  0;
BEGIN
    VS_SQL  := 'UPDATE  CH13_PHYSICIST
                SET     NAMES = :A,
                        BIRTH_DT = :A
                WHERE   IDS = :A';
    
    EXECUTE IMMEDIATE VS_SQL USING VS_NAME, VD_DT, VN_IDS;
    
    SELECT  NAMES
    INTO    VS_NAME
    FROM    CH13_PHYSICIST;
    
    DBMS_OUTPUT.PUT_LINE('UPDATE 후 이름 : ' || VS_NAME);
    
    VS_SQL := 'DELETE   CH13_PHYSICIST
               WHERE    IDS = :A ';
    
    EXECUTE IMMEDIATE VS_SQL USING VN_IDS;
    
    SELECT  COUNT(*)
    INTO    VN_CNT
    FROM    CH13_PHYSICIST;
    
    DBMS_OUTPUT.PUT_LINE('VN_CNT : ' || VN_CNT);
    COMMIT;
END;

동적 쿼리로 익명 블록이나 PL/SQL 함수, 프로시저를 호출할 때는 변수이름도 맞춰줘야한다.

CREATE OR REPLACE PROCEDURE CH13_BIND_PROC1 (PV_ARG1 IN VARCHAR2,
                                             PN_ARG2 IN NUMBER,
                                             PD_ARG3 IN DATE)
IS
 
BEGIN
    DBMS_OUTPUT.PUT_LINE('PV_ARG1 : ' || PV_ARG1);
    DBMS_OUTPUT.PUT_LINE('PN_ARG2 : ' || PN_ARG2);    
    DBMS_OUTPUT.PUT_LINE('PD_ARG3 : ' || PD_ARG3);
END;

DECLARE
    VS_DATA1    VARCHAR2(30)    := 'Albert Einstein';
    VN_DATA2    NUMBER          := 100;
    VD_DATA3    DATE            := SYSDATE;
    
    VS_SQL      VARCHAR2(1000);
BEGIN
    CH13_BIND_PROC1 (VS_DATA1, VN_DATA2, VD_DATA3);
    
    DBMS_OUTPUT.PUT_LINE('-----------------------------------');
    VS_SQL := 'BEGIN CH13_BIND_PROC1(:A, :B, :C); END;';
    
    EXECUTE IMMEDIATE VS_SQL USING VS_DATA1, VN_DATA2, VD_DATA3;
END;

CREATE OR REPLACE PROCEDURE CH13_BIND_PROC2 ( PV_ARG1 IN VARCHAR2,
                                              PV_ARG2 OUT VARCHAR2,
                                              PV_ARG3 IN OUT VARCHAR2)
IS
 
BEGIN
    DBMS_OUTPUT.PUT_LINE('PV_ARG1 : ' || PV_ARG1);
    
    PV_ARG2 := '두번째 OUT 변수';
    PV_ARG3 := '세번째 INOUT 변수';
END;

DECLARE
    VS_DATA1    VARCHAR2(30) := 'Albert Einstein';
    VS_DATA2    VARCHAR2(30);
    VS_DATA3    VARCHAR2(30);
    
    VS_SQL      VARCHAR2(1000);
BEGIN
    VS_SQL := 'BEGIN CH13_BIND_PROC2 (:A, :B, :C); END;';
    
    EXECUTE IMMEDIATE VS_SQL USING VS_DATA1, OUT VS_DATA2, IN OUT VS_DATA3;
    
    DBMS_OUTPUT.PUT_LINE('VS_DATA2 = ' || VS_DATA2);
    DBMS_OUTPUT.PUT_LINE('VS_DATA3 = ' || VS_DATA3);
END;

CREATE OR REPLACE PROCEDURE CH13_DDL_PROC (PD_ARG1 IN DATE)
IS
 
BEGIN
    CREATE TABLE CH13_DDL_TAB (COL1 VARCHAR2(30));
    DBMS_OUTPUT.PUT_LINE('PD_ARG1 : ' || PD_ARG1);
END;

위 쿼리는 DDL문을 직접 사용해 오류가 발생했다. 동적쿼리를 이용하면 어떻게 될까?

CREATE OR REPLACE PROCEDURE CH13_DDL_PROC (PD_ARG1 IN DATE)
IS
    VS_SQL VARCHAR2(1000);
BEGIN
    VS_SQL :=  'CREATE TABLE CH13_DDL_TAB (COL1 VARCHAR2(30))';
    EXECUTE IMMEDIATE VS_SQL;
    
    DBMS_OUTPUT.PUT_LINE('PD_ARG1 : ' || PD_ARG1);
END;