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

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;

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 hereEND 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 PERSONBEGIN  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.

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;