Re: Q: To check a record's existence in a table, FAST!

From: Andy Hardy <aph_at_ahardy.demon.co.uk>
Date: 1997/04/25
Message-ID: <zexYzBAGtOYzEwyv@ahardy.demon.co.uk>#1/1

In article <335E273E.3B24_at_internex.com.au>, Jonas Plumecocq <jp_at_internex.com.au> writes
[severe snip]
> The row count is immaterial to me, i just want to know if any record
>
> exists or not. The count(*) used in second option is used just to
> facilitate the checking for value return (0 or 1) in a SQL statement
> .
> like
>
> select count(*) into nCount from table where x='aa' and rownum=1;
> if nCount = 1 then
> ....
> end if;

This is fine - when using the rownum pseudo-column Oracle assigns a number with each row returned using a COUNT function (not to be confused with 'count(*)'). If the rownum is specified in the where clause, it will use a COUNT STOP - this will end the query when the count exceeds that specified.

I think that I'd prefer to see an IF %FOUND or EXCEPTION defined the above block to cater more clearly for when the record does not exist.

Andy
Andy Hardy. PGP key available on request

Received on Fri Apr 25 1997 - 00:00:00 CDT