Oracle에서 DBMS_SQL로 동적 쿼리와 REF CURSOR를 OUT 파라미터로 반환하기

 

오라클에서 동적 쿼리를 처리할 때, 특히 다양한 조건과 바인드 변수를 유연하게 관리해야 하는 경우 DBMS_SQL 패키지는 강력한 도구입니다. 이 글에서는 DBMS_SQL을 사용하여 동적 쿼리를 생성하고, 바인드 변수를 처리한 뒤, 결과를 REF CURSOR로 변환하여 OUT 파라미터로 반환하는 방법을 자세히 설명합니다. 이 접근법은 복잡한 쿼리나 런타임에 컬럼 구조가 변하는 경우에 특히 유용합니다.

---

1. 왜 DBMS_SQL을 사용하는가?

EXECUTE IMMEDIATE와 OPEN ... FOR는 간단한 동적 쿼리에 적합하지만, 바인드 변수의 개수가 많거나 런타임에 컬럼 구조를 동적으로 처리해야 할 때는 한계가 있습니다. DBMS_SQL은 다음과 같은 장점을 제공합니다:

• 유연한 바인드 변수 처리: 바인드 변수의 이름과 값을 런타임에 명시적으로 바인딩.
• 동적 컬럼 처리: 쿼리 결과의 컬럼 구조를 런타임에 정의 가능.
• REF CURSOR 변환: DBMS_SQL.TO_REFCURSOR를 통해 DBMS_SQL 커서를 REF CURSOR로 변환하여 호출자에게 반환.
• SQL 인젝션 방지: 바인드 변수를 사용해 안전한 쿼리 실행.

---

 

 

2. 예제: 동적 쿼리와 REF CURSOR 반환

아래는 다양한 파라미터(p_deptno, p_job, p_sal)를 받아 동적 쿼리를 생성하고, DBMS_SQL로 처리한 뒤 REF CURSOR를 OUT 파라미터로 반환하는 프로시저입니다.

프로시저 코드

CREATE OR REPLACE PROCEDURE dynamic_query_dbms_sql(
  p_deptno IN NUMBER,
  p_job IN VARCHAR2,
  p_sal IN NUMBER,
  p_cursor OUT SYS_REFCURSOR
) AS
  v_sql VARCHAR2(1000); -- 동적 쿼리
  v_cursor NUMBER; -- DBMS_SQL 커서
  v_bind_count NUMBER := 0; -- 바인드 변수 카운트
  v_rows NUMBER; -- 실행된 행 수
BEGIN
  -- 기본 쿼리
  v_sql := 'SELECT empno, ename FROM emp WHERE 1=1';
  
  -- 조건 및 바인드 변수 추가
  IF p_deptno IS NOT NULL THEN
    v_sql := v_sql || ' AND deptno = :b' || (v_bind_count + 1);
    v_bind_count := v_bind_count + 1;
  END IF;
  
  IF p_job IS NOT NULL THEN
    v_sql := v_sql || ' AND job = :b' || (v_bind_count + 1);
    v_bind_count := v_bind_count + 1;
  END IF;
  
  IF p_sal IS NOT NULL THEN
    v_sql := v_sql || ' AND sal > :b' || (v_bind_count + 1);
    v_bind_count := v_bind_count + 1;
  END IF;
  
  -- DBMS_SQL 처리
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
  
  -- 바인드 변수 바인딩
  IF p_deptno IS NOT NULL THEN
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':b1', p_deptno);
  END IF;
  IF p_job IS NOT NULL THEN
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':b2', p_job);
  END IF;
  IF p_sal IS NOT NULL THEN
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':b3', p_sal);
  END IF;
  
  -- 쿼리 실행
  v_rows := DBMS_SQL.EXECUTE(v_cursor);
  
  -- DBMS_SQL 커서를 REF CURSOR로 변환
  p_cursor := DBMS_SQL.TO_REFCURSOR(v_cursor);
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(v_cursor) THEN
      DBMS_SQL.CLOSE_CURSOR(v_cursor);
    END IF;
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    RAISE;
END;
/

 

 

호출자 코드

DECLARE
  v_cursor SYS_REFCURSOR; -- 반환받을 커서
  v_empno NUMBER;
  v_ename VARCHAR2(50);
BEGIN
  -- 프로시저 호출
  dynamic_query_dbms_sql(
    p_deptno => 10,
    p_job => 'CLERK',
    p_sal => 1000,
    p_cursor => v_cursor
  );
  
  -- 커서에서 결과 처리
  LOOP
    FETCH v_cursor INTO v_empno, v_ename;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('EMPNO: ' || v_empno || ', ENAME: ' || v_ename);
  END LOOP;
  
  -- 커서 닫기
  CLOSE v_cursor;
