Herong's Tutorial Notes on SQL
Dr. Herong Yang, Version 3.02

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 

Dr. Herong Yang, updated in 2006
Herong's Tutorial Notes on SQL - MySQL - Stored Procedure Language