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;