PL/SQL程序中不能直接执行DDL语句。为什么?
假设我们在pl/sql程序中有这样的一条DDL语句—— drop table emp;在第一次解析pl/sql中的“drop table emp;”这条语句时,emp表存在,我们假设编译成功并执行(事实上pl/sql中直接使用DDL是不能通过的)。但第二次解析的时候,发现emp不存在(已经被删除了),编译不通过——出现错误。
PL/SQL中直接执行DDL报错:
SQL> BEGIN
2 drop table emp;
3 END;
4 /
drop table emp;
*
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00103: Encountered the symbol "DROP" when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
如何在pl/sql中执行DDL呢?
答案是:dynamic SQL
我们可以在PL/SQL program中使用dynamic SQL执行以下类型的语句:
Data definition language (DDL): CREATE, DROP, GRANT, and REVOKE
Session control language (SCL): ALTER SESSION and SET ROLE
The TABLE clause in the SELECT statement
PL/SQL中通过Dynamic sql执行DDL:
SQL> BEGIN
2 EXECUTE IMMEDIATE 'drop table emp';
3 END;
4 /PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_UTILITY.EXEC_DDL_STATEMENT('drop table emp');
3 END;
4 /PL/SQL procedure successfully completed.