EXECUTE IMMEDIATE 예제

NSD(Native Dynamic SQL)는 EXECUTE IMMEDIATE 구문을 통해 실행된다.

 

예제를 위해 아래와 같이 테이블을 생성하자.

1 2 3 4 5 6 7 8

DROP TABLE t1 PURGE; DROP TABLE t2 PURGE;   CREATE TABLE t1 AS SELECT LEVEL AS c1, MOD (LEVEL, 2) AS c2 FROM DUAL CONNECT BY LEVEL <= 10;   CREATE TABLE t2 AS SELECT LEVEL AS c1, MOD (LEVEL, 3) AS c2 FROM DUAL CONNECT BY LEVEL <= 10;

 

단일 행인 경우 INTO 절을 사용할 수 있다. 바인드 변수는 USING 절을 사용하면 된다. 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

CREATE OR REPLACE PROCEDURE p1 (     i_table_name    IN    VARCHAR2   , i_c2            IN    NUMBER ) IS     l_sql_text    VARCHAR2(32767);     l_c1          NUMBER;     l_cn          NUMBER; BEGIN     l_sql_text := q'[SELECT MIN(c1) AS c1, COUNT(*) AS cn FROM ]' || i_table_name || q'[ WHERE c2 = :v1]';       EXECUTE IMMEDIATE l_sql_text INTO l_c1, l_cn USING i_c2;       DBMS_OUTPUT.PUT_LINE (l_c1 || ',' || l_cn); END; /

아래는 p1 프로시저를 수행한 결과다.

1 2 3 4 5 6 7

SET SERVEROUT ON   EXEC p1 ('T1', 0); 2,5   EXEC p1 ('T2', 0); 3,3

 

복수 행인 경우 SYS_REFCURSOR 타입을 사용해야 한다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

CREATE OR REPLACE PROCEDURE p2 (     i_table_name    IN    VARCHAR2   , i_c2            IN    NUMBER ) IS     TYPE trc1 IS RECORD (c1 NUMBER, c2 NUMBER);     l_sql_text    VARCHAR2(32767);     l_cur1        SYS_REFCURSOR;     l_trc1        trc1; BEGIN     l_sql_text := q'[SELECT c1, c2 FROM ]' || i_table_name || q'[ WHERE c2 = :v1]';       OPEN l_cur1 FOR l_sql_text USING i_c2;       LOOP         FETCH l_cur1 INTO l_trc1;         EXIT WHEN l_cur1%NOTFOUND;         DBMS_OUTPUT.PUT_LINE (l_trc1.c1 || ',' || l_trc1.c2);     END LOOP;       CLOSE l_cur1; END; /

아래는 p2 프로시저를 수행한 결과다.

1 2 3 4 5 6 7 8 9 10 11

EXEC p2 ('T1', 0); 2,0 4,0 6,0 8,0 10,0   EXEC p2 ('T2', 0); 3,0 6,0 9,0