MySQL - Stored Procedure Language

Part:   1   2  3 

This chapter describes:

• Local variables in stored procedures.
• Execution flow control statements.
• Cursor related statements.

All notes in chapter are based MySQL 5.0 server.

Local Variables

A stored procedure can have local variables.

To define a local variable, you can use the DECLARE statement:

DECLARE variable data_type [DEFAULT value];

To assign a new value to a variable, you can use the SET statement:

SET variable = expression;

The SELECT statement can also be used to assign values to variables:

SELECT expression, expression, ... INTO variable, variable, ... 
   [FROM clause];

Once a variable is defined, it can be used in any expressions in any statements.

Execution Flow Control Statements

To selectively executing a group of statements, you can use the IF statement:

IF condition THEN 
   statement_list
ELSE IF condition THEN 
   statement_list
ELSE IF condition THEN 
   statement_list
......
ELSE 
   statement_list
END IF

To repeatedly executing a group of statements without any conditions, you can use the LOOP statement:

LOOP
   statement_list
END LOOP

To repeatedly executing a group of statements with an ending condition, you can use the REPEAT statement:

REPEAT
   statement_list
UNTIL condition
END REPEAT

To repeatedly executing a group of statements with an ending condition, you can use the WHILE statement:

WHILE condition
   statement_list
END WHILE

(Continued on next part...)

Part:   1   2  3