동적 SQL
- 정적 SQL과 동적 SQL의 비교
| 항목 | 정적SQL | 동적SQL |
| 사용방법 | PL/SQL 프로그램 안에 SQL문을 직접 삽입 | 실행 중 변경 가능한 문자열 변수 또는 문자열 상수로 제공된 SQL문을 실행 |
| 사용 예 | SELECT COUNT(*) INTO v_cnt FROM EMP; |
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM emp' INTO v_cnt; |
| 사용 가능한 SQL | -DML(SELECT, INSERT, UPDATE, DELETE, MERGE) - TCL(COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION, LOCK TABLE) |
- 모든 SQL문(DML, TCL, DDL, DCL) |
| 장점 | - 프로그램을 작성하기 쉽다 - 성능이 더 높다 |
- 사전에 정의되지 않은 SQL을 실행 시에 확정하여 실행할 수 있다. - DML과 TCL외에도 DDL, DCL을 사용할 수 있다. |
| 단점 | -조건에 따라 약간씩의 변형이 필요한 SQL의 경우 수많은 SQL이 각각 작성되어야 한다. -DML과 TCL 외에는 사용이 불가능하다. |
-프로그램을 작성하기가 복잡 |
| 용도 | -SQL 문장이 한 개 또는 몇 개 이하의 변형으로 고정된 경우 | -SQL이 몇 개 문장으로 고정될 수 없는 경우 -어떤 SQL이 실행될지 예측할 수 없는 경우 |
일반적으로 정적SQL이 훨씬 더 작성이 간단하고 컴파일 시에 SQL문이 올바른지를 사전에 검사해 주기 때문에 오류를 사전에 발견할 수 있으며 데이터베이스 레벨에서 PL/SQL 프로그램과 다른 스키마 오브젝트 간의 의존성을 관리해 주는 장점이 있기 때문에 실제로 더 많이 사용된다. 하지만 PL/SQL 프로그램의 작성 시에 SQL문장이 고정되지 않은 상황이라면 정적SQL의 사용이 불가피하다. 또한 DDL과 DCL은 정적SQL로는 사용할 수 없다. DDL이나 DCL을 정적 SQL로 사용하려고 하면 컴파일 오류가 발생한다.
동적 SQL을 실행하는 방법에는 여러가지가 있지만 일반적으로 세 가지의 방법이 있다.
1. EXECUTE IMMEDIATE문을 사용하여 SQL을 실행하는 방법
2. 커서 변수를 사용하여 OPEN, FETCH, CLOSE문으로 SELECT문을 실행하는 방법
3. 오라클 내장 패키지인 DBMS_SQL을 사용하는 방법
첫번쨔와 세번째 방법은 SQL 유형에 제한이 없어 DML, TCL, DDL, DCL 모두에 사용할 수 있는 방법이고, 두 번째 방법은 SELECT문에만 사용할 수 있다.
EXECUTE IMMEDIATE문을 사용하는 방법
EXECUTE IMMEDIATE SQL문자열 [ INTO 변수목록 ] [ USING 바인드변수목록 ] ;DECLARE
v_insert_stmt CONSTANT VARCHAR2(100) := 'INSERT INTO t VALUES(1, ''서울'')';
BEGIN
-- DDL 실행, 리터럴 사용
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE t';
EXCEPTION WHEN OTHERS THEN
NULL; -- 테이블이 없을 때 발생하는 오류는 무시함
END;
EXECUTE IMMEDIATE 'CREATE TABLE t(a NUMBER, b VARCHAR2(10))';
-- DML 실행, 문자열 변수 사용
EXECUTE IMMEDIATE v_insert_stmt;
-- TCL 실행, 리터럴 사용
EXECUTE IMMEDIATE 'COMMIT';
END;쿼리 결과를 변수에 저장
동적 SELECT문의 경우에는 INTO절을 추가하여 결과를 변수로 저장할 수 있다. INTO절에 사용하는 변수에는 스칼라 변수, 컬렉션 변수, 레코드 변수가 모두 사용 가능하다
DECLARE
v_query CONSTANT VARCHAR2(200) := 'SELECT empno, ename
FROM emp
WHERE empno = 7788';
TYPE emp_type IS RECORD(
empno emp.empno%TYPE,
ename emp.ename%TYPE
);
v_rec emp_type; -- 레코드 변수
v_empno emp.empno%TYPE -- 스칼라 변수
v_ename emp.ename%TYPE -- 스칼라 변수
TYPE emp_arr IS TABLE OF emp_type;
v_emps emp_arr; -- 레코드 컬렉션 변수
BEGIN
-- INTO 스칼라 변수
EXECUTE IMMEDIATE v_query INTO v_empno, v_ename;
DBMS_OUTPUT_PUT_LINE(v_empno||', '||v_ename);
-- INTO 레코드 변수
EXECUTE IMMEDIATE v_query INTO v_rec;
DBMS_OUTPUT_PUT_LINE(v_rec.empno||', '||v_rec.ename);
-- INTO 레코드 컬렉션 변수
EXECUTE IMMEDIATE 'SELECT empno, ename FROM emp' BULK COLLECT INTO v_emps;
DBMS_OUTPUT_PUT_LINE('사원 수: '||v_emps.COUNT);
END;바인드 변수의 사용
동적SQL문에 바인드 변수를 사용할 경우에는 SQL 문자열에 바인드 변수에 대한 Placeholder를 포함시키고 USING절에 바인드될 변수 또는 값을 지정한다. 정적 SQL에서는 바인드 변수 앞에 :를 붙이지 않고도 사용이 가능했지만 동적SQL문에서는 상황이 다르다. 동적 SQL문은 쿼리를 문자열로 작성하는 데 문자열 안데 바인드 변수를 직접 집어넣을 수 없다.
때문에 동적 SQL에서는 바인드 변수가 들어갈 자리에 바인드 변수의 위치를 표시하는 플레이스 홀더를 대신 넣는다.
실제 변수를 바인딩하는 동작은 EXECUTE IMMEDIATE문을 사용하여 동적 SQL을 실행할 때 이루어지는데 쿼리 문자열에 포함된 Placeholder가 USING절에 나열된 변수로 대치된다. 동적 SQL애서 쿼리 문자열에 포함시킬 Placeholder 접두사가 :이다.
DECLARE
v_query CONSTANT VARCHAR2(200) := 'SELECT COUNT(*) FROM emp
WHERE deptno = :deptno
AND job = :job';
v_deptno emp.deptno%TYPE;
v_cnt PLS_INTEGER;
BEGIN
v_deptno := 20;
-- 바인드 값은 변수, 상수, 리터럴을 모두 사용할 수 있다.
EXECUTE IMMEDIATE v_query
INTO v_cnt
USING IN v_deptno, 'CLERK';
DBMS_OUTPUT_PUT_LINE('COUNT = '||v_cnt);
END;
바인드 변수의 모드
- 입력(IN)모드: 바인드 변수 앞에 키워드 'IN'을 지정하여 값이 PL/SQL에서 데이터베이스 서버로 전달된다. 입력 모드 변수의 값은 동적 SQL의 실행 전후에 변경이 없다. 모드의 생략 시 이 모드가 기본으로 사용된다.
- 출력(OUT)모드: 바인드 변수 앞에 키워드 'OUT'을 지정하며 값이 데이터베이스 서버에서 PL/SQL로 전달된다.
동적 SQL을 실행하기 전에 바인드 변수가 가지고 있던 값은 실행 결과에 아무런 영향도 미치지 않는다. - 입출력(IN OUT)모드: 바인드 변수 앞에 키워드 'IN OUT'을 지정하며 값이 양방향으로 전달된다.
동적SQL을 실행하기 전에 가지고 있던 값이 데이터베이스 서버로 전달되어 참조되며, 데이터
베이스 서버에서 변경된 값이 다시 PL/SQL 변수로 되돌려진다.
출력 모드와 입출력 모드는 DML의 RETURNING절이나 PL/SQL절에서 사용된다.
바인드 변수 플레이스 홀더의 이름과 순서
바인드 변수가 여러 개 사용될 때 SQL문의 종류에 따라 플레이스 홀더와 바인드 값의 사용 방법이 달라진다.
익명 PL/SQL문도 아니고 CALL문도 아닐 경우
이 유형에서 플레이스 홀더를 여러 개 사용할 경우 위치에 의한 바인딩 방법이 사용된다. 플레이스 홀더의 이름은 아무런 의미도 없다. 따라서 플레이스 홀더의 이름이 서로 달라야 할 필요도 없다.
DECLARE
v_ename_in VARCHAR2(10) := 'Scott';
v_ename VARCHAR2(10);
v_job VARCHAR2(10) := 'ANALYST';
BEGIN
EXECUTE IMMEDIATE 'SELECT ename
FROM emp
WHERE ename IN (:ename, UPPER(:ename),
LOWER(:ename), INITCAP(:ename))
AND job = :job'
INTO v_ename
USING v_ename_in, v_ename_in, v_ename_in, v_ename_in, v_job;
DBMS_OUTPUT_PUT_LINE('이름='||v_ename ||', 업무='||v_job);
END;익명 PL/SQL문이거나 CALL문일 경우
CALL은 PL/SQL 저장 서브프로그램을 호출하는 문장이므로 이 유형은 PL/SQL을 실행하는 경우라 할 수 있다.
이 유형에서는 이름에 의한 바인딩 방법이 사용된다. 이 유형에는 플레이스 홀더의 이름과 나타나는 순서가 중요하다.
USING절에 나열하는 바인드 값의 개수는 플레이스 홀더가 사용된 횟수가 아니라 플레이스 홀더명의 개수가 된다.
DECLARE
c_stmt CONSTANT VARCHAR2(1000) :=
Q'<BEGIN
:a := :a + :b;
DBMS_OUTPUT_PUT_LINE('a='||:a||', b='||:b);
END;>';
v_a NUMBER := 2;
v_b NUMBER := 3;
BEGIN
EXECUTE IMMEDIATE c_stmt USING IN OUT v_a, v_b;
END;바인드 변수 v_a를 IN OUT모드로 지정한 이유는 동적SQL문에서 :a의 값이 변경되기 때문이다. 변경되는 바인드 변수를 IN모드로 지정하면 'ORA-06536'오류가 발생할 것이다. v_b는 변경이 없으므로 기본 모드인 IN모드로 사용하였다.
커서 변수를 사용하는 방법
커서 변수에 대해 OPEN, FETCH, CLOSE문을 사용하는 동적SQL은 SELECT문에 대해서만 사용가능하다.
EXECUTE IMMEDIATE에서와 같이 바인드 변수를 사용하기 위해 OPEN문에서 USING절을 사용하며 결과를 변수에 저장하기 위해 FETCH문에서 INTO절을 사용한다.
DECLARE
TYPE empcur_type IS REF CURSOR;
v_emp_cur empcur_type; -- 커서변수
emp_rec emp%ROWTYPE;
v_stmt VARCHAR2(200);
v_empno NUMBER;
BEGIN
-- 실행할 동적 SQL문
v_stmt := 'SELECT * FROM emp WHERE empno = :empno';
v_empno := '7788'; -- 바인드 변수의 값으로 사용할 사번
-- 쿼리문 v_stmt에 대한 v_emp_cur 커서를 OPEN
OPEN v_emp_cur FOR v_stmt USING v_empno;
-- 결과 로우를 한 건씩 FETCH
LOOP
FETCH v_emp_cur INTO emp_rec;
EXIT WHEN v_emp_cur%NOTFOUND;
END LOOP;
-- 사용 완료된 커서를 CLOSE
CLOSE v_emp_cur;
END;DBMS_SQL 내장 패키지를 사용하는 방법
DBMS_SQL 패키지는 동적 SQL의 실행을 위해 오라클에 내장된 패키지이다. DBMS_SQL을 사용하면 DML, TCL, DDL, DCL을 제한 없이 실행할 수 있다. DBMS_SQL을 사용해서 쿼리를 실행하는 과정은 상당히 복잡하지만 대신에 오라클 SQL의 모든 기능을 활용할 수 있어 유연성이 매우 높다.
DBMS_SQL 패키지를 사용하면 먼저 커서를 생성해야 하는데, 커서는 사용을 마치고 닫히기 전에는 다양한 경로로 재실행이 가능하다. 동일한 SQL문에 대해 바인스 변수 값을 변경하여 실행하거나 변경없이 실행하는 것도 가능하고, SQL문을 바꿔서 재실행하는 것도 가능하다 REF CURSOR에서 설명한 약한 커서와 유사하다고 할 수 있다.
DECLARE
v_cursor_id NUMBER;
v_sql_stmt VARCHAR2(4000) := Q'<SELECT *
FROM emp
WHERE deptno = :deptno
AND hiredate >= TO_DATE(:hiredate, 'YYYY-MM-DD')>';
TYPE vc_array IS TABLE OF VARCHAR2(100);
v_bind_var vc_array;
v_bind_val vc_array;
v_ret NUMBER;
v_desc_tab DBMS_SQL.DESC_TAB;
v_col_cnt PLS_INTEGER;
v_str_var VARCHAR2(100);
v_num_var NUMBER;
v_date_var DATE;
v_row_cnt PLS_INTEGER;
BEGIN
--바인드 변수와 값 목록
v_bind_var := vc_array('deptno', 'hiredate'); -- 바인드 변수
v_bind_val := vc_array('10' , '1980-02-20'); -- 바인드 값
-- SQL 커서를 열고 커서 번호를 반환받는다
v_cursor_id := DBMS_SQL.OPEN_CURSOR;
-- SQL 파싱
DBMS_SQL.PARSE(v_cursor_id, v_sql_stmt, DBMS_SQL.NATIVE);
-- 바인드 변수에 값을 바인드
FOR i IN 1 .. v_bind_var.COUNT LOOP
DBMS_SQL.BIND_VARIABLE(v_cursor_id, v_bind_var(i), v_bind_val(i));
END LOOP;
-- SELECT문의 칼럼 정보 가져옴
DBMS_SQL.DESCRIBE_COLUMNS(v_cursor_id, v_col_cnt, v_desc_tab);
-- SELECT될 칼럼을 정의
FOR i IN 1 .. v_col_cnt LOOP
IF v_desc_tab(i).col_type = DBMS_SQL.NUMBER_TYPE THEN -- NUMBER형 컬럼
DBMS_SQL.DEFINE_COLUMN(v_cursor_id, i, v_num_var);
ELSIF v_desc_tab(i).col_type = DBMS.SQL.DATE_TYPE THEN -- 일시간형 칼럼
DBMS_SQL.DEFINE_COLUMN(v_cursor_id, i, v_date_var);
ELSE -- 그 외는 모두 문자형으로 처리
DBMS_SQL.DEFINE_COLUMN(v_cursor_id, i, v_str_var, 100);
END IF;
END LOOP;
-- 커서를 싱행
v_ret := DBMS_SQL.EXECUTE(v_cursor_id);
-- 값을 출력
v_row_cnt := 0;
WHILE DBMS_SQL.FETCH_ROWS(v_cursor_id);
LOOP
v_row_cnt := v_row_cnt + 1;
DBMS_OUTPUT_PUT_LINE(v_row_cnt||'번째 로우');
FOR i IN 1.. v_col_cnt LOOP
IF (v_desc_tab(i).col_type = DBMS_SQL.NUMBER_TYPE) THEN -- NUMBER형 칼럼 값
DBMS_SQL.COLUMN_VALUE(v_cursor_id, i, v_num_var);
DBMS_OUTPUT_PUT_LINE(CHR(9)||rpad(v_desc_tab(i).col_name, 8, ' ')||
' : ' || v_num_var);
ELSIF (v_desc_tab(i).col_type = DBMS_SQL.DATE_TYPE) THEN -- 일시간형 칼럼 값
DBMS_SQL.COLUMN_VALUE(v_cursor_id, i, v_date_var);
DBMS_OUTPUT_PUT_LINE(CHR(9)||rpad(v_desc_tab(i).col_name, 8, ' ')||
' : ' || TO_CHAR(v_date_var, 'YYYY-MM-DD'));
ELSE -- 그 외는 모든 문자형 값
DBMS_SQL.COLUMN_VALUE(v_cursor_id, i, v_str_var);
DBMS_OUTPUT_PUT_LINE(CHR(9)||rpad(v_desc_tab(i).col_name, 8, ' ')||
' : ' || v_str_var);
END IF;
END LOOP;
END LOOP;
-- 커서 CLOSE
DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
END;
-- DBMS_SQL을 이용한 DML문 처리
DECLARE
v_cursor_id NUMBER;
v_sql_stmt VARCHAR2(5000) := Q'<INSERT INTO emp(empno, ename, job, mgr, hiredate,
sal, comm, deptno)
VALUES(:empno, :ename. :job, :mgr, SYSDATE,
:sal, :comm, :deptno)
RETURNING hiredate into :hiredate>';
v_emp emp%ROWTYPE;
v_ret NUMBER;
v_desc_tab DBMS_SQL.DESC_TAB;
v_col_cnt PLS_INTEGER;
v_str_var VARCHAR2(100);
v_num_var NUMBER;
v_date_var DATE;
v_row_cnt PLS_INTEGER;
BEGIN
-- INSERT할 값
v_emp.empno := 7000;
v_emp.ename := '이순신';
v_emp.job := '군인';
v_emp.mgr := NULL;
v_emp.hiredate := NULL; -- hiredate는 SYSDATE를 반환받음
v_emp.sal := 9999;
v_emp.comm := NULL;
v_emp.deptno := 40;
-- 기존 테스트 데이터 삭제
DELETE FROM emp WHERE empno = v_emp.empno;
-- SQL 커서를 열고 커서번호 반환받음
v_cursor_id := DBMS_SQL.OPEN_CURSOR;
-- SQL 파싱
DBMS_SQL.PARSE(v_cursor_id, v_sql_stmt, DBMS_SQL.NATIVE);
-- 바인드 변수에 값을 바인드
DBMS_SQL.BIND_VARIABLE(v_cursor_id, 'empno', v_emp.empno);
DBMS_SQL.BIND_VARIABLE(v_cursor_id, 'ename', v_emp.ename);
DBMS_SQL.BIND_VARIABLE(v_cursor_id, 'job', v_emp.job);
DBMS_SQL.BIND_VARIABLE(v_cursor_id, 'mgr', v_emp.mgr);
DBMS_SQL.BIND_VARIABLE(v_cursor_id, 'hiredate', v_emp.hiredate);
DBMS_SQL.BIND_VARIABLE(v_cursor_id, 'sal', v_emp.sal);
DBMS_SQL.BIND_VARIABLE(v_cursor_id, 'comm', v_emp.comm);
DBMS_SQL.BIND_VARIABLE(v_cursor_id, 'deptno', v_emp.deptno);
-- 커서 실행
v_ret := DBMS_SQL.EXECUTE(v_cursor_id);
-- OUT 변수 값을 받음
DBMS_SQL.VARIABLE_VALUE(v_cursor_id, 'hiredate', v_emp.hiredate); -- RETURNING절의 반환값
DBMS_OUTPUT_PUT_LINE(v_emp.ename|| '의 입사일 : '||TO_CHAR(v_emp.hiredate, 'YYYY-MM-DD'));
DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
END;다음의 설명을 위해 위 예제에서 생성한 테스트 데이터는 삭제하고 다음 설명으로 넘어가자.
동적 PL/SQL
동적 SQL이라고 해서 SQL만 동적으로 실행할 수 있는 것은 아니다. PL/SQL문도 동적으로 실행 가능하다.
동적 PL/SQL은 EXECUTE IMMEDIATE문과 DBMS_SQL 패키지를 사용하여 실행할 수 있다.
EXECUTE IMMEDIATE문을 사용할 때 바인드 변수를 위한 USING절의 사용이 가능하다.
하지만 SELECT문이 아니므로 INTO절은 사용 불가능하다. 동적 PL/SQL의 실행 결과를 변수에 저장할 시에는 OUT모드의 바인드 변수를 사용한다. USING절에 사용되는 변수의 개수는 '익명 PL/SQL문이거나 CALL문일 경우'에서 설명한 것처럼 동적 PL/SQL문에서 사용된 바인드 변수의 개수와 동일해야 한다. 또한 PL/SQL문 내에서 바인드 변수가 처음 나타난 순서대로 USING절에 변수들을 나열해야 하는 것도 잊지 말아야 한다.
DECLARE
v_stmt VARCHAR2(1000);
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_dname dept.dname%TYPE;
BEGIN
-- 실행할 동적 PL/SQL문
-- 사번을 입력으로 하여 사원명과 소속 부서를 출력
v_stmt := 'DECLARE
vv_ename emp.ename%TYPE;
vv_dname dept.dname%TYPE;
BEGIN
DBMS_OUTPUT_PUT_LINE(''조회할 사번 = ''||:empno);
SELECT ename, dname
INTO vv_ename, vv_dname
FROM emp e, dept d
WHERE e.empno = :empno
AND e.deptno = d.deptno;
:ename := vv_ename;
:dname := vv_dname;
END;';
v_empno := 7788;
--동적 PL/SQL문 실행
EXECUTE IMMEDIATE v_stmt
USING IN v_empno, -- 입력변수(IN은 생략가능)
OUT v_ename, -- 출력변수(OUT 필수)
OUT v_dname; -- 출력변수(OUT 필수)
DBMS_OUTPUT_PUT_LINE(v_ename||'의 소속 부서 = '||v_dname);
END;v_stmt에 저장된 PL/SQL문에서 바인드 변수는 :empno, :ename, :dname순서로 나타났으므로 USING절의 변수도 이와 대응되는 순서인 v_empno, v_ename, v_dname 순서로 사용되었다. 동적 PL/SQL에서 바인드 변수인 :empno는 두 번 사용되었지만 USING절에ㅔ서는 한번만 지정되었다. v_empno는 입력 값으로만 사용되므로 IN 모드로 지정되었으며, v_ename과 v_dname은 PL/SQL을 실행한 결과값을 받아오는 변수이므로 OUT을 지정했다.