동적 쿼리 - EXECUTE IMMEDIATE / OPEN-FOR

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

 

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

 

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

 

주의 : 바인드 변수(:) 사용 시 쿼리 내부에서 변수명은 의미가 없고 변수 순서, 개수가 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;

출처: http://gent.tistory.com/39 [젠트의 프로그래밍 세상]