I am currently migrating a SQL Server stored procedure to Oracle stored procedure:
In SQL Server, we use
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
IF v_counter1 = 0 THEN
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!
06-04-2002, 02:40 PM
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.
06-04-2002, 02:56 PM
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?
06-04-2002, 03:23 PM
where exists (select null from xxx where yyy = zzz)