행위

다이니믹 SQL 바인드 변수 처리

DB CAFE

1 다이니믹 SQL 바인드 변수 처리

1.1 문제점

: 사용 빈도가 매우 높은 리터럴 쿼리로 인한 DB 대량 부하 발생 

1.2 성능 개선 포인트

: 다이나믹 + 리터럴 쿼리 =>  다이나믹+바인드변수 로 변경  

1.3 사용법

/* -------------------------------------------- */
-- 1. SQL 구문 동적으로 작성 (바인드 변수 포함)
-- 2. SQL 구문의 바인드 변수에 실제값 바인드
-- 3. 실행(커서오픈) > 패치 > 종료(커서 닫기)
/* -------------------------------------------- */

DECLARE
   TYPE CURSOR_TYPE  IS REF CURSOR;
   C_CUR        CURSOR_TYPE;
   curid        NUMBER;
   ret          NUMBER;
     
   V_SQL        VARCHAR2(10000);
   V_OWNER      VARCHAR2(100);
   V_TABLE_NAME VARCHAR2(100);
   

  /* 함수의 경우는  입력받을 변수로 선언은 불필요함*/
   P_OWNER      VARCHAR2(100);
   P_TNAME      VARCHAR2(100);
   
BEGIN

 

  /* 1.다이나믹 쿼리 변수 */ 
   V_SQL := 'SELECT /* SJH TEST01 */ OWNER, TABLE_NAME FROM ALL_TABLES WHERE 1 = 1';
   P_OWNER := 'XXX';
   P_TNAME := 'TB_CM_CUST%';
   

  /* 2. 동적인 쿼리 생성 : 바인드 변수 처리 */
   IF P_OWNER IS NOT NULL THEN V_SQL := V_SQL || ' AND OWNER LIKE :P_OWNER ';           END IF; 
   IF P_TNAME IS NOT NULL THEN V_SQL := V_SQL || ' AND TABLE_NAME LIKE :P_TABLE_NAME '; END IF;
   
  /* 3. 오픈 커서 & SQL 파싱  */

   -- 커서 오픈 
   curid := DBMS_SQL.OPEN_CURSOR;
   -- SQL 파싱 
   DBMS_SQL.PARSE(curid, V_SQL, DBMS_SQL.NATIVE);
   
  /* 4. 바인드 변수에 변수값 대응 */
   IF P_TNAME IS NOT NULL THEN  DBMS_SQL.BIND_VARIABLE(curid, 'P_TABLE_NAME', P_TNAME); END IF;
   IF P_OWNER IS NOT NULL THEN  DBMS_SQL.BIND_VARIABLE(curid, 'P_OWNER', P_OWNER); END IF;
   
  /* 5. SQL 실행 */


   ret := DBMS_SQL.EXECUTE(curid);


   -- 커서id(curid) 를 이용하여 커서변수로 처리 
   C_CUR := DBMS_SQL.TO_REFCURSOR(curid);


   -- 커서 패치 
   LOOP
       FETCH C_CUR INTO V_OWNER, V_TABLE_NAME;
       DBMS_OUTPUT.ENABLE(100);
       DBMS_OUTPUT.PUT_LINE(V_OWNER||'.'||V_TABLE_NAME);
       
       EXIT WHEN C_CUR%NOTFOUND;
   END LOOP;


   -- 커서 종료  
   CLOSE C_CUR;
END;