從一個案例看PL/SQL代碼片的編譯與運行

從一個案例看PL/SQL代碼片的編譯與運行

PL/SQL語言是Oracle針對資料庫商務邏輯需求開發的一種面向過程的結構化編程語句。在Oracle核心中，存在PL/SLQ引擎和SQL引擎兩個重要組成部分，分別用於處理結構化的PL/SQL語句和SQL語句。

同所有進階語言一樣，PL/SQL語句同樣存在編譯和運行兩個關鍵步驟。在Compile環節，主要實現文法許可權檢查、對象方法檢查和文法結構檢查。在運行Runtime階段，相同的過程其實還是會進行，一些語句錯誤都是在運行時發生檢測。

本篇主要通過一個錯誤案例，詳細分析一下兩者的特點和差異。

1、問題說明

朋友向筆者諮詢一個問題，為什麼寫好的PL/SQL匿名塊在執行狀態時，報錯沒有被exception所捕獲。說明：基於資料保護原因，程式碼片段為類比版本。

實驗環境為11gR2，具體版本號碼為11.2.0.4。

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0    Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

類比程式碼片段如下：

SQL> declare

  2    i varchar2(10);

  3  begin

  4    select err

  5    into i 

  6    from t

  7    where rownum<2;

  8  exception

  9    when others then

 10      dbms_output.put_line('Errors Catch!');

 11  end;

 12  /

  select err

        *

ERROR at line 4:

ORA-06550: line 4, column 10:

PL/SQL: ORA-00904: "ERR": invalid identifier

ORA-06550: line 4, column 3:

PL/SQL: SQL Statement ignored

朋友的問題是，資料表T的確沒有err列，但是執行的時候為什麼沒有在exception部分被捕獲，而是直接報錯了呢？

資料表T的描述如下：

SQL> desc t;

Name    Type              Nullable Default Comments 

------- ------------------ -------- ------- -------- 

USER_ID VARCHAR2(100 BYTE) Y             

2、解析與測試

這個問題直觀看比較唬人，但是仔細分析起來還是有概念障礙的問題。這就是程式的編譯時間和運行時。

我們編寫PL/SQL程式的時候，無論是procedure還是package，都有一個顯示compile的動作，成功之後我們才可以exec執行程式。在compile的時候，所進行的工作對於任何語言編譯器來講，都是差不多的。

常見的大多數都是驗證和校正，比如使用變數是否定義？該使用者有無使用對象、變數許可權？變數可見度範圍？或者文法使用是否正確？經過compile之後，編譯器會認為程式已經具備了執行的前提條件，會將其轉化為目標對象。

但是，經過編譯的程式，執行過程（也就是運行時Runtime）就萬無一失嗎？肯定不是，記憶體、CPU、磁碟的資源約束，內在邏輯操作的錯誤、數字運算的錯誤都會引起一系列的運行時故障。我們說，PL/SQL中的exception，就是針對runtime的錯誤而言的。

一種假設：錯誤使用的是一個匿名塊。在執行過程中，實際上是編譯和運行兩步走的情況。程式還沒有進入runtime階段，都被編譯compile發現了錯誤，拋了出來。

我們通過一系列簡單實驗，來證明結論。首先，建立一個單獨的預存程序，不執行只是編譯，看結果如何？

SQL> create or replace procedure TEST

  2  as

  3    i varchar2(10);

  4  begin

  5    select err

  6    into i 

  7    from t

  8    where rownum<2;

  9  exception

 10    when others then

 11      dbms_output.put_line('Errors Catch!');

 12  end;

 13  /

Warning: Procedure created with compilation errors.

編譯錯誤，通過show error顯示：

SQL> show error             

Errors for PROCEDURE TEST:

LINE/COL ERROR

-------- -----------------------------------------------------------------

5/3      PL/SQL: SQL Statement ignored

5/10    PL/SQL: ORA-00904: "ERR": invalid identifier

錯誤資訊和匿名塊的完全相同。此時並沒有進入運行時，可以推想匿名塊的報錯是在編譯時間發生的。

那麼怎麼能夠迴避編譯時間檢查，將這個錯誤“送到”運行時，讓exception捕獲到呢？我們可以使用字串。

SQL> create or replace procedure TEST

  2  as

  3    i varchar2(10);

  4  begin

  5    execute immediate 'select err from t where rownum<2'

  6            into i;

  7 

  8  exception

  9    when others then

 10      dbms_output.put_line('Errors Catch!');

 11  end;

 12  /

Procedure created.

execute immediate可以直接執行字串類SQL語句。該預存程序成功建立，說明PL/SQL引擎沒有進行針對字串的檢查。

執行：

SQL> set serveroutput on;

SQL> exec test;

Errors Catch!

PL/SQL procedure successfully completed.

果然可以被exception捕獲，同預想效果相同。根據這個思路，修改朋友的代碼如下：

SQL> declare

  2    i varchar2(10);

  3  begin

  4    execute immediate 'select err from t where rownum<2'

  5            into i;

  6             

  7  exception

  8    when others then

  9      dbms_output.put_line('Errors Catch!');

 10  end;

 11  /

Errors Catch!

PL/SQL procedure successfully completed.

順利通過編譯Compile檢查，到Runtime時候報錯。

3、結論

PL/SQL語句是我們進行資料庫結構化、過程化處理的重要工具。應該意識到，PL/SQL本身也是一種程式設計語言，都需要Compile和Runtime階段，每個階段有不同的任務使命。

Oracle資料庫之PL/SQL程式基礎設計 

PL/SQL Developer實用技巧分享

使用PL/Scope分析PL/SQL代碼 

在PL/SQL中使用日期類型