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 one row at a time. Give a unique, nonreserved 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 is 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 can specify a single SQLSTATE code number, or you can 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.

Table of Contents for MySQL in a Nutshell, 2nd Edition

Name

Synopsis

Table of Contents for
MySQL in a Nutshell, 2nd Edition