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.

Solution 1:

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;

Solution 2:

IMO code with a stand-alone SELECT used to check to see if a row exists in a table is not taking proper advantage of the database. In your example you've got a hard-coded ID value but that's not how apps work in "the real world" (at least not in my world - yours may be different :-). In a typical app you're going to use a cursor to find data - so let's say you've got an app that's looking at invoice data, and needs to know if the customer exists. The main body of the app might be something like

FOR aRow IN (SELECT * FROM INVOICES WHERE DUE_DATE < TRUNC(SYSDATE)-60)
LOOP
  -- do something here
END LOOP;

and in the -- do something here you want to find if the customer exists, and if not print an error message.

One way to do this would be to put in some kind of singleton SELECT, as in

-- Check to see if the customer exists in PERSON

BEGIN
  SELECT 'TRUE'
    INTO strCustomer_exists
    FROM PERSON
    WHERE PERSON_ID = aRow.CUSTOMER_ID;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    strCustomer_exists := 'FALSE';
END;

IF strCustomer_exists = 'FALSE' THEN
  DBMS_OUTPUT.PUT_LINE('Customer does not exist!');
END IF;

but IMO this is relatively slow and error-prone. IMO a Better Way (tm) to do this is to incorporate it in the main cursor:

FOR aRow IN (SELECT i.*, p.ID AS PERSON_ID
               FROM INVOICES i
               LEFT OUTER JOIN PERSON p
                 ON (p.ID = i.CUSTOMER_PERSON_ID)
               WHERE DUE_DATA < TRUNC(SYSDATE)-60)
LOOP
  -- Check to see if the customer exists in PERSON

  IF aRow.PERSON_ID IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Customer does not exist!');
  END IF;
END LOOP;

This code counts on PERSON.ID being declared as the PRIMARY KEY on PERSON (or at least as being NOT NULL); the logic is that if the PERSON table is outer-joined to the query, and the PERSON_ID comes up as NULL, it means no row was found in PERSON for the given CUSTOMER_ID because PERSON.ID must have a value (i.e. is at least NOT NULL).

Share and enjoy.

Solution 3:

Many ways to skin this cat. I put a simple function in each table's package...

function exists( id_in in yourTable.id%type ) return boolean is
  res boolean := false;
begin
  for c1 in ( select 1 from yourTable where id = id_in and rownum = 1 ) loop
    res := true;
    exit; -- only care about one record, so exit.
  end loop;
  return( res );
end exists;

Makes your checks really clean...

IF pkg.exists(someId) THEN
...
ELSE
...
END IF;

Solution 4:

select nvl(max(1), 0) from mytable;

This statement yields 0 if there are no rows, 1 if you have at least one row in that table. It's way faster than doing a select count(*). The optimizer "sees" that only a single row needs to be fetched to answer the question.

Here's a (verbose) little example:

declare
  YES constant      signtype := 1;
  NO  constant      signtype := 0;
  v_table_has_rows  signtype;
begin

  select nvl(max(YES), NO)
    into v_table_has_rows
    from mytable -- where ...
  ;

  if v_table_has_rows = YES then
    DBMS_OUTPUT.PUT_LINE ('mytable has at least one row');
  end if;

end;