Jump to Navigation

MySQL: Stored programs quick reference

This is a quick reference guide for writing MySQL stored programs. It is assumed you have some experience with similar programming languages (for example, Oracle PL/SQL). This has been written for MySQL 5.6.

Defining stored programs

There are four kinds of stored programs:

  • Procedures
  • Functions
  • Triggers
  • Events

Functions can be called from SQL queries.

If binary logging is enabled, you are only allowed to create a function or trigger if you have SUPER rights or if @@global.log_bin_trust_function_creators is set to true.

When defining a stored program, you should set the delimiter temporarily to another value to stop the statements being interpreted correctly:

DELIMITER @@
-- CREATE PROCEDURE, etc, each ending on @@
DELIMITER ;

Procedures and functions can run with the privileges of the definer or the invoker; by default, definer rights are used. Events and triggers always run with definer rights. When creating a procedure, function, event or trigger, you can specify who the definer is; by default, it is the current user. You need SUPER rights to specify someone else.

Procedures

CREATE [ DEFINER = username | DEFINER = CURRENT USER ] PROCEDURE proc_name 
( [ IN | OUT | INOUT ] param type [ , IN | OUT | INOUT ] param type ]... )
[ COMMENT 'comment' | [ NOT ] DETERMINISTIC | CONTAINS SQL | NO SQL | READS SQL DATA |
MODIFIES SQL DATA | SQL SECURITY DEFINER | SQL SECURITY INVOKER ]...
BEGIN stmts END;
ALTER PROCEDURE proc_name [ COMMENT 'comment' | LANGUAGE SQL | [ NOT ] DETERMINISTIC | CONTAINS SQL |
NO SQL | READS SQL DATA | MODIFIES SQL DATA | SQL SECURITY DEFINER | SQL SECURITY INVOKER ]... ;
DROP PROCEDURE [ IF EXISTS ] proc_name;

Stored procedures can only be recursive if @@max_sp_recursion_depth is greater then 0.

Parameters can be for input (IN) or output (OUT) or both (INOUT); the default is IN.

A deterministic procedure returns the same results if it is called with the same input parameters; by default, a procedure is not deterministic. MySQL does not check whether this is true.

A NO SQL procedure contains no SQL queries; a READS SQL DATA procedure contains only SELECT queries; a MODIFIES SQL DATA procedure contains DML statements; a CONTAINS SQL procedure may contain any SQL. By default, CONTAINS SQL is used.

Functions

