Re: count(*) vs. a cursor when determining the existing record on primary key

I can't wait to see what the experts say, but I thunked on it a few and this is what I came up with.

With a count                                           With a local var and exception                             With a cursor

declare                     |  declare                          |  declare


                            |                                   |

  l_count integer( 1 );     |    l_key my_table.p_key%type;     |    l_key my_table.p_key%type;


                            |                                   |

begin                       |  begin                            |    cursor c_check_for_key is


                            |                                   |      select p_key

  select count(* )          |    select p_key                   |        from my_table
    into l_count            |      into l_key                   |       where p_key = 'a_value';
    from my_table           |      from my_table                |
   where p_key = 'a_value'  |     where p_key = 'a_value'       |  begin
     and row_count = 1;     |       and row_count = 1;          |


                            |                                   |    open c_check_for_key;

  if l_count = 0            |    do something found;            |
  then                      |                                   |    fetch c_check_for_key
    do something not found  |  exception                        |     into l_key;
  else                      |    when no_data_found             |
    do something found      |      then                         |    if c_check_for_key%notfound
  end if;                   |        do something wasn't found  |    then                       


                            |                                   |      do something not found   

end;                        |  end;                             |    else                       
                                                                |      do something found       
                                                                |    end if;                    
                                                                |                               
                                                                |    close c_check_for_key;     
                                                                |                               
                                                                |  end;

Personally, I prefer the first example, using a count.

"jgk" <javapda_at_yahoo.com> wrote in message news:6851e45e.0306161018.1e31fa7f_at_posting.google.com...

> If you have a table with a primary key and you want to determine if a
> particular primary key exists what is the fastest way to accomplish
> this?
> 
> In Steve Feuerstein's great book "Oracle PL/SQL Best Practices"
> SQL-08: Use COUNT only when the actual number of occurrences is
> needed...he gives some case(s) where a cursor should be used instead
> of the count(*) function.
> 
> If you have a table.  The table has a primary key field.  If you want
> to test for existence of a matching primary key is it better to use
> the COUNT function and see if there is a match > 0 (it would always be
> 1 or zero)...or should a cursor be used?
> 
> Thanks,
> 
> JGK