② 바인드 변수 처리 1
좀더 복잡한 형태의 동적 SQL문을 만들어 보자.
입력
DECLARE
--출력 변수 선언
vn_emp_id employees.employee_id%TYPE;
vs_emp_name employees.emp_name%TYPE;
vs_job_id employees.job_id%TYPE;
vs_sql VARCHAR2(1000);
BEGIN
-- SQL문을 변수에 담기
vs_sql := 'SELECT employee_id, emp_name, job_id
FROM employees
WHERE job_id = ''SA_REP''
AND salary < 7000
AND manager_id =148 ';
EXECUTE IMMEDIATE vs_sql INTO vn_emp_id, vs_emp_name, vs_job_id;
DBMS_OUTPUT.PUT_LINE( 'emp_id : ' || vn_emp_id );
DBMS_OUTPUT.PUT_LINE( 'emp_name : ' || vs_emp_name );
DBMS_OUTPUT.PUT_LINE( 'job_id : ' || vs_job_id );
END;
결과
emp_id : 173
emp_name :Sundita Kumar
job_id : SA_REP
위 예제에서 사용한 SQL문은 WHERE 조건이 총 3개인데 조건절의 각 컬럼 값으로 상수를 사용했다. 그런데 정적 SQL이든 동적 SQL이든, 위 예제처럼 조건 값을 상수로 처리하는 것은 성능면에서 매우 좋지 않다.
일반적으로 SQL문을 실행하면 오라클은 먼저 해당 문장에 대한 구문검사와 문법적 오류 검사를 거쳐 가능한 여러 가지 실행 계획을 세운다. 그후 가장 효율적인 실행 계획을 선택하는 등 복잡한 과정을 거쳐 해당 SQL문을 실행하고 처리하는데 이러한 일련의 과정이 상당히 무거운 작업이다. 그런데 동일한 SQL 문장이 여러 번 실행되면 오라클은 이전에 실행한 적이 있었던 SQL 문장인지 여부를 메모리에서 찾아보는데, 만약 찾으면 구문 검사와 오류 검사 등의 과정을 거치지 않고 이전에 실행했던 정보를 활용해 처리한다. 따라서 이때는 처리 속도가 상당히 빠르다. 독자 여러분도 SQL문을 맨 처음 실행했을 때는 오래 걸리지만 그 뒤 여러 번 실행했을 때는 결과가 빨리 나온 것을 본 적이 있을 텐데, 바로 이런 이유 때문이다(물론 SQL문 실행 결과도 메모리에 남아 있는 상태라면 DISK I/O가 발생하지 않기 때문이기도 하다).
하지만 똑같은 SQL 문장이라도 WHERE 조건의 비교 값을 상수로 처리하면, 상수 값이 달라질 때마다 오라클은 이 문장을 이전과는 다른 문장으로 인식해 일련의 복잡한 작업을 매번 수행하게 된다. 반면 WHERE 조건의 비교 값을 변수 처리하면, 조건 값이 달라지더라도 SQL구문은 변하지 않으므로 오라클은 동일한 문장으로 인식해서 SQL문의 처리 성능이 좋아진다. 이렇게 SQL 구문상에서 조건절에 들어가는 값으로 변수를 사용하는 것을 바인드 변수를 사용한다고 말한다. DBA나 전문 튜너들이 튜닝을 시작하기에 앞서 가장 먼저 확인하는 사항 중 하나가 바로 SQL문에서 바인드 변수를 사용했는지 여부일 정도로 이 변수는 성능에 있어 매우 중요한 요소다.
정적 SQL에서 WHERE 조건 비교 값에 변수를 선언해 바인드 변수를 활용하듯, 동적 SQL에서도 바인드 변수를 사용할 수 있다. 성능 문제를 떠나서 개발자 입장에서도 동적 SQL에서는 바인드 변수를 사용하는 것이 낫다. 왜냐하면 동적 SQL의 특성 상 SQL문 자체를 문자열로 만드는 작업을 해야 하는데, 앞의 예제에서 보듯이 상수를 사용하다 보면 구문을 작성할 때 실수할 확률이 높고(문자형 데이터는 따옴표를 2개씩 찍어야 한다) 코드 가독성도 떨어진다. 특히 조건 값이 많고 게다가 그 타입이 모두 문자형이라면 따옴표의 홍수에 파묻히게 될 것이다. 하지만 바인드 변수 처리를 하면 SQL 구문을 작성할 때, 해당 값이 문자형인지 숫자형인지 신경 쓸 필요 없이 적당한 변수만 선언하면 따옴표의 지옥에서 벗어날 수 있다. 그럼 동적 SQL에서 바인드 변수를 어떻게 처리하는지 바로 직전 예제를 바인드 변수로 변경해 보자.
입력
DECLARE
--출력 변수 선언
vn_emp_id employees.employee_id%TYPE;
vs_emp_name employees.emp_name%TYPE;
vs_job_id employees.job_id%TYPE;
vs_sql VARCHAR2(1000);
-- 바인드 변수 선언과 값 설정
vs_job employees.job_id%TYPE := 'SA_REP';
vn_sal employees.salary%TYPE := 7000;
vn_manager employees.manager_id%TYPE := 148;
BEGIN
-- SQL문을 변수에 담기(바인드 변수 앞에 :를 붙인다)
vs_sql := 'SELECT employee_id, emp_name, job_id
FROM employees
WHERE job_id = :a
AND salary < :b
AND manager_id = :c ';
-- SQL문에서 선언한 순서대로 USING 다음에 변수를 넣기
EXECUTE IMMEDIATE vs_sql INTO vn_emp_id, vs_emp_name, vs_job_id
USING vs_job, vn_sal, vn_manager;
DBMS_OUTPUT.PUT_LINE( 'emp_id : ' || vn_emp_id );
DBMS_OUTPUT.PUT_LINE( 'emp_name : ' || vs_emp_name );
DBMS_OUTPUT.PUT_LINE( 'job_id : ' || vs_job_id );
END;
결과
emp_id : 173
emp_name :Sundita Kumar
job_id : SA_REP
동적 SQL에서 바인드 변수를 사용하려면, 먼저 SQL 구문에서 변수가 들어갈 부분에 ‘:변수명을 추가한 후 변수를 선언한 순서대로 EXECUTE IMMEDIATE 문에서 USING 다음에 해당 변수를 나열하면 된다. 주의할 점은 ‘:변수명’에서 변수명 자체는 의미가 없다. 즉 아무 명칭이나 사용할 수 있고 심지어는 동일한 이름을 사용해도 된다. 다만 변수의 순서와 타입을 USING 다음에 명시한 변수의 순서와 타입과 반드시 맞춰야 한다. 앞의 예제를 다음과 같이 바꾸더라도 올바르게 동작한다.
입력
...
...
vs_sql := 'SELECT employee_id, emp_name, job_id
FROM employees
WHERE job_id = :a
AND salary < :b
AND manager_id = :c ';
-- SQL문에서 선언한 순서대로 USING 다음에 변수를 넣기
EXECUTE IMMEDIATE vs_sql INTO vn_emp_id, vs_emp_name, vs_job_id
USING vs_job, vn_sal, vn_manager;
...
...
결과
emp_id : 173
emp_name :Sundita Kumar
job_id : SA_REP
job_id와 salary에 명시한 바인드 변수는 ‘:a’ 이고 manager_id에 명시한 바인드 변수는 ‘:c’ 이다. 하지만 SQL구문에서 명시한 바인드 변수명은 아무런 의미가 없고, 그 순서와 개수와 타입이 중요하다.
바인드 변수 매핑
• :a → vs_job
• :a → vn_sal
• :c → vn_manager
만약 ‘:a’를 두 번 썼다고 해서 USING 다음에 vs_job을 두 번 명시하거나, 동일한 변수라고 해서 vs_job, vn_manager 만 명시하면 오류가 발생한다.
• EXECUTE IMMEDIATE … USING vs_job, vs_job, vn_manager (X)
• EXECUTE IMMEDIATE … USING vs_job, vn_manager (X)
• EXECUTE IMMEDIATE … USING vs_job, vn_sal, vn_manager (O)