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

oracle, plsql, select, sql

Solution

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;

Problem

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.

Original source

Related problems