Proper way of checking if row exists in table in PL SQL block

Question

I was writing some tasks yesterday and it struck me that I don t really know THE PROPER and ACCEPTED way of checking if row exists in table when I m using  PL SQL  For examples sake let s use table  PERSON  ID  Name     Obviously I can t do  unless there s some secret method  something like  BEGIN    IF EXISTS SELECT id FROM person WHERE ID   10       -- do things when exists   ELSE     -- do things when doesn t exist   END IF  END   So my standard way of solving it was  DECLARE   tmp NUMBER  BEGIN    SELECT id INTO tmp FROM person WHERE id   10     --do things when record exists EXCEPTION   WHEN no data found THEN   --do things when record doesn t exist END    However I don t know if it s accepted way of doing it  or if there s any better way of checking  I would really apprieciate if someone could share their wisdom with me

User · Answer

If you are using an explicit cursor  It should be as follows   DECLARE    CURSOR get id IS      SELECT id        FROM person        WHERE id   10     id value  person id ROWTYPE  BEGIN     OPEN get id     FETCH get id INTO id value       IF  get id FOUND  THEN      DBMS OUTPUT PUT LINE  Record Found        ELSE      DBMS OUTPUT PUT LINE  Record Not Found        END IF     CLOSE get id   EXCEPTION   WHEN no data found THEN   --do things when record doesn t exist END

User · Answer

Select  YOU WILL SEE ME  as ANSWER from dual where exists  select 1 from dual where 1   1    Select  YOU CAN NOT SEE ME  as ANSWER from dual where exists  select 1 from dual where 1   0    Select  YOU WILL SEE ME  TOO  as ANSWER from dual where not exists  select 1 from dual where 1   0

User · Answer

IMO code with a stand-alone SELECT used to check to see if a row exists in a table is not taking proper advantage of the database  In your example you ve got a hard-coded ID value but that s not how apps work in  the real world   at least not in my world - yours may be different  -   In a typical app you re going to use a cursor to find data - so let s say you ve got an app that s looking at invoice data  and needs to know if the customer exists  The main body of the app might be something like  FOR aRow IN  SELECT   FROM INVOICES WHERE DUE DATE  lt  TRUNC SYSDATE -60  LOOP   -- do something here END LOOP    and in the -- do something here you want to find if the customer exists  and if not print an error message   One way to do this would be to put in some kind of singleton SELECT  as in  -- Check to see if the customer exists in PERSON  BEGIN   SELECT  TRUE      INTO strCustomer exists     FROM PERSON     WHERE PERSON ID   aRow CUSTOMER ID  EXCEPTION   WHEN NO DATA FOUND THEN     strCustomer exists     FALSE   END   IF strCustomer exists    FALSE  THEN   DBMS OUTPUT PUT LINE  Customer does not exist     END IF    but IMO this is relatively slow and error-prone  IMO a Better Way  tm  to do this is to incorporate it in the main cursor   FOR aRow IN  SELECT i    p ID AS PERSON ID                FROM INVOICES i                LEFT OUTER JOIN PERSON p                  ON  p ID   i CUSTOMER PERSON ID                 WHERE DUE DATA  lt  TRUNC SYSDATE -60  LOOP   -- Check to see if the customer exists in PERSON    IF aRow PERSON ID IS NULL THEN     DBMS OUTPUT PUT LINE  Customer does not exist       END IF  END LOOP    This code counts on PERSON ID being declared as the PRIMARY KEY on PERSON  or at least as being NOT NULL   the logic is that if the PERSON table is outer-joined to the query  and the PERSON ID comes up as NULL  it means no row was found in PERSON for the given CUSTOMER ID because PERSON ID must have a value  i e  is at least NOT NULL    Share and enjoy

User · Answer

You can do EXISTS in Oracle PL SQL  You can do the following  DECLARE     n rowExist NUMBER    0  BEGIN     SELECT CASE WHEN EXISTS         SELECT 1       FROM person       WHERE ID   10       THEN 1 ELSE 0 INTO n rowExist END FROM DUAL           IF n rowExist   1 THEN        -- do things when it exists     ELSE        -- do things when it doesn t exist     END IF   END     Explanation  In the query nested where it starts with SELECT CASE WHEN EXISTS and after the parenthesis  SELECT 1 FROM person WHERE ID   10  it will return a result if it finds a person of ID of 10  If the there s a result on the query then it will assign the value of 1 otherwise it will assign the value of 0 to n rowExist variable  Afterwards  the if statement checks if the value returned equals to 1 then is true otherwise it will be 0   1 and that is false

User · Answer

select max  1     into my if has data   from MY TABLE    X  where X my field     my condition    and rownum         1    Not iterating through all records   If MY TABLE has no data  then my if has data sets to null

User · Answer

I wouldn t push regular code into an exception block  Just check whether any rows exist that meet your condition  and proceed from there   declare   any rows found number  begin   select count      into   any rows found   from   my table   where  rownum   1 and              other conditions        if any rows found   1 then           else           end if

User · Answer

select nvl max 1   0  from mytable    This statement yields 0 if there are no rows  1 if you have at least one row in that table  It s way faster than doing a select count     The optimizer  sees  that only a single row needs to be fetched to answer the question   Here s a  verbose  little example   declare   YES constant      signtype    1    NO  constant      signtype    0    v table has rows  signtype  begin    select nvl max YES   NO      into v table has rows     from mytable -- where            if v table has rows   YES then     DBMS OUTPUT PUT LINE   mytable has at least one row      end if   end

User · Answer

Many ways to skin this cat   I put a simple function in each table s package     function exists  id in in yourTable id type   return boolean is   res boolean    false  begin   for c1 in   select 1 from yourTable where id   id in and rownum   1   loop     res    true      exit  -- only care about one record  so exit    end loop    return  res    end exists    Makes your checks really clean     IF pkg exists someId  THEN     ELSE     END IF

[sql] Proper way of checking if row exists in table in PL/SQL block

Examples related to sql

Examples related to oracle

Examples related to select

Examples related to plsql