使用 DBMS_SQL包执行DDL语句
----------------------------------------------------
The DBMS_SQL package can be used to execute DDL statements directly from PL/SQL.
例 1:
这是一个创建一个表的过程的例子。该过程有两个参数：表名和字段及其类型的列表。
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语句是由Parese命令执行的。因此，不能对DDL语句使用bind变量，否则你就会受到一个错误信息。
下面的在DDL语句中使用bind变量的例子是错误的。
**** Incorrect Example ****
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;
/
虽然在过程创建时，没有错误信息。但在运行时，你将得到错误信息"ORA-00903: invalid table name" 。
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
例2:
删除表的过程。使用表明作为参数。
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.
例 3:
执行任何DDL语句的过程。以DDL语句为参数。
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.