Re: SQL select into

From: Matt B. <mcb_at_fightspam.sd.znet.com>
Date: Thu, 28 Sep 2000 23:30:59 -0700
Message-ID: <st8dgsstgtna28_at_corp.supernews.com>

"Greg" <actiris_at_loxinfo.co.th> wrote in message news:39D1AC38.AD5552A0_at_loxinfo.co.th...
> I have a lot of select into
> query's
>
> If U make a select into and there are no records
>
> U got the error ORA-01403: no data found
>
> so I do first a
>
> select count (....)
> into counter
> ...
>
> if counter = 1 then
> select .....
> into
> .....
> end if;
>
> Now im wondering if there is a better solution then
> the way I do it......sure is.....
>
> like do it all in one step
>
> Thanks Greg

begin;

select ...
into ...
from ...
where...;

exception
when no_data_found then
do_something;
end;

An even better way is to use a cursor:

declare
cursor blah is

select ...
from ...
where ...;

begin
open blah;
fetch blah into ...;
if blah%notfound then

   close blah;
   do_something_when_no_data_is_found;
else

   close blah;
   do_something_when_there_is_data;
end if;
end;

I learned recently from someone on this NG (Sybrand?) that this is better because the latter only does one hit whereas select-into always does two - once for what you want (one row) and again to see if there are more (how it determines if it needs to raise the TOO_MANY_ROWS exception).

-Matt Received on Fri Sep 29 2000 - 08:30:59 CEST