[Oracle] 오라클 동적 쿼리 사용법 & 예제 (PL/SQL)

동적쿼리란?

동적쿼리란 실행시에 쿼리문장이 만들어져 실행되는 쿼리문을 말합니다. 쿼리문이 변하냐 변하지 않느냐에따라 변하지 않으면 정적쿼리 변한다면 동적쿼리로 생각하시면 됩니다. 대부분 동적쿼리를 사용할때에는 텍스트문장으로 쿼리문을 가지고 있다가 실행할때마다 텍스트 쿼리문장을 바꿔서 실행하는 방식을 사용합니다.

 

동적쿼리 사용법

BEGIN
EXECUTE IMMEDIATE '쿼리문 (선언한 변수활용)'
END;

동적쿼리를 사용하는 방법은 DECLARE상에서 동적으로 받을 변수를 먼저 생성한 후 생성한 변수를 활용해서 쿼리문을 작성한 후 EXECUTE IMMEDIATE로 실행시켜주시면 됩니다.

 

동적쿼리 예제

CREATE OR REPLACE FUNCTION FUNC1(
    W_PRODUCT_ID IN NUMBER
)
RETURN VARCHAR2
IS
    PRODUCT_NAME VARCHAR2(100);
BEGIN
    EXECUTE IMMEDIATE
    'SELECT PRODUCT_NAME
    FROM
    PRODUCTS
    WHERE
    PRODUCT_ID = '||  W_PRODUCT_ID
    INTO PRODUCT_NAME;
    RETURN PRODUCT_NAME;
END; 

함수로 최대한 간단하게 동적쿼리를 만들어봤습니다. 동적쿼리를 만드는 방법은 이렇게 파라미터로 받은 변수들을 문자열 쿼리문안에 넣어주고 EXECUTE IMMEDIATE로 실행시켜주면 됩니다. 

 

SELECT FUNC1(1) FROM DUAL

동적쿼리로 만들어준 함수를 실행시키면 파라미터로 보낸 PRODUCT_ID의 PRODUCT_NAME이 리턴됩니다.

 

 동적쿼리가 필요한 경우 

1. 컴파일 시에 SQL문장을 확정할 수 없는 경우

동적쿼리가 가장 많이 실행되는 시점은 실행시점에 따라 WHERE절에 조건이 달라질 때입니다. 컴파일 시에 SQL문장이 확정될 수 없는경우 동적쿼리를 사용합니다.

 

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

PL/SQL의 익명블록이나, 함수, 프로시저, 패키지본문에서는 DDL문을 실행할 수 없습니다. 하지만 동적 SQL을 사용하면 DDL문을 실행시킬 수 있습니다.

 

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

DDL문과 마찬가지로 PL/SQL에서 ALTER SYSTEM / SESSION명령어를 직접 사용할 수 없으나 동적SQL을 사용하면 사용이 가능합니다.