# MySQL/Stored Programs

MySQL supports some procedural extensions to SQL. By using them, you can
manage the control flow, create loops and use cursors. These features
allow you to create stored programs, which may be of 3 kinds:

-   Triggers - programs which are *triggered* before / after a certain
    event involves a table (DELETE, INSERT, UPDATE);
-   Events - programs which are executed regularly after some time
    intervals;
-   Stored Procedures - programs which can be called via the CALL SQL
    command.

MySQL future versions will support stored program written in other
languages, not only SQL. You will have the ability to manage new
languages as PLUGINs. Also, the stored procedures will be compiled into
C code, and thus they will be faster.

## Triggers

### Managing Triggers

Triggers were added in MySQL 5.0.2. They work on persistent tables, but
can\'t be associated with TEMPORARY tables.

#### CREATE TRIGGER

To create a new trigger:

``` sql
 CREATE TRIGGER `delete_old` AFTER INSERT ON `articles`
     FOR EACH ROW BEGIN
         DELETE FROM `articles` ORDER BY `id` ASC LIMIT 1
     END
```

This example trigger defines a stored program (which is the simple
DELETE statement) called \`delete_old\`. It\'s automatically fired when
a new record is INSERTed into \`articles\`. It\'s called after the
INSERT, not before. If a single INSERT adds more than one row to the
table, \`delete_old\` is called more than once. The idea is simple: when
a new record is created, the oldest record is DELETEd.

A trigger may be executed BEFORE or AFTER a certain SQL statement. This
is important because a trigger may execute one or more statements which
activate other triggers; so, it may be important to decide their time
order, to ensure the database\'s integrity.

The statement which fires the trigger must be a basic DML command:

-   **INSERT**, which includes LOAD DATA and REPLACE
-   **DELETE**, which includes REPLACE, but not TRUNCATE
-   **UPDATE**

A special case is INSERT \... ON DUPLICATE KEY UPDATE. If the INSERT is
executed, both BEFORE INSERT and AFTER INSERT are executed. If the
INSERT is not executed, and thus an UPDATE is executed instead, the
order of events is the following: BEFORE INSERT, BEFORE UPDATE, AFTER
UPDATE.

You can also specify the table\'s name by using the following syntax:

`` ... ON `my_database`.`my_table` ... ``

Triggers\' names must be unique in a database. Two tables located in the
same database can\'t be associated to two different triggers with the
same name.

Unlike other DBMSs and standard SQL, all triggers are fired FOR EACH
ROW, and can\'t be executed for each statement.

A stored program must be specified between BEGIN and END reserved words.
You can\'t use dynamic SQL here (the PREPARE statement); use can call a
stored procedure, instead. If you execute only one statement, you can
omit the BEGIN and END words.

You can access to the old value of a field (the value it has before the
execution of the statement) and to the new value (the value it has after
the execution of the statement. Example:

``` sql
 CREATE TRIGGER `use_values` AFTER INSERT ON `example_tab`
     FOR EACH ROW BEGIN
         UPDATE `changelog` SET `old_value`=OLD.`field1`, `new_value`=NEW.`field1` WHERE `backup_tab`.`id`=`example_tab`.`id`
     END
```

#### DROP TRIGGER

To DROP a trigger you can use the following syntax:

``` sql
 DROP TRIGGER `my_trigger`
```

Or:

``` sql
 DROP TRIGGER `my_database`.`my_trigger`
```

Or:

``` sql
 DROP TRIGGER IF EXISTS `my_trigger`
```

To alter an existing trigger, you must DROP and re-CREATE it.

### Metadata

#### SHOW CREATE TRIGGER

This command returns the CREATE TRIGGER statement used to create the
trigger and some information about the settings which may affect the
statement.

``` sql
SHOW CREATE TRIGGER delete_old;
```

-   **Trigger** - Trigger name
-   **sql_mode** - The value of SQL_MODE at the time of the execution of
    the statement
-   **SQL Original Statement**
-   **character_set_client**
-   **collation_connection**
-   **Database Collation**

This statement was added in MySQL 5.1.

#### SHOW TRIGGERS

If you want to have a list of all the triggers in the current database,
you can type the following:

``` sql
 SHOW TRIGGERS
```

If you want to have a list of the triggers contained in another
database, you can use:

``` sql
 SHOW TRIGGERS IN `my_db`
 SHOW TRIGGERS FROM `my_db` -- synonym
