DYNAMIC SQL 이란?　　　
　　　
PL/SQL은 Binding이 Compile시에 일어나므로 Database Object의 Name이 Compile시에 고정되어야 하는 등의 제한이
있다.　　　
PL/SQL 2.1(RDBMS 7.1)이후 Version에서는 DBMS_SQL Package로 Dynamic SQL Statement의 사용을 가능하게 한다. 이는
Database Object의 Name을 Runtime에 줄 수 있을 뿐더러 DDL문장을 기술할 수도 있는 장점이 있다.　　　

　* Function Open_Cursor　　　
　 : SQL문의 실행에 필요한 새로운 Cursor를 열고 Cursor ID Number를Return 한다.　　　

　* Function Is_Open　　　
　 : 주어진 Cursor가 현재 Open되어 있으면 TRUE를, 아니면 FALSE를 Return한다.　　　

　* Procedure Parse　　　
　 : Statement를 Check하고 Cursor와 결합시킨다.　　　

　* Procedure Bind_Variable　　　
　 : Program내에서 Data를 저장한 Placeholder의 값을 제공하는 역할을 한다.　　　

　* Procedure Define_Column　　　
　 : Cursor로부터 Select된 Column의 값을 받는 변수를 지정한다.　　　

　* Function Execute　　　
　 : SQL문을 실행하고 처리된 Row의 수를 Return한다. (Insert, Update, Delete인 경우에만　　　
　　　 해당)　　　

　* Function Fetch_Rows　　　
　 : Cursor로부터 Row를 Fetch하고 실제로 Fetch된 Row의 수를 Return한다. 이 Row들은 Buffer에　　　
　　　 들어가며,　 Column_Value를 호출하여 읽어들여야 한다.　　　　

　* Function Execute_And_Fetch　　　
　 : Execute와 Fetch Row를 동시에 수행하고 실제로 Fetch된 Row의 수를 Return한다.　　　

　* Procedure Variable_Value　　　
　 : 주어진 변수의 값을 Return한다.　　　

　* Procedure Column_Value　　　
　 : Fetch_Rows에 의해 Fetch된 Data의 값을 Return한다.　　　

　* Procedure Close_Cursor　　　
　 : Cursor를 닫는다.　　　

　　　　
l Using The DBMS_SQL Package To Execute DDL Statements:　　　

< Example 1 >　　　
　　 Table을 Create하는 Procedure로 Table Name, Column Name과 그Type을Parameter로 받는다.　　　

CREATE OR REPLACE PROCEDURE ddlproc (tablename varchar2,　　　　
cols varchar2) AS　　　
　 cursor1 INTEGER;　　　
BEGIN　　　
　 cursor1 := dbms_sql.open_cursor;　　　
　 dbms_sql.parse(cursor1, 'CREATE TABLE ' || tablename ||　　　
　　　 ' ( ' || cols || ' )', dbms_sql.v7);　　　
　 dbms_sql.close_cursor(cursor1);　　　
end;　　　
/　　　
　　　　
SQL> execute ddlproc ('MYTABLE','COL1 NUMBER, COL2 VARCHAR2(10)');　　　
　　　　
PL/SQL procedure successfully completed.　　　
　　　　
SQL> desc mytable;　　　
　Name　　　　　　　　　　　　　　　　　　　　　　　　　　　 Null?　 Type　　　
　-------------------------- ------ ------------　　　
　COL1　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 NUMBER　　　
　COL2　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 VARCHAR2(10)　　　
　　　　
　 DDL Statement는 Parse Command에 의해 수행된다. 그러므로 DDL Statement에서는 Bind Variable을 사용할 수가
없다.　　　

다음은 DDL Statement내에 Bind Variable을 사용한 잘못된 예이다.　　　

CREATE OR REPLACE PROCEDURE ddlproc (tablename　　　　
VARCHAR2, colname　　 VARCHAR2,　 coltype　　 VARCHAR2)　　　　
AS　　　
　 cursor1 INTEGER;　　　
　 ignore　 INTEGER;　　　
BEGIN　　　
　 cursor1 := dbms_sql.open_cursor;　　　
　 dbms_sql.parse(cursor1,'CREATE TABLE :x1 (:y1 :z1)',　　　　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
dbms_sql.v7);　　　
　 dbms_sql.bind_variable(cursor1, ':x1', tablename);　　　
　 dbms_sql.bind_variable(cursor1, ':y1', colname);　　　
　 dbms_sql.bind_variable(cursor1, ':z1', coltype);　　　
　 ignore := dbms_sql.execute(cursor1);　　　
　 dbms_sql.close_cursor(cursor1);　　　
end;　　　
/　　　

