2010-01-25

select into

How come lines 9 and 10 do not execute in the following is_active function? Documentation says that use into_clause only when there is one row in the result. Should the remaining code be ignored when there are no rows. Be sure to have exactly that one row available when using select into structure. Count(*) is a way here to be sure there is the one row.



SQL> CREATE OR REPLACE function is_active(i_is_active number)
2 return number as
3 ret number := 0;
4 begin
5 dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
6 select 1 into ret
7 from dual
8 where i_is_active = 1;
9 dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
10 if ret is null then ret := 0;
11 end if;
12 return ret;
13 end;
14 /

Function created.

SQL>
SQL> select is_active(1),is_active(0) from dual
2 ;

IS_ACTIVE(1) IS_ACTIVE(0)
------------ ------------
1

-0-before-1-
-1-after -1-
-0-before-0-
SQL>
SQL>
SQL> CREATE OR REPLACE function is_active_usingcount(i_is_active number)
2 return number as
3 ret number := 0;
4 begin
5 dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
6 select count(*) into ret
7 from dual
8 where i_is_active = 1;
9 dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
10 return ret;
11 end;
12 /

Function created.

SQL>
SQL> select is_active_usingcount(1),is_active_usingcount(0) from dual
2 ;

IS_ACTIVE_USINGCOUNT(1) IS_ACTIVE_USINGCOUNT(0)
----------------------- -----------------------
1 0

-0-before-1-
-1-after -1-
-0-before-0-
-0-after -0-



You get "ORA-01422: exact fetch returns more than requested number of rows" if there are more than one row. Should there be an exception also to the case when there are no rows?

Update 4.11.2010:
Well there is a exception thrown. Just not catching it anywhere.

CREATE OR REPLACE function is_active(i_is_active number)
return number as
ret number := 0;
begin
dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
select 1 into ret
from dual
where i_is_active = 1;
dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
if ret is null then ret := 0;
end if;
return ret;
EXCEPTION
WHEN NO_DATA_FOUND THEN return -1;
end;
/

select is_active(1),is_active(0) from dual;

IS_ACTIVE(1) IS_ACTIVE(0)
------------ ------------
1 -1

No comments:

Post a Comment

About Me

My Photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.