首页 > 代码库 > Oracle 游标示例,带异常处理

Oracle 游标示例,带异常处理

Oracle游标示例一则,带异常处理。

DECLARE        CURSOR c_dl IS            SELECT ID, NSRSBH, WSPZXH, ZXYY_DM, HZRQ, SWJG_DM, GXSJ              FROM T_GUOS_ZXXX             WHERE gxsj > begin_gxsj;        c_row c_dl%ROWTYPE;    BEGIN        FOR c_row IN c_dl LOOP            BEGIN                IF (c_row.GXSJ > max_gxsj) THEN                    BEGIN                        max_gxsj := c_row.GXSJ;                    END;                END IF;                handled_count := handled_count + 1;                --                MERGE INTO T_FR_GUOSZX T                USING (SELECT c_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                         FROM dual) NT                ON (T.NSRSBH = NT.NSRSBH)                WHEN MATCHED THEN                    UPDATE                       SET ZXWSXH = NT.WSPZXH                WHEN NOT MATCHED THEN                    INSERT                        (ID, NSRSBH, ZXWSXH, ZXYY, ZXRQ, ZXJG, GXSJ)                    VALUES                    ...                COMMIT;                --当出现异常时,将异常插入日志表,并继续下一次循环            EXCEPTION                WHEN OTHERS THEN                    ROLLBACK;                    error_count := error_count + 1;                    errorcode   := SQLCODE;                    errmsg      := SQLERRM;                    BEGIN                        --执行出错时,将错误信息记录到日志表                 COMMIT;                    END;            END;        END LOOP;    END;

 

Oracle 游标示例,带异常处理