Existence checking


Link to this posting

Postby Ursego » 22 Feb 2013, 09:41

Use EXISTS instead of COUNT to check if a record, satisfying your search criteria, exists in a table.

Elegant programming means not only producing a code which is elegant cosmetically and easy to read. For example, inefficient code doesn't look very elegant to me even if it is perfect cosmetically... I don't feel comfortable seeing the following construction which performs full table scan even if the wanted record is just the first of the million:

Code: Select all
SELECT COUNT(1)
  INTO v_count
  FROM...;
IF v_count > 0 THEN...

There is another, much nicer way to define record existence - the scan will stop immediately after finding the first occurrence:

Code: Select all
BEGIN
   SELECT 1
     INTO v_exists
     FROM DUAL
    WHERE EXISTS (SELECT 'x' FROM...);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      v_exists := 0;
END;
IF v_exists = 1 THEN...
User avatar
Ursego
Site Admin
 
Posts: 113
Joined: 19 Feb 2013, 20:33

IF you want to ((lose weight) OR (have unbelievable (brain function AND mental clarity))) THEN click:




free counters

eXTReMe Tracker