Record Exists

Copied from some tips put together by Chris Rudd, ultimately sourced from Steven Feuerstein
/*
Note that the following shows three ways of checking if a record exists which have very similar response times, one way (implicit cursor) which is bad, and a fifth way
( select count(*) ) which is a VERY BAD THING and will result in broken fingers for anyone found using it!
*/
SET VERIFY OFF
@ssoo
DECLARE
/* Different approaches to answering "at least one?" */
CURSOR empcur
IS
SELECT employee_id
FROM employee_big WHERE department_id = &&secondparm;
v NUMBER;
b BOOLEAN;
BEGIN
plvtmr.set_factor (&&firstparm);
plvtmr.capture;
FOR i IN 1 .. &&firstparm
LOOP
BEGIN
SELECT employee_id INTO v FROM employee_big
WHERE department_id = &&secondparm;
b := TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN b := FALSE;
WHEN TOO_MANY_ROWS THEN b := TRUE;
END;
END LOOP;
PLVtmr.show_elapsed ('Implicit');

plvtmr.capture;
FOR i IN 1 .. &&firstparm
LOOP
OPEN empcur;
FETCH empcur INTO v;
b := empcur%FOUND;
CLOSE empcur;
END LOOP;
PLVtmr.show_elapsed ('Explicit');

plvtmr.capture;
FOR i IN 1 .. &&firstparm
LOOP
SELECT COUNT(*) INTO v
FROM employee_big WHERE department_id = &&secondparm;
b := v > 0;
END LOOP;
PLVtmr.show_elapsed ('COUNT');

/* Ohio OUG Contributions.... */
plvtmr.capture;
FOR i IN 1 .. &&firstparm
LOOP
SELECT COUNT(1) INTO v
FROM employee_big WHERE department_id = &&secondparm
AND ROWNUM <> 0;
END LOOP;
PLVtmr.show_elapsed ('COUNT ROWNUM<2'); department_id =" &&secondparm);"> @atleastone 1000 20
Implicit Elapsed: .45 seconds. Factored: .00045 seconds.
Explicit Elapsed: .12 seconds. Factored: .00012 seconds.
COUNT Elapsed: 2.21 seconds. Factored: .00221 seconds.
COUNT ROWNUM<2> @atleastone 20000 20
Implicit Elapsed: 8.06 seconds. Factored: .0004 seconds.
Explicit Elapsed: 2.46 seconds. Factored: .00012 seconds.
COUNT Elapsed: 42.21 seconds. Factored: .00211 seconds.
COUNT ROWNUM<2 Elapsed: 2.42 seconds. Factored: .00012 seconds.
EXISTS Elapsed: 2.63 seconds. Factored: .00013 seconds.

* /

END;
/