用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_idIN 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_ShipBlHeadercur_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_idIN 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
Function Desc:Copy the records from DS DB to Actual DS DB
Created by :Author
Created Date :
FUNCTION CopyDsToActualDs (
p_i_job_id IN VARCHAR
p_i_as_of_date_idIN VARCHAR)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(
