Oracle存储过程自定义异常未触发返回no data found错误求助

你的存储过程存在执行顺序逻辑漏洞：当传入不存在的产品名称时，执行完产品计数查询后，没有先做存在性判断就直接触发了库存查询语句：

• 计数查询SELECT count(*) INTO CHECKPROD FROM PRODUCTS WHERE productname = NOMPROD无匹配记录时会返回0，本身不会报错
• 后续直接执行的SELECT unitsinstock-quantity INTO CHECKQTY FROM products WHERE productname = NOMPROD属于无返回结果的SELECT INTO语句，会直接触发Oracle系统自带的NO_DATA_FOUND异常，直接跳入EXCEPTION块的OTHERS分支，根本没有执行后续的IF判断逻辑，因此自定义的产品不存在异常不会被触发。

调整代码执行顺序，先完成客户、产品的存在性校验，确认产品存在后再执行库存查询逻辑，修改后的代码如下：

CREATE OR REPLACE PROCEDURE Insert_ord(
   ID_CLIE IN CUSTOMERS.CUSTOMERID%TYPE, 
   NOMPROD IN products.productname%TYPE,
   QUANTITY IN order_details.quantity%TYPE
)
IS
    CHECKCLI INT; 
    CHECKPROD INT; 
    CHECKQTY INT; 
    ERR_CLI EXCEPTION; 
    ERR_PRODUCT EXCEPTION; 
    ERR_QTY EXCEPTION; 
BEGIN 
    -- 先校验客户是否存在
    SELECT count(*) INTO CHECKCLI FROM customers WHERE customerid = ID_CLIE; 
    IF CHECKCLI = 0 THEN
        RAISE ERR_CLI; 
    END IF;

    -- 再校验产品是否存在
    SELECT count(*) INTO CHECKPROD FROM PRODUCTS WHERE productname = NOMPROD;
    IF CHECKPROD = 0 THEN
        RAISE ERR_PRODUCT; 
    END IF;

    -- 确认产品存在后再查询库存
    SELECT unitsinstock - QUANTITY INTO CHECKQTY FROM products WHERE productname = NOMPROD;
    IF CHECKQTY < 0 THEN
        RAISE ERR_QTY; 
    ELSE
        DBMS_OUTPUT.PUT_LINE('NO ERRORS');
    END IF; 
EXCEPTION
    WHEN ERR_CLI THEN 
        DBMS_OUTPUT.PUT_LINE('CLIENT DOESNT EXISTS');
    WHEN ERR_PRODUCT THEN 
        DBMS_OUTPUT.PUT_LINE('PRODUCT DOESNT EXISTS');
    WHEN ERR_QTY THEN 
        DBMS_OUTPUT.PUT_LINE('NOT ENOUGH PRODUCTS');
    WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE(SQLCODE || ':'|| SQLERRM);
END;
/

补充优化建议 #

如果products表的productname字段没有唯一约束，存在重名产品时，库存查询的SELECT INTO语句会触发TOO_MANY_ROWS异常，你可以将库存查询语句修改为使用聚合函数的版本规避该问题：

SELECT NVL(MAX(unitsinstock),0) - QUANTITY INTO CHECKQTY FROM products WHERE productname = NOMPROD;

内容的提问来源于stack exchange，提问作者Juan Posso