Select into is essentially shorthand for:
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;
So it makes sense that your variables have the values from the first row.
But that said, you can't rely on this behaviour. As the docs say:
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(emphasis mine)
https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/selectinto_statement.htm#LNPLS01345 To show this undefined nature, watch what happens when you place the query inside a procedure with the variables as out parameters:
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 rowsSo when it comes to variable values after TOO_MANY_ROWS, all bets are off.
For further discussion on exceptions in PL/SQL, see:
https://blogs.oracle.com/plsql-and-ebr/a-surprising-program