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.
Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.