```

If you want to list the triggers whose name matches to a LIKE
expression:

``` sql
 SHOW TRIGGERS FROM `my_db` LIKE 'my_%'
```

More complex filters:

``` sql
 SHOW TRIGGERS WHERE table='users'
```

You can\'t use LIKE and WHERE together.

The columns returned by this statement are:

-   **Trigger** - Trigger\'s name
-   **Event** - The SQL command that fires the trigger
-   **Table** - The table that is associated to the trigger
-   **Statement** - The statement that is executed by the trigger
-   **Timing** - BEFORE or AFTER
-   **Created** - It\'s always NULL
-   **sql_mode** - The SQL_MODE which was set when the trigger was
    created
-   **Definer** - The user who created the trigger
-   **character_set_client** - The value of the \`character_set_client\`
    variable when the trigger was created
-   **collation_connection** - The value of the \`collation_connection\`
    variable when the trigger was created
-   **Database Collation** - The COLLATION used by the database (and the
    trigger)

#### INFORMATION_SCHEMA.TRIGGERS

The INFORMATION_SCHEMA virtual database has a \`TRIGGERS\` table. It has
the following fields:

-   **TRIGGER_CATALOG** - What catalog contains the trigger (not
    implemented yet)
-   **TRIGGER_SCHEMA** - What SCHEMA (DATABASE) contains the trigger
-   **TRIGGER_NAME** - Trigger\'s name
-   **EVENT_MANIPULATION** - INSERT / UPDATE /DELETE
-   **EVENT_OBJECT_CATALOG** - Not implemented yet
-   **EVENT_OBJECT_SCHEMA** - SCHEMA containing the table associated to
    the trigger
-   **EVENT_OBJECT_NAME** - Name of the table associated to the trigger
-   **ACTION_ORDER** - Not implemented yet
-   **ACTION_CONDITION** - Not implemented yet
-   **ACTION_STATEMENT** - Statement(s) to be executed when trigger
    activates
-   **ACTION_ORIENTATION** - Not implemented yet
-   **ACTION_TIMING** - BEFORE / AFTER
-   **ACTION_REFERENCE_OLD_TABLE** - Not implemented
-   **ACTION_REFERENCE_NEW_TABLE** - Not implemented
-   **ACTION_REFERENCE_OLD_ROW** - Not implemented
-   **ACTION_REFERENCE_NEW_ROW** - Not implemented
-   **CREATED** - Creation time (not implemented yet)
-   **SQL_MODE** - SQL_MODE valid for this trigger\'s execution
-   **DEFINER** - User who created the trigger, in the form
    \'user@host\'
-   **CHARACTER_SET_CLIENT** - The value of the \`character_set_client\`
    variable when the trigger was created
-   **COLLATION_CONNECTION** - The value of the \`collation_connection\`
    variable when the trigger was created
-   **DATABASE_COLLATION** - The COLLATION used by the database (and the
    trigger)

## Events

Events are also called Scheduled Events or Temporal Triggers. They are
planned events which are executed at certain times, or at specified time
intervals. They are similar to the UNIX
cron|crontab.

Once an Event is started, it must be completely executed. If it is
re-activated before it ends its execution, a new instance of the same
Event will be created. If this can happen, it may be a good idea to use
LOCKs to assure data consistence.

The Event Scheduler is a thread which is permanently in execution. It
starts the Events when they must be started. If you don\'t need Events,
you can disable the Event Scheduler. You can do this starting MySQL with
the following option:

`mysqld --event-scheduler=DISABLED`

Or you can add a line to the my.cnf configuration file:

`event_scheduler=DISABLED`

If the Event Scheduler is not disabled, you will be able to turn it
ON/OFF runtime. It is controlled by a global system variable:

``` sql
 SELECT event_scheduler -- values: ON / OFF / DISABLED
 SET GLOBAL event_scheduler = ON
 SET GLOBAL event_scheduler = OFF
```

If the Event Scheduler is ON, you can check its status with SHOW
PROCESSLIST. It is shown like all other threads. Its \`User\` is
\'event_scheduler\'. When it is sleeping, the value for \`State\` is
\'Waiting for next activation\'.

### Managing Events

You can use the SQL commands CREATE EVENT, ALTER EVENT and DROP EVENT.

#### CREATE EVENT

The simplest case. We want a SQL command to be executed tomorrow:

``` sql
 CREATE EVENT `newevent`
   ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
   DO
     INSERT INTO `mydatabase`.`news` (`title`, `text`) VALUES ('Example!', 'This is not a reale news')
