This is the article of Pona, I put it up with it, Pona is not mind! ^ _ ^
In PL / SQL, there are three ways to cause an abnormal abnormality that will cause a large amount of data when processing a large amount of data.
1. Put the data to be processed in the recordset with Fetch Into A CURSOR% Type. When a data does not match, use the label << next_record >> and goto next_record jump statement to ignore this, go to the next process.
-------------------------------------------------- -----------------------------
- 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_shipblhead;
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: = Checkexists Faccdate
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 Tan
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, when using the CURSOR for loop cycle, in the loop loop, write the problem in a separate Block block, this block includes a complete BEGIN, END section, and Exception exception handling section. This will continue to execute the next article even if a data is abnormal.
-------------------------------------------------- -----------------------------
- 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. When using the CURSOR for loop cycle, in the loop cycle, remove the problem of the problem into a subunies, separately.
-------------------------------------------------- ----------------------------
- 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: INSTOAAALSCC
- Function Desc: DEAL with INSERT Section.
- created by: Author
- Created Date: 2003-03-13
-------------------------------------------------- ----------------------------
Function INSTOAAALSCC (
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: INSTOAAALSCC'
COM_LOG.PUTLINE (p_i_job_id, x_vch_error_msg);
X_VCH_ERROR_MSG: = P_i_JOB_ID
|| 'The key of the record thing failed to insert is:';
COM_LOG.PUTLINE (p_i_job_id, x_vch_error_msg);
ROLLBACK;
RETURN GMY_GA000_PKG.GN # ng;
End INSTOAAALSCC;
