MySQL Stored Procedure Programming

Name

DAT-03: Replace complex expressions with functions

Synopsis

A Boolean function evaluates to one of three values: TRUE (1), FALSE (0), or NULL. You can use Boolean 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

Consider this code:

      SELECT salary, status, hire_date
        INTO v_total_sal, v_emp_status,  v_hire_date
        FROM employees
       WHERE employee_id=in_employee_id;

      IF (v_total_sal BETWEEN 10000 AND 50000)
         AND  v_emp_status  = 'N'
         AND DATEDIFF(NOW(  ), v_hire_date)>  365
      THEN
         CALL give_raise (in_employee_id);
      END IF;

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

      IF eligible_for_raise(in_employee_id) THEN
         CALL give_raise(in_employee_id);
      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 to other functions or procedures.

Challenges

Before you modify existing code, make sure you have solid unit test scripts in place so you can quickly verify that your changes haven’t introduced bugs into the program.