我编写了以下查询,期望在找不到行时抛出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.