CREATE [ DEFINER = username | DEFINER = CURRENT USER ] FUNCTION func_name 
( [ param type [ , param type ]... ) RETURNS type
[ COMMENT 'comment' | LANGUAGE SQL | [ NOT ] DETERMINISTIC | CONTAINS SQL | NO SQL | READS SQL DATA |
MODIFIES SQL DATA | SQL SECURITY DEFINER | SQL SECURITY INVOKER ]...
BEGIN stmts END;
ALTER FUNCTION func_name [ COMMENT 'comment' | LANGUAGE SQL | [ NOT ] DETERMINISTIC | CONTAINS SQL |
NO SQL | READS SQL DATA | MODIFIES SQL DATA | SQL SECURITY DEFINER | SQL SECURITY INVOKER ]... ;
DROP FUNCTION [ IF EXISTS ] func_name;

Stored functions can not be recursive.

A deterministic function returns the same results if it is called with the same input parameters; by default, a function is not deterministic. MySQL does not check whether this is true.

A NO SQL function contains no SQL queries; a READS SQL DATA function contains only SELECT queries; a MODIFIES SQL DATA function contains DML statements; a CONTAINS SQL function may contain any SQL. By default, CONTAINS SQL is used.

When binary logging is active, all functions need to be declared as DETERMINISTIC, NO SQL or READS SQL DATA.

Triggers

CREATE [ DEFINER = username | DEFINER = CURRENT USER ] TRIGGER trigger_name
BEFORE INSERT ON table FOR EACH ROW stmt;
CREATE [ DEFINER = username | DEFINER = CURRENT USER ] TRIGGER trigger_name
AFTER INSERT ON table FOR EACH ROW stmt;
CREATE [ DEFINER = username | DEFINER = CURRENT USER ] TRIGGER trigger_name
BEFORE UPDATE ON table FOR EACH ROW stmt;
CREATE [ DEFINER = username | DEFINER = CURRENT USER ] TRIGGER trigger_name
BEFORE UPDATE ON table FOR EACH ROW stmt;
CREATE [ DEFINER = username | DEFINER = CURRENT USER ] TRIGGER trigger_name
BEFORE DELETE ON table FOR EACH ROW stmt;
CREATE [ DEFINER = username | DEFINER = CURRENT USER ] TRIGGER trigger_name
BEFORE DELETE ON table FOR EACH ROW stmt;
DROP TRIGGER [ IF EXISTS ] trigger_name;

Note that all triggers are row level triggers, and only apply to DML statements.

In UPDATE and DELETE triggers, you can use OLD.colname to access the old column value; in UPDATE and INSERT triggers, you can use NEW.colname to access the new column value. In a BEFORE UPDATE or BEFORE INSERT trigger, you can change a NEW.colname value. Auto-increment NEW.colname columns will have value 0 in BEFORE triggers.

You may use a compound statement as the trigger body.

Failure in a trigger causes the originating command to fail, and if possible, to roll-back.

Events

CREATE [ DEFINER = username | DEFINER = CURRENT USER ] EVENT [ IF NOT EXISTS ] event_name 
ON SCHEDULE AT TIMESTAMP timestamp [ + INTERVAL interval ]
[ ON COMPLETION [ NOT ] PRESERVE ] [ ENABLE | DISABLE | DISABLE ON SLAVE ] [ COMMENT 'comment' ]
DO stmt;
CREATE [ DEFINER = username | DEFINER = CURRENT USER ] EVENT [ IF NOT EXISTS ] event_name
ON SCHEDULE EVERY interval [ STARTS timestamp [ + INTERVAL interval ] ] [ ENDS timestamp [ + INTERVAL interval ] ]
[ ON COMPLETION [ NOT ] PRESERVE ] [ ENABLE | DISABLE | DISABLE ON SLAVE ] [ COMMENT 'comment' ]
DO stmt;
ALTER [ DEFINER = username | DEFINER = CURRENT USER ] EVENT event_name
[ ON SCHEDULE AT TIMESTAMP timestamp [ + INTERVAL interval ] ] |
ON SCHEDULE EVERY interval [ STARTS timestamp [ + INTERVAL interval ] ] [ ENDS timestamp [ + INTERVAL interval ] ]
[ ON COMPLETION [ NOT ] PRESERVE ] [ RENAME TO event_name ]
[ ENABLE | DISABLE | DISABLE ON SLAVE ] [ COMMENT 'comment' ]
DO stmt;
DROP EVENT [ IF EXISTS ] event_name;

Events occur once on a pre-defined moment, or repeatedly, possibly within a certain time window.

It may be a good idea to use a lock, for example with the GET_LOCK() function, to stop having a repeating event start before the previous run has finished.

You may have to enable the event scheduler by setting variable @@global.event_scheduler to ON.

Basic language constructs

Compound statements

[ label: ] BEGIN stmts END [ label ];

Note that you can not use a compound statement on its own; it may only be used within stored procedures, functions, triggers or events.

You can nest compound statements.

Flow control

IF cond THEN stmts [ ELIF cond THEN stmts ]... [ ELSE stmts ] END IF;
CASE expr WHEN expr THEN stmts [ WHEN expr THEN stmts ]... [ ELSE stmts ] END CASE;
CASE WHEN cond THEN stmts [ WHEN cond THEN stmts ]... [ ELSE stmts ] END CASE;
[ label: ] LOOP stmts END LOOP [ label ];
[ label: ] REPEAT stmts UNTIL cond END REPEAT [ label ];
[ label: ] WHILE cond DO stmt END WHILE b label ];

