ITPub博客

首页 > Linux操作系统 > Linux操作系统 > PL/SQL与DDL语句

PL/SQL与DDL语句

原创 Linux操作系统 作者:realkid4 时间:2013-11-18 22:11:09 0 删除 编辑
 

 

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语句严格划分分为DMLDDLTransaction ControlDCLData 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
、一种变通方法


那么,是不是就没有办法了呢?还是有一些变通的途径的。我们程序语言的处理,分为
CompileRuntime两个阶段。很多的语法、语义、甚至权限错误,都是在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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7631622