출처 : http://gent.tistory.com/39


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;

 

 

 

 



-- SELECT 1건 샘플

DECLARE
    TABLE_NAME USER_TABLES.TABLE_NAME%TYPE;

BEGIN
    EXECUTE IMMEDIATE 'SELECT TABLE_NAME FROM USER_TABLES WHERE ROWNUM = 1' INTO TABLE_NAME;
    DBMS_OUTPUT.PUT_LINE('TABLE NAME : ' || TABLE_NAME);

END;
/


-- SELECT CURSOR 샘플

DECLARE
    V_QUERY VARCHAR2(4000);
    R USER_TABLES%ROWTYPE;
    C1 SYS_REFCURSOR;

BEGIN
    V_QUERY := V_QUERY || 'SELECT TABLE_NAME';
    V_QUERY := V_QUERY || '     , TABLESPACE_NAME';
    V_QUERY := V_QUERY || '  FROM USER_TABLES';
    V_QUERY := V_QUERY || ' WHERE ROWNUM < :ROW_CNT';

    OPEN C1 FOR V_QUERY USING 10;

    LOOP                                                            
        FETCH C1 INTO R.TABLE_NAME, R.TABLESPACE_NAME;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('TABLE NAME : ' || R.TABLE_NAME || ', TABLESPACE NAME : ' || R.TABLESPACE_NAME);
    END LOOP;

    CLOSE C1;

END;
/




+ Recent posts