主页 / 问题 / 1062525
Accepted
максим ильин
Asked:2020-12-24 17:24:05 +0000 UTC2020-12-24 17:24:05 +0000 UTC

如果发生错误，如何将 id 从循环传递给 Exeption？

  • 772

更新时，会弹出限制错误。您需要确定更新在哪条记录上捕获了错误。我不能将此条目传递给异常块，因为它发誓：

必须声明标识符“CUR.CSRES_RESOLUTION_ID”

我了解循环已关闭。在那种情况下如何转移CSRES_RESOLUTION_ID异常？

DECLARE
    err_code VARCHAR2(100);
BEGIN 
    FOR CUR IN (
        SELECT a.csres_resolution_id FROM CS_RESOLUTION A
        WHERE A.CSRES_RESOLUTION_NUMBER IN ('0356040','0356043','0356044')
    ) LOOP
        UPDATE CS_CASE B
        SET B.DCCST_CASE_STATUS_CODE='CLOSED BY PAYMENT'
        WHERE B.csres_resolution_id =CUR.csres_resolution_id;
    END LOOP;
COMMIT;
EXCEPTION WHEN OTHERS THEN
    err_code := SQLCODE;
    dbms_output.put_line(err_code||' '||CUR.csres_resolution_id );
END;
oracle

2 个回答

  1. 此任务不需要循环，请使用FORALL. 使用SAVE EXCEPTIONS
    谓词，所有异常都将保存到数组中。该数组的元素属性是以异常结束的子句的 DML 索引。SQL%BULK_EXCEPTIONSSQL%BULK_EXCEPTIONS(iх).ERROR_INDEX

    问题中的块示​​例： 

    declare
    type targetRow is record (rid rowid, csres_resolution_id number);
    type targetRows is table of targetRow;
    targets targetRows;
begin 
    select b.rowid, b.csres_resolution_id bulk collect into targets  
    from cs_resolution a
    join cs_case b on b.csres_resolution_id = a.csres_resolution_id
    where a.csres_resolution_number in ('0356040','0356043','0356044');

    declare
        dmlerrors exception;
        pragma exception_init (dmlerrors, -24381);
    begin 
        forall ix in indices of targets save exceptions
            update cs_case b set 
                b.dccst_case_status_code = 'CLOSED BY PAYMENT'
            where b.rowid = targets(ix).rid;
        dbms_output.put_line (sql%rowcount||' row(s) updated.');    

    exception when dmlerrors then
        for ix in 1..sql%bulk_exceptions.count loop
            dbms_output.put_line (
                'Error id='||targets(sql%bulk_exceptions(ix).error_index).csres_resolution_id||
                ' '||sqlerrm||(-(sql%bulk_exceptions(ix).error_code)));
        end loop;
    end;
end;
/
    • 4
  2. Best Answer

    鉴于需要向所有记录输出错误的注释，应将异常处理移至循环本身：

    DECLARE
    err_code VARCHAR2(100);
BEGIN 
    FOR CUR IN (SELECT a.csres_resolution_id FROM CS_RESOLUTION A
                 WHERE A.CSRES_RESOLUTION_NUMBER IN ('0356040','0356043','0356044')) LOOP
      BEGIN 
          UPDATE CS_CASE B
             SET B.DCCST_CASE_STATUS_CODE='CLOSED BY PAYMENT'
           WHERE B.csres_resolution_id =CUR.csres_resolution_id;
      EXCEPTION WHEN OTHERS THEN
          err_code := SQLCODE;
          dbms_output.put_line(err_code||' '||CUR.csres_resolution_id );
      END;
    END LOOP;
    COMMIT;
END;

    如果有必要为任何错误回滚整个事务，则可能值得创建一个变量 - 一个错误的标志，并且仅在未设置时提交。

    • 2

相关问题