　Procedure를 create했을때에는 error를 만나지 않았지만, runtime에는 "ORA-00903: invalid table name" 이라는 error가
난다.　　　
　　　　
SQL> execute ddlproc ('MYTABLE', 'COL1', 'NUMBER');　　　

begin ddlproc ('MYTABLE', 'COL1', 'NUMBER'); end;　　　
　　　　
*　　　
ERROR at line 1:　　　
ORA-00903: invalid table name　　　
ORA-06512: at "SYS.DBMS_SYS_SQL", line 239　　　
ORA-06512: at "SYS.DBMS_SQL", line 25　　　
ORA-06512: at "SCOTT.DDLPROC", line 8　　　
ORA-06512: at line 1　　　
　　　　

< Example 2 >　　　
　 Table을 Drop하는 Procedure로 Table Name을 Parameter로 받는다.　　　
　　　　

create or replace procedure droptable (table_name varchar2) as　　　
　 cursor1 integer;　　　
begin　　　
　 cursor1 := dbms_sql.open_cursor;　　　
　 dbms_sql.parse(cursor1, 'DROP TABLE ' || table_name,　　　　
　　　　　　　　　　　　　　　　　　　　　　　　　　 dbms_sql.v7);　　　
　 dbms_sql.close_cursor(cursor1);　　　
end;　　　
/　　　
　　　　
SQL> begin　　　
　 2　　　 droptable('MYTABLE');　　　
　 3　 end;　　　
　 4　 /　　　
　　　　
PL/SQL procedure successfully completed.　　　

　　　　
< Example 3 >　　　
　 DDL Statemenet를 수행하는 Procedure로 DDL Statement자체를　 Parameter로 받는다.　　　
　　　　
create procedure anyddl (s1 varchar2) as　　　
　 cursor1 integer;　　　
begin　　　
　 cursor1 := dbms_sql.open_cursor;　　　
　 dbms_sql.parse(cursor1, s1, dbms_sql.v7);　　　
　 dbms_sql.close_cursor(cursor1);　　　
end;　　　
/　　　
　　　　
SQL> execute anyddl('CREATE TABLE MYTABLE (COL1 NUMBER)');　　　
　　　　
PL/SQL procedure successfully completed.　　　
　　　　
SQL> desc mytable;　　　
　Name　　　　　　　　　　　　　　　　　　　　　　　　　　　 Null?　　 Type　　　
　-------------------------　　 -----　　 -----------　　　
　COL1　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 NUMBER　　　
　　　　
SQL> execute anyddl('drop table mytable');　　　
　　　　
PL/SQL procedure successfully completed.　　　
　　　　
　　 l Using the DBMS_SQL Package to Execute Dynamic SQL Statements:　　　

