Advanced Search

Return to Snippet

Revision: 8382
at September 17, 2008 17:36 by stews
Initial Code 
CREATE OR REPLACE FUNCTION person_exists(person_id IN VARCHAR2) RETURN BOOLEAN IS
    l_count PLS_INTEGER;
BEGIN
    SELECT DISTINCT 1 
      INTO l_count 
      FROM people 
     WHERE = person_id;

    RETURN TRUE;
EXCEPTION
    WHEN no_data_found THEN
        RETURN FALSE;
    WHEN too_many_rows THEN
        RETURN TRUE;
END person_exists;
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    
I often need to know if a record exists in a table.  This method is probably the most efficient.  Too many people don't use the exceptions that Oracle provides, which is a shame.

I think I got it from Tom Kyte's site?  Or Steven Feuerstein?

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Record Exists function template

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    
table

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
PL/SQL