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를 닫는다.


Using the DBMS_SQL Package to Execute DDL statements:
-----------------------------------------------------

< Example 1: >

이 procedure는 table을 create하는 것으로 table name과 column, 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을 사용할 수가 없다.

-------------------------------------------------------------------
위의 예에서 CREATE command 시 SQL*Plus에서는 잘 되지만 dbms_sql 내에서
수행 시 privilege가 없다는 error가 나면 system user가 해당 User에게
CREATE TABLE (VIEW ..) privilege를 주어야 한다.

SQL> GRANT CREATE TABLE TO scott;
-------------------------------------------------------------------


다음은 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: >

이 procedure는 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: >

이 procedure는 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.

Using the DBMS_SQL Package to Execute Dynamic SQL
Statements:
-------------------------------------------------------------

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


< Example 4: >

이 예제는 runtime 시에 주어진 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 clause 전체를 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.


이 예제는 procedure를 생성하는 예제이다

create or replace procedure test(txt varchar2) as
c integer;
rows integer;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c,'create or replace '|| txt ,dbms_sql.native);
rows := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
end;

SQL>exec test('procedure ttum(aa number) AS kk number; BEGIN kk:= 0 ;
kk := kk + aa ; END ; ');

SQL> exec test ('procedure ttt1(aa number) as kk varchar2(10);
begin select 1 into kk from dual; end;');

***********************************************************************
보다 자세한 사항은 Oracle7 Server Documentation Addendum을 참조.
***********************************************************************

Forms 3.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도 사용할 수 없다.

만약, ORA-1031 에러가 발생하면 다음과 같이 해 주십시오.

sys user connect
@?/rdbms/admin/dbmssql.sql
grant create any table to [user_name];
grant execute on dbms_sql to [user_name];
CREATE ANY TABLE 권한은 DBA ROLE에도 포함되어 있지 않습니다.