從一個案例看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階段，每個階段有不同的任務使命。