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。