Proper way of checking if row exists in table in PL/SQL block

I was writing some tasks yesterday and it struck me that I don’t really know THE PROPER and ACCEPTED way of checking if row exists in table when I’m using PL/SQL.

For examples sake let’s use table:

PERSON (ID, Name);

Obviously I can’t do (unless there’s some secret method) something like:

BEGIN 
  IF EXISTS SELECT id FROM person WHERE ID = 10; 
    -- do things when exists
  ELSE
    -- do things when doesn't exist
  END IF;
END;

So my standard way of solving it was:

DECLARE
  tmp NUMBER;
BEGIN 
  SELECT id INTO tmp FROM person WHERE id = 10; 
  --do things when record exists
EXCEPTION
  WHEN no_data_found THEN
  --do things when record doesn't exist
END;

However I don’t know if it’s accepted way of doing it, or if there’s any better way of checking, I would really apprieciate if someone could share their wisdom with me.

Answer

I wouldn’t push regular code into an exception block. Just check whether any rows exist that meet your condition, and proceed from there:

declare
  any_rows_found number;
begin
  select count(*)
  into   any_rows_found
  from   my_table
  where  rownum = 1 and
         ... other conditions ...

  if any_rows_found = 1 then
    ...
  else
    ...
  end if;

Advertisement

Answer