[IT실무고급교육★탑크리에듀]동적 SQL을 이용하여 DDL을 실행(ORACLE DYNAMIC SQL)

동적 SQL을 이용하여 DDL을 실행(ORACLE DYNAMIC SQL) 

 Dynamic SQL을 이용하면  CREATE, DROP, GRANT, REVOKE 등을 사용할 수 있다. 
 ALTER SESSION, SET 명령어등 SCL(Session Control Language)을 사용할 수 있다. 
 Dynamic SQL을 이용하면 SELECT절에 TABLE구를 사용할 수 있다. 

SQL> DROP TABLE x; 

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. 

SQL> select * from x; 
선택된 레코드가 없습니다. 

SQL> desc x; 
 이름                                      널?      유형 
 ----------------------------------------- -------- -------------------------- 
 X                                                  VARCHAR2(1) 

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

-- type_emp라는 사번(empno)과 이름(ename)을 가지는 새로운 타입을 정의, 이타입이 아래 t_emplist라는 테이블 타입의 값으로 들어가는 TYPE이다. 
SQL> CREATE TYPE type_emp AS OBJECT (empno NUMBER, ename VARCHAR2(20)) 
  2  / 
유형이 생성되었습니다. 

-- t_emplist라는 테이블 타입을 선언, 이 타입이 아래 dept_temp 테이블 emps 칼럼의 타입이 된다. 결국 emps 칼럼은 여러 사원들을 담을 수 있는 구조가 된 것이다. 
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