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;
오라클 (Oracle) 11g 를 위한 토드(Toad) 무료 버전 다운...
숫자를 문자로 변환 시 소수점 처리 (TO_CHAR, FM)