15일차 # 4-3: SQL Statements in PL/SQL

  * PL/SQL Static (Embedded) SQL: PL/SQL 내에서 사용 가능

- DML, TCL

  * PL/SQL Dynamic SQL: PL/SQL 내에서 직접 사용 불가능

- DDL, DCL

- 간접 사용: 1) EXECUTE IMMEDIATE '...'

    2) DMBS_SQL package ==> http://goo.gl/yksD5

  * Binding

- Using DDL and Dynamic SQL ==> http://goo.gl/D7TyW

- 1) Resolve references to Oracle objects

 2) Allocate storage Memory

 3) Assign storage address to program variables

- binding at compile time vs. binding at run time

 early/static binding    vs. late/dynamic binding

 efficiency              vs. flexibility

  * PL/SQL compile

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

|    PARSE    |

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

    ↓ ↓

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

|    BIND     |   <== bind variables

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

    ↓ ↓

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

|   EXECUTE   |

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

    ↓ ↓

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

|    FETCH    |   <== select statement

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

- Static SQL  : Parse + Bind ==> Compile-Time

               Execute + Fetch ==> Run-Time

  - Dynamic SQL : All Phases ==> Run-Time

   ==============================

drop table t1 purge;

create table t1 (no number);

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

create or replace procedure dynamic_sql is

 v_cnt number;

begin

 execute immediate 'drop table t1 purge';

 execute immediate 'create table t1 (no number)'; --> ORA-01031: 권한이 불충분합니다

--  : CREATE ANY TABLE 권한이 없기 때문

 for i in 1..100 loop

   insert into t1 values (i);

 end loop;

 commit;

 select count(*) into v_cnt

 from t1;

 p(v_cnt);

end;

/

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

exec dynamic_sql

--> ORA-01031: 권한이 불충분합니다

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

  <해결방법>: DBA가 CREATE ANY TABLE 권한을 사용자에게 부여

conn system/????@211.183.3.52:1521/orcl

show user

grant create any table to top01, top02, top03, top04, top05

, top06, top07, top08, top09, top10

, top11, top12, top20;

conn top??/top??@211.183.3.52:1521/orcl

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

set serveroutput on

exec dynamic_sql

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

select count(*) from t1;

  * Multi-Tx in One Block, Multi-Block in One Tx

set serveroutput on

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

declare

 v_cnt     number;

 v_sum_no  number;

begin

 select count(*) into v_cnt

 from t1;

 p(v_cnt);

 begin

   insert into t1 values (101);

   select sum(no) into v_sum_no

   from t1;

 end;

 p(v_cnt);

 p(v_sum_no);

 begin

   begin

     v_sum_no := v_sum_no + v_cnt;

   end;

   commit;

   begin

     select count(*) into v_cnt

     from t1;

     p(v_cnt);

   end;

 end;

end;

/

  문제) 이름을 저장하는 T_NAME 테이블을 생성하고 자신의 이름을 INSERT 하는

        dynamic sql을 포함하는 프로시저를 작성하여 실행하고

select 문을 이용하여 확인해 보세요.

create table t_name(no number);

create or replace procedure create_t_name

is

begin

 -- 테이블 생성

 execute immediate 'drop table t_name purge';

 execute immediate 'create table t_name (irum varchar2(10))';

 -- 이름 삽입

 insert into t_name values ('이순신');

end;

/

select * from t_name;