ORA-01403:no data found 及 select a into b 空值

1. 存储过程中 ORA-01403: no data found 错误

在存储过程中，select 字段名  into  变量 from 表名 where .........;

如果查询出来为空时, 会出现  ORA-01403: no data found 的错误

测试表：

create table TEST_TABLE
(
  T_ID   NUMBER,
  T_NAME VARCHAR2(20)
) ;

测试存储过程：

create or replace procedure pro_test is
v_id test_table.t_id%type;
begin
  select t_id into v_id from test_table where rownum = 1;
end pro_test;

错误：

2. 解決办法:

造成错误的原因主要是数据库中没有对应的数据。而当直接使用该查询语句时，是不会报错的，返回0记录。

2.1. 对查询字段使用聚合函数

增加一个min函数。这主要是因为聚合函数没有找到数据时，会返回0，而不是null。

存储过程改为：

create or replace procedure pro_test is
v_id test_table.t_id%type;
begin
  select min(t_id) into v_id from test_table where rownum = 1;
end pro_test;

这些主要是聚合类型的函数，如sum,count,max,min等。其的函数则不行，如to_char,substr.

另外，如使用nvl，即

  select nvl(t_id,1) into v_id from test_table where rownum = 1;

是没效果的，还是会报异常。nvl只对null值处理，而select t_id into  v_id from table是返回空记录。

缺点：1. 使用集合函数后可能偏离你所需要查找的值；2. 在数据量比较大时，这种方法明显会降低效率。

参考：oracle 中使用 select a into b 时遇到空值问题

2.2. 使用异常处理

即加入异常处理

create or replace procedure pro_test is
v_id test_table.t_id%type;
begin
  select t_id into v_id from test_table where rownum = 1;
  exception
    when no_data_found then -- no_data_found 也可以换成 others
      null;  --其它操作。。。
end pro_test;

但加入异常处理后，当抛出异常，程序无法继续下面的语句。即我们不能从异常句柄再重新回到当前块。这时可以增加begin...end 模拟TRY..CATCH..块

create or replace procedure pro_test is
  v_id test_table.t_id%type;
begin
  begin
    select t_id into v_id from test_table where rownum = 1;
  exception
    when no_data_found then
      -- no_data_found 也可以换成 others
      null; --其它操作。。。
  end;
  insert into test_table values (1, 'test');
end pro_test;

另外还有一个问题就是，当多个地方抛出同一个异常时，如果判别是哪条语句抛出的异常呢？

可以使用定位变量标记异常发生点。

create or replace procedure pro_test is
  v_id test_table.t_id%type;
  stmt integer := 1;
begin
  select t_id into v_id from test_table where rownum = 1;
  stmt := 2;
  select t_id into v_id from test_table where rownum = 1;

exception
  when no_data_found then  
    insert into errors values ('Error statment', stmt); 
end pro_test;

参考： 第七章 控制PL/SQL错误

2.3. 使用游标替代

create or replace procedure pro_test is
  v_id test_table.t_id%type;
  
  type cursor_type is ref cursor;
  cur cursor_type;
begin
  open cur for select t_id from test_table where rownum = 1;
  fetch cur into v_id;
  close cur;
end pro_test;

参考：ORA-01403:no data found exception的解决小道(转)  

其他：Oracle中的Null值解析