[PL/SQL]동적 SQL의 기초

동적 SQL의 기초

실행계획

다음의 세 구문은 결과가 같다.

SELECT * FROM EMP WHERE DEPTNO = 10;
 
 
SELECT * FROM EMP WHERE DEPTNO =  10;
 
 
SELECT * FROM EMP
WHERE DEPTNO = 10;

하지만 이 구문은 같다고 할 수있을까?

엄밀히 말하면 같다고 말할 수 없다.

SELECT * FROM V$SQL
WHERE SQL_TEXT LIKE 'SELECT * FROM EMP %';

이 구문을 사용하면, SQL_ID를 알 수 있고 이것을 이용해서

SELECT *
FROM V$SQL_PLAN
WHERE SQL_ID = '8xnv6u5p3pxjf';

를 이용하면, 실행 계획을 볼 수 있다.

맨위의 세 구문은 각각의 SQL_ID가 다르기 때문에 같다고 할 수 없다.

즉 완전히 문자가 서로 100%같지 않은 이상

그때그때 실행 계획에 따로 만들어 지는 것이다.

이렇게 SQL_ID가 다르지만 같은 결과를 내는 구문이 많아질수록 메모리의 낭비가 생기게 된다.

결론은 이러한 경우에는 동일한 문장의 패턴으로 하나의 실행계획을 공유해서 사용할 수 있도록 해야한다.

그렇다면 

SELECT * FROM EMP WHERE DEPTNO = 10;
SELECT * FROM EMP WHERE DEPTNO = 20;
SELECT * FROM EMP WHERE DEPTNO = 30;
SELECT * FROM EMP WHERE DEPTNO = 40;

이러한 구문의 경우 어떻게 해야 메모리의 낭비가 생기지 않을까??

이것에 대한 해결법은 바인딩 변수를 사용하는 것이다.

SELECT * FROM EMP WHERE DEPTNO = :A1;

이렇게 한다면 위의 경우보다 메모리의 낭비가 생기지 않는다.

이때 치환변수를 사용하면 안되나? 하는 의문이 생길수도 있는데,

SELECT * FROM EMP WHERE DEPTNO = &A2;

이렇게 치환변수를 사용할 경우

위의 10,20,30,40을 직접 써넣은 것처럼 메모리의 낭비가 발생한다.

위의 SQL_PLAN을 확인하면 결과를 쉽게 볼 수 있다. 

만약, 메모리를 낭비하는 명령어들을 실행할 경우 메모리를 다시 비우는 방법이 있을까?

그럴경우 다음과 같은 방법을 사용하자.

ALTER SYSTEM FLUSH SHARED_POOL ;

이 구문을 사용하면, 실행계획이 전부 삭제 된다.

<동적 SQL>

동적 SQL은 간단히 말해, PL/SQL에서 DML 을 실행하는 것을 말한다.

SQL문은 구문분석, 바인드, 실행, 패치중 일부 또는 전부를 거쳐 실행되는데, 동적 SQL문의 경우 모든 단계가 런타임에 수행되게 된다.

원래는 SELECT, DML, MERGE, COMMIT, SAVEPOINT, ROLLBACK 과 같은 내장 SQL문의 경우 구문 분석 및 바인드 단계가 컴파일 시 수행된다.

그러므로 동적 SQL을 사용하면 런타임의 구조를 변경할 수 있는 SQL문을 생성할 수 있다.

>NDS(Native Dynamic SQL)

다음 예시를 참고하자.

CREATE PROCEDURE ADDRO(TABLE_NAME VARCHAR2, ID NUMBER, NAME VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO '||TABLE_NAME||' VALUES (:1, :2)' USING ID,NAME;
END;
/

SQL을 배운지 얼마 되지 않아 잘못된 내용이 있을 수 있습니다. 틀린 내용이있다면, 댓글로 달아주세요.