PL/SQL与DDL语句

 

 

PL/SQL是我们在Oracle环境下进行数据处理的好工具。相对于集合操作方式的SQL语句，PL/SQL则是面向过程化的开发语言，实际工作中的便利性更好。

在PL/SQL代码里面，我们可以同时使用SQL语句和过程化的PL/SQL代码，两者交替使用。Oracle在处理PL/SQL代码过程中，也是使用SQL引擎和PL/SQL引擎交替处理。

一个朋友在开发中遇到报错内容，咨询笔者。这个问题涉及到PL/SQL代码中使用哪些SQL语句。记录下来，备有需要的朋友查询。

1、问题综述

这个朋友要写一个处理脚本，在PL/SQL语句中实现对多个数据表进行DDL操作。语句代码结构如下：

--

declare

  v_conn varchar2(50);

begin

 

  for i in 1 .. 18 loop 

    xxxx 

    --sql block 

    alter table table_1 modify ACCOUNT_NUM VARCHAR2(60);

    alter table table_2 modify ACCOUNT_NUM VARCHAR2(60);

  end loop; 

end;

/

执行之后，报错如下：

ORA-06550: 第 54 行, 第 5 列:

PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:

 

   ( begin case declare end exit for goto if loop mod null

   pragma raise return select update while with

   <<

   continue close current delete fetch lock insert open rollback

   savepoint set sql execute commit forall merge pipe purge

提示信息非常复杂，而且涉及的内容比较混乱。

2、问题分析

这个问题的关键在于，在PL/SQL代码中可以使用DDL语句吗？SQL语句严格划分分为DML、DDL、Transaction Control和DCL（Data Control Language）。其中，DML可以拆出select作为单独的检索语句类型。

我们的PL/SQL语句，运行的环境虽然是命令行结构，但是处理单元确是PL/SQL引擎。能否使用DDL语句，关键在于PL/SQL语句的处理。

下面通过一系列实验来证明结论。首先创建实验数据表T。

SQL> create table t as select * from dba_objects where 1=0;

Table created

执行一段类似的PL/SQL代码。

SQL> declare

  2  begin

  3    alter table t modify object_id number(100);

  4  end;

  5  /

declare

begin

  alter table t modify object_id number(100);

end;

ORA-06550: 第 3 行, 第 3 列:

PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:

   ( begin case declare exit for goto if loop mod null pragma

   raise return select update while with

   <<

   continue close current delete fetch lock insert open rollback

   savepoint set sql execute commit forall merge pipe purge

注意，这个报错信息和朋友的代码片段报错相同。显然，实验的结论是不能在PL/SQL中直接使用DDL语句。

这时候，笔者突发奇想。作为一种特殊的DDL语句，truncate table命令如何呢？

SQL>

declare

begin

  --alter table t modify object_id number(100);

  truncate table t;

end;

ORA-06550: 第 4 行, 第 12 列:

PLS-00103: Encountered the symbol "TABLE" when expecting one of the following:

   := . ( @ % ;

The symbol ":= was inserted before "TABLE" to continue.

看来，在PL/SQL语句中，不能直接的发出DDL语句。

3、一种变通方法

那么，是不是就没有办法了呢？还是有一些变通的途径的。我们程序语言的处理，分为Compile和Runtime两个阶段。很多的语法、语义、甚至权限错误，都是在Compile阶段完成的。我们DDL语句报错，其实就是Oracle感觉输入语句不符合PL/SQL代码规则才报错。

一种手段就是绕开Compile阶段，直接进入到Runtime阶段。我们可以使用execute immediate命令。

SQL> declare

  2  begin

  3    execute immediate 'alter table t modify object_id number(10)';

  4  end;

  5  /

PL/SQL procedure successfully completed

命令执行成功。

4、结论

使用execute immediate语句，就是让DDL语句逃离开编译时Compile的检验过程。这种方法虽然实现了我们的功能，但是注意，这种代码没有在Compile阶段进行验证，是可能出现Runtime异常报错的。

另一方面，我们可以思考一下为什么Oracle禁止在PL/SQL中使用DDL语句。从变通手段来看，PL/SQL对应的两个引擎实际是可以处理好的，为什么要拒绝这种语法呢？

从语法层面的拒绝，实际上是杜绝风险的出现。在笔者之前的系列Blog中，讨论过DDL语句在语句序列中对于程序事务Transaction的影响。在一个事务中，如果出现一个DDL语句（特别容易出现truncate table），即使这个DDL语句涉及的数据对象和事务无关，也是会有一个潜在的提交动作。这就破坏了我们程序代码事务一致性要求。如下实验：

SQL> create table t_1 as select * from dba_objects;

Table created

SQL> set serveroutput on

SQL> declare

  2    i number;

  3  begin

  4    select count(*) into i

  5    from t;

  6 

  7    dbms_output.put_line('Before i is '||to_char(i));

  8    insert into t select * from dba_objects;

  9 

 10    execute immediate 'truncate table t_1';

 11 

 12    rollback; --已经rollback了！

 13 

 14    select count(*) into i

 15    from t;

 16 

 17    dbms_output.put_line('After i is '||to_char(i));

 18  end;

 19  /

Before i is 0

After i is 72758

PL/SQL procedure successfully completed

上面代码告诉我们，即使我们没有commit事务，即使我们rollback了数据，最后数据还是被提交，事务还是被破坏。

所以，在PL/SQL代码中，慎用DDL。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-776894/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-776894/