JAVA高级面试进阶训练营视频教程

admin

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;