While working with PL/SQL, you may encounter the need to use something like:
IF EXISTS(SELECT ...) THEN
or
IF (SELECT ...) IS NULL THEN
but Oracle doesn't allow this, instead, you'll have to alter your function/procedure:
DECLARE
myFlag INTEGER;
BEGIN
SELECT COUNT(*) INTO myFlag
FROM table t
WHERE [conditions]
AND ROWNUM = 1;
IF myFlag = 1 THEN
--something was returned
ELSE
--else no data was found
END IF;
END;
Using both COUNT and ROWNUM=1 ensures us that, no matter whether something was returned by the SELECT or not, myFlag will in any case be either 1 or 0, which reflects the outcome you'd have had if you could have used the two statements at the beginning of this post.
