过程中动态SQL执行DDL

SQL> select object_name,status from user_objects where object_type='PROCEDURE';
SQL> col object_name for a30;
SQL> /

OBJECT_NAME STATUS
------------------------------ -------
AWEN_TEST VALID
AWEN_TEST_CREATE VALID
AWEN_TEST_DROP VALID

SQL> select text from all_source where owner='AWEN' and name=upper('awen_test');


TEXT
--------------------------------------------------------------------------------

procedure awen_test
as
v_sql varchar2(600);
vrow int;
i int;
l_rowcount number;
begin
v_sql:='truncate table a1';
vrow:=dbms_sql.open_cursor;
dbms_sql.parse(vrow,v_sql,dbms_sql.V7);
i:=dbms_sql.execute(vrow);
end awen_test;

SQL> select count(*) from a1;

COUNT(*)
----------
1

SQL> exec awen_test;

PL/SQL 过程已成功完成。

SQL> select count(*) from a1;

COUNT(*)
----------
0

SQL> select text from all_source where owner='AWEN' and name=upper('awen_test_dr
op');

TEXT
--------------------------------------------------------------------------------

procedure awen_test_drop
as
v_sql varchar2(600);
vrow int;
i int;
l_rowcount number;
begin
v_sql:='drop table a2';
vrow:=dbms_sql.open_cursor;
dbms_sql.parse(vrow,v_sql,dbms_sql.V7);
i:=dbms_sql.execute(vrow);
end awen_test_drop;


SQL> select count(*) from a1;

COUNT(*)
----------
0

SQL> desc a2;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------

ID NUMBER(8)

SQL> exec awen_test_drop;

PL/SQL 过程已成功完成。

SQL> desc a2;
ERROR:
ORA-04043: 对象 a2 不存在