오라클 동적 쿼리 사용법 & 예제 (PL/SQL)

오라클 동적 쿼리 사용법 & 예제 (PL/SQL)

1. 동적 쿼리란?

동적 쿼리는 실행 시점에 쿼리문을 생성하여 실행하는 방식입니다. 정적 쿼리와 달리 실행 시점에 쿼리문을 동적으로 변경하거나 조건에 따라 쿼리문을 생성할 수 있어 유연성이 높습니다. 오라클에서는 PL/SQL을 사용하여 동적 쿼리를 작성할 수 있습니다.

2. 동적 쿼리 작성 방법

오라클에서 동적 쿼리를 작성하는 방법은 크게 3가지가 있습니다.

2.1 EXECUTE IMMEDIATE 문 사용

EXECUTE IMMEDIATE 문을 사용하여 동적 쿼리를 실행할 수 있습니다. 이때 쿼리문은 문자열로 작성하고, 실행 시점에 문자열을 실행하기 위해 EXECUTE IMMEDIATE 문으로 실행합니다.

EXECUTE IMMEDIATE 'SELECT * FROM emp';

2.2 OPEN-FOR 문 사용

OPEN-FOR 문은 커서 변수를 사용하여 동적 쿼리를 실행하는 방법입니다. 쿼리문은 문자열로 작성하고, OPEN-FOR 문을 사용하여 커서를 열고 쿼리문을 실행합니다.

DECLARE
  CURSOR cur IS SELECT * FROM emp;
BEGIN
  OPEN cur FOR 'SELECT * FROM ' || table_name;
END;

2.3 DBMS_SQL 패키지 사용

DBMS_SQL 패키지는 동적 쿼리를 실행하기 위한 고급 기능을 제공합니다. DBMS_SQL 패키지를 사용하면 쿼리문을 동적으로 생성하고 바인드 변수를 사용할 수 있습니다. 하지만 복잡한 구조를 가지고 있어 사용하기 어려울 수도 있습니다.

DECLARE
  l_cursor   INTEGER;
  l_result   SYS_REFCURSOR;
  l_query    VARCHAR2(32767) := 'SELECT * FROM emp';
BEGIN
  l_cursor := DBMS_SQL.open_cursor;
  DBMS_SQL.parse(l_cursor, l_query, DBMS_SQL.native);
  DBMS_SQL.define_column(l_cursor, 1, l_result);
  DBMS_SQL.execute(l_cursor);
END;

3. 동적 쿼리 예제

실제로 동적 쿼리를 사용해보겠습니다.

3.1 특정 테이블 조회

다음 예제는 인자로 전달받은 테이블 이름을 이용하여 해당 테이블의 모든 데이터를 조회하는 동적 쿼리입니다.

CREATE OR REPLACE PROCEDURE get_table_data(p_table_name IN VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE 'SELECT * FROM ' || p_table_name;
END;

3.2 조건에 따른 동적 쿼리

다음 예제는 인자로 전달받은 부서 번호에 따라 동적으로 쿼리를 생성하여 해당 부서의 사원 정보를 조회하는 동적 쿼리입니다.

CREATE OR REPLACE PROCEDURE get_department_data(p_dept_id IN NUMBER) IS
BEGIN
  EXECUTE IMMEDIATE 'SELECT * FROM emp WHERE dept_id = :dept_id'
    USING p_dept_id;
END;

마무리

오라클에서는 PL/SQL을 사용하여 동적 쿼리를 작성하고 실행하는 방법을 살펴보았습니다. EXECUTE IMMEDIATE 문을 사용하거나 OPEN-FOR 문, DBMS_SQL 패키지를 활용하여 동적 쿼리를 작성할 수 있습니다. 동적 쿼리를 사용하면 실행 시점에 쿼리문을 동적으로 변경할 수 있어 유연한 쿼리 작성이 가능합니다.