oracle - PL/SQL异常处理

Question

我对PL/SQL编程还很陌生，并且过去2个小时一直在尝试解决此问题

这是我的代码

CREATE OR REPLACE PROCEDURE xx_upd_new_code AS   

  CURSOR c1 IS
    SELECT
      eite.t_ep_item_ep_id,
      bite.main_item
    FROM bidw.item@demantra bite,
      t_ep_item eite
    WHERE bite.item_code = eite.item
          AND bite.main_item = bite.old_code
          AND bite.current_flag = 1
          AND bite.main_item IS NOT NULL;

  a1 c1%ROWTYPE;

  BEGIN
    xx_dbex('Starts.', 'xx_upd_new_code', 'XX');

    OPEN c1;
    LOOP
      FETCH c1 INTO a1;
      EXIT WHEN c1%NOTFOUND;

      xx_dbex('Item: ' || a1.main_item, 'xx_upd_new_code', 'XX');

      UPDATE t_ep_item
      SET item = a1.main_item
      WHERE t_ep_item_ep_id = a1.t_ep_item_ep_id;

      COMMIT;

      xx_dbex('Ends.', 'xx_upd_new_code', 'XX');
    END LOOP;

    EXCEPTION 
    WHEN OTHERS THEN xx_dbex('Error.', 'xx_upd_new_code', 'XX');   
  END xx_upd_new_code;

每当遇到错误时，过程都会结束，但我希望它移至下一项。
xx_debex是存储日志的过程。

我一直在谷歌搜索，发现我可以编写两个异常并且如果不满足条件可以引发异常。
标识符a必须声明
标识符项必须声明

CREATE OR REPLACE PROCEDURE xx_upd_new_code AS   

  CURSOR c1 IS
    SELECT
      eite.t_ep_item_ep_id,
      bite.main_item
    FROM bidw.item@demantra bite,
      t_ep_item eite,
      items ite
    WHERE bite.item_code = eite.item
          AND bite.main_item = bite.old_code
          AND bite.current_flag = 1              
          AND bite.main_item IS NOT NULL;

  a1 c1%ROWTYPE;

  BEGIN
    xx_dbex('Starts.', 'xx_upd_new_code', 'XX');

    OPEN c1;
    LOOP
      FETCH c1 INTO a1;
      EXIT WHEN c1%NOTFOUND;

      IF item != a1.main_item THEN
        xx_dbex('Item: ' || a1.main_item, 'xx_upd_new_code', 'XX');

        UPDATE t_ep_item
        SET item = a1.main_item
        WHERE t_ep_item_ep_id = a1.t_ep_item_ep_id;
        COMMIT;

        xx_dbex('Ends.', 'xx_upd_new_code', 'XX');          
      ELSE
        RAISE a;
      END IF;

    END LOOP;

    EXCEPTION 
      WHEN a THEN xx_dbex('Error.', 'xx_upd_new_code', 'XX');
      WHEN OTHERS THEN NULL;   
  END xx_upd_new_code;

另外，在写的地方我可以写在两个不同的块中

CREATE OR REPLACE PROCEDURE xx_upd_new_code AS

  CURSOR c1 IS
    SELECT
      eite.t_ep_item_ep_id,
      bite.main_item
    FROM bidw.item@demantra bite,
      t_ep_item eite
    WHERE bite.item_code = eite.item
          AND bite.main_item = bite.old_code
          AND bite.current_flag = 1
          AND bite.main_item IS NOT NULL;

  a1 c1%ROWTYPE;

  BEGIN
    xx_dbex('Starts.', 'xx_upd_new_code', 'XX');

    BEGIN

      EXCEPTION
      WHEN OTHERS THEN xx_dbex('Error', 'xx_upd_new_code', 'XX');
    END;

    OPEN c1;
    LOOP
      FETCH c1 INTO a1;
      EXIT WHEN c1%NOTFOUND;

      xx_dbex('Item: ' || a1.main_item, 'xx_upd_new_code', 'XX');
      UPDATE t_ep_item
      SET item = a1.main_item
      WHERE t_ep_item_ep_id = a1.t_ep_item_ep_id;
      COMMIT;

    END LOOP;

    EXCEPTION WHEN OTHERS THEN
    xx_dbex('END', 'xx_upd_new_code', 'XX'); 
  END xx_upd_new_code;

我仍然无法弄清楚。

Answer 1

将光标循环分为两个部分/块。第一个用于调用日志记录过程的部分，另一个用于更新表的部分。您的代码主体应如下所示:

  BEGIN
     Open C1; 
     loop
        fetch C1 into A1;
        exit when C1%NOTFOUND
        BEGIN
           xx_dbex('Item: '||a1.main_item ,'xx_upd_new_code', 'XX');
        END;
        BEGIN
           UPDATE t_ep_item 
           SET item = a1.main_item
           where t_ep_item_ep_id=a1.t_ep_item_ep_id;
           COMMIT;
           xx_dbex('Ends.','xx_upd_new_code', 'XX');
        EXCEPTION
           WHEN OTHERS THEN 
              xx_dbex('Ends.','ERROR', 'XX');
        END;
  END LOOP;

希望你明白