```

The event name must be specified after \"EVENT\".

If you want to create a task which will be executed only once at a
certain time, you need the AT clause. If you don\'t want to specify an
absolute time, but we want the task to be executed when a time interval
is passed, \"AT CURRENT_TIMESTAMP + INTERVAL \...\" is a useful syntax.

If you want to create a recurring task (which will be executed at
regular intervals) you need the EVERY clause:

``` sql
 CREATE EVENT `newevent2`
   ON SCHEDULE EVERY 2 DAY
   DO
     OPTIMIZE TABLE `mydatabase`.`news`
```

You can also specify a start time and/or an end time. The task will be
executed at regular intervals from the start time until the end time:

``` sql
 CREATE EVENT `newevent2`
   ON SCHEDULE EVERY INTERVAL 1 DAY
   DO
     OPTIMIZE TABLE `mydatabase`.`news`
       STARTS CURRENT_TIMESTAMP + 1 MONTH
       ENDS CURRENT_TIMESTAMP + 3 MONTH
```

The allowed time units are:

``` sql
 YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND, MINUTE_SECOND
```

The DO clause specify which statement must be executed.

If a task is composed by more than 1 statement, the BEGIN \... END
syntax must be used:

``` sql
  delimiter |
  CREATE EVENT `newevent`
    ON SCHEDULE
      EVERY 1 DAY
    DO
      BEGIN
        DELETE FROM `logs`.`user` WHERE `deletion_time` < CURRENT_TIMESTAMP - 1 YEAR;
        DELETE FROM `logs`.`messages` WHERE `deletion_time` < CURRENT_TIMESTAMP - 1 YEAR;
        UPDATE `logs`.`activity` SET `last_cleanup` = CURRENT_TIMESTAMP;
    END |
  delimiter ;
```

If an EVENT with the same name already exists you get an error from the
server. To suppress the error, you can use the IF NOT EXISTS clause:

``` sql
 CREATE EVENT `newevent2`
   IF NOT EXISTS
   ON SCHEDULE EVERY 2 DAY
   DO
     OPTIMIZE TABLE `mydatabase`.`news`
```

After the EVENT is expired (when the timestamp specified in the AT
clause or in the ENDS clause), MySQL drops the event by default, as it
is no more useful. You may want to preserve it from deletion to ALTER it
someday and activate it again, or just to have its code somewhere. You
may do this with the ON COMPLETION clause:

``` sql
 CREATE EVENT `newevent2`
   ON SCHEDULE EVERY 2 DAY
   ON COMPLETION PRESERVE
   DO
     OPTIMIZE TABLE `mydatabase`.`news`
```

Or, you can explicitly tell MySQL to drop it, even if it\'s not
necessary:

``` sql
 CREATE EVENT `newevent2`
   ON SCHEDULE EVERY 2 DAY
   ON COMPLETION NOT PRESERVE
   DO
     OPTIMIZE TABLE `mydatabase`.`news`
```

If you don\'t tell MySQL to preserve the EVENT after it\'s expired, but
it is already expired immediatly after creation (which happens if you
specify a past TIMESTAMP in the AT / ENDS clause), the server creates
and drop it as you requested. However, in this case it will inform you
returning a 1588 warning.

You can also specify if an EVENT must be enabled. This is done by
specifying ENABLE, DISABLE or DISABLE ON SLAVES (used to execute the
event on the master and not replacate it on the slaves). The EVENT is
enabled by default.

``` sql
 CREATE EVENT `newevent2`
   ON SCHEDULE EVERY 2 DAY
   ON COMPLETION NOT PRESERVE
   DISABLE
   DO
     OPTIMIZE TABLE `mydatabase`.`news`
```

To modify this behaviour, you will use ALTER EVENT.

You can specify a comment for the EVENT. Comments have a 64 characters
limit. The comment must be a literal, not an expression. Example:

``` sql
 CREATE EVENT `newevent2`
   ON SCHEDULE EVERY 2 DAY
   ON COMPLETION NOT PRESERVE
   DISABLE
   COMMENT 'let\'s optimize some tables!'
   DO
     OPTIMIZE TABLE `mydatabase`.`news`
