模板
教程
环境
性能
功能
管理
PL/SQL中的几种异常处理方法的源代码
用Fetch into a cursor%TYPE把要处理的数据放到记录集里。当一条数据不符条件时，用标签NEXT_RECORD>>和GOTO NEXT_RECORD -- Function Name : CalculateImportCharge -- Function Desc : Calculate Import Charge -- Created by : Author -- Created Date :
用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 := CheckExistsOfAclearcase/" target="_blank" >ccDate (
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;
