Oracle PL/SQL Best Practices

Name

DAT-07: Replace complex expressions with Boolean variables and functions.

Synopsis

A Boolean expression evaluates to one of three values: TRUE, FALSE, or NULL. You can use Boolean variables and functions to hide complex expressions; the result is code that is virtually as readable as “straight” English—or whatever language you use to communicate with other human beings.

Example

IF total_sal BETWEEN 10000 AND 50000 AND
   emp_status (emp_rec.empno) = 'N' AND
   (MONTHS_BETWEEN 
      (emp_rec.hiredate, SYSDATE) > 10)
THEN
   give_raise (emp_rec.empno);
END IF;

Wow, that’s hard to understand! It’d be much easier if the code looked like this:

IF eligible_for_raise (totsal, emp_rec)
THEN
   give_raise (emp_rec.empno);
END IF;

And even if you don’t want to (or need to) bother with creating a separate function, you can still move the complexity to a local variable, as in:

DECLARE
  eligible_for_raise BOOLEAN :=
    total_sal BETWEEN 10000 AND 50000 AND
      emp_status (emp_rec.empno) = 'N' AND
      (MONTHS_BETWEEN 
         (emp_rec.hiredate, SYSDATE) > 10);
BEGIN
   IF eligible_for_raise 
   THEN
      give_raise (emp_rec.empno);
   END IF;

Benefits

It will be much easier for anyone to read your code; you can literally read it. If you then need to understand how the Boolean expression is computed, you can look “under the covers.”

This is a technique that can be applied (with care) to existing “spaghetti code.” As you go into a program to fix or enhance it, look for opportunities to simplify and shorten executable sections by shifting complexity ...