oracle – sql max函数没有出现异常

我编写了以下查询,期望在找不到行时抛出NO_DATA_FOUND异常.

BEGIN
  SELECT MAX(A_id) + 1 INTO id_variable from table_A;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
  SELECT MAX(A_id) + 1 INTO id_variable from table_A_archive;
END;

table_A中没有数据但没有抛出异常,最终id_variable值变为null.
我google’d并注意到MAX函数忽略空值但我找不到任何可以使它抛出异常的补救措施.

如何使它抛出一个异常,以便控制进入异常并查看归档表.

有没有其他选择,除了取count()然后只有count()>获取值0.

解决方法

不,它不会例外. MAX不会引发no_data_found,因为它将返回NULL值.

看到这个：

SQL> select max(a_id) from table_a;

 MAX(A_ID)
----------


SQL> select a_id from table_a;

no rows selected

SQL>

is there any other alternative than taking the count() and then getting the value only if count() > 0.

您可以拥有自定义异常,然后在值为NULL时将其引发.

例如,

SQL> CREATE TABLE table_A(a_id NUMBER);

Table created.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    id_variable NUMBER;
  3    is_null     EXCEPTION;
  4  BEGIN
  5    SELECT MAX(A_id) + 1 INTO id_variable FROM table_A;
  6    IF id_variable IS NULL THEN
  7      raise is_null;
  8    END IF;
  9  EXCEPTION
 10  WHEN is_null THEN
 11    DBMS_OUTPUT.PUT_LINE('Came into Exception');
 12  END;
 13  /
Came into Exception

PL/SQL procedure successfully completed.

SQL>

更新如果您不想引发异常并且只想在MAX返回NULL时从另一个表中进行选择,则添加IF-ELSE块.

例如,

SQL> CREATE TABLE table_A(a_id NUMBER);

Table created.

SQL> CREATE TABLE table_b(a_id NUMBER);

Table created.

SQL> INSERT INTO table_b VALUES(1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    id_variable NUMBER;
  3  BEGIN
  4    SELECT max(A_id) + 1 INTO id_variable from table_A;
  5    IF id_variable IS NULL
  6    THEN
  7    SELECT A_id + 1 INTO id_variable FROM table_b;
  8    END IF;
  9    DBMS_OUTPUT.PUT_LINE('ID value is '||id_variable);
 10  END;
 11  /
ID value is 2

PL/SQL procedure successfully completed.