Oracle PL/SQL含异常处理无输出，移除后正常的原因及解决办法

我是Oracle新手，编写了一段简单的PL/SQL程序，将查询得到的snap_id存入变量并打印。当添加处理null值的NO_DATA_FOUND异常块后，即使SQL查询有返回值（snap_id为743），程序也无输出；移除异常块后则能正常打印结果。请问为何异常块未生效？需要修改代码的哪部分？

Oracle数据库版本：19.3.0.0.0 RU

含异常处理的代码 #

set serveroutput on
DECLARE
snap number;
BEGIN
select max(dhsq.snap_id) into snap
        from dba_hist_sqlstat dhsq
        inner join dba_hist_snapshot dhss on (dhss.snap_id = dhsq.snap_id)
        where dhsq.sql_id='38mkqq9vpu6vf';
DBMS_OUTPUT.PUT_LINE(snap);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
  snap := NULL;
END;
/

执行结果：

PL/SQL procedure successfully completed.

无异常处理的代码 #

set serveroutput on
DECLARE
snap number;
BEGIN
select max(dhsq.snap_id) into snap
        from dba_hist_sqlstat dhsq
        inner join dba_hist_snapshot dhss on (dhss.snap_id = dhsq.snap_id)
        where dhsq.sql_id='38mkqq9vpu6vf';
DBMS_OUTPUT.PUT_LINE(snap);
END;
/

执行结果：

743
PL/SQL procedure successfully completed.

静态查询结果（7天内有效） #

select max(dhsq.snap_id) from dba_hist_sqlstat dhsq inner join dba_hist_snapshot dhss on (dhss.snap_id = dhsq.snap_id) where dhsq.sql_id='38mkqq9vpu6vf';

查询结果：

MAX(DHSQ.SNAP_ID)
-----------------
              743

原因分析 #

1. NO_DATA_FOUND异常根本不会触发：你用了MAX()聚合函数，这类函数有个特性——不管有没有匹配的行，都会返回一行结果。如果没有符合条件的行，MAX()会返回NULL，而不是让SELECT INTO语句无行返回。NO_DATA_FOUND异常只有在SELECT INTO完全没拿到任何行的时候才会抛出来，所以你的异常块从始至终都不会被执行。
2. 关于无输出的问题：按逻辑，保留异常块时程序应该正常输出743，因为SELECT INTO成功后会执行DBMS_OUTPUT.PUT_LINE(snap)，不会进入异常块。你说移除异常块才看到输出，大概率是工具的输出显示问题（比如缓冲没刷新），但核心问题还是你对NO_DATA_FOUND异常的使用场景理解错了。

修改方案 #

如果你想处理snap为NULL的情况（即聚合函数无有效结果返回），不需要用NO_DATA_FOUND异常，而是在SELECT之后主动判断变量是否为NULL：

修改后的代码（推荐） #

set serveroutput on
DECLARE
snap number;
BEGIN
select max(dhsq.snap_id) into snap
        from dba_hist_sqlstat dhsq
        inner join dba_hist_snapshot dhss on (dhss.snap_id = dhsq.snap_id)
        where dhsq.sql_id='38mkqq9vpu6vf';

-- 主动判断是否为NULL，处理无有效结果的情况
IF snap IS NULL THEN
  DBMS_OUTPUT.PUT_LINE('未找到匹配的snap_id');
ELSE
  DBMS_OUTPUT.PUT_LINE(snap);
END IF;
END;
/

备选方案（如需触发NO_DATA_FOUND） #

如果你一定要捕获NO_DATA_FOUND异常，需要避免使用聚合函数，改用普通SELECT INTO确保查询可能无行返回，比如用FETCH FIRST 1 ROW ONLY替代MAX()获取最大snap_id：

set serveroutput on
DECLARE
snap number;
BEGIN
select dhsq.snap_id into snap
        from dba_hist_sqlstat dhsq
        inner join dba_hist_snapshot dhss on (dhss.snap_id = dhsq.snap_id)
        where dhsq.sql_id='38mkqq9vpu6vf'
        ORDER BY dhsq.snap_id DESC
        FETCH FIRST 1 ROW ONLY;
DBMS_OUTPUT.PUT_LINE(snap);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('未找到匹配的snap_id');
END;
/

这样当查询无行返回时，才会触发NO_DATA_FOUND异常。

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