本文來源于網頁設計愛好者web開發社區http://www.html.org.cn收集整理，歡迎訪問。
這是pona的文章，我斗膽將其貼上來，pona不要介意哦！^_^
pl/sql里，有三種方法可以在處理大批量數據時不會因為一條或幾條數據錯誤而導致異常中止程序。
1、用fetch into a cursor%type把要處理的數據放到記錄集里。當一條數據不符條件時，用標簽<<next_record>>和goto next_record跳轉語句使程序忽略這一條，轉到下一條繼續處理。
-------------------------------------------------------------------------------
-- function name : calculateimportcharge
-- function desc : calculate import charge
-- created by : author
-- created date : 2003-05-16
-------------------------------------------------------------------------------
function calculateimportcharge (
p_i_job_id in varchar2,
p_i_as_of_date_id in varchar2) return number
as
cursor cur_shipblheader is
select import_folder_no
from gmy_ship_bl_header
where cancel_flg = gmy_ga000_pkg.bl_cancel_flg_off;
rec_shipblheader cur_shipblheader%rowtype;
begin
open cur_shipblheader;
fetch cur_shipblheader into rec_shipblheader;
while cur_shipblheader%found loop
x_num_error_code := gmy_ga000_pkg.checkvalidmasterblno (
p_i_job_id,
p_i_as_of_date_id,
rec_shipblheader.import_folder_no,
x_vch_message);
if x_num_error_code
in (gmy_ga000_pkg.gn#ng, gmy_ga000_pkg.invalid_bl_no) then
x_vch_message :=
p_i_job_id
|| ' warning: function checkvalidmasterblno @'
|| ' import folder '
|| rec_shipblheader.import_folder_no
|| ' - invalid bl no.';
com_log.putline (p_i_job_id, x_vch_message);
goto next_record;
end if;
x_num_error_code := checkexistsofaccdate (
p_i_job_id,
p_i_as_of_date_id,
rec_shipblheader.import_folder_no);
if x_num_error_code = gmy_ga000_pkg.gn#ng then
goto next_record;
end if;
commit;
<<next_record>>
fetch cur_shipblheader into rec_shipblheader;
end loop;
close cur_shipblheader;
return gmy_ga000_pkg.gn#ok;
exception
when others then
x_vch_message :=
p_i_job_id
|| ' error: function calculateimportcharge @ '
|| substr (sqlerrm (sqlcode), 1, 100);
com_log.putline (p_i_job_id, x_vch_message);
return gmy_ga000_pkg.gn#ng;
end calculateimportcharge;
2、當使用the cursor for loop循環時，在loop循環里，把會出問題的情況寫進一個獨立的block塊中，這個塊包括完整的begin、end部分及exception異常處理部分。這樣即使一條數據出現異常，也會繼續執行下一條。
-------------------------------------------------------------------------------
-- function name : generateinscostinfrec
-- function desc : generate records to transmit in inf table
-- created by : siss(ap)
-- created date : 2003-03-26
-- ----------------------------------------------------------------------------
function generateinscostinfrec (
p_i_job_id in varchar2,
p_i_as_of_date_id in varchar2) return number
as
cursor cur_cost is
select cost.rowid costrowid,
cost.import_folder_no,,
cost.insur_trans_id
from gmy_cost_bl cost,
gmy_common_mst mst
where cost.import_folder_no=invheader.import_folder_no
and cost.billing_amt_num is not null
and cost.billing_amt_num!=0
and cost.insur_db_cr!=0;
begin
for rec_cost in cur_cost loop
begin
x_num_ret_value := gmy_ga000_pkg.checkvalidmasterblno(
p_i_job_id,
p_i_as_of_date_id,
rec_cost.import_folder_no,
x_vch_error_msg);
if x_num_ret_value = gmy_ga000_pkg.valid_bl_no then
insert into gmy_cost_ins_inf(
cost_trx_id,,
created_by,
program_name)
values(
gmy_cost_ins_inf_s.nextval,
prg_name,
prg_name);
elsif x_num_ret_value = gmy_ga000_pkg.invalid_bl_no then
x_vch_error_msg := p_i_job_id
|| ' import folder '
|| rec_cost.import_folder_no
|| ' has repeated bl no. with other import folder.'
|| ' failed in insurance cost transmission.';
com_log.putline(p_i_job_id, x_vch_error_msg);
end if;
exception
when others then
if sql%rowcount > 0 then -- check for 'too many rows'
x_vch_error_msg := p_i_job_id||' '||
substr(sqlerrm(sqlcode),1,100);
com_log.putline(p_i_job_id, x_vch_error_msg);
else
x_vch_error_msg := p_i_job_id||' '||
substr(sqlerrm(sqlcode),1,100);
com_log.putline(p_i_job_id, x_vch_error_msg);
end if;
end;
end loop;
commit;
return gmy_ga000_pkg.gn#ok;
exception
when others then
x_vch_error_msg := p_i_job_id||' '||substr(sqlerrm(sqlcode),1,100);
com_log.putline(p_i_job_id, x_vch_error_msg);
rollback;
return gmy_ga000_pkg.gn#ng;
end generateinscostinfrec;
3、當使用the cursor for loop循環時，在loop循環里，把會出問題的情況拆分成子函數，分別處理。
----------------------------------------------------------------------------
-- function name : copydstoactualds
-- function desc : copy the records from ds db to actual ds db.
-- created by : author
-- created date : 2003-02-20
----------------------------------------------------------------------------
function copydstoactualds (
p_i_job_id in varchar2,
p_i_as_of_date_id in varchar2) return number
is
cursor cur_dsscc is
select *
from gmy_ds_scc;
begin
for rec_dshead in cur_dsscc loop
x_num_error_code := instoactualscc(
p_i_job_id,
p_i_as_of_date_id,
rec_dshead.order_by_code,
rec_dshead.po_code,
rec_dshead.wh);
end loop;
exception
when others then
x_vch_error_msg := p_i_job_id
||' function name: copydstoactualds';
com_log.putline(p_i_job_id,x_vch_error_msg);
x_vch_error_msg:=p_i_job_id||' '||substr(sqlerrm(sqlcode),1,100);
com_log.putline(p_i_job_id, x_vch_error_msg);
rollback;
return gmy_ga000_pkg.gn#ng;
end copydstoactualds;
----------------------------------------------------------------------------
-- function name : instoactualscc
-- function desc : deal with insert section.
-- created by : author
-- created date : 2003-03-13
----------------------------------------------------------------------------
function instoactualscc(
p_i_job_id in varchar2,
p_i_as_of_date_id in varchar2,
p_i_order_by_code in varchar2,
p_i_po_code in varchar2,
p_i_wh in varchar2
) return number
is
x_vch_error_msg varchar2(255);
begin
insert into gmy_actual_ds_scc(
order_by_code,
po_code,
wh )
values( p_i_order_by_code,
p_i_po_code,
p_i_wh);
commit;
return gmy_ga000_pkg.gn#ok;
exception
when others then
x_vch_error_msg := p_i_job_id||' function name: instoactualscc';
com_log.putline(p_i_job_id,x_vch_error_msg);
x_vch_error_msg := p_i_job_id
||' the key of the record that failed to insert is: ';
com_log.putline(p_i_job_id,x_vch_error_msg);
rollback;
return gmy_ga000_pkg.gn#ng;
end instoactualscc;
