Oracle 批量收集找不到数据

嗨，汤姆，

我这里有两个问题。你能澄清一下这些吗？


Q1。

我知道我们无法在select bulk-collect中获得未找到数据的异常，但我想做的是，在尝试处理时未找到或抛出错误的输入数据。
我有一个输入表，它有50个ID，只有45个ID存在于数据库中，我想停止处理第一个ID不存在于数据库或抛出错误也发回调用者说这个ID不存在。


Q2。

类似于上面的一个，我有输入表，我试图处理50更新使用forall，我想停止执行，当第一个错误发生并发送回ID失败。


你能建议我一些想法吗？

我不清楚你在这里做什么。

1.如果您使用批量收集，并且查询不返回任何行，则只会得到一个空集合。没有例外:

declare
  n dbms_sql.number_table;
begin
  select 1 bulk collect into n 
  from dual where 1 = 0;
  
  dbms_output.put_line ( 'Fetched ' || n.count );
end;
/

0

当您尝试访问不存在的数组元素时，会引发NO_DATA_FOUND。Bulk collect填充一个密集的数组，第一个索引是1:

declare
  n dbms_sql.number_table;
begin
  with rws as (
    select level * 2 x from dual
    connect by level <= 5
  )
    select x bulk collect into n
    from   rws
    where  x between 1 and 6;
  
  /* Loop through the array to find elements */
  for i in 1 .. n.count loop
    dbms_output.put_line ( 'index = ' || i || ' value = ' || n(i) );
  end loop;
  
  /* No element 10 in array => this raises NDF; */
  dbms_output.put_line ( n(10) );
end;
/
index = 1 value = 2
index = 2 value = 4
index = 3 value = 6

ORA-01403: no data found

因此，如果您已经批量收集结果，并且想要查找结果中缺少的行值，则需要

-遍历您搜索的值
-检查这些是否在您获取的数组中
-酌情提出错误

例如。

declare
  type n_arr is table of number;
  n n_arr;
begin
  with rws as (
    select level * 2 x from dual
    connect by level <= 5
  )
    select x bulk collect into n
    from   rws
    where  x between 1 and 6;
  
  /* Loop through the array to find elements */
  for i in 1 .. 6 loop
    if i not member of n then 
      dbms_output.put_line ( 'value ' || i || ' not in array' );
    end if;
  end loop;
end;
/
value 1 not in array
value 3 not in array
value 5 not in array

2. Forall一遇到错误就停止处理您可以通过检查错误处理程序中的sql % bulk_exceptions来找到哪个元素触发了它:

create table t (
  c1 int not null,
  c2 int not null
);

insert into t 
with rws as (
  select level x from dual
  connect by level <= 3
)
  select x, x from rws;
commit;

declare
  n dbms_sql.number_table;
begin
  n := dbms_sql.number_table ( 1 => 1, 2 => 2, 3 => 3 );
  forall i in 1 .. n.count
    update t
    set    c2 = case c1 when 2 then null else c2 end
    where  c1 = n(i);
    
exception
  when others then 
    dbms_output.put_line ( 'Updated ' || sql%rowcount );
    dbms_output.put_line ( 'Error index = ' || sql%bulk_exceptions(1).error_index );
    raise;
end;
/
Updated 1
Error index = 2

ORA-01407: cannot update ("CHRIS"."T"."C2") to NULL