ORACLE 문자열 쿼리 실행(동적쿼리)

현재 사용하는 패키지에서 사용하는 함수 중 리턴값이 쿼리 문자열인 함수가 있다. 이 함수의 리턴값을 받아서 동적으로 쿼리를 실행시키는데, 이러한 동적쿼리를 이전까지 사용한 경험이 없어 이번기회에 정리하고자한다.

패키지에서는 프로시저 내부에서 함수를 호출하고, 커서를 이용하여 쿼리 수행 결과 개수 만큼 로직을 반복하는식으로 구성되어있다.

즉, 쿼리 수행 결과의 값이 집합인 동적 쿼리를 OPEN FOR 구문을 통해 처리한다.

예를 들자면 아래와 같다.

1. OPEN FOR 구문을 통한 다중 행 동적 쿼리 처리

-- F_DYN_QUERY 함수 내부
DECLARE
	str_sql VARCHAR2(4000);
BEGIN
	SELECT QUERY
    INTO str_sql 
    FROM TABLE_QUERY -- 동적 쿼리 관리 테이블
   WHERE ID = '1';
	
	RETURN str_sql;
END;

-- P_PROC_MAIN 프로시저 내부
DECLARE
	TYPE DYN_CURSOR_TYPE IS REF CURSOR; -- 커서타입선언
  	dyn_cur         DYN_CURSOR_TYPE;    -- 커서변수선언
  	str_sql         VARCHAR2(4000);
  	col             VARCHAR2(10);
BEGIN
	str_sql:= F_DYN_QUERY(...);
	
	OPEN dyn_curFOR str_sql;
	LOOP dyn_cur
	FETCH dyn_curINTO col;
		EXIT WHEN dyn_cur%NOTFOUND;
		BEGIN
			DBMS_OUTPUT.PUT_LINE('data: '|| col);
		END;
END;

위 예시에서는 OPEN FOR 구문을 이용하여 커서를 선언하여 동적함수를 실행하며 아래의 순서로 진행한다.

1. 커서 변수를 선언한다.
   👉 REF CURSOR 타입을 선언한 뒤 해당 타입의 커서 변수인 ‘ DYN_CUR’ 를 선언한다.
2. 반환 받을 레코드 변수를 선언한다
   👉 ‘col’ 변수를 통해 레코드 값을 반환받도록 한다.
3. 동적 SQL문을 ‘F_DYN_QUERY’ 함수를 사용해 문자열 형태로 ‘str_sql’ 변수에 반환 받은 후, OPEN FOR문을 이용해 선언한 커서와 연결한다.
4. 루프를 돌리면서 커서를 패치해 값을 받아온다.
5. 루프가 종료되면 해당 커서를 닫는다.

그런데, OPEN FOR 구문처럼 루프를 돌면서 결과를 패치하는 식의 처리는 쿼리 결과로 반환되는 행의 수가 많은 경우 시간도 오래 걸리고, 성능면에서 좋지 않다.

따라서 일일이 한 행씩 읽으며 결과를 받는 대신 BULK COLLECT INTO 구문을 사용하여 배열로 받도록 프로시저 내부를 개선 시키도록 하겠다.

2. BULK COLLECT INTO 구문을 통한 향상된 다중 행 처리

BULK COLLECT INTO 구문은 쿼리 수행 결과를 배열로 읽어들인다.
이 방법은 루프를 사용해서 한번에 한 행씩 패치하는 것보다 방식도 간편하며, 성능면에서도 우수하다.

따라서 P_PROC_MAIN 프로시저 내부를 다음과 같이 변경해 볼 수 있겠다.

-- 향상된 P_PROC_MAIN 프로시저 내부
DECLARE
	str_sql VARCHAR2(4000);
	-- 레코드 선언
  	TYPE REC_COL IS RECORD ( 
		col VARCHAR2(10);
  	);
	-- 레코드를 항목으로 하는 중첩 테이블 선언
	TYPE NT_DATA IS TABLE OF REC_COL;
	-- 중첩테이블 변수 선언
  	vr_data NT_DATA;
BEGIN
	str_sql:= F_DYN_QUERY(...);
	
	EXECUTE IMMEDIATE str_sql BULK COLLECT INTO vr_data;
    FOR cnt IN 1..vr_data.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE('data: '|| vr_data(cnt).data1);
    END LOOP;
END;

향상된 프로시저는 BULK COLLECT INTO 구문을 이용하여 동적함수를 실행결과를 컬렉션 변수에 담아 처리하였고, 아래의 순서로 진행한다.

1. 레코드를 선언한다.
2. 레코드를 항목으로 하는 중첩테이블을 선언한다.
3. 중첩 테이블 변수를 선언한다.
4. 동적 SQL문을 ‘F_DYN_QUERY’ 함수를 사용해 문자열 형태로 ‘str_sql’ 변수에 반환 받은 후, EXECUTE IMMEDIATE 구문으로 쿼리를 실행시키고 그 결과를 컬렉션 변수에 담는다.
5. 루프를 돌리면서 컬렉션 변수의 값을 이용해 로직 처리한다.