1 2 Previous Next 21 Replies Latest reply: Jun 28, 2010 10:54 AM by 6363 RSS

    How do you do an IF EXISTS

    446249
      Hi, all. I'm new to the Oracle world, coming from the SQL Server world. I'm trying to do something like this,

      if exists (select * from mytable where id = 12345) begin
      -- do some processing on existing record
      end
      else begin
      -- do some other processing
      end

      The only places I've seen EXISTS is in the WHERE clauses. Help!
        • 1. Re: How do you do an IF EXISTS
          JustinCave
          Oracle's syntax isn't as clean here. You'd have to do something like
          DECLARE 
            cnt NUMBER;
          BEGIN
            SELECT COUNT(*)
             INTO cnt
              FROM mytable
            WHERE id = 12345;

            IF( cnt = 0 )
            THEN
              ...
            ELSE
              ...
            END IF;
          END;
          Justin
          Distributed Database Consulting, Inc.
          http://www.ddbcinc.com/askDDBC

          Fixing tags

          Message was edited by:
          Justin Cave
          • 2. Re: How do you do an IF EXISTS
            446249
            Thanks, Justin. I've implemented your code and it's working fine. However, I wonder why Oracle doesn't support the IF EXISTS. It sounds like a very easy way to do a lot of the processing. Can we ask them to add it to a wish list?
            • 3. Re: How do you do an IF EXISTS
              358102
              Check this out.
              http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fundamentals.htm#sthref346
              • 4. Re: How do you do an IF EXISTS
                58566
                SQL> declare
                  2   type emp_tab is table of emp%rowtype index by pls_integer;
                  3   T_emp  emp_tab;
                  4  begin
                  5   execute immediate 'select * from emp' BULK COLLECT INTO T_emp;
                  6   if T_emp.count > 0 then
                  7     dbms_output.put_line('Exists !');
                  8   else
                  9     dbms_output.put_line('Does not Exist !');
                10   end if;
                11  end;
                12  /
                Exists !

                PL/SQL procedure successfully completed.

                SQL> Rem  Another way.....
                SQL> declare
                  2   type emp_tab is table of emp%rowtype index by pls_integer;
                  3   T_emp  emp_tab;
                  4  begin
                  5   execute immediate 'select * from emp' BULK COLLECT INTO T_emp;
                  6   if T_emp.count > 0 then
                  7     dbms_output.put_line('Exists !');
                  8   else
                  9     dbms_output.put_line('Does not Exist !');
                10   end if;
                11  end;
                12  /
                Exists !

                PL/SQL procedure successfully completed.
                Thanks
                • 5. Re: How do you do an IF EXISTS
                  58566
                  Sorry the another way should be...
                  SQL> declare
                    2   emp_cnt   pls_integer;
                    3  begin
                    4   select count(*) into emp_cnt from emp where empno = 7369;
                    5   if emp_cnt > 0 then
                    6      dbms_output.put_line('Exists !');
                    7  else
                    8    dbms_output.put_line('Does not Exist !');
                    9  end if;
                  10  end;
                  11  /
                  Exists !

                  PL/SQL procedure successfully completed.
                  • 6. Re: How do you do an IF EXISTS
                    245482
                    You should probably require that rownum <= 1 if you're just checking for existance. I agree that EXISTS should be rolled into pl/sql syntax, but then Bryn Llewellyn (the pl/sql product manager) isn't asking me.
                    • 7. Re: How do you do an IF EXISTS
                      446249
                      Thanks, everybody, for your great responses. I hope Oracle will add this feature to some future version of PL/SQL. In the meantime, I think the COUNT function will have to do.
                      • 8. Re: How do you do an IF EXISTS
                        Laurent Schneider
                        declare
                            cursor c is select null from emp where ename='SCOTT';
                            x number;
                        begin
                            open c;
                            fetch c into x;
                            if (c%NOTFOUND) then dbms_output.put_line('NOT FOUND'); else dbms_output.put_line('FOUND'); end if;
                        end;
                        /
                        • 9. Re: How do you do an IF EXISTS
                          John Spencer
                          Depending on exactly what you want to do in your processing steps, I might model that in a couple of different ways. If you are sure that there is either zero or one row matching the condition (i.e. id is a promary key) I might do something like:
                          DECLARE
                             my_tab_rec mytable%ROWTYPE;
                          BEGIN
                             SELECT * INTO my_tab_rec
                             FROM mytable
                             WHERE id = 12345;
                             <do processing on existing record>
                             -- You can address columns as my_tab_rec.column1 etc.
                          EXCEPTION
                             WHEN NO_DATA_FOUND THEN
                                <do other processing>
                          END;
                          If you may have more than one row for an id, and you want to replicate SQLServer's behaviour (which would leave you with the last row returned by the query), you would need to do something more like:
                          DECLARE
                             CURSOR my_cur AS
                                SELECT * FROM mytable
                                WHERE id = 12345;
                             my_tab_rec mytable%ROWTYPE;
                             my_tab_prevrec mytable%ROWTYPE;
                          BEGIN
                             OPEN my_cur;
                             LOOP
                                FETCH my_cur INTO my_tab_rec;
                                EXIT WHEN my_cur%NOTFOUND;
                                my_tab_prevrec := my_tab_rec;
                             END LOOP;
                             IF my_tab_prevrec.id IS NOT NULL THEN
                                <do processing on existing record>
                             ELSE
                                <do other processing>
                             END IF;
                          END;
                          John
                          • 10. Re: How do you do an IF EXISTS
                            115987
                            Not very clean, but will post it anyway !!
                            SQL> l
                              1  create or replace function text_call return number is
                              2  begin
                              3  my_pack.v_a := 'Found';
                              4  return 1 ;
                              5* end;
                            SQL> /

                            Function created.

                            SQL> declare
                              2  v_cnt number;
                              3  begin
                              4  my_pack.v_a := 'Not Found';
                              5   select count(1) into v_cnt from dual where
                              6  text_call = 1 and 1=2;
                              7  dbms_output.put_line(my_pack.v_a);
                              8  end;
                              9  /
                            Not Found

                            PL/SQL procedure successfully completed.

                            SQL> declare
                              2  v_cnt number;
                              3  begin
                              4  my_pack.v_a := 'Not Found';
                              5   select count(1) into v_cnt from dual where
                              6  text_call = 1 and 1=1;
                              7  dbms_output.put_line(my_pack.v_a);
                              8  end;
                              9  /
                            Found

                            PL/SQL procedure successfully completed.

                            SQL>
                            • 11. Re: How do you do an IF EXISTS
                              446249
                              Thank you for the great responses, everybody :)
                              It's much appreciated. I've been amazed at all the posts!
                              • 12. Re: How do you do an IF EXISTS
                                Gabe2
                                <quote>In the meantime, I think the COUNT function will have to do.</quote>

                                Sure ... as long as you understand that, with the default Oracle isolation level [read committed], by the time you come to process the rows they may or may not exist.

                                You should strive to do all the processing in one SQL statement, whenever possible.
                                • 13. Re: How do you do an IF EXISTS
                                  215173
                                  SQL%found is an equivalent function for IF EXISTS.
                                  Have a look at the followinfg example.

                                  ----------------------------
                                  Declare
                                  emp_cnt number;
                                  Begin

                                  select count(*) into emp_cnt from emp where empno = 10;
                                  if SQL%found then
                                  dbms_output.put_line('Exists !');
                                       Else
                                       dbms_output.put_line('Does not Exist');
                                       End if ;
                                  End;
                                  /
                                  -----------------------------------
                                  • 14. Re: How do you do an IF EXISTS
                                    149764
                                    This will not work as for
                                    select count(*) into emp_cnt from emp where empno = 10;
                                    will always retun a singl row, irrespective of data. If no data found for the matching condition it will return 0(Zero) or else a non-zero number. SQL%Found condition will always evaluate to true as long as you can query EMP table .

                                    Thanks,
                                    Dharmesh Patel
                                    1 2 Previous Next