Page 1 of 1

Record existence checking

PostPosted: 22 Feb 2013, 09:41
by Ursego
Use EXISTS instead of COUNT to check if a record, satisfying certain 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 first or second:

Code: Select all
SELECT COUNT(1)
  INTO v_count_nr
  FROM...;
IF v_count_nr > 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_existence_flag_nr
     FROM DUAL
    WHERE EXISTS (SELECT 'x' FROM...);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      v_existence_flag_nr := 0;
END;
IF  v_existence_flag_nr = 1 THEN...