PL/SQL中如何执行DDL、SCL?

时间:2021-07-14 21:46:56

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.