오라클 Stored Procedure에서 파라미터 기반 동적 WHERE 조건, 필드, JOIN 제어 방법

 

---

소개

오라클 데이터베이스에서 Stored Procedure(SP)를 사용하면 복잡한 비즈니스 로직을 효율적으로 처리할 수 있습니다. 특히, 입력 파라미터에 따라 WHERE 조건, 선택 필드, 또는 JOIN 여부를 동적으로 제어하는 기능은 데이터 조회의 유연성을 크게 향상시킵니다. 이 글에서는 오라클 Stored Procedure에서 파라미터 값을 기반으로 동적 쿼리를 작성하는 방법을 자세히 설명합니다. 동적 SQL의 기본 개념부터 실제 예제, 그리고 주의점까지 다루겠습니다.

---

동적 쿼리의 필요성

일반적으로 쿼리는 고정된 형태로 작성됩니다. 하지만 실무에서는 사용자가 입력한 조건에 따라 쿼리의 구조가 달라져야 하는 경우가 많습니다. 예를 들어:

• 특정 조건이 있을 때만 WHERE 절에 조건 추가
• 특정 파라미터 값에 따라 반환할 필드 변경
• 필요에 따라 특정 테이블과 JOIN 수행

이런 요구사항을 해결하기 위해 Stored Procedure에서 동적 SQL을 사용합니다. 오라클에서는 주로 EXECUTE IMMEDIATE를 활용해 동적 쿼리를 실행합니다.

---

기본 원리

Stored Procedure에서 동적 쿼리를 작성하려면 다음 단계를 따릅니다:

1. 동적 SQL 문자열 생성: 파라미터 값을 확인하여 쿼리 문자열을 동적으로 구성.
2. 바인드 변수 사용: SQL 인젝션 방지를 위해 바인드 변수를 활용.
3. EXECUTE IMMEDIATE 실행: 동적 쿼리를 실행하고 결과를 반환.
4. 예외 처리: 쿼리 실행 중 발생할 수 있는 오류를 처리.

---

예제 시나리오

다음과 같은 요구사항을 가정해 봅시다:

• 테이블: EMPLOYEES (직원 정보), DEPARTMENTS (부서 정보)
• 파라미터:
  • p_dept_id: 부서 ID (NULL 가능)
  • p_include_salary: 급여 정보 포함 여부 (Y/N)
  • p_search_name: 직원 이름 검색 조건 (NULL 가능)
• 요구사항:
  • p_dept_id가 제공되면 해당 부서의 직원만 조회.
  • p_include_salary가 'Y'라면 급여 필드 포함.
  • p_search_name이 제공되면 이름에 해당 문자열 포함된 직원 조회.
  • 부서 정보는 p_dept_id가 있을 때만 JOIN 수행.

728x90

---

Stored Procedure 예제 코드

CREATE OR REPLACE PROCEDURE get_employees_dynamic (
    p_dept_id         IN NUMBER,
    p_include_salary  IN VARCHAR2,
    p_search_name     IN VARCHAR2,
    p_cursor          OUT SYS_REFCURSOR
) AS
    v_sql_query       VARCHAR2(1000);
    v_where_clause    VARCHAR2(500);
    v_select_fields   VARCHAR2(500);
    v_join_clause     VARCHAR2(200);
    v_bind_count      NUMBER := 0;
BEGIN
    -- 기본 SELECT 절 구성
    v_select_fields := 'SELECT e.employee_id, e.first_name, e.last_name';
    
    -- 급여 필드 포함 여부
    IF p_include_salary = 'Y' THEN
        v_select_fields := v_select_fields || ', e.salary';
    END IF;
    
    -- FROM 절 및 JOIN 구성
    v_sql_query := v_select_fields || ' FROM employees e';
    
    IF p_dept_id IS NOT NULL THEN
        v_join_clause := ' INNER JOIN departments d ON e.department_id = d.department_id';
        v_sql_query := v_sql_query || v_join_clause;
    END IF;
    
    -- WHERE 절 동적 구성
    v_where_clause := ' WHERE 1=1'; -- 조건 추가를 쉽게 하기 위한 기본 조건
    
    IF p_dept_id IS NOT NULL THEN
        v_where_clause := v_where_clause || ' AND e.department_id = :1';
        v_bind_count := v_bind_count + 1;
    END IF;
    
    IF p_search_name IS NOT NULL THEN
        v_where_clause := v_where_clause || ' AND e.first_name LIKE :2';
        v_bind_count := v_bind_count + 1;
    END IF;
    
    -- 최종 쿼리 조립
    v_sql_query := v_sql_query || v_where_clause;
    
    -- 동적 쿼리 실행
    IF v_bind_count = 0 THEN
        OPEN p_cursor FOR v_sql_query;
    ELSIF v_bind_count = 1 THEN
        IF p_dept_id IS NOT NULL THEN
            OPEN p_cursor FOR v_sql_query USING p_dept_id;
        ELSE
            OPEN p_cursor FOR v_sql_query USING '%' || p_search_name || '%';
        END IF;
    ELSE
        OPEN p_cursor FOR v_sql_query USING p_dept_id, '%' || p_search_name || '%';
    END IF;
    
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001, 'Error in get_employees_dynamic: ' || SQLERRM);
END get_employees_dynamic;
/

