MySQL Stored Procedure Programming

Name

PRG-03: Limit execution section sizes to a single page (50-60 lines) using modularization

Synopsis

Sure, you’re laughing out loud. You write code for the real world. It’s really complicated. Only 50 or 60 lines? You’re lucky if your programs are less than 500 lines! Well, it’s not a matter of complexity; it’s more an issue of how you handle that complexity.

If your executable sections go on for hundreds of lines, with a loop starting on page 2 and ending on page 6, and so on, you will have a hard time “grasping the whole” and following the logic of the program.

An alternative is to use step-wise refinement (a.k.a. “top down decomposition”): don’t dive into all the details immediately. Instead, start with a general description (written in actual code, mind you) of what your program is supposed to do. Then implement all subprogram calls in that description following the same method.

The result is that at any given level of refinement, you can take in and easily comprehend the full underlying logic at that level. This technique is also referred to as “divide and conquer.”

Example

Consider the following procedure. The entire program might be hundreds of lines long, but the main body of assign_workload (starting with BEGIN /*main*/) is only 24 lines long. Not only that, you can read it pretty much as an exciting novel: “For every telesales rep, if that person’s case load is less than his department’s average, assign the next open case to that person and schedule the next appointment for ...