Block Structure of Stored Programs

Most MySQL stored programs consist of one or more blocks (the only exception is when a stored program contains only a single executable statement). Each block commences with a BEGIN statement and is terminated by an END statement. So in the simplest case, a stored program consists of a program definition statement (CREATE PROCEDURE, CREATE FUNCTION, or CREATE TRIGGER) followed by a single block that contains the program code to be executed:

CREATE {PROCEDURE|FUNCTION|TRIGGER} program_name BEGIN program_statements END;

The purpose of a block is twofold:

To logically group related code segments

For instance, a handler declaration (see Chapter 6 for an explanation of error handlers) can include a block definition allowing it to execute multiple commands. All of the statements within the block will be executed if the handler is invoked.

To control the scope of variables and other objects

You can define a variable within a block that is not visible outside the block. Furthermore, you can declare a variable within a block that overrides the definition of a variable with the same name declared outside of the block.

4.1.1. Structure of a Block

A block consists of various types of declarations (e.g., variables, cursors, handlers) and program code (e.g., assignments, conditional statements, loops). The order in which these can occur is as follows:

1. Variable and condition declarations. Variables were discussed earlier in Chapter 3, and condition declarations are discussed in Chapter 6.
2. Cursor declarations, discussed in Chapter 5.
3. Handler declarations, discussed in Chapter 6.
4. Program code.

If you violate this orderfor instance, by issuing a DECLARE statement after a SET statementMySQL will generate an error message when you try to create your stored program code. The error messages do not always clearly indicate that you have used statements in the wrong order, so it's important to develop the habit of declaring things in the correct order.

You can also name a block with a label. The label can occur both before the BEGIN statement and after the END statement. Labeling a block has the following advantages:

• It improves code readabilityfor instance, by allowing you to quickly match the BEGIN statement with its associated END statement.
• It allows you to terminate block execution with the LEAVE statement (see the section describing this statement later in this chapter).

So a simplified representation of the structure of a block is:

[label:] BEGIN variable and condition declarations] cursor declarations handler declarations program code END[label];

4.1.2. Nested Blocks

If all stored programs contained only a single block, the block structure would be hardly worth mentioning. However, many programs include blocks that are defined within an enclosing blockat least within the main block that encloses all the stored program code. As suggested earlier, variables declared within a block are not available outside the block, but may be visible to blocks that are declared within the block. You can override an "outer" variable with a new definition within the block, and you can manipulate this variable without affecting the value of the "outer" variable.

Let's illustrate some of these principles with some examples.

In Example 4-1, we create a variable within a block. The variable is not available in the outer block, so this example generates an error.

Example 4-1. Declarations within a block are not visible outside the block

In Example 4-2, we modify a variable declared in the "outer" block inside of an "inner" block. The changes made are visible outside of the inner block.

Example 4-2. Variables within a block can override variables defined outside the block

In Example 4-3, we create a variable in the inner block with the same name as one in the outer block. When we change the value within the inner block, the changes are not reflected in the outer blockthat's because although the two variables have the same name, they are really two separate variables. Overriding a variable name inside of a block in this way can be fairly confusing, reducing code readability and possibly encouraging bugs. In general, don't override variable definitions in this way unless you have a very compelling reason.

Example 4-3. Changes made to an overloaded variable in an inner block are not visible outside the block

In our final nested blocks example (Example 4-4), we use a block label and the LEAVE statement to terminate block execution. We discuss the use of the LEAVE statement later in this chapter, but for now it's enough to point out that you can terminate execution of a block with a LEAVE statement at any time, providing that the block is labeled.

Example 4-4. Example of using a LEAVE statement to exit a labeled block