ORACLE SAVE EXCEPTION ×Ó¾ä

½ñÌìÔÚÒ»¸öÌû×ÓÀï¿´µ½shiyiwanµÄ»ØÌûÖÐÌá¼°µ½ÁËÁ½¸ö×Ô¼ºÒÔǰû¼û¹ýµÄ¸ÅÄsave exceptionºÍdml error logging¡£ÉÏÍøËÑÁËËÑÏà¹ØÄÚÈÝ£¬¿´ÁË¿´´ó¸ÅÃ÷°×Òâ˼£¬²»¹ýÔÚʵ¼ÊÔËÓÃÖл¹ÊÇûÔõôÓùý¡£±£´æÏÂÀ´£¬ÒÔºóÓõĵ½µÄ»°·½±ã²éÔÄ¡£
ÕâһƪÊǹØÓÚsave exceptionµÄ£¬ÁíÍâһƪdml error loggingµÄ²Î¼ûÈçÏÂÁ´½Ó
http://blog.csdn.net/wh62592855/archive/2009/11/13/4808012.aspx
==================================================================================
Oracle ´Ó9i ¿ªÊ¼ÔöÇ¿ÁËÅú DML ÔÚ·¢ÉúÒ쳣ʱ´¦ÀíµÄ´íÎó¼°Ê§°ÜµÄ³ÌÐòÖ´ÐÐÄÜÁ¦¡£ÕâÊÇͨ¹ýFORALL Óï¾äµÄ SAVE EXCEPTION ×Ó¾äÀ´ÊµÏÖ£¬ÆäÓï·¨ÈçÏÂ:
FORALL index in lower..upper save exceptions
SAVE EXCEPTIONS ×Ó¾äÊÇ oracle 9i ÖÐÒýÈëµÄ£¬ËüÔÚÒ»¸öÒþʽÓαêÊôÐÔ£¬SQL%BULK_EXCEPTIONS Öб£´æ´íÎóÐУ¬ÔÊÐí FORALL Óï¾ä¼ÌÐø´¦ÀíÆäÓàÐС£
ÏÂÃæÕâÒ»¶Î´úÂëÊÇ SQL%BULK_EXCEPTIONS µÄÓ¦ÓÃÀý×Ó£º
create or replace procedure batch_dml(errnum outnumber,errtext outvarchar2)
is
type user_id_tab is table of number index by binary_integer;
type user_name_tab is table of varchar2( 20 ) index by binary_integer;
type user_sex_tab is table of varchar2( 2 ) index by binary_integer;
 
user_id user_id_tab;
user_name user_name_tab;
user_sex user_sex_tab;
bulk_bind_excep EXCEPTION;
pragma exception_init(bulk_bind_excep,- 24381 );
begin
for idx in 1 .. 50000 loop
user_id(idx):=idx;
user_name(idx):= 'xxx' ||idx;
user_sex(idx):= 'F' ;
endloop;
user_id( 40000 ):= 39999 ;
user_id( 10000 ):= 9999 ;
delete from t_user;
forall idx in user_id.first..user_id.last save exceptions
insert into t_user values(user_id(idx), user_name(idx),user_sex(idx));
errnum:= 0 ;
errtext:= '' ;
exception
when bulk_bind_excep then
for i in 1 ..sql%bulk_exceptions.count loop
dbms_output.put_line( 'Iteration '
||SQL%bulk_exceptions(i).error_index|| 'failed with error '
||sqlerrm(sql%bulk_exceptions(i).error_code));
endloop;
commit;
when others then
commit;
errnum:=sqlcode;
errtext:=sqlerrm;
end batch_dml;
Õâ¸öÀý×ÓÊÇÐÞ¸ÄÁËÉÏÃæµÄ³ÌÐò£¬¼ÓÉÏ save exceptions Òì³£´¦Àí£¬µ±Åú dml·¢ÉúÒì³