[ORACLE / PROCEDURE] 동적쿼리, 문자열 쿼리 실행, EXECUTE IMMEDIATE

Oracle 프로시저 내에서 문자열 형태로된 쿼리문을 실행 시키기 위해 사용하는

EXECUTE IMMEDIATE 키워드에 대해 살펴봅니다.


1. 기본형 

EXECUTE IMMEDIATE "문자열쿼리";

[예제코드]

CREATE OR REPLACE PROCEDURE PC_INSERT_FRUIT
(
    P_FRUIT IN VARCHAR2
) 
IS

V_SQL VARCHAR2(200);

BEGIN

    -- # 실행 대상 쿼리
    V_SQL := 'INSERT INTO TB_SALES(FRUIT)'
            || ' VALUES(''' || P_FRUIT || ''')';

            
    -- # 실행 쿼리 출력         
    dbms_output.put_line(V_SQL);

                
    EXECUTE IMMEDIATE V_SQL;

END;

위 코드는 P_FRUIT  Parameter를 입력받아 TB_SALES 테이블에 삽입하는 프로시저 입니다.

V_SQL 변수에 실행시킬 쿼리문(INSERT)과 입력받은 P_FRUIT으로 작성합니다.

DBMS_OUTPUT 구문으로 실행 시키기 전 쿼리문을 출력해 확인해봅니다.

[프로시저 실행]

EXEC PC_INSERT_FRUIT('MANGO');

[결과]

[마지막 행에 'MANGO' 추가]

[DBMS 출력]

2. 반환형 (EXECUTE IMMEDIATE ~ INTO ~)

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

먼저 아래와 같은 테이블을 구성해줍니다.

우리는 여기서 과일명(FRUIT)을 입력받아 가격(PRICE)을 얻어오는 프로시저를 만들겁니다.

[예제코드]

CREATE OR REPLACE PROCEDURE PC_SELECT_FRUIT
(
    P_FRUIT IN VARCHAR2
) 
IS

V_SQL VARCHAR2(200);
V_PRICE VARCHAR2(40);

BEGIN
    -- # 실행 대상 쿼리
    V_SQL := 'SELECT PRICE' 
            || ' FROM TB_SALES'
            || ' WHERE FRUIT = ''' || P_FRUIT || ''' ';

            
    -- # 실행 쿼리 출력         
    dbms_output.put_line(V_SQL);

    EXECUTE IMMEDIATE V_SQL
        INTO V_PRICE;

    -- # 결과 출력        
    dbms_output.put_line('V_PRICE  : ' || V_PRICE);

END;

프로시저 변수인 V_PRICE에 문자열쿼리 실행의 결과값을 얻습니다.

마지막에 DBMS 출력을 통해 결과값을 확인합니다.

[프로시저 실행]

EXEC PC_SELECT_FRUIT('MANGO');

[결과]

[DBMS 출력]

 'MANGO' 행에 PRICE 값인 '500'을 얻어 출력





3. 바인드형 (EXECUTE IMMEDIATE ~ INTO ~ USING ~)

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

문자열 쿼리 안에서 외부 변수를 조건부로 사용할 때 사용합니다.

먼저 조건으로 사용할 COUNT 컬럼을 추가한 테이블을 준비합니다.

가격과 갯수 조건을 부여해 부합한 과일명을 얻어올 것입니다.

[예제코드]

CREATE OR REPLACE PROCEDURE PC_SELECT_FRUIT_BY_COUNT
(
    P_COUNT IN NUMBER,
    P_PRICE IN NUMBER
) 
IS

V_SQL VARCHAR2(200);
V_FRUIT VARCHAR2(40);
V_PRICE VARCHAR2(40);

BEGIN
    -- # 실행 대상 쿼리
    V_SQL := 'SELECT FRUIT, PRICE' 
            || ' FROM TB_SALES'
            || ' WHERE COUNT > :A'
            || ' AND PRICE = :B';

            
    -- # 실행 쿼리 출력         
    dbms_output.put_line(V_SQL);

    EXECUTE IMMEDIATE V_SQL
        INTO V_FRUIT, V_PRICE
        USING P_COUNT, P_PRICE;

    -- # 결과 출력        
    dbms_output.put_line('V_FRUIT  : ' || V_FRUIT);
    dbms_output.put_line('V_PRICE  : ' || V_PRICE);

END;

USING 문 뒤에 문자열쿼리(V_SQL)의 조건문의 ":A", ":B"에 들어갈 변수들을 순서대로 넣어줍니다.

[프로시저 실행]

-- (개수 / 가격)
EXEC PC_SELECT_FRUIT_BY_COUNT(1, 100);

[결과]

[DBMS 출력]

의도한 대로 가격(100)과 개수(1 이상)에 부합하는 'APPLE' 관련 정보가 출력되었습니다.

주의할 점은 반환값 변수(V_FRUIT, V_PRICE) 들은 단일값만 담을 수 있기 때문에 SELECT 조건에 부합하는 값이 2개 이상일 경우 오류가 발생합니다.