오라클 동적 쿼리 생성 방안

------------------- 1 번 안 --------------------

이중 loop를 사용할때 'open curs for 쿼리'를 이용하여 동적쿼리 생성

---

declare

    str varchar2(100);

    col varchar2(200);

    

    type my_curs_type is REF CURSOR; -- must be weakly typed

    curs my_curs_type;

 

    cursor A2 is select view_fild_name_1 from SESSION_TABLE where view_fild_name_1 is not null;

begin

      open A2;

      LOOP                

         fetch A2 into col;

         exit when A2%NOTFOUND;

         --dbms_output.put_line('STR : '|| col);

 

         open curs for 'select '|| col || ' from parktest';

         loop

            fetch curs into str;

            exit when curs%NOTFOUND;

            dbms_output.put_line('결과 : '|| str);

            execute immediate 'insert into sql_cap values(:1)' using str;

            commit;

            end loop;

            Close curs;

        end loop;                        

        CLOSE A2;

 

end;

 

------------------- 2 번 안 --------------------

쿼리문을 변수에 담아놓은 후 실행 및 INTO 사용

---

V_SQL := 'SELECT  CODE_NM FROM TB_CODE WHERE GROUP_CD=''' || v_CODE || ''';

 

1) CURSOR OPEN

  OPEN P_CURSOR FOR V_SQL;

 

2) EXECUTE

  EXECUTE IMMEDIATE V_SQL;

 

3) EXECUTE INTO

  EXECUTE IMMEDIATE V_SQL INTO V_DUTY;

 

------------------- 3 번 안 --------------------

가끔 컬럼의 내용이 지정되지 않고 동적으로 여러개 생성되게 해야 할 경우가 있습니다.

보통 오라클에서 이런 경우면 프로그램 단에서 쿼리를 작성해서 실행하는데...

패키지를 이용한 프로시저 쿼리를 사용할때 프로시저 내에서 동적으로 작성해주는 방법이 있습니다.

 

아래에 예를 적어봅니다...

그럼...

---

Procedure SYS_SELECT(  파라메터_1, 파라메터_2,

                                      out_cursor   OUT PG_STATISTICS.refcur)
IS
   CURSOR CUR IS
   Select SY_ID from Sy_Info; --CUR 커서에 조회내용 담기
   
   STR    VARCHAR2(4000);

BEGIN 
       STR := ' Select ';
              
       FOR REC IN CUR LOOP --CUR 커서에 담긴 내용에 따라 루프돌리며 실행
             BEGIN
      
                STR := STR || ' 동적컬럼생성 as ' || REC.컬럼명 || ','; 

                                                    --CUR 커서의 컬럼은 REC.컬럼명 으로 사용 
        
                exception when dup_val_on_index then     --예외처리              
                     null;
             END;
          --commit; --Insert, Update, Delete 사용시
       END LOOP;
    
       STR := STR || ' from 테이블 ';
       STR := STR || ' where 조건 = :파라메터_1 ' ;   

       STR := STR || ' and 조건 = :파라메터_2 ' ;        
             
       OPEN OUT_CURSOR FOR STR; -- USING 파라메터_1, 파라메터_2 ; 

     --STR에 담긴 쿼리내용 실행, USING 으로 :로 지정된 파라메터와 대응되는 파라메터 추가

               
END SYS_SELECT;

 

------------------- 4 번 안 --------------------

DBMS_SQL 함수를 이용한 방법

---

CREATE OR REPLACE PROCEDURE 프로시져명 (

  pSLIP_DATE      IN        SLIP_M.SLIP_DATE%TYPE,            /* 전표일자        */
          
) IS

  vCURSOR_ID                INTEGER;
  vDUMMY                    INTEGER;
  vAMOUNT                   NUMBER;

  v_DynamicSQL            VARCHAR2(1000);

BEGIN

    v_DynamicSQL = ' SELECT SUM(CR_AMT) FROM SLIP_M WHERE SLIP_DATE = :slip_date ' ;
              

    --[ Cursor내의 Block ]
    BEGIN
   
      --[ 처리를 위해 Cursor를 Open ]
      vCURSOR_ID := DBMS_SQL.OPEN_CURSOR;
   
     
      --[ Query를 Parse한다 ] -> 이부분이 Dynamic SQL 들어갈 부분
      DBMS_SQL.PARSE(vCURSOR_ID, v_DynamicSQL, DBMS_SQL.NATIVE);
   
     
      --[ 입력변수를 묶는다 ] - 입력변수 없으면 생략
      DBMS_SQL.BIND_VARIABLE(vCURSOR_ID, ':SLIP_DATE', pSLIP_DATE);
   
     
      --[ 출력변수를 정의한다 ]
      --[ 출력변수 지정시 char이면 자리수도 지정해주어야 합니다. ]

      DBMS_SQL.DEFINE_COLUMN(vCURSOR_ID, 1, vAMOUNT );
       
      --[ 문장을 실행한다 ]
      vDUMMY := DBMS_SQL.EXECUTE(vCURSOR_ID);
   
     
      --[ 인출 Loop ]
      LOOP
     
            --[ 행을 버퍼로 인출하고 Exit조건을 검사한다 ]
            IF DBMS_SQL.FETCH_ROWS(vCURSOR_ID) = 0 THEN
               EXIT;
            END IF;
           
            --[ 버퍼에서 PL/SQL 변수로 행들을 검색한다 ]
            DBMS_SQL.COLUMN_VALUE(vCURSOR_ID, 1, vAMOUNT );
   
     
      END LOOP;

      --[ Cursor를 닫는다 ]
      DBMS_SQL.CLOSE_CURSOR(vCURSOR_ID);

     
    --[ 예외 처리부 ]
    EXCEPTION

      WHEN OTHERS THEN
           DBMS_SQL.CLOSE_CURSOR(vCURSOR_ID);

    END;  
 
 
  --[ Commit ]
  COMMIT WORK;
                
                  
END 프로시져명;