Dynamic SQL

PL/SQL(Procedure, Package)을 사용하다 보면 동적으로 쿼리(Query)를 생성하거나 텍스트(text) 쿼리를 입력 받아서 실행해야하는 경우가 있다. 다음의 방법을 적절히 사용하면 좋은 결과를 얻을 수 있다.

 

EXECUTE IMMEDIATE: Inset, Update, Delete 구문을 실행하거나 Select 구문을 실행 시 INTO를 사용하여 단일 값을 리턴 받을 때 사용

OPEN-FOR: Select 구문을 실행 시 Cursor를 리턴 받을 때 사용

DBMS_SQL:

 

주의: 바인드 변수(:) 사용 시 쿼리 내부에서 변수명은 의미가 없고 변수 순서, 개수가 USING의 변수 순서, 개수와 일치해야 한다. 바인드 변수가 없다면 USING는 생략가능하다.

 

1. EXECUTE IMMEDIATE: INSERT, UPDATE, DELETE  등 구문 실행

 

CREATE OR REPLACE PROCEDURE PC_SET_HOLIDAY

(

          in_hldy_dte in date

        , in_hldy_nm in varchar2

        , in_use_yn in varchar2

) IS

        v_query varchar(1000);

        d_sysdate date;

BEGIN

        BEGIN

        -- 단일 값을 리턴받을때

                EXECUTE IMMEDIATE 'SELECT SYSDATE FROM DUAL' INTO d_sysdate;

        END;

 

        v_query := v_query || 'INSERT INTO HOLIDAY';

        v_query := v_query || ' VALUES(:1,:2,:3,:4)';

 

        BEGIN

                -- INSERT, UPDATE, DELETE 구문 실행

                EXECUTE IMMEDIATE v_query

                USING in_hldy_dte, in_hldy_nm, in_use_yn, d_sysdate;

        END;

END;

 

 

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

 

CREATE OR REPLACE PROCEDURE PC_GET_HOLIDAY

(

          in_fromdate in varchar2

        , in_todate in varchar2

        , out_cursor out SYS_REFCURSOR)

IS

        v_query varchar(1000);

BEGIN

        v_query := v_query || 'SELECT HLDY_DTE, HLDY_NM';

        v_query := v_query || ' FROM HOLIDAY';

        v_query := v_query || ' WHERE HLDY_DTE BETWEEN :in_fromdate';

        v_query := v_query || ' AND :in_todate';

 

        BEGIN

                -- CURSOR를 리턴 받을때

                OPEN out_cursor FOR v_query

                USING in_fromdate, in_todate;

        END;

END;

3. DBMS_SQL:

-- VARCHAR2 Type Function

CREATE OR REPLACE FUNCTION DSQL_CMD

(

        P_SQL       IN  VARCHAR2 -- SQL COMMAND

) RETURN INTEGER

IS

        V_RESULT    INTEGER         := NULL;

        V_CUR       INTEGER         := NULL;  -- DDL CURSOR

        SQL_CMD     VARCHAR2(32767) := NULL;

BEGIN

        V_CUR       := DBMS_SQL.OPEN_CURSOR;

        DBMS_SQL.PARSE(V_CUR, P_SQL, DBMS_SQL.NATIVE);

        V_RESULT    := DBMS_SQL.EXECUTE(V_CUR);

        DBMS_SQL.CLOSE_CURSOR(V_CUR);

        RETURN V_RESULT;

        EXCEPTION

        WHEN OTHERS THEN

                RETURN 99;

END;

-- COLB Type Function

CREATE OR REPLACE FUNCTION DSQL_CMD_CLOB

(

        P_SQL       IN  CLOB -- SQL COMMAND

) RETURN INTEGER

IS

        V_ARRAY     DBMS_SQL.VARCHAR2A;

        V_CURNO     NUMBER;

        V_UB        NUMBER;

        V_RESULT    INTEGER;

BEGIN

        V_CURNO     := DBMS_SQL.OPEN_CURSOR;

        V_UB        := CEIL(DBMS_LOB.GETLENGTH(P_SQL)/1000);

        FOR IDX IN 1 .. V_UB LOOP

                V_ARRAY(IDX) := DBMS_LOB.SUBSTR(P_SQL, 1000, (IDX-1)*1000+1);

        END LOOP;

        DBMS_SQL.PARSE(V_CURNO, V_ARRAY, 1, V_UB, FALSE, DBMS_SQL.NATIVE);

        V_RESULT    := DBMS_SQL.EXECUTE(V_CURNO);

        DBMS_SQL.CLOSE_CURSOR(V_CURNO);

        RETURN V_RESULT;

        EXCEPTION

        WHEN OTHERS THEN

                RETURN 99;

END;