Oracle cursor example, with exception handling.
DECLARE CURSORC_dl is SELECTID, NSRSBH, Wspzxh, ZXYY_DM, Hzrq, SWJG_DM, GXSJ fromt_guos_zxxxWHEREGxsj>BEGIN_GXSJ; C_row C_DL%ROWTYPE; BEGIN forC_rowinchC_DL LOOPBEGIN IF(C_row. Gxsj>MAX_GXSJ) Then BEGINMAX_GXSJ:=C_row. GXSJ; END; END IF; Handled_count:=Handled_count+ 1; --MERGE intot_fr_guoszx T USING (SELECTC_row. NSRSBH NSRSBH, C_row. Wspzxh Wspzxh, C_row. Zxyy_dm Zxyy_dm, C_row. Hzrq Hzrq, C_row. Swjg_dm Swjg_dm, C_row. GXSJ GXSJ fromdual) NT on(T.NSRSBH=NT. NSRSBH) whenMatched Then UPDATE SETZxwsxh=NT. Wspzxh when notMatched Then INSERT(ID, NSRSBH, Zxwsxh, Zxyy, Zxrq, ZXJG, GXSJ)VALUES ... COMMIT; --When an exception occurs, insert the exception into the log table and continue to the next loopEXCEPTION whenOTHERS Then ROLLBACK; Error_count:=Error_count+ 1; ErrorCode:=SQLCODE; ErrMsg:=SQLERRM; BEGIN --error messages are logged to the log table when an error is performed COMMIT; END; END; ENDLOOP; END;
Oracle cursor example with exception handling