[C# PL/SQL] 9.오라클 프로시저에서 동적 SQL 사용하기, EXECUTE IMMEDIATE, DBMS SQL 패키지 활용.

동적 SQL은 실행 시간에 SQL 문을 구성하고 실행할 수 있는 기능을 제공합니다. 오라클 데이터베이스에서 동적 SQL을 사용하는 것은 특히 다양한 조건에 따라 쿼리를 작성해야 할 때 유용합니다. 본 글에서는 오라클의 EXECUTE IMMEDIATE와 DBMS_SQL 패키지를 활용하여 동적 SQL을 어떻게 사용할 수 있는지에 대해 자세히 알아보겠습니다.

1. 동적 SQL이란?

동적 SQL은 런타임에 SQL 문을 구성하고 실행하는 기술로, 보통 고정된 쿼리 대신 변수나 조건에 따라 SQL 쿼리를 동적으로 생성할 수 있습니다. 이는 특히 다음과 같은 상황에서 유용합니다:

동적인 WHERE 조건이 필요한 경우
다양한 테이블이나 컬럼에 대해 동일한 쿼리를 실행해야 할 때
유저 입력에 따라 SQL 문을 조절해야 할 때

2. 동적 SQL의 장점과 단점

장점

유연성: 다양한 조건에 맞춰 SQL 문을 작성할 수 있습니다.
코드 재사용성: 동일한 로직을 여러 상황에서 재사용할 수 있습니다.

단점

성능 저하: 동적 SQL은 파싱 비용이 증가하여 성능에 영향을 줄 수 있습니다.
SQL 인젝션: 안전하게 처리하지 않으면 보안 취약점이 발생할 수 있습니다.

3. EXECUTE IMMEDIATE 사용하기

EXECUTE IMMEDIATE 문은 간단한 동적 SQL 실행에 매우 유용합니다. 이 문은 문자열 형태로 작성된 SQL 명령어를 즉시 실행합니다. 아래의 구문을 통해 사용법을 살펴보겠습니다:

EXECUTE IMMEDIATE 'SQL_문';

예제: 단순 SELECT 쿼리 실행


CREATE OR REPLACE PROCEDURE dynamic_sql_example AS
    v_sql VARCHAR2(100);
    v_result VARCHAR2(100);
BEGIN
    v_sql := 'SELECT first_name FROM employees WHERE employee_id = 100';
    EXECUTE IMMEDIATE v_sql INTO v_result;
    
    DBMS_OUTPUT.PUT_LINE('First Name: ' || v_result);
END;

위의 예제에서는 employees 테이블에서 특정 employee_id에 대한 first_name을 동적으로 조회합니다.

예제: 동적 INSERT 연산


CREATE OR REPLACE PROCEDURE dynamic_insert_example (
    p_employee_id IN NUMBER,
    p_first_name IN VARCHAR2
) AS
    v_sql VARCHAR2(200);
BEGIN
    v_sql := 'INSERT INTO employees (employee_id, first_name) VALUES (' || p_employee_id || ', ''' || p_first_name || ''')';
    EXECUTE IMMEDIATE v_sql;
    
    DBMS_OUTPUT.PUT_LINE('Inserted Employee: ' || p_employee_id);
END;

이 프로시저는 인자로 전달받은 employee_id와 first_name을 사용하여 새로운 레코드를 추가합니다.

4. DBMS_SQL 패키지 활용하기

DBMS_SQL 패키지는 좀 더 복잡한 동적 SQL 작업을 처리하기 위한 강력한 도구입니다. 이 패키지를 사용하면 SQL 문을 준비하고 실행 결과를 다룰 수 있습니다.

DBMS_SQL로 동적 SQL 실행하기

DBMS_SQL을 사용하여 동적 SQL을 실행하려면 다음 단계를 거쳐야 합니다:

SQL 문을 정의하고 커서를 열어 준비합니다.
필요한 변수를 바인딩합니다.
SQL 문을 실행합니다.
결과를 가져옵니다.
자원을 해제합니다.

예제: DBMS_SQL을 이용한 SELECT 쿼리


CREATE OR REPLACE PROCEDURE dynamic_dbms_sql_example AS
    v_cursor INTEGER;
    v_sql VARCHAR2(100);
    v_employee_name VARCHAR2(100);
    v_status INTEGER;
BEGIN
    v_sql := 'SELECT first_name FROM employees WHERE employee_id = :id';
    
    -- 커서 열기
    v_cursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
    
    -- 바인딩 변수 설정
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':id', 100);
    
    -- SQL 실행
    v_status := DBMS_SQL.EXECUTE(v_cursor);
    
    -- 결과 가져오기
    IF DBMS_SQL.FETCH_ROWS(v_cursor) > 0 THEN
        DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_employee_name);
        DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
    END IF;
    
    -- 자원 해제
    DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
    WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(v_cursor) THEN
            DBMS_SQL.CLOSE_CURSOR(v_cursor);
        END IF;
        RAISE;
END;

이 프로시저는 특정 employee_id에 대한 first_name을 DBMS_SQL 패키지를 통해 동적으로 조회하는 예제입니다.

5. 사용 시 주의사항

동적 SQL을 사용할 때는 몇 가지 주의해야 할 점이 있습니다:

SQL 인젝션 공격에 취약하므로 사용자 입력을 철저히 검증해야 합니다.
가급적 인라인 쿼리보다 바인딩 변수를 사용하여 쿼리를 구성해야 합니다.
성능을 위해 정적 SQL을 선호할 수 있는 경우 동적 SQL 사용을 최소화해야 합니다.

6. 결론

오라클에서 동적 SQL을 사용하는 방법인 EXECUTE IMMEDIATE와 DBMS_SQL 패키지에 대해 알아보았습니다. 이러한 기능들은 SQL 쿼리를 동적으로 생성하여 유연성을 제공하지만, 그 사용에 있어 보안 및 성능상의 주의가 필요합니다. 적절히 활용하면 데이터베이스 작업의 생성을 크게 단순화하며 다양한 비즈니스 조건에 맞춘 쿼리를 보다 쉽게 구현할 수 있습니다.

7. 참고자료

Oracle Documentation on Dynamic SQL
SQL Injection Prevention Techniques
Performance Tuning for Oracle SQL