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·¢ÉúÒì³
Ïà¹ØÎĵµ£º
À´Ô´£º²»Ïê ×÷ÕߣºØýÃû ʱ¼ä£º2009-9-6 17:14:04 Tags£º Orac ʹÓ÷½·¨
ÐèÇó£ºÒµÎñÂß¼ÔÚ´¦ÀíÊý¾Ýʱ£¬ÐèÒª·µ»ØMessage²¢×èÖ¹³ÌÐòµÄ¼ÌÐøÔËÐС£µ«ÊÇ£¬ÔÚ´æ´¢¹ý³ÌʹÓÃOracleÊý¾Ý¿âµÄRaise¸ù±¾ÎÞ·¨Âú×ãÏÖÔÚµÄÒªÇó¡£
½â¾ö·½·¨£º
ʹÓÃRAISE_APPLICATION_ERROR
RAISE_APPLICATION_ERROR ( erro ......
select 'create sequence '||sequence_name||
' minvalue '||min_value||
' maxvalue '||max_value||
' start with '||last_number||
&n ......
ÔÚÊý¾Ý²Ö¿â»·¾³ÖУ¬ÎÒÃÇͨ³£ÀûÓÃÎﻯÊÓͼǿ´óµÄ²éÑ¯ÖØÐ´¹¦ÄÜÀ´ÌáÉýͳ¼Æ²éѯµÄÐÔÄÜ£¬µ«ÊÇÎﻯÊÓͼµÄ²éÑ¯ÖØÐ´¹¦ÄÜÓÐʱºòÎÞ·¨ÖÇÄܵØÅжϲéѯÖÐһЩÏà¹ØÁªµÄÌõ¼þ£¬ÒÔÖÁÓÚÓ°ÏìÐÔÄÜ¡£±ÈÈçÎÒÃÇÓÐÒ»ÕÅÏúÊÛ±ísales£¬ÓÃÓÚ´æ´¢¶©µ¥µÄÏêϸÐÅÏ¢£¬°üº¬½»Ò×ÈÕÆÚ¡¢¹Ë¿Í±àºÅºÍÏúÊÛÁ¿¡£ÎÒÃÇ´´½¨Ò»ÕÅÎﻯÊÓͼ£¬°´Ô´洢ÀÛ¼ÆÏúÁ¿ÐÅÏ¢£¬¼ ......
ÓÉÓÚÏµÍ³ÒÆÖ²£¬ÔÀ´µÄÊý¾Ý¿â±àÂëºÍÊ±Çø¶¼»»ÁË£¬ÔÀ´µÄһЩSQLÎÄÒ²³ö´íÁË¡£¡£
¾³£±À³ö"ORA-01846: not a valid day of the week
"´íÎó¡£
¾²âÊÔ£¬ÒÔÏÂÕâ¸ö¼òµ¥Óï¾äÒ²»á´í£¡£¡
SQL> select next_day(sysdate,'FRIDAY') from DUAL;
select next_day(sysdate,'FRIDAY') from DUAL
ORA-01 ......
select trim(leading | trailing | both ' ' from ' abc d ') from dual;
È¥µô×Ö·û´® ' abc d ' µÄÇ°Ãæ/ºóÃæ/ǰºóµÄ¿Õ¸ñ
ÀàËÆº¯Êý£ºltrim, ......