Oracle PL/SQL中SELECT INTO变量时的异常处理问题

问题根源 #

1. SELECT INTO的异常触发逻辑：当SELECT ... INTO语句返回0行时，Oracle PL/SQL会直接抛出内置的NO_DATA_FOUND异常（错误码-1403），此时会立刻跳转到EXCEPTION块执行，根本不会执行到你写的IF SQL%NOTFOUND THEN判断逻辑。
2. 错误码绑定错误：你用PRAGMA EXCEPTION_INIT绑定的错误码-2293，对应的是CHECK约束违反的错误，和“无匹配记录”的场景完全无关，所以即使触发了异常，也不会进入ct_exception的处理分支。

替代处理方案 #

方案1：直接使用内置异常（推荐） #

利用Oracle自带的NO_DATA_FOUND和TOO_MANY_ROWS异常，这是处理单条查询最简洁的方式：

DECLARE
    prodid NUMBER(22);
BEGIN
    SELECT c.pid INTO prodid FROM catalog1 c WHERE sid = &a;
    -- 查询成功后的后续逻辑
    DBMS_OUTPUT.PUT_LINE('Found Product ID: ' || prodid);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Record not found');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Multiple records match the given sid');
END;

方案2：自定义异常绑定正确错误码 #

如果一定要用自定义异常，需绑定NO_DATA_FOUND对应的错误码-1403：

DECLARE
    ct_exception EXCEPTION;
    prodid NUMBER(22);
    PRAGMA EXCEPTION_INIT(ct_exception, -1403); -- 绑定无记录的错误码
BEGIN
    SELECT c.pid INTO prodid FROM catalog1 c WHERE sid = &a;
EXCEPTION
    WHEN ct_exception THEN
        DBMS_OUTPUT.PUT_LINE('Record not found');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Multiple records match the given sid');
END;

方案3：用COUNT判断记录数（适合复杂逻辑） #

如果需要在无异常的情况下处理记录数判断，可以先统计行数：

DECLARE
    prodid NUMBER(22);
    record_count NUMBER;
BEGIN
    SELECT COUNT(*), MAX(pid) 
    INTO record_count, prodid 
    FROM catalog1 c 
    WHERE sid = &a;

    IF record_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Record not found');
    ELSIF record_count > 1 THEN
        DBMS_OUTPUT.PUT_LINE('Multiple records match the given sid');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Found Product ID: ' || prodid);
    END IF;
END;

内容的提问来源于stack exchange，提问作者Atharv desai