VARIABLES

Variables are used to store information locally.

  • They can be used to store constants or query results.
  • They are deleted when MySQL is restarted.
  • The definition is divided into three parts: Declare, Set, and Select...Into.

DECLARE

  • It must be inside a BEGIN...END block.
  • It is used to declare a variable, as we do in other programming languages.
  • Syntax: DECLARE variable_name type [DEFAULT value]
  • If we use DEFAULT, we can instantiate a variable upon declaration.

SET

  • The SET statement assigns a value to a previously created variable.
  • Subqueries can be stored as variable values.
  • Syntax:
  • SET var_name = expr [var_name = expr]

SELECT ... INTO

  • SELECT stores the selected columns into variables.
  • Only one record can be returned.
  • Syntax:
  • SELECT column_name INTO variable_name

HANDLERS

CONCEPT

Allows specific handling of certain conditions (usually related to errors)

TYPES

For a CONTINUE handler, it continues the current routine after executing the statement of the

For an EXIT handler, it terminates the execution of the current BEGIN ... END compound statement.

The UNDO type handler is not yet supported.

SYNTAX

DECLARE condition_name HANDLER FOR condition_value action

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;