동적쿼리(Dynamic Query)

 

PLEdit에서 작업을 하다 보면 동적 쿼리를 사용해야 할 때가 있다.

 

컴파일 후에 쿼리가 변하지 않으면 정적 쿼리

변하면 동적 쿼리라고 한다.

동적 쿼리는 다른 말로 TEXT쿼리라고도 하며

사용하는 방법은 크게 2가지가 있다.

 

1. EXECUTE IMMEDIATE

첫 번째 방법은 단 일행 값을 리턴 받는 SELECT문

또는 INSERT, UPDATE, DELETE와 같은 DML에서 사용한다.

 

문자열로 쿼리를 작성한 후 EXECUTE IMMEDIATE 명령어로

작성한 문자열 쿼리를 실행한다.

문자열 쿼리에는 바인딩 변수를 사용할 수 있으며

Object의 Parameter도 concat으로 연결해서 사용할 수 있다.

CREATE OR REPLACE FUNCTION FT_TEST1(
                                        IN_PARAM1    IN    VARCHAR2
                                    )
 
RETURN VARCHAR2
IS
 
    V_AAA        VARCHAR2(100);
    V_RESULT    VARCHAR2(100);
 
BEGIN
    EXECUTE IMMEDIATE
    'SELECT COL1
       INTO V_RESULT
       FROM TEST_TABLE
      WHERE COL2 = :V_AAA
        AND COL3 = ' || IN_PARAM1
    ;
 
    RETURN V_RESULT;
END;
 

위의 쿼리에서 볼 수 있듯 바인딩 변수는 문자열에 바로 넣어서 사용하고

파라미터는 concat 으로 연결해서 사용한다.

 

2. OPEN-FOR (CURSOR를 리턴 받을 때)

단일행이 아니라 복수행의 결과를 반환 시에는 REF CURSOR를

응용하는 방법을 사용한다.

CREATE OR REPLACE PROCEDURE PC_TEST(
                                        IN_PARAM1    IN    VARCAHR2
                                        ,OUT_CURSOR    OUT    SYS_REFCURSOR
                                    )
IS
    V_AAA    VARCHAR2(100);
    v_sqlstring    VARCHAR2(100);
 
BEGIN
    v_sqlstring :=     v_sqlstring || ' SELECT COL1, COL2, COL3';
    v_sqlstring :=     v_sqlstring || ' FROM TEST_TABLE';
    v_sqlstring :=     v_sqlstring || ' WHERE COL4 = :V_AAA';
    v_sqlstring :=     v_sqlstring || ' AND COL5 = ' || IN_PARAM1;
 
    OPEN OUT_CURSOR FOR v_sqlstring USING 바인딩변수;
 
END;
 

 

주의!

바인딩 변수 사용 시 쿼리 내부에서 변수명은 의미가 없다

변수 순서, 변수 개수가 일치해야 한다.

바인드 변수가 없으면 USING은 생략이 가능하다.

문자열 쿼리를 작성한 뒤 커서로 실행해 OUT으로 내보내는 방식을 사용한다.