CODEWITHSUNDEEP
sqloracle-databaseplsql
Mr. Boy
Mr. Boy

Reputation: 63748

What does SELECT INTO var do if the statement returns more than one row?

Given a PL/SQL function which looks a bit like:

Function f(pVar IN VARCHAR2) return VARCHAR2 IS
      vs_ret VARCHAR2 := NULL;
    BEGIN
      select name into vs_ret from people where nickname = pVar;
      return vs_ret;
    END f;

What happens if people.nickname has no uniqueness constraint? What happens if (when) two people have the same nickname - will it lead to an error or just return the value from first row the statement returns?

This appears to be existing functionality which I'm tweaking, so options are somewhat limited to change everything.

Upvotes: 0

Views: 5474

Answers (3)

MT0
MT0

Reputation: 168061

It will throw a predefined TOO_MANY_ROWS (ORA-01422) exception. You can handle the exception like this:

CREATE FUNCTION f(
  pVar IN VARCHAR2
) RETURN VARCHAR2
IS
  vs_ret VARCHAR2;
BEGIN
  SELECT name
  INTO   vs_ret
  FROM   people
  WHERE  nickname = pVar;

  RETURN vs_ret;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    RETURN NULL; -- or you could do: RETURN 'Error: Too Many Rows';
  WHEN NO_DATA_FOUND THEN
    RETURN NULL; -- or you could do: RETURN 'Error: Not Found';
END f;

Or, you can leave the exception unhandled; in which case, the exception will get passed back up the hierarchy of calling blocks and each will get a chance to handle it and if it remains unhandled will terminate the query with a ORA-01422: exact fetch returns more than requested number of rows.

An alternative, if you only want the first name returned regardless of how many matches there actually are, is to add AND ROWNUM = 1 to the WHERE clause of the SELECT query (that way there will never be more than one row returned - although there could still be zero rows returned).

Another alternative, if you really do want multiple values (or no values) returned, is to use BULK COLLECT INTO and a collection:

CREATE FUNCTION f(
  pVar IN VARCHAR2
) RETURN SYS.ODCIVARCHAR2LIST
IS
  vs_ret SYS.ODCIVARCHAR2LIST;
BEGIN
  SELECT name
  BULK COLLECT INTO vs_ret
  FROM   people
  WHERE  nickname = pVar;

  RETURN vs_ret;
END f;

Upvotes: 3

hol
hol

Reputation: 8423

You will get an error, that you might want to catch if you know something meaningful to do when the error occurs.

drop table people;
create table people (name varchar2(200), nickname varchar2(200));
insert into people values('name1','nick1');
insert into people values('name1','nick1');
select * from people;
create or replace function f(pVar IN VARCHAR2) 
return VARCHAR2 
IS
 vs_ret people.name%type;
BEGIN
  select name into vs_ret from people where nickname = pVar;
  return vs_ret;
END f;
select f('nick1') from dual;    
--- ==> error
create or replace function f(pVar IN VARCHAR2) 
return VARCHAR2 
IS
 vs_ret people.name%type;
BEGIN
  select name into vs_ret from people where nickname = pVar;
  return vs_ret;
exception
  when TOO_MANY_ROWS then
    return null; -- This is no good solution but just to demo it
END f;
select f('nick1') from dual;    
-- ==> null as Output

Script Output

Table dropped.
Table created.
1 row created.
1 row created.

NAME                                                                            
--------------------------------------------------------------------------------
NICKNAME                                                                        
--------------------------------------------------------------------------------
name1                                                                           
nick1                                                                           

name1                                                                           
nick1                                                                           


2 rows selected.
Function created.
select f('nick1') from dual
       *
Error at line 1
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "DB.F", line 6

Function created.

F('NICK1')                                                                      
--------------------------------------------------------------------------------

1 row selected.

Upvotes: 0

Bryan Dellinger
Bryan Dellinger

Reputation: 5294

unsure if this is useful to you but in instances where I don't care about too many rows or no data found I change it to a cursor.

that way in the no data found it doesn't go into the loop, and for too many rows it just loops that many times.

/* Formatted on 12/17/2015 8:18:33 AM (QP5 v5.115.810.9015) */
FUNCTION f (pVar IN VARCHAR2)
   RETURN VARCHAR2
IS
   vs_ret   VARCHAR2 := NULL;
BEGIN
   FOR c IN (SELECT   name
               FROM   people
              WHERE   nickname = pVar)
   LOOP
      vs_ret := c.name;
   END LOOP;

   RETURN vs_ret;
END f;

Upvotes: 0

Related Questions