MySQL Stored Procedure Programming

Name

STYL-03: Self-document using block and loop labels

Synopsis

While block and loop labels are often necessary to allow for variable scoping or as targets for LEAVE or ITERATE statements, they can also be a big help in improving the readability of code.

Use a label directly in front of loops and nested blocks:

• To name that portion of code and thereby self-document what it’s doing

• So that you can repeat that name with the END statement of that block or loop

• To provide a target for a LEAVE or ITERATE statement

This recommendation is especially important when you have multiple nestings of loops (and possibly inconsistent indentation), as in the following:

    WHILE condition DO
       some code
       WHILE condition DO
          some code
       END WHILE;
       some code
    END WHILE;

Example

In this example we use labels for a block and two nested loops, and then apply them in the appropriate END statements. We can now easily see which loop and block are ending, no matter how badly the code is indented!

    CREATE PROCEDURE display_book_usage(  )
      READS SQL DATA
    BEGIN
       DECLARE v_month INT;
       DECLARE v_x  INT;
       DECLARE yearly_analysis_csr CURSOR FOR SELECT ...;
       DECLARE monthly_analysis_csr CURSOR FOR SELECT ...;

       OPEN yearly_analysis_csr;
       yearly_analysis:
       LOOP
         FETCH yearly_analysis_csr INTO v_month;
         OPEN monthly_analysis_csr;
         monthly_analysis:
         LOOP
           FETCH monthly_analysis_csr INTO v_x;
    ... Lots of monthly analysis code ...
    END LOOP monthly_analysis;
    ...Lots of yearly analysis code
    END LOOP yearly_analysis;

Benefits

If you use labels, it’s much ...