---

코드 설명

1. 파라미터 정의

• p_dept_id: 부서 ID로, NULL일 경우 모든 부서 조회.
• p_include_salary: 'Y'일 때만 salary 필드 포함.
• p_search_name: 직원 이름 검색 조건.
• p_cursor: 결과를 반환하기 위한 REF CURSOR.

2. 동적 쿼리 구성

• SELECT 절: 기본적으로 employee_id, first_name, last_name을 선택. p_include_salary = 'Y'일 경우 salary 추가.
• FROM 및 JOIN 절: p_dept_id가 NULL이 아니면 DEPARTMENTS 테이블과 INNER JOIN 수행.
• WHERE 절: WHERE 1=1로 시작하여 조건 추가를 간단히 처리. p_dept_id와 p_search_name에 따라 조건 추가.

3. 바인드 변수 사용

• p_dept_id와 p_search_name은 바인드 변수(:1, :2)로 처리하여 SQL 인젝션 방지.
• 바인드 변수의 개수(v_bind_count)를 추적하여 OPEN ... USING 구문에서 적절히 처리.

4. 예외 처리

• EXCEPTION 블록에서 오류를 캡처하여 사용자 정의 에러 메시지 반환.

 

728x90

---

Stored Procedure 호출 예제

DECLARE
    l_cursor SYS_REFCURSOR;
    l_employee_id NUMBER;
    l_first_name VARCHAR2(50);
    l_last_name VARCHAR2(50);
    l_salary NUMBER;
BEGIN
    -- 급여 포함, 부서 ID 10, 이름에 'John' 포함
    get_employees_dynamic(10, 'Y', 'John', l_cursor);
    
    LOOP
        FETCH l_cursor INTO l_employee_id, l_first_name, l_last_name, l_salary;
        EXIT WHEN l_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(l_employee_id || ', ' || l_first_name || ' ' || l_last_name || ', ' || l_salary);
    END LOOP;
    CLOSE l_cursor;
END;
/

---

주의점

1. SQL 인젝션 방지:
   • 동적 쿼리에서 사용자 입력을 직접 문자열에 삽입하지 말고, 반드시 바인드 변수를 사용하세요.
   • 예: v_where_clause := v_where_clause || ' AND e.first_name LIKE ''' || p_search_name || ''''; (X, 취약)
   • 대신: v_where_clause := v_where_clause || ' AND e.first_name LIKE :2'; (O, 안전)
2. 성능 고려:
   • 동적 쿼리는 하드 파싱(hard parsing)을 유발할 수 있으므로, 가능하면 바인드 변수를 활용해 쿼리 재사용성을 높이세요.
   • 동일한 쿼리 구조를 반복적으로 실행할 경우, 쿼리 캐싱을 고려하세요.
3. 가독성과 유지보수:
   • 동적 쿼리는 디버깅이 어려울 수 있으므로, 쿼리 문자열을 로그로 출력하거나 디버깅용 변수에 저장하여 확인하세요.
   • 예: DBMS_OUTPUT.PUT_LINE(v_sql_query);
4. REF CURSOR 사용:
   • 결과셋이 동적일 경우 SYS_REFCURSOR를 사용하여 유연하게 처리.
   • 호출하는 애플리케이션에서 커서를 적절히 닫는 것을 잊지 마세요.

---

추가 개선 아이디어

• 쿼리 캐싱: 자주 실행되는 쿼리 패턴을 미리 정의하여 성능 최적화.
• 파라미터 검증: 입력 파라미터의 유효성을 검증하여 예외 상황 방지.
• 로그 추가: 동적 쿼리와 실행 결과를 로깅하여 디버깅 용이성 향상.

---

결론

오라클 Stored Procedure에서 파라미터를 활용한 동적 쿼리는 유연하고 강력한 데이터 처리 방법을 제공합니다. EXECUTE IMMEDIATE와 바인드 변수를 적절히 사용하면 안전하고 효율적인 쿼리 실행이 가능합니다. 이 글에서 다룬 예제를 바탕으로 실제 비즈니스 요구사항에 맞게 커스터마이징하여 사용해 보세요.