05/12/2012

[SQL] Oracle IF SELECT statement

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.