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 :
FUNCTION CalculateImportCharge (
p_i_job_id IN VARCHAR
p_i_as_of_date_id IN VARCHAR) RETURN NUMBER
AS
CURSOR cur_ShipBlHeader IS
SELECT import_folder_no
FROM GMY_SHIP_BL_HEADER
WHERE CANCEL_FLG = GMY_GA_PKGBL_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_GA_PKGCheckValidMasterBlNo (
p_i_job_id
p_i_as_of_date_id
rec_ShipBlHeaderimport_folder_no
x_vch_message);
IF x_num_error_code
IN (GMY_GA_PKGgn#NG GMY_GA_PKGINVALID_BL_NO) THEN
x_vch_message :=
p_i_job_id
|| WARNING: Function CheckValidMasterBlNo @
|| Import folder
|| rec_ShipBlHeaderimport_folder_no
|| Invalid BL No;
COM_LOGPUTLINE (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_ShipBlHeaderimport_folder_no);
IF x_num_error_code = GMY_GA_PKGgn#NG THEN
GOTO NEXT_RECORD;
END IF;
COMMIT;
<<NEXT_RECORD>>
FETCH cur_ShipBlHeader INTO rec_ShipBlHeader;
END LOOP;
CLOSE cur_ShipBlHeader;
RETURN GMY_GA_PKGgn#OK;
EXCEPTION
WHEN OTHERS THEN
x_vch_message :=
p_i_job_id
|| ERROR: Function CalculateImportCharge @
|| SUBSTR (SQLERRM (SQLCODE) );
COM_LOGPUTLINE (p_i_job_id x_vch_message);
RETURN GMY_GA_PKGgn#NG;
END CalculateImportCharge;
当使用the Cursor FOR Loop循环时在Loop循环里把会出问题的情况写进一个独立的block块中这个块包括完整的beginend部分及exception异常处理部分这样即使一条数据出现异常也会继续执行下一条
Function Name : GenerateInsCostInfRec
Function Desc : Generate records to transmit in INF table
Created by : SISS(AP)
Created Date :
FUNCTION GenerateInsCostInfRec (
p_i_job_id IN VARCHAR
p_i_as_of_date_id IN VARCHAR) RETURN NUMBER
AS
CURSOR cur_cost IS
SELECT costROWID costRowId
costimport_folder_no
costinsur_trans_id
FROM GMY_COST_BL cost
GMY_COMMON_MST mst
WHERE costimport_folder_no=invheaderimport_folder_no
AND costbilling_amt_num IS NOT NULL
AND costbilling_amt_num!=
AND costinsur_db_cr!=;
BEGIN
FOR rec_cost IN cur_cost LOOP
BEGIN
x_num_ret_value := GMY_GA_PKGCheckValidMasterBlNo(
p_i_job_id
p_i_as_of_date_id
rec_costimport_folder_no
x_vch_error_msg);
IF x_num_ret_value = GMY_GA_PKGVALID_BL_NO THEN
INSERT INTO GMY_COST_INS_INF(
cost_trx_id
created_by
program_name)
VALUES(
GMY_COST_INS_INF_SNEXTVAL
PRG_NAME
PRG_NAME);
ELSIF x_num_ret_value = GMY_GA_PKGINVALID_BL_NO THEN
x_vch_error_msg := p_i_job_id
|| Import folder
|| rec_costimport_folder_no
|| has repeated BL No with other import folder
|| Failed in insurance cost transmission;
COM_LOGPUTLINE(p_i_job_id x_vch_error_msg);
END IF;
EXCEPTION
WHEN OTHERS THEN
IF SQL%ROWCOUNT > THEN check for too many rows
x_vch_error_msg := p_i_job_id|| ||
SUBSTR(SQLERRM(SQLCODE));
COM_LOGPUTLINE(p_i_job_id x_vch_error_msg);
ELSE
x_vch_error_msg := p_i_job_id|| ||
SUBSTR(SQLERRM(SQLCODE));
COM_LOGPUTLINE(p_i_job_id x_vch_error_msg);
END IF;
END;
END LOOP;
COMMIT;
RETURN GMY_GA_PKGgn#OK;
EXCEPTION
WHEN OTHERS THEN
x_vch_error_msg := p_i_job_id|| ||SUBSTR(SQLERRM(SQLCODE));
COM_LOGPUTLINE(p_i_job_id x_vch_error_msg);
ROLLBACK;
RETURN GMY_GA_PKGgn#NG;
END GenerateInsCostInfRec;
当使用the Cursor FOR Loop循环时在Loop循环里把会出问题的情况拆分成子函数分别处理
Function Name : CopyDsToActualDs
