다이니믹 SQL 바인드 변수 처리
문제점
: 사용 빈도가 매우 높은 리터럴 쿼리로 인한 DB 대량 부하 발생
성능 개선 포인트
: 다이나믹 + 리터럴 쿼리 => 다이나믹+바인드변수 로 변경
사용법
/* -------------------------------------------- */
-- 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;