[PL/SQL] 동적 SQL(Dynamic SQL) - NDS 방식

SQL문 사용 시에 동적으로 계속 바뀌는 경우가 있다.

예를 들어 학생 테이블에서 검색하는 경우,

조건에 따라 학생의 번호만으로 찾을 수도 있고 혹은 이름으로 찾을 수도 있는데

이것을 정적으로 사용하면 같은 SQL문을 중복해서 사용해야 한다.

이럴때 SQL문을 문자열로 저장 해 놓고 자주 변하는 조건만 바꿔주면 재사용성도 높아지고 유지보수도 편해진다.

동적 SQL을 사용하는 방법은 원시동적 SQL을 사용하는 방식과 DBMS_SQL이란 시스템 패키지를 사용하는 방식이 있는데 SQL문을 문자열 형태로 만들어 실행한다는것은 같지만 구조와 사용법은 약간 다르다.

NDS (Native Dynamic SQL)

---

EXECUTE IMMEDIATE문

EXECUTE IMMEDIATE SQL문_문자열

[ INTO OUT변수, OUT변수 ... ]

[ USING 

[ IN | OUT | IN OUT ] 매개변수1, 

[ IN | OUT | IN OUT ] 매개변수2,

.... ];

BEGIN
      EXECUTE IMMEDIATE 'select employee_id, emp_name, job_id from employees where job_id = ''AD_ASST'' ';
END;

Single quote를 두 개 사용한 이유는 문자열 안에서 또다른 문자열 (AD_ASST)를 구분하기 위해서이다.

DECLARE
  vn_emp_id employees.employee_id%TYPE;
  vs_emp_name employees.emp_name%TYPE;
  vs_job_id employees.job_id%TYPE;
  
BEGIN
  EXECUTE IMMEDIATE 'SELECT employee_id, emp_name, job_id
                    FROM employees WHERE job_id = ''AD_ASST'' '
                    INTO vn_emp_id, vs_emp_name, vs_job_id;
                    
  DBMS_OUTPUT.PUT_LINE(vn_emp_id);
  DBMS_OUTPUT.PUT_LINE(vs_emp_name);
  DBMS_OUTPUT.PUT_LINE(vs_job_id);
  
END;

PL/SQL 프로시저가 성공적으로 완료되었습니다.

200

Jennifer Whalen

AD_ASST

일반적으로는 변수에 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
  vs_sql := 'SELECT employee_id, emp_name, job_id FROM employees WHERE job_id = ''AD_ASST'' ';
 
 
  EXECUTE IMMEDIATE vs_sql INTO vn_emp_id, vs_emp_name, vs_job_id;
                    
  DBMS_OUTPUT.PUT_LINE(vn_emp_id);
  DBMS_OUTPUT.PUT_LINE(vs_emp_name);
  DBMS_OUTPUT.PUT_LINE(vs_job_id);
  
END;

바인드 변수의 사용

---

바인드 변수(Bind Valiable) : 쿼리문에서 조건절에 사용하는 상수를 변수에 대입하여 사용하는 것. 이때 이 변수를 바인드 변수라고 한다.

바인드 변수를 사용하는 이유는 동일한 SQL문이 여러번 실행되면 오라클은 메모리상 처리 결과를 캐싱해 놓고 계속 참조하며, 구문검사와 오류검사등을 생략하여 빠른 속도로 결과값을 가져올 수 있다.

그런데 만약 조건절에 있는 상수로 처리하고, 상수가 계속 변하게 된다면 오라클 옵티마이저는 이것을 SQL문 전체가 다르다고 판단하여, 쿼리 작업을 계속 새로 하게된다. 그러면 성능 저하가 발생하여 좋지 않다.

조건절에 있는 상수를 변수에 대입하여 사용하면, 변수의 값이 달라지더라도 옵티마이저가 SQL문 전체가 달라졌다고 인식하지는 않아서 앞에서 얘기했던 처리 절차를 단축시킬 수 있어 쿼리 속도가 빨라진다.

바인드 변수는 따로 변수를 선언하여 값을 할당한 이후에, 

: 변수명 

으로 조건절에 명시해 준다.

EXECUTE IMMEDIATE sql문자열 INTO 담을 변수1, 담을 변수2....

