PL/SQL中錯誤的異常處理

PL/SQL中錯誤的異常處理

PL/SQL中對於錯誤的處理是很重要的一個部分，就跟寫程式中對於異常的處理一樣。可能程式中正常的流程實現部分不是很複雜，但是對於各種可能發生的異常情況都需要面面俱到的處理要佔一半以上的代碼量。

首先PL/SQL中的異常處理需要重視，但是異常的處理絕大多數的人寫PL/SQL的時候都不夠重視,通常在資料處理完之後對於異常的處理都是以如下的形式結尾：

when others then
  dbms_output.put_line('error......');

如果你也是這樣的習慣來用when others，就需要注意了。tom大師在自己的書《深入資料庫體繫結構》也做了詳細的解釋，下面標黃的這句話讓人很有感觸。

• In my experience, error handling is done wrong more often than it is done right
• You don’t want to catch all exceptions
• You want to catch specific exceptions you are expecting (which means they aren’t exceptions)
• You should ignore all others – or of you do catch them to clean up a resource, RE-RAISE them

Oracle的PL/SQL小組在新版本的改進中曾希望他提出3點建議，他的第一點建議就是去除when others,最後這個部分還是保留了，但是如果你的PL/SQL中when others沒有後面沒有raise 或者raise_application_error的部分，就會給出警告。

SQL> alter session set plsql_warnings='enable:all';
 Session altered.

SQL> create or replace procedure test_proc
  2  as
  3  begin
  4  dbms_output.put_line('this is a test');
  5  exception
  6  when others
  7  then
  8  dbms_output.put_line('error handling...');
  9  end;
  10  /

SP2-0804: Procedure created with compilation warnings

SQL> show errors
 Errors for PROCEDURE TEST_PROC:

LINE/COL ERROR
 -------- -----------------------------------------------------------------
 1/1      PLW-05018: unit TEST_PROC omitted optional AUTHID clause;
          default value DEFINER used

6/6      PLW-06009: procedure "TEST_PROC" OTHERS handler does not end in
        RAISE or RAISE_APPLICATION_ERROR

簡單解釋一下原因，就是因為在PL/SQL的執行中，打個比方，有兩個Insert子句。
insert into t values(1);
 insert into t values(2);

在PL/SQL中是這樣執行的
savepoint insert_point1;
 insert into t values(1);
 if error then rollback to insert_point1;
 savepoint insert_point2;
 insert into t values(2);
 if error then rollback to insert_point2;
這個步驟就相當於在一個事物中，每一個原子操作如果失敗,都會在錯誤處理中進行rollback;
但是如果你在資料處理中，已經顯式做了事物提交，那麼你在錯誤處理的時候再rollback就晚了，前一部分已經提交了。
 這個情景就類似下面的情境
insert into t values(1);
 commit;
 insert into t values('xxxxx');  --這個語句會報錯
 那麼在整個事物的處理中第一部分已經提交了，第二部分出錯了也沒辦法做復原了。
 在when others 中加入了rollback也無濟於事。

 可能大家在更多的錯誤處理中都是簡單把錯誤資訊列印出來而已，這樣的處理結果相當於你重寫了錯誤的處理方法，
 下面隱式的錯誤處理就被覆蓋了，除非你定義了合理的錯誤處理情境，使用raise或者raise_application_error對錯誤做了正確的處理。
if error then rollback to insert_point1;
 if error then rollback to insert_point2;

所以在錯誤的處理中還是建議不要使用when others 子句。這種籠統的錯誤處理可能更多的時候會造成資料的不一致。
 如果一定想要使用when others then
可以採用類似下面的形式。
begin
 savepoint sp;
 process;--這個部分就是具體的處理代碼
exception
 when others then
    rollback to sp;
    dbms_output.put_line('Error,||sqlerrm);
 end;
 /
這樣即使代碼中的某一部分出現異常，也能保證整個事物是一個原子操作。

rlwrap - 解決Linux下SQLPLUS退格、上翻鍵亂碼問題

SQLPLUS spool 到動態記錄檔名

Oracle SQLPLUS提示符設定

通過設定SQLPLUS ARRAYSIZE(行預取)加快SQL返回速度

PL/SQL Developer實用技巧分享