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

[zxmgh]

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!

[stecal]

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.

[zxmgh]

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?

[pando]

select count(*)
into l_cnt
from dual
where exists (select null from xxx where yyy = zzz)