```

You can also specify which user must be used to check privileges during
the execution of the EVENT. By default, the CURRENT_USER is used. You
can specify that explicitly:

``` sql
 CREATE DEFINER = CURRENT_USER
   EVENT `newevent2`
   ON SCHEDULE EVERY 2 DAY
   DO
     OPTIMIZE TABLE `mydatabase`.`news`
```

To specify a different user, you must have the SUPER privilege. In that
case, you must specify both the username and the host:

``` sql
 CREATE DEFINER = 'allen@localhost'
   EVENT `newevent2`
   ON SCHEDULE EVERY 2 DAY
   DO
     OPTIMIZE TABLE `mydatabase`.`news`
```

#### ALTER EVENT

The ALTER EVENT statement can be used to modify an existing EVENT.

``` sql
 CREATE EVENT `newevent2`
   ON SCHEDULE EVERY 2 DAY
   ON COMPLETION NOT PRESERVE
   RENAME TO `example_event`
   DISABLE
   COMMENT 'let\'s optimize some tables!'
   DO
     OPTIMIZE TABLE `mydatabase`.`news`
```

RENAME TO is used to rename the EVENT.

You only need to specify the clauses that you want to change:

``` sql
 CREATE EVENT `newevent2` ENABLE;
```

#### DROP EVENT

You need the EVENT privilege to drop an event. To drop an event you can
type:

``` sql
 DROP EVENT `event_name`
```

If the EVENT does not exist, you get a 1517 error. To avoid this, you
can use the IF EXISTS clause:

``` sql
 DROP EVENT IF EXISTS `event_name`
```

If the EVENT needs to be executed only once or just for a known time
period, by default MySQL drops it automatically when it is expired (see
the ON COMPLETE clause in CREATE EVENT).

### Metadata

#### SHOW CREATE EVENT

This command returns the CREATE EVENT statement used to create the
trigger and some information about the settings which may affect the
statement.

Syntax:

``` sql
 SHOW CREATE EVENT newevent2;
```

-   **Event** - Event name.
-   **sql_mode** - SQL mode which was in effect when the CREATE EVENT
    statement was executed.
-   **time_zone** - Time zone that was used when the statement was
    executed.
-   **Create Event** - Statement used to create the event.
-   **character_set_client**
-   **collation_connection**
-   **Database Collation**

#### SHOW EVENTS

The statement shows information about the EVENTs which are in the
current database or in the specified database:

``` sql
 SHOW EVENTS

 SHOW EVENTS FROM `my_nice_db`
 SHOW EVENTS IN `my_nice_db` -- synonym

 SHOW EVENTS LIKE 'my_%' -- name starts with 'my_'
 SHOW EVENTS WHERE definer LIKE 'admin@%' -- filters on any field