When no CASE expression or condition matches, and no ELSE clause is present, a Case not found for CASE statement error results (error 1339, SQLSTATE 20000).

ITERATE label;
LEAVE label;

Note that the labels are obligatory for ITERATE and LEAVE; that means that you have to label the loops for which you want to use them. It is allowed to LEAVE or ITERATE  from a nested loop into an outer loop. LEAVE can also be used to leave a compound statement.

Note that you do not need to use BEGIN...END to be able to execute multiple statements.

Labels

A label may only be used before a BEGIN, LOOP, REPEAT or WHILE, where it is followed by a colon. The label can optionally be repeated after an END, END LOOP, END REPEAT or END WHILE without colon. Labels must be used in ITERATE and LEAVE statements, also without colon.

Return

RETURN expr;

Only for use within functions: ends the function call and returns a value.

Varables

There are three kinds of variables in MySQL:

  • System variables
    Pre-defined variables with session or system scope
  • User variables
    Start with @; session scope.
  • Local variables
    Need to be declared; compound statement scope. Function and procedure parameters behave like local variable, except that the declaration is done in the procedure or function header.

System variables

SET GLOBAL variable [ = | := ] expr;
SET GLOBAL variable [ = | := ] DEFAULT;
SET [ SESSION | LOCAL ] variable [ = | := ] expr;
SET [ SESSION | LOCAL ] variable [ = | := ] DEFAULT;
SET @@global.variable [ = | := ] expr;
SET @@global.variable [ = | := ] DEFAULT;
SET @@session.variable [ = | := ] expr;
SET @@session.variable [ = | := ] DEFAULT;
SET @@variable [ = | := ] expr;
SET @@variable [ = | := ] DEFAULT;
SET variable [ = | := ] expr;
SET variable [ = | := ] DEFAULT;

SHOW GLOBAL VARIABLES [ LIKE pattern | WHERE expr ];
SHOW [ SESSION | LOCAL ] VARIABLES [ LIKE pattern | WHERE expr ];

Many system variables exist both with global scope and with session scope.

@@variable or variable equals @@session.variable if the variable exists with session scope, and @@global.variable if not.

Assigning DEFAULT to a session scope variable sets it to the value of the corresponding global scope variable; assigning DEFAULT to a global scope variable sets it to a compiled-in default.

Within expressions, use @@global.variable, @@session.variable or @@variable or variable.

Note that the syntax variable can be confused with a local variable; a declared local variable in scope has priority.

User variables

SET @variable [ = | := ]  expr;

User variables have session scope. Be careful that you use unique names.

You do not declare user variables and their type is inferred automatically.  They can be an integer, decimal, floating-point, binary or nonbinary string, or have a NULL value.

It is also possible to assign a value to a user variable within an SQL statement. Be careful, though: evaluation order is mostly undefined. An example: SELECT @a := 100;

Only user variable can be used for prepared statements (PREPARE, EXECUTE and variables referred to in the prepared statement), but user variables can  not be used with cursors (FETCH INTO).

Local variables

DECLARE variable [ , variable ]... type [ DEFAULT expr ];
SET variable [ = | := ] expr;

Local variables must be declared; if not, you may accidentally refer to a system variable. Undeclared variable use is an error. DECLARE statements must be at the start of the compound statement.

Local variable mist be used with cursors (FETCH INTO) but can not be used at all for prepared statements (PREPARE, EXECUTE and variables referred to in the prepared statement).

Set statement

SET assignment [ , assignment ]... ;

It is possible to combine several assignments within a single assignment. You may mix variable types.

Dynamic SQL and cursors

Cursors

DECLARE cursor CURSOR FOR select_statement _without_into;
OPEN cursor;
FETCH [ [ NEXT ] FROM ] cursor INTO variable [ ,variable ]... ;
CLOSE cursor;

Cursor declarations come after variable declarations and before condition declarations.

Cursors are automatically closed when the compound block in which they are declared is left.

