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

Question

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 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; 

Answer 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.