#108. PL/SQL강좌, 동적SQL(Dynamic SQL)이란? 개요,정의,동적 SQL을 이용하여 DDL을 실8행,PLSQL교육

7. Dynamic SQL

7.1 Dynamic SQL 이란?

<!--[if !supportLists]-->n  <!--[endif]-->PL/SQL은 Binding이 Compile시에 일어나므로 Database Object의 Name이 Compile시에 고정되어야 하는 등의 제한이 있지만 Dynamic SQL을 사용한다면 SQL문의 최종완성을 런타임중에 할 수 있다는 장점이 있다. 결국 동적으로 SQL문을 만들어 내는 것이다.

<!--[if !supportLists]-->n  <!--[endif]-->DBMS_SQL Package로 Dynamic SQL문의 사용을 가능하게 했는데 이는 Database Object의 Name을 Runtime에 줄 수 있을 뿐 아니라 DDL문장을 기술할 수도 있는 장점이 있다.　　

<!--[if !supportLists]-->n  <!--[endif]-->다음과 같은 경우 Dynamic SQL을 사용한다.

- DDL을 사용하는 경우

- SQL문장을 동적으로 만들어 내는 경우(검색조건, 정렬조건, 힌트구문)

    - 컴파일타임에 존재하지 않는 DB Object를 참조하는 경우(테이블의 이름을 동적으로 생성)

- PL/SQL블록에서 다른 프로시저등을 호출하는 경우

7.1.1 동적 SQL을 이용하여 DDL을 실행

<!--[if !supportLists]-->n  <!--[endif]-->Dynamic SQL을 이용하면  CREATE, DROP, GRANT, REVOKE 등을 사용할 수 있다.

<!--[if !supportLists]-->n  <!--[endif]-->ALTER SESSION, SET명령어등 SCL(Session Control Language)을 사용할 수 있다.

<!--[if !supportLists]-->n  <!--[endif]-->Dynamic SQL을 이용하면 SELECT절에 TABLE구를 사용할 수 있다.

SQL> DECLARE

  2  BEGIN

  3      EXECUTE IMMEDIATE 'create table x ( x varchar2(1))' ;

  4

  5      DBMS_OUTPUT.PUT_LINE('Table Created.');

  6  END;

  7  /

Table Created.

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select * from x;

선택된 레코드가 없습니다.

SQL> desc x;

 이름                                      널?      유형

 ----------------------------------------- -------- --------------------------

 X                                                  VARCHAR2(1)

-- 아래는 동적SQL을 이용하여 SELECT절에 TABLE구를 사용하는 예제이다.

SQL> SET SERVEROUTPUT ON

SQL> CREATE TYPE type_emp AS OBJECT (empno NUMBER, ename VARCHAR2(20))

  2  /

유형이 생성되었습니다.

SQL> CREATE TYPE t_emplist AS TABLE OF type_emp

  2  /

유형이 생성되었습니다.

SQL> CREATE TABLE dept_temp (deptno NUMBER, emps t_emplist)

  2      NESTED TABLE emps STORE AS emp_table;

테이블이 생성되었습니다.

SQL> INSERT INTO dept_temp VALUES (

  2      10,

  3      t_emplist(

  4          type_emp(1, 'SCOTT'),

  5          type_emp(2, 'TIGER')));

1 개의 행이 만들어졌습니다.

SQL> INSERT INTO dept_temp VALUES (

  2      20,

  3      t_emplist(

  4          type_emp(1, 'JCLEE'),

  5          type_emp(2, 'ORACLEJAVACOMMUNITY')));

1 개의 행이 만들어졌습니다.

SQL> DECLARE

  2      v_deptno dept.deptno%TYPE;

  3      v_empno emp.empno%TYPE;

  4      v_ename  emp.ename%TYPE;

  5  BEGIN

  6      -- 아래 TABLE구는 동적SQL로만 실행 가능하다.

  7      EXECUTE IMMEDIATE 'SELECT d.deptno, e.empno, e.ename

  8                         FROM dept_temp d, TABLE(d.emps) e

  9                         WHERE d.deptno = 10 AND e.empno = 1'

 10              INTO v_deptno, v_empno, v_ename;

 11      DBMS_OUTPUT.PUT_LINE(v_deptno || ':' || v_empno || ':' || v_ename);

 12  END;

 13  /

10:1:SCOTT

PL/SQL 처리가 정상적으로 완료되었습니다.