Dynamic SQL 소개

Dynamic SQL이란 무엇인가?

Dynamic SQL은 실행 시점에 SQL 구문을 생성하고 실행하는 프로그래밍 기법입니다. 정적 SQL과는 달리, Dynamic SQL은 쿼리 형태, 테이블 이름, 조건절 등을 변수나 로직에 따라 동적으로 구성할 수 있어 매우 유연하게 데이터베이스와 상호작용할 수 있게 해줍니다.

Dynamic SQL의 필요성

  • 유연성: 쿼리 로직을 런타임에 변경해야 할 때 Dynamic SQL은 강력한 솔루션을 제공합니다. 예를 들어, 사용자 인터페이스에서 선택한 필드에 따라 다른 쿼리를 실행해야 할 경우 유용합니다.
  • 재사용성: 유사한 쿼리 패턴을 갖지만, 세부적인 조건이나 필드만 변경되는 경우, 코드 중복을 줄이고 유지보수를 용이하게 합니다.
  • 메타데이터 기반 개발: 데이터 딕셔너리나 설정 테이블에 저장된 정보를 이용하여 쿼리를 구성할 수 있습니다.

Dynamic SQL의 장점과 단점

장점

  • 높은 유연성: 복잡한 조건과 다양한 요구 사항에 맞춰 SQL 구문을 변경 가능
  • 코드 재사용성: 유사한 로직을 가진 SQL 구문을 동적으로 생성하여 코드 중복 감소
  • 메타데이터 활용: 데이터 딕셔너리 등의 정보를 활용하여 동적인 쿼리 생성

단점

  • SQL Injection 공격 위험: 문자열 연결 방식으로 Dynamic SQL을 구현할 경우 보안 취약점 발생 가능
  • 성능 저하 가능성: 매번 새로운 쿼리를 생성하므로 쿼리 캐싱 효과 감소
  • 복잡성 증가: 코드 가독성 저하 및 디버깅 어려움 증가

Dynamic SQL 구현 방법

Oracle Database에서 Dynamic SQL을 구현하는 방법은 크게 두 가지입니다.

  • DBMS_SQL 패키지 사용
  • Native Dynamic SQL (NDS) 사용

DBMS_SQL 패키지

DBMS_SQL 패키지는 Oracle에서 제공하는 표준 패키지로서, Dynamic SQL을 실행하기 위한 다양한 프로시저와 함수를 제공합니다.

예시 코드


DECLARE
  l_cursor  INTEGER := DBMS_SQL.OPEN_CURSOR;
  l_statement VARCHAR2(4000) := 'SELECT * FROM employees WHERE department_id = :dept_id';
  l_dept_id NUMBER := 50;
  l_emp_rec employees%ROWTYPE;
BEGIN
  DBMS_SQL.PARSE(l_cursor, l_statement, DBMS_SQL.NATIVE);
  DBMS_SQL.DEFINE_ROW(l_cursor, 'employee_id', l_emp_rec.employee_id);
  DBMS_SQL.DEFINE_ROW(l_cursor, 'first_name', l_emp_rec.first_name, 100);
  DBMS_SQL.DEFINE_ROW(l_cursor, 'last_name', l_emp_rec.last_name, 100);
  -- ... 다른 컬럼 정의 ...
  DBMS_SQL.BIND_VARIABLE(l_cursor, ':dept_id', l_dept_id);
  DBMS_SQL.EXECUTE(l_cursor);

  LOOP
   IF DBMS_SQL.FETCH_ROW(l_cursor) > 0 THEN
    DBMS_SQL.GET_ROW(l_cursor, l_emp_rec);
    DBMS_OUTPUT.PUT_LINE(l_emp_rec.first_name || ' ' || l_emp_rec.last_name);
   ELSE
    EXIT;
   END IF;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(l_cursor);
END;
/

설명

  1. DBMS_SQL.OPEN_CURSOR: 커서(cursor)를 엽니다.
  2. DBMS_SQL.PARSE: SQL 구문을 분석합니다.
  3. DBMS_SQL.DEFINE_ROW: 각 컬럼의 데이터 유형과 길이를 정의합니다.
  4. DBMS_SQL.BIND_VARIABLE: 바인드 변수 값을 설정합니다.
  5. DBMS_SQL.EXECUTE: SQL 구문을 실행합니다.
  6. DBMS_SQL.FETCH_ROW: 결과 집합에서 행을 가져옵니다.
  7. DBMS_SQL.GET_ROW: 가져온 행의 데이터를 변수에 저장합니다.
  8. DBMS_SQL.CLOSE_CURSOR: 커서를 닫습니다.

