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
BEGINprogram_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.
Tip
A compound statement consists of a BEGIN-END
block, which encloses one or
more stored program commands.
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:
If you violate this order—for instance, by issuing a DECLARE
statement after a SET
statement—MySQL 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.
Tip
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 readability—for instance, by allowing you to quickly match the
BEGIN
statement with its associatedEND
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:]
BEGINvariable and condition declarations
]cursor declarations
handler declarations
program code
END[label];
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 block—at 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.
mysql> CREATE PROCEDURE nested_blocks1( ) BEGIN DECLARE outer_variable VARCHAR(20); BEGIN DECLAREinner_variable
VARCHAR(20); SETinner_variable
='This is my private data'; END; SELECTinner_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.
mysql> CREATE PROCEDURE nested_blocks2( ) BEGIN DECLAREmy_variable
varchar(20); SETmy_variable
='This value was set in the outer block'; BEGIN SETmy_variable
='This value was set in the inner block'; END; SELECTmy_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 block—that’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.
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)
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.
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)
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.