DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: What's the syntax of "IF EXISTS" in Oracle?

  1. #1
    Join Date
    Apr 2001
    Posts
    127

    Question

    Hi, All:

    I am currently migrating a SQL Server stored procedure to Oracle stored procedure:

    In SQL Server, we use

    IF EXISTS(...)
    BEGIN
    ...
    END
    ELSE
    BEGIN
    ...
    END

    I don't know how to efficiently do the EXISTS check in Oracle, so what I did is define a counter variable, and do like this:

    SELECT COUNT(*) INTO v_counter1
    FROM ...
    WHERE ...;

    IF v_counter1 = 0 THEN
    ...
    ELSE
    ...
    END IF;

    Though I don't think it is efficient because if there are many records in the table satisfy the condition, it will take a long time to count the number. Really I don't need the number but just if there is just one case.

    I know my way is stupid, just give some recommendations.

    Thanks a lot!

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Using "where rownum = 1" at the end of your query will cut down your work a bit. If whatever exists, there is at least one, and there will be one with rownum equal to one.

    You have to do the select or count somewhere along the line to find out if you have any.

  3. #3
    Join Date
    Apr 2001
    Posts
    127
    But it still needs to go through the whole table, right? If there are millions of records there, do you think there is any performance gain?

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    select count(*)
    into l_cnt
    from dual
    where exists (select null from xxx where yyy = zzz)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width