DECLARE

This MySQL statement declares local variables and other items related to routines.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

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

Explanation

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 one row at a time. Give a unique, non-reserved word for the cursor's name. This is followed by CURSOR FOR and then a SELECT statement. It must not have an INTO clause. To call or open a cursor, use the OPEN statement within the same routine in which the declaration was made. To retrieve data from a cursor, which is done one row at a time, use the FETCH statement. When finished, use the CLOSE statement to close an open cursor.

The last syntax for this statement declares a handler. With a handler, you can specify an SQL statement to be executed given a specific condition that occurs within a routine. Three types of handlers are allowed: CONTINUE, EXIT, and UNDO. Use CONTINUE to indicate that the routine is to continue after the SQL statement given is executed. The EXIT parameter indicates that the BEGIN...END compound statement that contains the declaration should be exited when the condition given is met. UNDO is meant to instruct MySQL to undo the compound statement for which it given. However, this parameter is not yet supported by MySQL.

The handler's FOR clause may contain multiple conditions in a comma-separated list. There are several related to the SQLSTATE: you could specify a single SQLSTATE code number, list SQLWARNING to declare any SQLSTATE code starting with 01, NOT FOUND for any SQLSTATE code starting with 02, or SQLEXCEPTION for all states that don't start with 01 or 02. Another condition you can give is a MySQL error code number. You can also specify the name of a condition you previously created with its own DECLARE statement.