Exists?????? 4

[NiteCrawlr]

Isn't there in Oracle 8 anything that has the same function as the <b>If Exists<b> (in SQL Server)?? I have this Stored procedure in SQL SERVER:

***************
CREATE PROCEDURE [SP_EM_VERIF_NOVIS]
@EMPLID char(11),
@RESULTADO numeric(11) output
AS
Set @resultado = 0
IF Exists( Select [MANAGER_ID] FROM [PS_EM_ORG_NOVIS_VW] WHERE ([MANAGER_ID] = @EMPLID))
BEGIN
SET @RESULTADO = '1'
END
ELSE
BEGIN
SET @RESULTADO = '0'
END

RETURN @resultado
***************

What would this look like in Oracle?
PS: It can not have Select Count, I'm trying to avoid using it, that's why I'm using EXISTS.

 

[karluk]

I agree that &quot;select count(*)&quot; is a poor substitute. Oracle can't stop the query when it finds the first match. This could lead to extremely poor performance.

As far as I know there isn't an Oracle equivalent to the SQL Server &quot;if exists&quot; syntax. To replace it, I would define a cursor with your select statement, open it, and fetch a single row. Then your if...then...else logic would be based on whether the fetch returned a row (cursor%notfound will be true or false).

I once attended an SQL performance workshop where this technique was recommended as a more efficient alternative to &quot;select count(*)&quot;.

 

[stevecal]

Karluk,

This looks like a very neat solution to a problem that has annoyed me for some time.

The other way of doing it which bypasses the SELECT Count(*) overrun is to do a: SELECT 1 INTO var FROM [...].
Then you have a DATA_NOT_FOUND exception the other side of the block to handle the 0 value.

 

[MikeJones]

Stevecal,

It's abit academic but it might be of interest to know that the select into is twice as costly as the open fetch close method. When doing a select 1 into ... Oracle will also do another fetch to make sure that the query returns only 1 row, otherwise it needs to return an error.

The only other method I know is to wrap the actual query uo into an exists, but this looks really sloppy, I prefer karluk's method.

Mike.

 

[stevecal]

Mike,

I agree. This has been a pain from my point of view for some time. The cursor method is going to be used in my code from now on!!!!

 

[carp]

I've played around with count(*), the cursor idea and a variation on the count(*) idea.
Here are the codes I used for the experiment:

First, the count(*) approach:
DECLARE
v_flag NUMBER := 0;
BEGIN
SELECT count(*) INTO v_flag
FROM c_account
WHERE last_action = 'UPDATE';
END;
/

Then the cursor approach:
DECLARE
CURSOR action_cursor IS SELECT *
FROM c_account
WHERE last_action = 'UPDATE';
v_action_record c_account%ROWTYPE;
BEGIN
OPEN action_cursor;
FETCH action_cursor INTO v_action;
CLOSE action_cursor;
END;
/

Finally, a variation on the count(*) approach:

DECLARE
v_flag number := 0;
BEGIN
SELECT COUNT(*) INTO v_flag
FROM c_account
WHERE last_action = 'UPDATE' AND ROWNUM < 2;
END;
/

I set timing on and ran each method six times. I threw out the results of the first run and averaged the results of the final five runs. As expected, method 1 was relatively slow (average time of about .72 seconds). Furthermore, the cursor method DID run faster (average time about .56 seconds). But the third method ran the fastest (average time about .12 seconds). Furthermore, the first and second methods slowed rather dramatically when I went from a 1000 row to a 4000 row to a 32000 row table, while the third method remained fairly constant.

I believe the third method is a pretty close approximation of &quot;where exists&quot;.