异常处理
这是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;