淺談例外表的應用

實驗：例外表的使用

當我們用程式向表中插入大量資料時，可能有幾條是不滿足條件的，這時候我們可能希望滿足條件的資料被執行，而程式不報錯。不滿足條件的資料呢，可能想把它放到其他地方，以備查驗。
oracle為我們提供了一種簡單方便的實現方法，通過內部的包即可將例外內容收集，並保證事務正常結束。
我們來看實驗：

環境：oracle 10g+windows2003
首先建立了一個測試表：
create table t1 as select object_name,object_type from dba_objects where 1=2;
然後我們給其新增約束：alter table t1 modify object_type not null;
這時，如果直接插入object_type為空的記錄就會報錯：

SQL> insert into T1 (OBJECT_NAME, OBJECT_TYPE)
2 values ('ICOL$', '');

insert into T1 (OBJECT_NAME, OBJECT_TYPE)
values ('ICOL$', '')

ORA-01400: 無法將 NULL 插入 ("SCOTT"."T1"."OBJECT_TYPE")

下面用dbms_errlog包來捕獲例外：
--建立例外捕獲表T1_ERROR
begin
dbms_errlog.create_error_log('T1','T1_ERROR','SCOTT');
end;
/

執行成功後我們來插入幾條資料：
begin
insert into T1 (OBJECT_NAME, OBJECT_TYPE)
values ('ICOL$', '') log errors into t1_error reject limit unlimited;
insert into T1 (OBJECT_NAME, OBJECT_TYPE)
values ('I_USER1', 'TABLE')log errors into t1_error reject limit unlimited;
commit ;
end;
/

可以看到不滿足條件的記錄被插入到了例外捕獲表中：
SQL> select * from t1_error;

ORA_ERR_NUMBER$ ORA_ERR_MESG$                                                                    ORA_ERR_ROWID$                                                                   ORA_ERR_OPTYP$ ORA_ERR_TAG$                                                                     OBJECT_NAME                                                                      OBJECT_TYPE
--------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
           1400 ORA-01400: 無法將 NULL 插入 ("SCOTT"."T1"."OBJECT_TYPE")                                                                                                          I                                                                                               ICOL$
例外捕獲表的結構為：
SQL> desc t1_error;
Name            Type           Nullable Default Comments
--------------- -------------- -------- ------- --------
ORA_ERR_NUMBER$ NUMBER         Y
ORA_ERR_MESG$   VARCHAR2(2000) Y
ORA_ERR_ROWID$ UROWID(4000)   Y
ORA_ERR_OPTYP$ VARCHAR2(2)    Y
ORA_ERR_TAG$    VARCHAR2(2000) Y
OBJECT_NAME     VARCHAR2(4000) Y
OBJECT_TYPE     VARCHAR2(4000) Y

以上列值還是比較好理解的，ORA_ERR_NUMBER$是oracle例外程式碼；ORA_ERR_MESG$是例外提示資訊；ORA_ERR_ROWID$是進行更新、刪除操作時產生例外的列rowid；ORA_ERR_OPTYP$的取值有I(insert)、U(update)、D(delete);

當執行update語句時，
begin
update t1 set object_type='' where object_name='CON$' log errors into t1_error reject limit unlimited;
update t1 set object_type='111' where object_name='UNDO$' log errors into t1_error reject limit unlimited;
commit ;
end;
/

SQL> select * from t1_error t;

ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ ORA_ERR_OPTYP$ ORA_ERR_TAG$ OBJECT_NAME OBJECT_TYPE
--------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1407 ORA-01407: 無法更新 ("SCOTT"."T1"."OBJECT_TYPE") 為 NULL AAAM24AAEAAAGMkAAC U CON$
ORA_ERR_ROWID$的值對應的是產生例外的待修改的列rowid。

在建立時我們並不需要去指定該表的結構，自動包含了例外程式碼、例外資訊、例外行rowid、例外型別、標誌以及所對應表的所有列。

確實很強大。但還不是全部。

試想，如果表的欄位比較多，不需要在例外捕獲表中重複寫入那麼多欄位，那麼只包含有約束的行就行啦，來看實驗：

