为什么没有触发异常 NO_DATA_FOUND？

Question

所以我遇到的问题是，如果我执行以下过程并且游标找不到传递的参数，它会继续执行块（插入语句），但不会抛出 NO_DATA_FOUND 异常错误，而是抛出父/外键错误。

CREATE OR REPLACE PACKAGE ASSIGNMENT3 IS

PROCEDURE END_CAMPAIGN(CTITLE IN CAMPAIGN.CAMPAIGNTITLE%TYPE);

END ASSIGNMENT3;
/    

CREATE OR REPLACE PACKAGE BODY ASSIGNMENT3 AS    
    PROCEDURE END_CAMPAIGN(CTITLE IN CAMPAIGN.CAMPAIGNTITLE%TYPE) IS
        CURSOR ADCOST_CUR IS
        SELECT ACTUALCOST
        FROM ADVERTISEMENT
        WHERE ADVERTISEMENT.CAMPAIGNTITLE = CTITLE;
        V_TOTALCOST NUMBER;

        BEGIN
        V_TOTALCOST := 0;
          FOR INVOICE_REC IN ADCOST_CUR
          LOOP
            V_TOTALCOST := V_TOTALCOST + INVOICE_REC.ACTUALCOST;
          END LOOP;
          INSERT INTO INVOICE(INVOICENO, CAMPAIGNTITLE, DATEISSUED, DATEPAID, BALANCEOWING, STATUS)
          VALUES (AUTOINCREMENTINVOICE.nextval, CTITLE, SYSDATE, NULL,V_TOTALCOST,NULL);
            EXCEPTION WHEN NO_DATA_FOUND THEN
              DBMS_OUTPUT.PUT_LINE('ERROR:The campaign title you entered returned no record(s), please enter a valid campaign title.');
        COMMIT;
        END END_CAMPAIGN;


        END ASSIGNMENT3;
        /


        SET SERVEROUTPUT ON
        EXECUTE ASSIGNMENT3.END_CAMPAIGN('Panasonic 3D TV');

虽然父外键错误是正确的，但如果游标不返回行，我不希望执行该块。为什么会发生这种情况？

另外，就放置 COMMIT 而言，我到底应该在哪里告诉它 COMMIT？异常发生前还是异常发生后？

这是一份大学作业。

Answer 1

至于在哪里放置提交，我想说答案是不在过程内。客户端（本例中为 sqlplus）应确定事务是否将提交或回滚，因为结束活动的调用可能只是更广泛流程的一部分。另外假设一个活动可以在没有任何广告的情况下存在，那么我将明确检查该活动标题是否相对于活动表有效？如下建议：

CREATE OR REPLACE PACKAGE ASSIGNMENT3 IS

    PROCEDURE END_CAMPAIGN(CTITLE IN CAMPAIGN.CAMPAIGNTITLE%TYPE);

END ASSIGNMENT3;
/   

CREATE OR REPLACE PACKAGE BODY ASSIGNMENT3 AS    
    PROCEDURE END_CAMPAIGN(CTITLE IN CAMPAIGN.CAMPAIGNTITLE%TYPE) IS

        V_VALID_CAMPAIGN INTEGER; 
        V_TOTALCOST NUMBER;

    BEGIN

        -- Check this campaign title is valid 
        /* Will get you NO_DATA_FOUND here if CTITLE is invalid so wrap in 
           another BEGIN END block to throw own custom error that the client 
           of this procedure can handle (if it wants) */
        BEGIN 
            SELECT 1 
            INTO V_VALID_CAMPAIGN 
            FROM CAMPAIGN
            WHERE CAMPAIGNTITLE = CTITLE;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN 
                RAISE_APPLICATION_ERROR(-20000,'The campaign title you entered returned no record(s), please enter a valid campaign title.'); 
        END;

        -- Now tot up the cost of ads in this campaign and raise the invoice
        SELECT SUM(ACTUALCOST)
        INTO V_TOTALCOST
        FROM ADVERTISEMENT
        WHERE ADVERTISEMENT.CAMPAIGNTITLE = CTITLE;

        INSERT INTO INVOICE(INVOICENO, CAMPAIGNTITLE, DATEISSUED, DATEPAID, BALANCEOWING, STATUS)
        VALUES (AUTOINCREMENTINVOICE.nextval, CTITLE, SYSDATE, NULL,V_TOTALCOST,NULL);

    END END_CAMPAIGN;

END ASSIGNMENT3;
/

EXECUTE ASSIGNMENT3.END_CAMPAIGN('Panasonic 3D TV');
COMMIT;

Answer 2

当您像这样循环游标时，如果游标找不到匹配的行，则循环根本不会执行。仅当 BEGIN/END 块内有不返回任何行的 SELECT ... INTO ... 语句时，才会引发 NO_DATA_FOUND 异常。

现在放置 COMMIT 的地方，它是 EXCEPTION 块的一部分 - 但您的缩进意味着无论是否发生异常，您都希望它执行。在这种情况下，我会立即将 COMMIT 放在 INSERT 之后，因为只有 INSERT 成功才重要。

Answer 3

“那么有没有办法在以下情况下触发 NODATAFOUND 异常：
如果在表中找不到 CTITLE 参数，则使用游标”

您可以做的是测试 V_TOTAL_COST 的值。如果它为零，则引发异常，如下所示：

PROCEDURE END_CAMPAIGN(CTITLE IN CAMPAIGN.CAMPAIGNTITLE%TYPE) IS
    CURSOR ADCOST_CUR IS
    SELECT ACTUALCOST
    FROM ADVERTISEMENT
    WHERE ADVERTISEMENT.CAMPAIGNTITLE = CTITLE;
    V_TOTALCOST NUMBER;

    BEGIN
      V_TOTALCOST := 0;
      FOR INVOICE_REC IN ADCOST_CUR
      LOOP
        V_TOTALCOST := V_TOTALCOST + INVOICE_REC.ACTUALCOST;
      END LOOP;

      if v_total_cost = 0 then
          raise no_data_found;
      end if;

      INSERT INTO INVOICE(INVOICENO, CAMPAIGNTITLE, DATEISSUED, DATEPAID, BALANCEOWING, STATUS)
      VALUES (AUTOINCREMENTINVOICE.nextval, CTITLE, SYSDATE, NULL,V_TOTALCOST,NULL);
      COMMIT;
    EXCEPTION WHEN NO_DATA_FOUND THEN
          DBMS_OUTPUT.PUT_LINE('ERROR:The campaign title you entered returned no record(s), please enter a valid campaign title.');

    END END_CAMPAIGN;

这假设您有一个 ACTUAL_COST 不能为零的业务规则。

或者，有一个更笨拙的解决方法，即在循环中递增计数器并在循环后测试它是否为零。