To detect whether all rows are processed, use a handler for NOT FOUND or for error 1329, SQLSTATE 02000.

Handlers

DECLARE condition CONDITION FOR error_code;
DECLARE condition CONDITION FOR SQLSTATE [ VALUE ] sqlstate;
DECLARE CONTINUE HANDLER FOR condition_value [ , condition_value ]... stmt;
DECLARE EXIT HANDLER FOR condition_value [ , condition_value ]... stmt;

Options for condition_value are:

  • error_code
  • SQLSTATE [ VALUE ] sqlstate
  • condition
  • SQLWARNING
  • NOT FOUND
  • SQLEXCEPTION

A CONTINUE handler runs the associated statement and continues at the place the condition occurred; an EXIT handler runs the associated statement and exits the compound statement the handler was defined in.

All warning and errors have both a unique MySQL error code and a more standard possibly non-unique five-character sqlstate. SQLWARNING includes all error codes starting with 01, NOT FOUND with 02 and SQLEXCEPTION all error codes not starting with 00, 01 or 02. Error codes starting with 00 actually indicate successes.

The statement to run may be a compound statement.

Handler declarations come after variable and condition declarations, at the beginning of a compound statement.

A condition declaration just assigns a human-readable name to a condition.

Signals

SIGNAL SQLSTATE [ VALUE ] sqlstate [ SET signal_info_name = value [ , signal_info_name = value ]... ] ;
SIGNAL condtion [ SET signal_info_name = value [ , signal_info_name = value ]... ] ;
RESIGNAL;
RESIGNAL SQLSTATE [ VALUE ] [ SET signal_info_name = value [ , signal_info_name = value ]... ] ;
RESIGNAL condtion [ SET signal_info_name = value [ , signal_info_name = value ]... ] ;

RESIGNAL re-raises and possible modified the condition; it should be used within a handler. SIGNAL just raises a new condition.

It is not possible to raise conditions based on a MySQL error code. The values for signal_info_item must be literals or variables, more complex expressions are not supported.

The following options are defined for signal_info_name: CLASS_ORIGINSUBCLASS_ORIGIN, MESSAGE_TEXT, MYSQL_ERRNO, CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CATALOG_NAME, SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, CURSOR_NAME.

Dynamic statements

PREPARE stmt_name FROM 'string_lit';
PREPARE stmt_name FROM @user_variable;
EXECUTE stmt_name [ USING @user_variable [ , @user_variable ]... ;
DEALLOCATE PREPARE stmt_name;

Note that you can only PREPARE from a string literal or a user variable; preparing from a more complex expression or from a local variable is not possible.

Placeholders (question marks) in the prepared statement are substituted by the values of the user variables used in the EXECUTE statement. You can only use user variables; expressions or local variables are not allowed. Placeholders can only be used for literals, not for column names, table names or keywords. Never use quotes around the placeholder.

Prepared statements have session scope.

Prepared statements can only refer to user variables, not to procedure of function parameters or to local variables.

Many types of statements may be prepared; but you can not define a cursor.

You can not use PREPARE within a cursor or function definition.

Code style

This reference guide assumes you know enough about MySQL to write select queries, DML and DDL. Some general observations:

  • You can put any SELECT, DML or DDL query in your code, at any point;
  • You can use SELECT ... INTO var1,var2,... to get the contents of a table row; note that the SELECT may only return one record. This is often quicker than using a cursor.

Functions versus procedures

In many cases, it is easier to use procedures with OUT variables than functions, especially for more serious environments where binary logging is activated, either for point in time recovery or replication. The price to pay is some clarity of code; and of course, only functions can be called directly from SQL queries.

Debugging

There are several options for debugging your stored programs by "printf" statements:

  1. Insert data into a logging table;
  2. SELECT INTO OUTFILE or SELECT INTO DUMPFILE;
  3. SELECT

The third option is usually the easiest. Output just goes to the screen if you use the mysql client. You can do SELECT variable; without specifying a table to just display the variable.

Tags:


Technical_article | by Dr. Radut