[mrgordonz]

Hi All,

I have the following 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

[shoblock]

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;

[mrgordonz]

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?

[shoblock]

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;

[andrewst]

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:

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!)

[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.

[mrgordonz]

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

[andrewst]

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.

[andrewst]

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!