Re: Preferred way to check a record exists

From: Cris Carampa <cris119_at_operamail.com>
Date: Thu, 18 Sep 2003 11:47:55 +0200
Message-ID: <bkbv2r$bho$1@panco.nettuno.it>

David wrote:

> What's the preferred way to check whether a record actually exists in a
> table? If I wanted to check whether author is listed, is it naive to do
> something like this, or it better to catch the NO_DATA_FOUND exception?
> Maybe there's another approach altogether...

I think count(*) may have performance problems. If I cannot assume that the select returns exacly one row I prefer to use this approach instead:

declare

   cursor c_persons (

     addr in varchar2,
     name in varchar2

   ) is
   select 'x'
   from people
   where AddAuthor.address = addr
   and author = name ;

   found char(1) ;

begin

   open c_persons(Address, Name) ;
   fetch c_persons into found ;
   close c_persons ;

IF found is not null THEN

         DBMS_OUTPUT.PUT_LINE (author || ' in ' || AddAuthor.address ||
             ' was found in the database, now registering as an author ...
');
     ELSE
         DBMS_OUTPUT.PUT_LINE (author || ' in ' || AddAuthor.address ||
             ' not found');

-- 
Cris Carampa (spamto:cris119_at_operamail.com)

"Mussolini non ha mai ammazzato nessuno"
(Silvio Berlusconi)

Received on Thu Sep 18 2003 - 04:47:55 CDT