오라클 데이터베이스(Oracle Database) - Dynamic SQL, 동적쿼리

I. 오라클 동적 쿼리 개요 동적 쿼리란?

 

1. 오라클에서 동적 쿼리란

 

SQL문의 구조나 조건이 실행시점에 결정되는 쿼리입니다. 이를 통해 개발자는 프로그램 실행 도중에 SQL문을 조작하거나 변경할 수 있습니다. 정적 쿼리와 비교하여 동적 쿼리는 더 유연하게 데이터를 조회하거나 처리할 수 있습니다.

 

2. 동적 쿼리의 필요성

 

일반적인 SQL문은 실행 전에 문장이 완전히 결정되기 때문에, 동적으로 조건을 변경하거나 쿼리 구조를 수정하는 것이 불가능합니다. 동적 쿼리는 이러한 한계를 극복하기 위해 사용되며, 특히 사용자의 입력에 따라 다양한 조건과 결과를 반환해야 하는 경우에 유용합니다.

 

II. 오라클 동적 쿼리 구현 방법

 

1. 동적 SQL 구현하기 : EXECUTE IMMEDIATE 오라클에서 동적 SQL을 구현하기 위해 제공하는 문법인 EXECUTE IMMEDIATE를 사용할 수 있습니다. 이 문법을 사용하면 SQL문을 문자열로 작성한 후, 실행시점에 해당 문자열을 SQL문으로 실행시킬 수 있습니다.

예제)

DECLARE v_sql VARCHAR2(200); 
BEGIN v_sql := 
'INSERT INTO emp (empno, ename) 
VALUES (1, ''JOHN'')'; 
EXECUTE IMMEDIATE v_sql; 
END;

 

2. 동적 SQL 구현하기 : OPEN-FOR, FETCH, CLOSE 커서 사용 EXECUTE IMMEDIATE로 실행되는 동적 SQL과 달리, SELECT문의 결과를 반환할 수 있는 동적 SQL 구현을 위해 오라클에서는 OPEN-FOR, FETCH, CLOSE 커서를 사용할 수 있습니다.

예제)

DECLARE v_sql VARCHAR2(200);
v_refcur SYS_REFCURSOR;
... 
BEGIN v_sql := 
'SELECT * 
FROM emp 
WHERE empno = 1';
OPEN v_refcur 
FOR v_sql; 
... 
FETCH v_refcur 
INTO ...; 
... 
CLOSE v_refcur; 
END;

 

III. 오라클 동적 쿼리 사용시 주의사항

 

1. SQL Injection 위험 동적 쿼리는 사용자 입력을 기반으로 쿼리를 생성하므로, 잘못된 입력이 주입되는 경우 데이터베이스에 위험이 발생할 수 있습니다. 이를 방지하기 위해 입력 값을 검증하거나 바인딩 변수를 사용하여 쿼리를 안전하게 작성해야 합니다.

 

2. 성능 이슈 동적 쿼리를 사용하면 SQL문이 실행시점에 생성되고 컴파일되므로, 오라클의 SQL Plan Cache를 활용할 수 없게 됩니다. 때문에 동적 쿼리 실행 시, 쿼리 최적화 처리가 정적 쿼리에 비해 느릴 수 있습니다. 성능 이슈를 줄이기 위해 필요한 경우에만 동적 쿼리를 사용해야 합니다.

 

IV. 올바른 동적 쿼리 작성 요령

 

1. 구조적으로 작성하기 동적 쿼리의 경우, SQL문을 직접 조작 할 수 있으므로 쿼리의 가독성 및 유지보수를 위해 구조적인 코드 작성이 필요합니다. 이를 위해 공백, 개행 및 주석을 통해 신중한 코드 구조를 갖출 필요가 있습니다.

 

2. 바인딩 변수 사용하기 입력 값에 따라 쿼리가 생성되는 경우, 오라클의 바인딩 변수를 사용하면 SQL Injection 위험을 줄일 수 있습니다. 바인딩 변수를 사용하면 쿼리 최적화도 더 효과적으로 처리됩니다.

 

결론

 

오라클 동적 쿼리는 실행 시점에 쿼리 구조와 조건을 변경할 수 있어 유연한 데이터 조회 및 처리가 가능합니다. 하지만 SQL Injection 위험과 성능 이슈를 고려하여 바인딩 변수를 사용하고 필요한 경우에만 동적 쿼리를 사용해야 합니다. 코드 구조와 가독성 또한 중요한 요소이므로 신중한 작성이 필요합니다.