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