USING sql문자열에서 사용한 바인드변수1, 바인드변수2, ...... ; 

 
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);
  pd_job_id employees.job_id%TYPE := 'AD_ASST';    
BEGIN
  vs_sql := 'SELECT employee_id, emp_name, job_id FROM employees WHERE job_id= :pd_job_id ';
 
  EXECUTE IMMEDIATE vs_sql INTO vn_emp_id, vs_emp_name, vs_job_id
  USING pd_job_id;
                    
  DBMS_OUTPUT.PUT_LINE(vn_emp_id);
  DBMS_OUTPUT.PUT_LINE(vs_emp_name);
  DBMS_OUTPUT.PUT_LINE(vs_job_id);
  
END;

pd_job_id를 바인드 변수로 사용하였고 USING 절에 사용한 바인드 변수를 명시해 주었다.

중요한 것은 USING 절에는 반드시 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);
  
  pd_job_id employees.job_id%TYPE := 'SA_REP';
  pd_salary employees.salary%TYPE := 7000;
  pd_manager_id employees.manager_id%TYPE := 148;
BEGIN
  vs_sql := 'SELECT employee_id, emp_name, job_id FROM employees WHERE job_id = :hi AND salary < :hi AND manager_id = :hi ';
 
  EXECUTE IMMEDIATE vs_sql INTO vn_emp_id, vs_emp_name, vs_job_id
  USING pd_job_id, pd_salary, pd_manager_id;
                    
  DBMS_OUTPUT.PUT_LINE(vn_emp_id);
  DBMS_OUTPUT.PUT_LINE(vs_emp_name);
  DBMS_OUTPUT.PUT_LINE(vs_job_id);
  
END;

PL/SQL 프로시저가 성공적으로 완료되었습니다.

173

Sundita Kumar

SA_REP

SQL 문장 안에서 실제 변수명과는 다른 이름을 사용했음에도 컴파일이 잘 되는것을 볼 수 있다.

실제 USING절에 선언한 변수 순서대로 매핑되는것에 유의하자.

익명블록이나 함수, 프로시저를 호출할때 전달하는 매개변수를 동적SQL로 호출할 수도 있는데, 

이 때는 반드시 변수타입과 개수, 변수명도 맞추어 주어야 한다.

또한 USING 절에 매개변수의 범위인 IN, OUT, INOUT 등도 함께 선언해야 한다.

  vs_sql := 'BEGIN 
  test_proc1(:pd_job_id, :pd_salary, :pd_manager_id); 
  END;';
 
  EXECUTE IMMEDIATE vs_sql INTO vn_emp_id, vs_emp_name, vs_job_id
  USING IN pd_job_id, OUT pd_salary, IN OUT pd_manager_id;
 

DDL문이나 ALTER SESSION에 관한 SQL문도 이렇게 문자열에 저장하여 실행시키면 사용이 가능하다. 

OPEN FOR문

---

동적 SQL에서는 한 개 이상의 로우를 결과 집합으로 반환하는 SELECT문을 OPEN FOR문과 커서 변수를 사용해 결과 값을 받아올 수 있다.

OPEN 커서변수 FOR 동적SQL문

USING 바인드변수1, 바인드변수2....;

DECLARE
    TYPE cur_query_employees IS REF CURSOR;
    cur_employees cur_query_employees;
    row_emp employees%ROWTYPE;
    vs_sql VARCHAR2(1000);
    vn_count NUMBER := 1;
BEGIN
    vs_sql := 'SELECT * FROM employees';
    OPEN cur_employees FOR vs_sql;
  LOOP 
      FETCH cur_employees INTO row_emp;
        EXIT WHEN cur_employees%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(vn_count || ' - ' || row_emp.emp_name);
        vn_count := vn_count + 1;
      END LOOP;
    
    CLOSE cur_employees;
    
  END;

1 - Donald OConnell

2 - Douglas Grant

3 - Jennifer Whalen

4 - Michael Hartstein

5 - Pat Fay

6 - Susan Mavris

.............

102 - Sarah Bell

103 - Britney Everett

104 - Samuel McCain

105 - Vance Jones

106 - Alana Walsh

107 - Kevin Feeney

