MySQL in a Nutshell, 2nd Edition

Name

DECLARE

Synopsis

DECLARE variable data_type [DEFAULT value]

DECLARE condition CONDITION FOR
{SQLSTATE [VALUE] value |  error_code]

DECLARE cursor CURSOR FOR SELECT...

DECLARE {CONTINUE|EXIT|UNDO} HANDLER FOR
  {[SQLSTATE [VALUE] value]
   [SQLWARNING]
   [NOT FOUND]
   [SQLEXCEPTION]
   [error_code]
   [condition]}
SQL_statement

This statement declares local variables and other items related to routines. It must be used within a BEGIN...END compound statement of a routine, after BEGIN and before any other SQL statements. There are four basic uses for DECLARE: to declare local variables, conditions, cursors, and handlers. Within a BEGIN...END block, variables and conditions must be declared before cursors and handlers, and cursors must be declared before handlers.

The first syntax shows how to declare variables. It includes the data type and, optionally, default values. A variable declared with this statement is available only within the routine in which it is declared. If the default is a string, place it within quotes. If no default is declared, NULL is the default value.

A condition is generally either an SQLSTATE value or a MySQL error code number. The second syntax is used for declaring a condition and associating it with an SQLSTATE or an error code. When declaring a condition based on an SQLSTATE, give the SQLSTATE VALUE clause followed by the state. Otherwise, give the error code number.

The third syntax declares a cursor, which represents—within a procedure—a results set that is retrieved ...