EXCEPTION
  WHEN OTHERS THEN
    IF v_cursor%ISOPEN THEN
      CLOSE v_cursor;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Error in caller: ' || SQLERRM);
END;
/

---

3. 코드 설명

프로시저 동작

1. 동적 쿼리 생성:
   • 기본 쿼리: SELECT empno, ename FROM emp WHERE 1=1
   • 입력 파라미터(p_deptno, p_job, p_sal)가 NULL이 아닌 경우, 조건을 추가(AND deptno = :b1, AND job = :b2, 등).
   • 바인드 변수 이름(:b1, :b2, ...)은 v_bind_count로 동적으로 생성.
2. DBMS_SQL 처리:
   • DBMS_SQL.OPEN_CURSOR: 새로운 커서를 생성.
   • DBMS_SQL.PARSE: 쿼리를 파싱.
   • DBMS_SQL.BIND_VARIABLE: 각 바인드 변수에 값을 바인딩.
   • DBMS_SQL.EXECUTE: 쿼리를 실행.
3. REF CURSOR 변환:
   • DBMS_SQL.TO_REFCURSOR: 실행된 DBMS_SQL 커서를 REF CURSOR로 변환하여 p_cursor에 할당.
   • 변환 후 DBMS_SQL 커서는 자동으로 닫힘.
4. 예외 처리:
   • 오류 발생 시 DBMS_SQL 커서를 명시적으로 닫고, 오류 메시지를 출력한 뒤 상위 호출자에게 예외를 전달.

호출자 동작

1. 프로시저 호출:
   • dynamic_query_dbms_sql을 호출하여 REF CURSOR를 v_cursor에 받음.
2. 결과 처리:
   • FETCH를 사용하여 커서의 결과를 행 단위로 읽음.
   • 결과를 DBMS_OUTPUT.PUT_LINE으로 출력.
3. 커서 닫기:
   • 작업 완료 후 CLOSE v_cursor로 커서를 명시적으로 닫음.
   • 예외 발생 시 열린 커서를 안전하게 닫음.

---

4. 주요 장점

• 유연성: 바인드 변수의 개수와 타입을 런타임에 동적으로 처리.
• 안전성: 바인드 변수를 사용하여 SQL 인젝션 방지.
• 호환성: REF CURSOR는 PL/SQL뿐만 아니라 Java, .NET 등 외부 애플리케이션에서도 사용 가능.
• 확장성: 복잡한 쿼리나 동적 컬럼 구조에도 적용 가능.

---

5. 주의사항

1. 커서 관리:
   • 호출자가 REF CURSOR를 닫는 책임을 가짐. 닫지 않으면 리소스 누수가 발생할 수 있음.
2. DBMS_SQL.TO_REFCURSOR:
   • 오라클 11g 이상에서 지원. 이전 버전에서는 별도의 처리 필요.
3. 성능:
   • 동적 쿼리는 파싱 오버헤드가 크므로, 동일 쿼리 구조는 커서 캐싱을 활용.
   • 바인드 변수를 사용하여 하드 파싱을 최소화.
4. SQL 인젝션 방지:
   • 테이블명이나 컬럼명은 DBMS_ASSERT.SQL_OBJECT_NAME으로 검증.
   • 조건 값은 항상 바인드 변수로 처리.
5. 복잡도:
   • DBMS_SQL은 EXECUTE IMMEDIATE보다 코드가 장황. 간단한 쿼리라면 OPEN ... FOR 사용을 고려.

---

6. 언제 사용해야 하나?

DBMS_SQL과 REF CURSOR를 사용한 이 접근법은 다음과 같은 경우에 적합합니다:

• 바인드 변수의 개수가 런타임에 결정되는 경우.
• 쿼리 결과의 컬럼 구조가 동적으로 변하는 경우.
• 외부 애플리케이션(Java, .NET 등)이 REF CURSOR를 처리해야 하는 경우.
• 복잡한 조건과 안전한 쿼리 실행이 필요한 경우.

간단한 동적 쿼리라면 EXECUTE IMMEDIATE나 OPEN ... FOR가 더 간결할 수 있으니, 요구사항에 따라 적절히 선택하세요.

---

7. 결론

DBMS_SQL을 사용하면 동적 쿼리와 바인드 변수를 유연하게 처리할 수 있으며, DBMS_SQL.TO_REFCURSOR를 통해 결과를 REF CURSOR로 반환하여 호출자와의 호환성을 높일 수 있습니다. 이 글의 예제를 참고하여 복잡한 동적 쿼리 요구사항을 안전하고 효율적으로 구현해 보세요. 

 

---