SQL> truncate table t1;

Table truncated

SQL> create table error_show(ora_err_number$ number,ora_err_mesg$ varchar2 (200),object_type varchar2(200));

Table created

SQL>

begin
insert into T1 (OBJECT_NAME, OBJECT_TYPE)values ('ICOL$', '') log errors into error_show reject limit unlimited;
insert into T1 (OBJECT_NAME, OBJECT_TYPE)values ('I_USER1', 'TABLE')log errors into error_show reject limit unlimited;
insert into T1 (OBJECT_NAME, OBJECT_TYPE)values ('', 'TABLE') log errors into error_show reject limit unlimited;
insert into T1 (OBJECT_NAME, OBJECT_TYPE)values ('UNDO$', 'TABLE') log errors into error_show reject limit unlimited;
/*update t1 set object_type='' where object_name='CON$' log errors into t1_error reject limit unlimited;
update t1 set object_type='111' where object_name='UNDO$' log errors into t1_error reject limit unlimited;*/
commit ;
end;

ORA-38900: 缺失必需的列 "ORA_ERR_ROWID$" (例外日誌表 "ERROR_SHOW")
ORA-06512: 在 line 2

後面有嘗試缺少ORA_ERR_NUMBER$列，報錯相似。因此得出結論：當使用自定義例外表時，表中還是必須包含oracle指定的列：ORA_ERR_NUMBER$ NUMBER、ORA_ERR_MESG$、ORA_ERR_ROWID$、ORA_ERR_OPTYP$和ORA_ERR_TAG$的，我們可以修改的只是其他的列。仔細思考一下，由於例外表建立時並不知道會產生哪類錯誤；oracle需要保證所有的例外都被顯示出來，而這五列保證了錯誤顯示的完整性。

當然，我們是可以只包含以上這五列的。

SQL> drop table error_show;

Table dropped

SQL> create table error_show(ORA_ERR_NUMBER$ NUMBER,ORA_ERR_MESG$ VARCHAR2(2000),ORA_ERR_ROWID$ UROWID(4000),
ORA_ERR_OPTYP$ VARCHAR2(2),ORA_ERR_TAG$ VARCHAR2(2000));

Table created

執行匿名塊：
begin
insert into T1 (OBJECT_NAME, OBJECT_TYPE)values ('ICOL$', '') log errors into error_show reject limit unlimited;
insert into T1 (OBJECT_NAME, OBJECT_TYPE)values ('I_USER1', 'TABLE')log errors into error_show reject limit unlimited;
insert into T1 (OBJECT_NAME, OBJECT_TYPE)values ('', 'TABLE') log errors into error_show reject limit unlimited;
insert into T1 (OBJECT_NAME, OBJECT_TYPE)values ('UNDO$', 'TABLE') log errors into error_show reject limit unlimited;
/*update t1 set object_type='' where object_name='CON$' log errors into t1_error reject limit unlimited;
update t1 set object_type='111' where object_name='UNDO$' log errors into t1_error reject limit unlimited;*/
commit ;
end;
/

SQL>

PL/SQL procedure successfully completed

當只定義了oracle預設的5列作為例外表時，可以將不同表的例外都插入一個例外表中。從ora_err_mesg$列可以區分出是哪個表報錯的。

例如，將t表和t1表的例外資料都放入error_show表，查詢結果為：
SQL> select * from error_show;

ORA_ERR_NUMBER$ ORA_ERR_MESG$                                                                    ORA_ERR_ROWID$                                                                   ORA_ERR_OPTYP$ ORA_ERR_TAG$
--------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------- --------------------------------------------------------------------------------
           1400 ORA-01400: 無法將 NULL 插入 ("SCOTT"."T1"."OBJECT_TYPE")                                                                                                          I
           1400 ORA-01400: 無法將 NULL 插入 ("SCOTT"."T"."OBJECT_NAME")

總的來說，例外表的功能還是蠻強大的，值得好好利用，也確實能為我們減輕很多工作負擔。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-744994/，如需轉載，請註明出處，否則將追究法律責任。

淺談例外表的應用

相關文章