Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Location
    Brisbane, Australia
    Posts
    10

    Unanswered: If Exists (select ...) Then

    Hi All,

    I have the following code:
    Code:
    IF EXISTS (SELECT * FROM tbl_some_table WHERE some_condition) THEN
      -- Do something here;
    ELSE
      -- DO something else
    END IF;
    but I get an error which I don't understand:

    PLS-00204: function or pseudo-column 'EXISTS' may be used inside a SQL statement only

    I have been using T-SQL for a number of years now and I am finding the transition to PL/SQL somewhat tricky - there seem to be heaps of little things that one version allows and the other doesn't.

    I know the solution to the above problem will be easy for all the Oracle gurus out there, but I can't find any examples in my books that illustrate the PL/SQL way of checking to see if any records exist that satisfy the condition. I should also point out that this snippet of code is from a procedure.

    So I have two questions:
    1. How do I modify the above code so that it will work?
    2. Can anyone point me to a good site that documents some of the main differences between T-SQL and PL/SQL?

    Regards,

    Paul Hobbs

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    declare found boolean := false;
    for x in (SELECT * FROM tbl_some_table WHERE some_condition) loop
    found := true;
    -- Do something here;
    end loop;
    IF not found THEN
    -- DO something else
    END IF;

  3. #3
    Join Date
    May 2004
    Location
    Brisbane, Australia
    Posts
    10
    I see the logic of the code you suggest, but where dows the "x" come from?

    for x in (....) loop

    Is this a variable I need to declare?

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    you might want to read up on pl/sql looping methods. This will explain the "x" and why it isn't declared anywhere.


    You could declare a variable and do a select ... into..., but this requires exception handling, which I hate.

    You could use select count(*) into..., since this group method guarantees that a single row will always be returned, thus removing the need for exception handling, but it has its downsides as well.

    I find that if you simply want to know is any row exists, then use a cursor loop. Actually, I left out the "exit". The loop I originally posted could execute more than once:

    declare
    found boolean := false;
    for x in (SELECT * FROM tbl_some_table WHERE some_condition) loop
    found := true;
    -- Do something here;
    exit;
    end loop;
    IF not found THEN
    -- DO something else
    END IF;

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Beware - that FOR loop will execute as many times as there are records in the query. You could ensure that it only executes once like this:

    for x in (SELECT * FROM tbl_some_table WHERE some_condition AND ROWNUM = 1) loop

    My preference would be:
    Code:
    DECLARE
      l_exists INTEGER;
    BEGIN
      SELECT COUNT(*) INTO l_exists
      FROM some_table
      WHERE some_condition
      AND ROWNUM = 1;
      IF l_exists = 1 THEN
        -- Do something
      ELSE
        -- Do something else
      END IF;
    END;
    (Well, actually my real preference would be your original EXISTS clause, but unfortunately Oracle doesn't allow it!)

  6. #6
    Join Date
    Apr 2004
    Posts
    246
    Geez Andrew, I can barely type fast enough to keep ahead of you. You usually have them answered before I finish reading the question.

    I prefer the cursor loop, because 1) I hate exceptions, and even more so, the common practice of embedding begin/end blocks to accomodate them, and 2) since the query could return multiple rows, and you only want to know if one exists, the count(*) add overhead of actually going through all the rows.

  7. #7
    Join Date
    May 2004
    Location
    Brisbane, Australia
    Posts
    10
    Thanks Gents,

    I tried both variations (FOR x ... LOOP and SELECT count(*) INTO), and both worked perfectly. I am not sure which to choose - I don't know enough about Oracle to know which approach would have the biggest performance hit.

    I am very grateful for the speedy (and extremely helpful) assistance I have been getting on this forum. One day when I am a little more skilled I hope to repay the service to a younger, newer newbie!

    Cheers,

    Paul Hobbs

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by shoblock
    Geez Andrew, I can barely type fast enough to keep ahead of you. You usually have them answered before I finish reading the question.

    I prefer the cursor loop, because 1) I hate exceptions, and even more so, the common practice of embedding begin/end blocks to accomodate them, and 2) since the query could return multiple rows, and you only want to know if one exists, the count(*) add overhead of actually going through all the rows.
    Well, personal taste certainly plays a large part... and I find my own personal taste changes over time! But note that with the SELECT I used:
    1) The NO_DATA_FOUND and TOO_MANY_ROWS exceptions will never be raised, because a COUNT(*) without a GROUP BY is guaranteed to return 1 row.
    2) The ROWNUM=1 ensures that Oracle stops counting after finding 1 row.

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by mrgordonz
    I tried both variations (FOR x ... LOOP and SELECT count(*) INTO), and both worked perfectly. I am not sure which to choose - I don't know enough about Oracle to know which approach would have the biggest performance hit.
    You should find that the performance is similar (good) either way - Oracle will fetch 1 row from the table. So it comes down to your personal preference: which way makes most sense to you!

Posting Permissions

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