MySQL/Stored Programs

< MySQL

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:

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:

 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:

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:

 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:

 DROP TRIGGER `my_trigger`

Or:

 DROP TRIGGER `my_database`.`my_trigger`

Or:

 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.

SHOW CREATE TRIGGER delete_old;

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:

 SHOW TRIGGERS

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

 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:

 SHOW TRIGGERS FROM `my_db` LIKE 'my_%'

More complex filters:

 SHOW TRIGGERS WHERE table='users'

You can't use LIKE and WHERE together.

The columns returned by this statement are:

INFORMATION_SCHEMA.TRIGGERS

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

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 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:

 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:

 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:

 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:

 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:

 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:

  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:

 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:

 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:

 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.

 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:

 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:

 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:

 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.

 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:

 CREATE EVENT `newevent2` ENABLE;

DROP EVENT

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

 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:

 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:

 SHOW CREATE EVENT newevent2;

SHOW EVENTS

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

 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

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:

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

Managing Stored Routines

CREATE PROCEDURE 

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

CALL 

 CALL `Module1` ();

DROP PROCEDURE 

 DROP PROCEDURE `Module1` ;

Modification 

 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 

 SHOW PROCEDURE STATUS;

SHOW CREATE FUNCTION / PROCEDURE 

 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:

 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 
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 
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 
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 cursors allow to treat each row differently, but it considerably slows the queries.

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:

-- 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]:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

Moreover, the error type can be indicated:

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

References

  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
This article is issued from Wikibooks. The text is licensed under Creative Commons - Attribution - Sharealike. Additional terms may apply for the media files.