Native Dynamic SQL (NDS)

NDS는 PL/SQL 블록 내에서 직접 Dynamic SQL 구문을 실행할 수 있는 기능을 제공합니다. 코드를 더 간결하게 만들 수 있으며, 일반적으로 DBMS_SQL보다 성능이 좋습니다.

예시 코드


DECLARE
  l_statement VARCHAR2(4000) := 'SELECT * FROM employees WHERE department_id = :dept_id';
  l_dept_id NUMBER := 50;
  TYPE emp_cursor_type IS REF CURSOR;
  l_emp_cursor  emp_cursor_type;
  l_emp_rec employees%ROWTYPE;
BEGIN
  OPEN l_emp_cursor FOR l_statement USING l_dept_id;

  LOOP
   FETCH l_emp_cursor INTO l_emp_rec;
   EXIT WHEN l_emp_cursor%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE(l_emp_rec.first_name || ' ' || l_emp_rec.last_name);
  END LOOP;

  CLOSE l_emp_cursor;
END;
/

설명

  1. TYPE emp_cursor_type IS REF CURSOR: REF CURSOR 유형을 정의합니다.
  2. OPEN l_emp_cursor FOR l_statement USING l_dept_id: SQL 구문을 열고 바인드 변수를 사용하여 실행합니다.
  3. FETCH l_emp_cursor INTO l_emp_rec: 결과 집합에서 행을 가져와 변수에 저장합니다.
  4. CLOSE l_emp_cursor: 커서를 닫습니다.

SQL Injection 방지

Dynamic SQL을 사용할 때 가장 중요한 것은 SQL Injection 공격을 방지하는 것입니다. 다음은 안전한 Dynamic SQL 작성을 위한 몇 가지 방법입니다.

  • 바인드 변수 사용: 사용자 입력 값을 SQL 구문에 직접 연결하는 대신, 바인드 변수를 사용하여 값을 전달합니다.
  • 입력 값 검증: 사용자 입력 값을 검증하여 예상치 못한 문자나 SQL 키워드를 제거합니다.
  • 최소 권한 원칙 적용: Dynamic SQL을 실행하는 사용자에게 필요한 최소한의 권한만 부여합니다.

잘못된 예 (SQL Injection 취약)


DECLARE
  l_dept_id VARCHAR2(100) := V('P5_DEPT_ID'); -- 사용자 입력
  l_statement VARCHAR2(4000) := 'SELECT * FROM employees WHERE department_id = ' || l_dept_id;
BEGIN
  -- ... 
END;
/

위 코드는 l_dept_id 변수에 사용자 입력 값이 직접 연결되므로, SQL Injection에 취약합니다.

올바른 예 (바인드 변수 사용)


DECLARE
  l_dept_id NUMBER := TO_NUMBER(V('P5_DEPT_ID')); -- 사용자 입력
  l_statement VARCHAR2(4000) := 'SELECT * FROM employees WHERE department_id = :dept_id';
BEGIN
  -- ...
END;
/

위 코드는 바인드 변수 :dept_id를 사용하여 사용자 입력 값을 전달하므로, SQL Injection 공격을 방지할 수 있습니다. 또한, 사용자 입력값을 숫자형으로 변환하여 데이터 유형 불일치로 인한 오류를 방지합니다.

Dynamic SQL 성능 최적화

Dynamic SQL의 성능을 최적화하기 위한 몇 가지 방법은 다음과 같습니다.

  • 쿼리 캐싱 활용: Dynamic SQL을 사용하더라도 쿼리 캐싱을 통해 성능을 향상시킬 수 있습니다. 특히, 자주 실행되는 쿼리의 경우 효과적입니다.
  • 실행 계획 안정화: SQL Plan Management 기능을 사용하여 Dynamic SQL의 실행 계획을 안정화할 수 있습니다.
  • 최적화된 데이터 모델 설계: Dynamic SQL을 사용하더라도 데이터 모델 설계가 부실하면 성능 저하를 초래할 수 있습니다. 적절한 인덱스 및 파티셔닝 전략을 적용해야 합니다.

결론

Dynamic SQL은 Oracle Database에서 쿼리를 생성하고 실행하는 매우 강력한 방법입니다. 다만, SQL Injection과 같은 보안 문제와 성능 문제를 주의해야 합니다. 바인드 변수를 사용하고 입력 값을 검증하며, 적절한 성능 최적화 기법을 적용하여 안전하고 효율적인 Dynamic SQL을 사용하십시오.

위로 스크롤