使用 DBMS_SQL包執行DDL語句
2024-07-21 02:34:25
供稿：網友
使用 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.