```

-   **Db** Database name.
-   **Name** Event name.
-   **Definer** User which created the EVENT and the host he used, in
    the form user@host.
-   **Time zone** Timezone in use for the EVENT. If it never changed, it
    should be \'SYSTEM\', which means: server\'s timezone.
-   **Type** \'ONE TIME\' for EVENTs which are executed only once,
    \'RECURRING\' for EVENTs which are executed regularly.
-   **Executed At** The TIMESTAMP of the moment the EVENT will be
    executed. NULL for recursive EVENTs.
-   **Interval Value** Number of intervals between EVENT\'s executions.
    See next field. NULL for EVENTs which are executed only once.
-   **Interval Field** Interval type to wait between EVENTs executions.
    For example, if \`Interval Field\` is \'SECOND\' and \`Interval
    Value\` is 30, the EVENT will be executed every 30 seconds. NULL for
    EVENTs which are executed only once.
-   **Starts** First execution DATETIME for recurring EVENTs. NULL for
    events which are executed only once.
-   **Ends** Last execution DATETIME for recurring EVENTs. NULL for
    events which are executed only once.
-   **Status** ENABLED, DISABLED, or SLAVESIDE_DISABLED. For ENABLED and
    DISABLED, see above. SLAVESIDE_DISABLED was added in 5.1 and means
    that the EVENT is enabled on the master but disabled on the slaves.
-   **Originator** Id of the server where the EVENT was created. If it
    has been created on the current server this value is 0. Added in
    5.1.
-   **character_set_client**
-   **collation_connection**
-   **Database Collation**

#### INFORMATION_SCHEMA.EVENTS

The INFORMATION_SCHEMA virtual database has a \`EVENTS\` table. It\'s
non-standard and has been added in 5.1. EVENTS has the following fields:

-   **EVENT_CATALOG** Always NULL (CATALOGs are not implemented in
    MySQL).
-   **EVENT_SCHEMA** Database name.
-   **EVENT_NAME** Event name.
-   **DEFINER** User which created the EVENT and the host he used, in
    the form user@host.
-   **TIME_ZONE** Timezone in use for the EVENT. If it never changed, it
    should be \'SYSTEM\', which means: server\'s timezone.
-   **EVENT_BODY** Language used to write the routine that will be
    executed.
-   **EVENT_DEFINITION** Routine that will be executed.
-   **EVENT_TYPE** \'ONE TIME\' for EVENTs which are executed only once,
    \'RECURRING\' for EVENTs which are executed regularly.
-   **EXECUTE_AT** The TIMESTAMP of the moment the EVENT will be
    executed. NULL for recursive EVENTs.
-   **INTERVAL_VALUE** Number of intervals between EVENT\'s executions.
    See next field. NULL for EVENTs which are executed only once.
-   **INTERVAL_FIELD** Interval type to wait between EVENTs executions.
    For example, if \`Interval Field\` is \'SECOND\' and \`Interval
    Value\` is 30, the EVENT will be executed every 30 seconds. NULL for
    EVENTs which are executed only once.
-   **SQL_MODE** SQL mode which was in effect when the EVENT has been
    created.
-   **STARTS** First execution DATETIME for recurring EVENTs. NULL for
    events which are executed only once.
-   **ENDS** Last execution DATETIME for recurring EVENTs. NULL for
    events which are executed only once.
-   **STATUS** ENABLED, DISABLED, or SLAVESIDE_DISABLED. For ENABLED and
    DISABLED, see above. SLAVESIDE_DISABLED was added in 5.1 and means
    that the EVENT is enabled on the master but disabled on the slaves.
-   **ON_COMPLETION** \'NOT PRESERVE\' (the EVENT will be deleted) or
    \'PRESERVE\' (the EVENT won\'t be deleted\'.
-   **CREATED** Creation DATETIME.
-   **LAST_ALTERED** Last edit\'s DATETIME. If the EVENT has never been
    altered, \`LAST_ALTERED\` has the same value as \`CREATED\`.
-   **LAST_EXECUTED** Last execution TIMESTAMP. If the EVENT has never
    been executed yet, this value is NULL.
-   **EVENT_COMMENT** Comment associated to the EVENT. Is there is no
    comment, this value is an empty string.
-   **ORIGINATOR** Id of the server where the EVENT was created. If it
    has been created on the current server this value is 0. Added in
    5.1.
-   **character_set_client**
-   **collation_connection**
-   **Database Collation**

## Stored Routines

Stored Routines are modules written in SQL (with some procedural
extensions) which may be called within another statement, using the CALL
command.

Stored Routines are called FUNCTIONs if they return a result, or
PROCEDUREs if they don\'t return anything. STORED PROCEDUREs must not be
confused with the PROCEDUREs written in C or LUA which can be used in a
SELECT statement; STORED FUNCTIONs must not be confused with UDF, even
if they both are created with a CREATE FUNCTION statement.

### Advantages of Stored Routines

-   They reduce network traffic: they may contain many statements, but
    only one statement need to be sent to invoke them.
-   Ability to keep the logic within the database.
-   Reusable modules which can be called from external programs, no
    matter in what language they are written.
-   You can modify the Stored Routines without changing your programs.
-   The user which invokes a Stored Routine doesn\'t need to have access
    to the tables which it reads / writes.
-   Calling Stored Routines are faster than executing single statements.

### Managing Stored Routines

#### CREATE PROCEDURE

``` sql
 CREATE DEFINER = `root`@`localhost` PROCEDURE `Module1` ( ) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER OPTIMIZE TABLE wiki1_page;
```

#### CALL

``` sql
 CALL `Module1` ();
```

#### DROP PROCEDURE

``` sql
 DROP PROCEDURE `Module1` ;
```

#### Modification

``` sql
 DROP PROCEDURE `Module1` ;
 CREATE DEFINER = `root`@`localhost` PROCEDURE `Module1` ( ) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER
 BEGIN
  OPTIMIZE TABLE wiki1_page;
  OPTIMIZE TABLE wiki1_user;
 END 