　DBMS_SQL package는 dynamic SQL statement를 수행하는데 이용되어 질 수 있는데 이는 runtime전에 statement의 일부분
혹은 전체를 알 수 없는 경우에 쓰여진다.　　　

　　　　
< Example 4 >　　　
　 이 예제는 Run Time시에 주어진 Number보다 더 높은 Employee Number를 가진 모든 Employee 의 이름과
Employee Number를 Return한다.　　　
　　　　
CREATE or REPLACE PROCEDURE rows_greater_than (low_value　　　　
　　　 number) AS　　　
　 cursor1　 integer;　　　
　 rows_processed　 integer;　　　
　 myempno number;　　　
　 myename varchar2(20);　　　
BEGIN　　　
　 cursor1 := dbms_sql.open_cursor;　　　
　 dbms_sql.parse (cursor1, 'select empno, ename from emp　　　　
　　　　　　　　 where empno > :x', dbms_sql.v7);　　　
　 dbms_sql.bind_variable(cursor1, 'x', low_value);　　　
　 dbms_sql.define_column (cursor1, 1, myempno);　　　
　 dbms_sql.define_column (cursor1, 2, myename, 20);　　　
　 rows_processed := dbms_sql.execute (cursor1);　　　
　 loop　　　
　　　 if dbms_sql.fetch_rows (cursor1) > 0 then　　　
　　　　　 dbms_sql.column_value (cursor1, 1, myempno);　　　
　　　　　 dbms_sql.column_value (cursor1, 2, myename);　　　
　　　　　 dbms_output.put_line(to_char(myempno) || '　　 ' || myename);　　　
　　　 else　　　
　　　　　 exit;　　　
　　　 end if;　　　
　 end loop;　　　
　 dbms_sql.close_cursor (cursor1);　　　
EXCEPTION　　　
　 WHEN OTHERS THEN　　　
　　　 dbms_output.put_line(sqlerrm);　　　
　　　 if dbms_sql.is_open (cursor1) then　　　
　　　　　 dbms_sql.close_cursor (cursor1);　　　
　　　 end if;　　　
END;　　　
/　　　
　　　　
　 DBMS_OUTPUT package을 사용하기 전에 먼저 SET SERVEROUTPUT ON command를 사용하여야 한다.　　　
　　　　
SQL> set serveroutput on　　　
SQL> execute rows_greater_than(7500);　　　
7521　　 WARD　　　
7566　　 JONES　　　
7654　　 MARTIN　　　
7698　　 BLAKE　　　
7782　　 CLARK　　　
7788　　 SCOTT　　　
7839　　 KING　　　
7844　　 TURNER　　　
7876　　 ADAMS　　　
7900　　 JAMES　　　
7902　　 FORD　　　
8100　　 MILLER　　　
　　　　
PL/SQL procedure successfully completed.　　　
　　　　
SQL> execute rows_greater_than(8000);　　　
8100　　 MILLER　　　
　　　　
PL/SQL procedure successfully completed.　　　
　　　　

< Example 5 >　　　
　이 예제는 Where Clause의 Parameter를 Column_Name과 Operator(<,<=,=,>=,>), New_Value로 받아서
Employee 이름과 Number를 Return한다.　　　

　　　　
CREATE or REPLACE PROCEDURE get_rows (column_name　　　　
varchar2,　 comparison_type varchar2, new_value number)　　　
　AS　　　
　 cursor1　 integer;　　　
　 rows_processed　 integer;　　　
　 myempno number;　　　
　 myename varchar2(20);　　　
BEGIN　　　
　 cursor1 := dbms_sql.open_cursor;　　　
　 dbms_sql.parse (cursor1, 'select empno, ename from emp　　　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 where ' || column_name ||　　　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ' ' || comparison_type || ' :x',　　　
　　　　　　　　　　　　　　　　　　　　　 dbms_sql.v7);　　　
　 dbms_sql.bind_variable(cursor1, 'x', new_value);　　　
　 dbms_sql.define_column (cursor1, 1, myempno);　　　
　 dbms_sql.define_column (cursor1, 2, myename, 20);　　　
　　　 rows_processed := dbms_sql.execute (cursor1);　　　
　 loop　　　
　　　 if dbms_sql.fetch_rows (cursor1) > 0 then　　　
　　　　　 dbms_sql.column_value (cursor1, 1, myempno);　　　
　　　　　 dbms_sql.column_value (cursor1, 2, myename);　　　
　　　　　 dbms_output.put_line(to_char(myempno) || '　　 ' || myename);　　　
　　　 else　　　
　　　　　 exit;　　　
　　　 end if;　　　
　 end loop;　　　
　 dbms_sql.close_cursor (cursor1);　　　
EXCEPTION　　　
　 WHEN OTHERS THEN　　　
　　　 dbms_output.put_line(sqlerrm);　　　
　　　 if dbms_sql.is_open (cursor1) then　　　
　　　　　 dbms_sql.close_cursor (cursor1);　　　
　　　 end if;　　　
END;　　　
/　　　
　　　　
SQL> begin　　　
　 2　　　 get_rows('EMPNO', '<', 2000);　　　
　 3　 end;　　　
　 4　 /　　　
1111　　　
　　　　
PL/SQL procedure successfully completed.　　　
　　　　
SQL> execute get_rows('SAL', '>', 3000);　　　
7566　　 JONES　　　
7788　　 SCOTT　　　
7839　　 KING　　　
7902　　 FORD　　　
　　　　
PL/SQL procedure successfully completed.　　　
　　　　
SQL> begin　　　
　 2　　　 get_rows('DEPTNO', '>=', 20);　　　
　 3　 end;　　　
　 4　 /　　　
7369　　 SMITH　　　
7499　　 ALLEN　　　
7521　　 WARD　　　
7566　　 JONES　　　
7654　　 MARTIN　　　
7698　　 BLAKE　　　
7788　　 SCOTT　　　
7844　　 TURNER　　　
7876　　 ADAMS　　　
7900　　 JAMES　　　
7902　　 FORD　　　
　　　　
PL/SQL procedure successfully completed.　　　
　　　　
< Example 6 >　　　
　　 이 예제는 Where Clase 전체를 Runtime시에 Parameter로 받는다.　　　
　　　　
CREATE or REPLACE PROCEDURE get_rows (where_clause varchar2) AS　　　
　 cursor1　 integer;　　　
　 rows_processed　 integer;　　　
　 myempno number;　　　
　 myename varchar2(20);　　　
BEGIN　　　
　 cursor1 := dbms_sql.open_cursor;　　　
　 dbms_sql.parse (cursor1, 'select empno, ename from emp where '　　　　
　　　　　　　　　　　　　　 || where_clause, dbms_sql.v7);　　　
　 dbms_sql.define_column (cursor1, 1, myempno);　　　
　 dbms_sql.define_column (cursor1, 2, myename, 20);　　　
　　　 rows_processed := dbms_sql.execute (cursor1);　　　
　 loop　　　
　　　 if dbms_sql.fetch_rows (cursor1) > 0 then　　　
　　　　　 dbms_sql.column_value (cursor1, 1, myempno);　　　
　　　　　 dbms_sql.column_value (cursor1, 2, myename);　　　
　　　　　 dbms_output.put_line(to_char(myempno) || '　　 ' || myename);　　　
　　　 else　　　
　　　　　 exit;　　　
　　　 end if;　　　
　 end loop;　　　
　 dbms_sql.close_cursor (cursor1);　　　
EXCEPTION　　　
　 WHEN OTHERS THEN　　　
　　　 dbms_output.put_line(sqlerrm);　　　
　　　 if dbms_sql.is_open (cursor1) then　　　
　　　　　 dbms_sql.close_cursor (cursor1);　　　
　　　 end if;　　　
END;　　　
/　　　
　　　　
SQL> execute get_rows('ENAME = ''KING'' ');　　　
7839　　 KING　　　
　　　　
PL/SQL procedure successfully completed.　　　
　　　　
SQL> execute get_rows('SAL > 1000 AND DEPTNO = 10');　　　
7782　　 CLARK　　　
7839　　 KING　　　
8100　　 MILLER　　　
　　　　
PL/SQL procedure successfully completed.　　　
　　　　
< Example 7 >　　　
　 이 예제는 Non-Query SQL Statement를 실행한다.　　　
　　　　
create procedure anysql (s1 varchar2) as　　　
　 cursor1 integer;　　　
　 return_value integer;　　　
begin　　　
　 cursor1 := dbms_sql.open_cursor;　　　
　 dbms_sql.parse(cursor1, s1, dbms_sql.v7);　　　
　 return_value := dbms_sql.execute(cursor1);　　　
　 dbms_sql.close_cursor(cursor1);　　　
end;　　　
/　　　
　　　　
　　　　
SQL> execute anysql('CREATE TABLE MYTABLE (COL1 number, 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 col2  varchar2(3))');　　　
　　　　
PL/SQL procedure successfully completed.　　　
　　　　
SQL　　　　　　　　　　　　　　　　　　　　
SQL> desc mytable;　　　
　Name　　　　　　　　　　　　　　　　　　　　　　　　　　　 Null?　　　 Type　　　
　-------------------------- ------- -----------　　　
　COL1　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 NUMBER　　　
　COL2　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 VARCHAR2(3)　　　
　　　　
SQL> execute anysql('INSERT INTO MYTABLE VALUES(1, ''ABC'')');　　　
　　　　
PL/SQL procedure successfully completed.　　　
　　　　
SQL> begin　　　
　 2　　　 anysql(　　　
　 3　　　　　 'declare　　　
　 4　　　　　　　　 var1 varchar2(3);　　　
　 5　　　　　　 begin　　　
　 6　　　　　　　　 select col2　　　
　 7　　　　　　　　　　 into var1　　　
　 8　　　　　　　　　　 from mytable　　　
　 9　　　　　　　　　　 where col1 = 1;　　　
　10　　　　　　　　 dbms_output.put_line(''var1 = '' || var1);　　　
　11　　　　　　 end;');　　　
　12　 end;　　　
　13　 /　　　
var1 = ABC　　　
　　　　
PL/SQL procedure successfully completed.　　　
　　　　
　　　　

　　　　　
　　 더 자세한 사항은 Oracle7 Server Documentation Addendum을 참조.　　　

　 n Forms3.0은 PL/SQL 1.1만을 지원하므로 dynamic SQL을 쓸 수 없으며 stored procedure를 만들어 호출해서 사용해야
한다. 이때 Forms가 부른 stored procedure나 function은 그 내에서 commit을 사용할 수 없게 되어 있기 때문에 COMMIT이나
ROLLBACK을 만나면 ORA-00034: Commit and Rollback from PL/SQL disabled for this session이란 error가 난다. 따라서 DDL
statement도 사용을 할 수 없다.