[PL/SQL] 동적쿼리 (EXECUTE IMMEDIATE)

1. EXECUTE IMMEDIATE

오라클 프로시저 내에서 문자열 형태로 된 쿼리문을 실행시키기 위해 사용한다.

 

1. 기본형

EXECUTE IMMEDIATE "문자열 쿼리";

 

CREATE OR REPLACE PROCEDURE PC_INSERT_FRUIT
    (P_FRUIT IN VARCHAR2,
     P_PRICE IN NUMBER)
IS
    V_ID NUMBER := 0;
    V_SQL VARCHAR2(200);
BEGIN
    SELECT NVL(MAX(ID), 0) + 1
      INTO V_ID
      FROM FRUITS;

    V_SQL := 'INSERT INTO FRUITS (ID, NAME, PRICE) '
                      || 'VALUES (''' || V_ID || ''', ''' || P_FRUIT || ''', ''' || P_PRICE || ''')';

    DBMS_OUTPUT.PUT_LINE(V_SQL);

    EXECUTE IMMEDIATE V_SQL;
END;

 

 

2. 반환형

EXECUTE IMMEDIATE "문자열 쿼리" INTO "반환받을 변수";

 

SELECT문을 통해 조회한 특정 값을 변수에 담을 때 사용할 수 있다.

 

CREATE OR REPLACE PROCEDURE PC_SELECT_FRUIT
    (P_ID NUMBER)
IS
    V_SQL VARCHAR2(200);
    V_PRICE NUMBER := 0;
BEGIN
    V_SQL := 'SELECT PRICE '
            || 'FROM FRUITS '
           || 'WHERE ID = ''' || P_ID || ''' ';

    DBMS_OUTPUT.PUT_LINE(V_SQL);

    EXECUTE IMMEDIATE V_SQL
                 INTO V_PRICE;

    DBMS_OUTPUT.PUT_LINE('V_PRICE : ' || V_PRICE);
END;

 

 

3. 바인드형

EXECUTE IMMEDIATE "문자열 쿼리" USING "조건부 변수";

 

문자열 쿼리 안에 사용할 값을 변수에서 받아올 때 사용할 수 있다.

변수의 값을 사용할 위치에는 콜론(:)이 붙은 임의의 이름을 적는다.

 

그리고 USING 절에서 콜론이 붙은 이름 위치에 값을 넣고 싶은 순서에 맞추어 변수를 쉼표로 구분해 작성하면 된다.

 

CREATE OR REPLACE PROCEDURE PC_INSERT_FRUIT
    (P_FRUIT IN VARCHAR2,
     P_PRICE IN NUMBER)
IS
    V_ID NUMBER := 0;
    V_SQL VARCHAR2(200);
BEGIN
    SELECT NVL(MAX(ID), 0) + 1
      INTO V_ID
      FROM FRUITS;

    V_SQL := 'INSERT INTO FRUITS (ID, NAME, PRICE) '
                      || 'VALUES (:ID, :NAME, :PRICE)';

    DBMS_OUTPUT.PUT_LINE(V_SQL);

    EXECUTE IMMEDIATE V_SQL
                USING V_ID, P_FRUIT, P_PRICE;
END;

 

 

4. 모두 사용하기

EXECUTE IMMEDIATE "쿼리 문자열" INTO "반환받을 변수" USING "조건부 변수";

 

CREATE OR REPLACE PROCEDURE PC_SELECT_FRUIT
    (P_START_PRICE NUMBER,
     P_END_PRICE NUMBER)
IS
    V_SQL VARCHAR2(200);

    TYPE FRU_REC IS RECORD (NAME VARCHAR2(200), PRICE NUMBER);
    TYPE FRU_TAB IS TABLE OF FRU_REC INDEX BY BINARY_INTEGER;
    FRUIT_TAB_OBJ FRU_TAB;
	
BEGIN
    V_SQL := 'SELECT NAME, PRICE '
           || ' FROM FRUITS '
          || ' WHERE PRICE BETWEEN :A AND :B';

    DBMS_OUTPUT.PUT_LINE(V_SQL);

    EXECUTE IMMEDIATE V_SQL
    BULK COLLECT INTO FRUIT_TAB_OBJ
                USING P_START_PRICE, P_END_PRICE;

    FOR I IN 1..FRUIT_TAB_OBJ.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(FRUIT_TAB_OBJ(I).NAME || ' ' || FRUIT_TAB_OBJ(I).PRICE);
    END LOOP;
END;