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.

A compound statement consists of a BEGIN-END block, which encloses one or more stored program commands.

 

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.

The order of statements in a block must be Variables and conditions, followed by Cursors, then Exception handlers, and finally Other statements. We remember this order using the following mnemonic: "Very Carefully Establish Order" in your stored programs.

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

mysql> CREATE PROCEDURE nested_blocks1( )
BEGIN
 DECLARE outer_variable VARCHAR(20);
 BEGIN
 DECLARE inner_variable VARCHAR(20);
 SET inner_variable='This is my private data';
 END;
 SELECT inner_variable,' This statement causes an error ';
END;
$$


Query OK, 0 rows affected (0.00 sec)


mysql> CALL nested_blocks1( )
--------------


ERROR 1054 (42S22): Unknown column 'inner_variable' in 'field list'

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

mysql> CREATE PROCEDURE nested_blocks2( )
BEGIN
 DECLARE my_variable varchar(20);
 SET my_variable='This value was set in the outer block';
 BEGIN
 SET my_variable='This value was set in the inner block';
 END;
 SELECT my_variable, 'Changes in the inner block are visible in the outer block';
END;
$$


Query OK, 0 rows affected (0.00 sec)




mysql> CALL nested_blocks2( )
//


+---------------------+-----------------------------------------------------------+
| my_variable | Changes in the inner block are visible in the outer block |
+---------------------+-----------------------------------------------------------+
| This value was set | |
| in the inner block | Changes in the inner block are visible in the outer block |
+---------------------+-----------------------------------------------------------+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.01 sec)

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

mysql> CREATE PROCEDURE nested_blocks3( )
BEGIN
 DECLARE my_variable varchar(20);
 SET my_variable='This value was set in the outer block';
 BEGIN
 DECLARE my_variable VARCHAR(20);
 SET my_variable='This value was set in the inner block';
 END;
 SELECT my_variable, 'Can''t see changes made in the inner block';
END;
//


Query OK, 0 rows affected (0.00 sec)


mysql> CALL nested_blocks3( )
$$


+---------------------------+-------------------------------------------+
| my_variable | Can't see changes made in the inner block |
+---------------------------+-------------------------------------------+
| This value was set in the | |
| outer block | Can't see changes made in the inner block |
+---------------------------+-------------------------------------------+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.00 sec)

Avoid overriding a variable declared within an outer block inside an inner 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

mysql> CREATE PROCEDURE nested_blocks5( )
 outer_block: BEGIN
 DECLARE l_status int;
 SET l_status=1;
 inner_block: BEGIN
 IF (l_status=1) THEN
 LEAVE inner_block;
 END IF;
 SELECT 'This statement will never be executed';
 END inner_block;
 SELECT 'End of program';
END outer_block$$


Query OK, 0 rows affected (0.00 sec)


mysql> CALL nested_blocks5( )$$


+----------------+
| End of program |
+----------------+
| End of program |
+----------------+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.00 sec)