时间戳

首页 > 解决方案 > NO DATA FOUND oracle 使用游标

oracle - NO DATA FOUND oracle 使用游标

问题描述

我一直在网上搜索使用不同的解决方案建议来处理此代码中的任何数据，但无济于事。如果没有找到数据，我该如何处理异常。我怎么解决这个问题。虽然我不是甲骨文专家！

    DECLARE

          nCheckOption   INT;
          no_data_found EXCEPTION;

          CURSOR TYPE_cursor IS
                         SELECT
                                                      D_NAL_REF.TRANS
                                                      , D_NAL_REF.INJ
                                                      , D_NAL_REF.REF
                         FROM  D_NAL_REF D_NAL_REF 
                         WHERE D_NAL_REF.REF IN
                                       (SELECT AG_REF.REF
                                       FROM   AG_REF A_REF
                                       WHERE A_REF.DESCEND_REF = 10
                                       );
BEGIN
          FOR rec IN TYPE_cursor
          LOOP

                         nCheckOption := 0;

                         SELECT 1
    INTO            nCheckOption
    FROM          PERSON_TYPE WHERE TRANS = rec.TRANS AND INJ = rec.INJ;

                         IF nCheckOption = 1 THEN

                                       UPDATE              PERSON_TYPE
                                       SET                      PERSON_TYPE.TYPE = rec.REF
                                       WHERE TRANS = rec.TRANS
                                       AND                     PERSON_TYPE.INJ = rec.INJ;



                         END IF;

EXCEPTION
  WHEN no_data_found
  THEN
     DBMS_OUTPUT.PUT_LINE ('Trapped the error!?');
          END LOOP;
END;
/

标签： oracle

解决方案

重写代码以消除内部 SELECT，这是代码中唯一可以看到可能引发 NO_DATA_FOUND 异常的地方：

BEGIN
  FOR rec IN (SELECT d.TRANS,
                     d.INJ,
                     d.REF
                FROM D_NAL_REF d
                WHERE d.REF IN (SELECT a.REF
                                  FROM AG_REF a
                                  WHERE a.DESCEND_REF = 10) AND
                      (d.TRANS, d.INJ) IN (SELECT DISTINCT TRANS, INJ
                                             FROM PERSON_TYPE))
  LOOP
    UPDATE PERSON_TYPE
      SET TYPE = rec.REF
      WHERE TRANS = rec.TRANS AND
            INJ = rec.INJ;
  END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Trapped the error!?');
END;

推荐阅读