I'm looking to start a discussion on this topic. I have reviewed this same question all over the internet and I thought I'd try to centralize some of it right here in this community...
To start the thread, here is one idea:
In my opinion, the most efficient and safest way to determine if a row exists is by using a FOR-LOOP... You will have a difficult time if you are looking to insert a row or do something based on the row NOT being there and only on the row not being there but, this will certainly help you if you need to determine if a row exists. (Even if it doesn't)... You can use the loop to insert a value into a declared variable to tell yourself whether it exists or not. Then you can run an IF-Check after your loop to see if it is null or not...
If you are only interested in knowing that 1 record exists in your potential multiple return set, than you can exit your loop after it hits it for the first time after the code is executed that you want to have execute...
The loop will not be entered into at all if no record exists. You will not get any complaints from Oracle or such if the row does not exist but you are bound to find out if it does regardless. Its what I use 90% of the time (of course dependent on my needs)...
EXAMPLE:
DECLARE
v_exist varchar2(20);
BEGIN
FOR rec IN
(SELECT LOT, COMPONENT
FROM TABLE
WHERE REF_DES = (SELECT REF_DES FROM TABLE2 WHERE ORDER = '1234')
AND ORDER = '1234')
LOOP
v_exist := "IT_EXISTS"
INSERT INTO EAT_SOME_SOUP_TABLE (LOT, COMPONENT)
VALUES (rec.LOT, rec.COMPONENT);**
--Since I don't want to do this for more than one iteration (just in case there may have been more than one record returned, I will EXIT;
EXIT;
END LOOP;
IF v_exist IS NULL
THEN
--do this
END IF;
END;