Oracle 当引发过多行异常时，选择into子句变量初始化

嗨，

我创建了一个表，并在2列中插入了具有重复值的两行。

我写了一个匿名块 (选择) 通过在WHERE子句中传递2个值将数据提取到3个变量中，但这些列中的值不是唯一的，因此引发了太多行异常。

仍然是它的初始化变量与数据从第一行，但问题是有时它初始化两个变量有时一个变量不是所有变量提到的in子句。

我想知道为什么？

选择进入本质上是:

open cur;

fetch cur into var;
if cur%notfound then
  raise no_data_found;
end if;

fetch cur into var;
if cur%found then
  raise too_many_rows;
end if;

close cur;

因此，您的变量具有第一行的值是有意义的。

但这就是说，您不能依靠这种行为。正如医生所说:

By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure your WHERE clause is specific enough to only match one row

(强调我的)

https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/selectinto_statement.htm#LNPLS01345

要显示这种未定义的性质，请注意将查询放在具有变量作为out参数的过程中时会发生什么:

create or replace procedure p ( 
  l_cust_item_id  out number,
  l_inv_item_name out varchar2, 
  l_inv_item_id   out varchar2,
  p_cust_name     in varchar2,
  p_cust_item_name in varchar2
) as
begin

  dbms_output.put_line('************before select************'); 
  dbms_output.put_line('l_cust_item_id :-'||l_cust_item_id||chr(10)||'l_inv_item_name :-'||l_inv_item_name||chr(10)||'l_inv_item_id :-'||l_inv_item_id); 

  select mcixrf.customer_item_id, 
         concatenated_segments, 
         inventory_item_id 
    into l_cust_item_id, l_inv_item_name, l_inv_item_id 
    from mtl_customer_item_xrefs_v mcixrf 
   where 1 = 1 
     and mcixrf.customer_name = p_cust_name 
     and mcixrf.customer_item_number = p_cust_item_name;
       
end p;
/

declare 
  l_cust_item_id  number; 
  l_inv_item_name varchar2(50); 
  l_inv_item_id   varchar2(50); 
begin
  p(l_cust_item_id, l_inv_item_name, l_inv_item_id, 'Oracle', '456123');
exception 
  when too_many_rows then 
    dbms_output.put_line('************exception ***********'); 
    dbms_output.put_line('l_cust_item_id :-'||l_cust_item_id||chr(10)||'l_inv_item_name :-'||l_inv_item_name||chr(10)||'l_inv_item_id :-'||l_inv_item_id); 
    dbms_output.put_line(sqlerrm); 
end;
/

************before select************
l_cust_item_id :-
l_inv_item_name :-
l_inv_item_id :-
************exception ***********
l_cust_item_id :-
l_inv_item_name :-
l_inv_item_id :-
ORA-01422: exact fetch returns more than requested number of rows

所以当涉及到TOO_MANY_ROWS之后的变量值时，所有的赌注都关闭了。

有关PL/SQL中异常的进一步讨论，请参见:

https://blogs.oracle.com/plsql-and-ebr/a-surprising-program