compound statement

Implements a SQL Script block that can contain a sequence of SQL statements, control-of-flow statements, local variable declarations, and exception handlers.

Syntax

[ label : ]
      BEGIN
      [ { declare_variable | declare_condition } ; [...] ]
      [ declare_cursor ; [...] ]
      [ declare_handler ; [...] ]
      [ SQL_statement ; [...] ]
      END [ label ]

declare_variable
  DECLARE variable_name [, ...] datatype [ DEFAULT default_expr ]

declare_condition
  DECLARE condition_name CONDITION [ FOR SQLSTATE [ VALUE ] sqlstate ]

declare_cursor
  DECLARE cursor_name [ ASENSITIVE | INSENSITIVE ] CURSOR
    FOR query

declare_handler
  DECLARE handler_type HANDLER FOR condition_values handler_action

handler_type
  { EXIT | CONTINUE }

condition_values
 { { SQLSTATE [ VALUE ] sqlstate | condition_name } [, ...] |
   { SQLEXCEPTION | NOT FOUND } [, ...] }

Parameters

Examples

-- A compound statement with local variables, an exit handler and a nested compound.
> BEGIN
    DECLARE a INT DEFAULT 1;
    DECLARE b INT DEFAULT 5;
    DECLARE EXIT HANDLER FOR DIVIDE_BY_ZERO
      div0: BEGIN
        VALUES (15);
      END div0;
    SET a = 10;
    SET a = b / 0;
    VALUES (a);
END;
15

-- A compound statement with a cursor and a CONTINUE handler for iteration.
> BEGIN
    DECLARE x INT;
    DECLARE done BOOLEAN DEFAULT false;
    DECLARE total INT DEFAULT 0;
    DECLARE my_cursor CURSOR FOR SELECT id FROM range(5);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

    OPEN my_cursor;
    REPEAT
      FETCH my_cursor INTO x;
      IF NOT done THEN
        SET total = total + x;
      END IF;
    UNTIL done END REPEAT;
    CLOSE my_cursor;

    VALUES (total);
  END;
10