Record existence checking

Share this topic:



Link to this posting

Postby Ursego » 22 Feb 2013, 09:41

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...
User avatar
Ursego
Site Admin
 
Posts: 111
Joined: 19 Feb 2013, 20:33

Return to Ocean of Databases

Who is online

Users browsing this forum: No registered users and 1 guest


Record existence checking

Share this topic:


If you think that this site is not too bad, please LIKE it in Facebook. Thanks!





free counters

eXTReMe Tracker