```

### Metadata

#### SHOW FUNCTION / PROCEDURE STATUS

``` sql
 SHOW PROCEDURE STATUS;
```

#### SHOW CREATE FUNCTION / PROCEDURE

``` sql
 SHOW CREATE PROCEDURE Module1;
```

#### INFORMATION_SCHEMA.ROUTINES

The virtual database INFORMATION_SCHEMA has a table called \`ROUTINES\`,
with the functions and procedures information.

#### INFORMATION_SCHEMA.PARAMETERS

This table contains all the stored functions values.

## Procedural extensions to standard SQL

### Delimiter

MySQL uses a character as delimiter - MySQL knows that where that
character occurs a SQL statement ends and possibly another statement
begins. That character is \';\' by default. When you create a stored
program which contains more than one statements, you enter only one
statement: the CREATE command. However, it contains more then one
statements in its body, separated with a \';\'. In that case, you need
to inform MySQL that \';\' does not identify the end of the CREATE
statement: you need another delimiter.

In the following example, \'\|\' is used as a delimiter:

``` sql
 delimiter |
 CREATE EVENT myevent
    ON SCHEDULE EVERY 1 DAY
    DO
      BEGIN
        TRUNCATE `my_db`.`my_table`;
        TRUNCATE `my_db`.`another_table`;
      END
 delimiter ;
```

### Flow control

The keywords are: `IF, CASE, ITERATE, LEAVE LOOP, WHILE, REPEAT`[^1].

### Loops

##### WHILE

``` sql
DELIMITER $$
CREATE PROCEDURE counter()
  BEGIN
    DECLARE x INT;
    SET x = 1;
    WHILE x  <= 5 DO
      SET  x = x + 1;
    END WHILE;
    SELECT x;  -- 6
  END$$
DELIMITER ;
```

##### LOOP

``` sql
DELIMITER $$
CREATE PROCEDURE counter2()
  BEGIN
    DECLARE x INT;
    SET x = 1;
    boucle1: LOOP
      SET  x = x + 1;
        IF x > 5 THEN
          LEAVE boucle1;
      END IF;
    END LOOP boucle1;
    SELECT x;  -- 6
  END$$
DELIMITER ;
```

##### REPEAT

``` sql
DELIMITER $$
CREATE PROCEDURE counter3()
  BEGIN
    DECLARE x INT;
    SET x = 1;
    REPEAT
      SET x = x + 1; UNTIL x > 5
    END REPEAT;
    SELECT x;  -- 6
  END$$
DELIMITER ;
```

### Cursors

The Cursor (databases)|cursors allow to treat each
row differently, but it considerably slows the queries.

``` sql
DELIMITER $$
CREATE PROCEDURE cursor1()
BEGIN
    DECLARE result varchar(100) DEFAULT "";
    DECLARE c1 CURSOR FOR
        SELECT page_title
        FROM wiki1.wiki1_page
        WHERE page_namespace = 0;
    OPEN c1;
    FETCH c1 INTO result;
    CLOSE c1;
    SELECT result;
END;$$
DELIMITER ;
```

They should be declared and open before the loop which should treat
every records differently. To know the table end, we should create a
handler after the cursor:

``` sql
-- Concatenate all a table column values on a row
DELIMITER $$
CREATE PROCEDURE cursor2()
BEGIN
    DECLARE result varchar(100) DEFAULT "";
    DECLARE total text DEFAULT "";
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE c2 CURSOR FOR
        SELECT page_title
        FROM wiki1.wiki1_page
        WHERE page_namespace = 0;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN c2;
    REPEAT
        FETCH c2 INTO result;
        set total = concat(total, result);
    UNTIL done END REPEAT;
    CLOSE c2;
    SELECT total;
END;$$
DELIMITER ;
```

### Error handling

A handler declaration permits to specify a treatment in case of
error[^2]:

``` mysql
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
```

Moreover, the error type can be indicated:

``` mysql
DECLARE CONTINUE HANDLER FOR SQLSTATE [VALUE] sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLWARNING
DECLARE CONTINUE HANDLER FOR NOT FOUND
```

## References

```{=html}
<references/>
```
fr:MySQL/Procédures stockées

[^1]: <http://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html>

[^2]: <http://dev.mysql.com/doc/refman/5.7/en/declare-handler.html>