OPEN FOR문을 통해 받아온 직원들의 이름이다. 

이번에는 바인드 변수를 사용해서 라인번호 30번과 50번 사이의 직원들의 이름을 출력해본다.

DECLARE
    TYPE cur_query_employees IS REF CURSOR;
    cur_employees cur_query_employees;
    row_emp employees%ROWTYPE;
    vs_sql VARCHAR2(1000);
    vn_count NUMBER := 1;
    
    vn_min NUMBER := 30;
    vn_max NUMBER := 50;
BEGIN
 
    vs_sql := 'select employee_id, emp_name, email, phone_number,hire_date, salary, 
    manager_id, commission_pct, retire_date, department_id, 
    job_id, create_date, update_date from 
        (select rownum as cnt, employees.* from employees where rownum < :vn_max) insql 
                where insql.cnt > :vn_min';
        
    OPEN cur_employees FOR vs_sql USING vn_max, vn_min;
    vn_count := vn_min + 1;
  LOOP 
      FETCH cur_employees INTO row_emp;
        EXIT WHEN cur_employees%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(vn_count || ' - ' || row_emp.emp_name);
        vn_count := vn_count + 1;
      END LOOP;
    
    CLOSE cur_employees;
    
  END;

31 - Adam Fripp

32 - Payam Kaufling

33 - Shanta Vollman

......

47 - Renske Ladwig

48 - Stephen Stiles

49 - John Seo

성공적으로 출력이 됨을 볼 수 있다. 

BULK COLLECT INTO

---

다중 로우를 처리할 때, 커서나 레코드 변수 혹은 반복문을 사용하는 방식은 데이터의 양이 늘어나면 늘어날수록 시간도 많이 걸리고 성능면에서도 좋지 않다.

BULK COLLECT INTO절은 일일이 한 로우씩 읽는 것이 아니라 집합적으로 데이터를 처리할 수 있도록 해 준다.

성능 확인을 위해 커서 변수와 BULK COLLECT를 통한 출력 속도 차이를 벤치마크 한다.

정확한 비교를 위해 새로운 새션 생성 후 테스트, 하나의 테스트가 끝나면 다시 새로운 세션을 생성하여 테스트 하였다.

CUSTOMERS 테이블에는 55500건의 데이터를 가지고 있다.

1. 커서를 사용한 방법.

DECLARE
  TYPE cust_cur IS REF CURSOR;
  var_cust_cur cust_cur;
  cust_row customers%rowtype;
BEGIN
  OPEN var_cust_cur FOR SELECT * FROM CUSTOMERS;
  LOOP
  FETCH var_cust_cur INTO cust_row;
  EXIT WHEN var_cust_cur%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(cust_row.cust_name);
  END LOOP;
END;

1.8초에서 2초 사이의 시간이 측정되었다.

2. BULK COLLECT 방식이다.

DECLARE
    TYPE nt_cust is table of customers%rowtype; 
    va_nt_cust nt_cust;
BEGIN
    SELECT *
      BULK COLLECT INTO va_nt_cust
      FROM customers;
   
      FOR i in 1.. va_nt_cust.COUNT
        LOOP
        DBMS_OUTPUT.PUT_LINE(va_nt_cust(i).cust_name);
        END LOOP;
    
END;

소요 시간이 절반 이하로 줄었다. 엄청난 개선이다.

동적 sql문을 사용한 BULK COLLECT INTO 구문이다.

  DECLARE
      TYPE nt_cust is table of customers%rowtype; 
      va_nt_cust nt_cust;
      va_sql varchar2(1000);
  BEGIN
     
     va_sql := ' SELECT * FROM customers';
        
      EXECUTE IMMEDIATE va_sql BULK COLLECT INTO va_nt_cust;
     
        FOR i in 1.. va_nt_cust.COUNT
          LOOP
          DBMS_OUTPUT.PUT_LINE(va_nt_cust(i).cust_name);
          END LOOP;
      
  END;

EXECUTE 구문 뒤에 BULK COLLECT를 선언하고 데이터를 저장할 컬렉션 변수를 걸어준다.

만약 바인드변수를 작성한 경우에는 INTO 뒤에 USING 절을 통해 사용한 바인드 변수를 반드시 명시해 주어야 한다.