MYSQL-TOOLS

Comparison of graphical tools for MySQL

Home Programming with MySQL Programming with MySQL

Programming with MySQL

Print PDF
User Rating: / 0
PoorBest 

Stored procedures

Stored procedures (SPs) are an important addition to MySQL 5.0 onwards. These are custom programming scripts with embedded SQL statements that are stored in a compiled form and executed directly by the MySQL server, generally with no external programming interface requirements. SPs allow store logic rules on the database.

Why Stored Procedures?

  • Faster Execution:Reduced need for data transfer back and forth between a program and the database server.

    Note: The use of SPs does not guarantee improved speed as a lot depends on the code inside of the SP.

  • Reduced code redundancy: There generallt are similar code segments in each application for managing data such as templated inserts and updates and can be moved into a database-side SP.
  • Maintenance: If there are changes in the underlying database schema, code changes can be localized to a few SPs.
  • Better database security: In security-sensitive applications, such as banking, direct access to tables by user programs is a problem and with SPs, data access can be monitored, and logged if necessary. Also, one can impose centralized security rules in database procedures.

Drawbacks for SPs

  • Lack of Portability: SPs are hardly portable from one database system to another, requiring significant coding and testing efforts.
  • DB Server Load: Intensive use of SPs burdens the MySQL server programs, which could be a negative.
  • Limited Programming: SP programming is generally not as rich as conventional development platforms such as Java or PHP. This may also lead to sub-optimal code hurting performance somewhat.

    Note:Future releases of MySQL will likely offer an interface that permits creating SP code using external programming languages so SPs can use all of the features of languages such as Java.

Routines consist primarily of a collection of regular SQL commands. In addition, you can create loops and conditional branches, and cursors to run through all the records in a table.

There are two types of routines: procedures and functions. The following table summarizes the most important differences between the two.

Differences Between Procedures and Functions
Procedures Functions
Invoking Use CALL Use in any SQL command, such as SELECT, UPDATE
Return Values Can return one or more SELECT results and out parameters Returns a single value via RETURN matching data type of the return value specified in the declaration with RETURNS.
Parameters Value (input) and reference (output) parameters (IN, OUT, INOUT) Only value (input) parameters allowed, needs no tagging such as IN,etc.
Commands allowed in code All DDL and DML SQL commands supported No support for commands that access tables.
Call other routines May invoke other procedures and/or functions Only functions can be called but not procedures.

SP Implementation

The SQL 2003 standard: The syntax of SPs in MySQL 5.0 is fortunately based on a standard, namely SQL:2003. Though SPs are more or less incompatible in different database servers.

Internal storage of SPs: MySQL stores SPs in the table mysql.proc. In the columns of this table are stored the name of the underlying database, the name and type (PROCEDURE or FUNCTION) of the SP, the parameters, the actual code, and various other attributes.

Note: Every SP and function is associated with a particular database.

Creating Routines

New Routines are created using CREATE FUNCTION or CREATE PROCEDURE. You need the privilege Create Routine in order to be able to execute these commands. The following lines summarize the syntax for these commands:

CREATE FUNCTION name ([parameterlist]) RETURNS datatype
[options] sqlcode
CREATE PROCEDURE name ([parameterlist])
[options] sqlcode

It is allowed for a function and a procedure to have the same name, as they have different calling mechanisms.

DELIMITER ;

DROP FUNCTION IF EXISTS reduce;
DELIMITER $$

CREATE FUNCTION reduce(str VARCHAR(255), len INT)
RETURNS VARCHAR(255)
BEGIN
IF ISNULL(str) THEN RETURN NULL; END IF;
IF len = 0 OR CHAR_LENGTH( str ) = 0 OR CHAR_LENGTH( str ) <= len THEN RETURN str;
 END IF;

IF len < 15 THEN
RETURN LEFT( str, len );
ELSE
RETURN CONCAT( LEFT( str, len - 10 ), ' ___ ', RIGHT( str, 5 ) );
END IF;
END$$

DELIMITER ;

Code Explanation

Change delimiting to $$ to allow for semicolon use inside of function: delimiter $$

Two parameters are passed to the function reduce(): a string str and an integer len representing the maximum return length.

The return type is a string (data type VARCHAR(255)).

If len is less than 15, then LEFT len characters are extracted from the string passed.

If len is greater than or equal to 15, then oversized strings are shortened so that the resultant string contains the first len - 10 and the last 5 characters, with the string " ___ " in between.

Revert back delimiting to semicolon : delimiter ;

Specification Options

In the definition, the following options can be specified:

  • LANGUAGE SQL: The only permissible setting for the option LANGUAGE is currently SQL. This setting holds by default. Future versions of MySQL will likely offer the option of defining SPs in other programming languages.
  • [NOT] DETERMINISTIC: A Routine is considered deterministic when it always returns the same result with the same parameters. (Routines whose result depends on a database table are thus not deterministic.)
  • By default, SPs are nondeterministic. However, deterministic SPs can be executed with greater efficiency. (For example, it is possible to store the result of an SP for particular parameters in a cache.) At the moment, however, the MySQL DETERMINISTIC option is ignored.
  • SQL SECURITY DEFINER or INVOKER: The SQL SECURITY mode specifies the access privileges with which an SP is to be executed. Details will be given later in the lesson.
  • COMMENT 'text': The comment text is stored together with the SP.

Deleting Routines

Use usual DROP commands to delete routines:

DROP FUNCTION [IF EXISTS] name 
DROP PROCEDURE [IF EXISTS] name

Changing Routines

ALTER FUNCTION/PROCEDURE name
[NAME newname]
[SQL SECURITY DEFINER/INVOKER]
[COMMENT 'newcomment']

MySQL currently (version 5.0.3) offers no way of changing the code of an existing SP. If that is what you would like to do, then you must first delete the SP with DROP, and then create it anew with CREATE.

Listing Existing Routines

The two commands SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS return a list of all defined procedures and functions. Using LIKE pattern you can limit the output to those procedures/functions whose names correspond to a given search pattern. In any case, you will obtain the SPs of all databases (not only the current database):

SHOW FUNCTION STATUS;
SHOW PROCEDURE STATUS;

SHOW FUNCTION STATUS LIKE 'repeat%';
SHOW PROCEDURE STATUS LIKE 'film%';

Code Explanation

Use SHOW STATUS for functions and procedures.

LIKE is used to filter the list shown.

More complete information is provided by the table information_schema.routines. It contains all data of the table mysql.proc, though in part it employs different column names:

SELECT routine_name, routine_type, created
FROM information_schema.routines
WHERE routine_schema='sakila';

Code Explanation

We will SELECT information from the routines table in information_schema.

Determining the Code of an SP

If you know the name of an SP, you can access its code. Execute the command SHOW CREATE FUNCTION/PROCEDURE name:

Code Sample: Programming/Demos/Show-Create-Function.sql

SHOW CREATE FUNCTION reduce;
Code Explanation

Use SHOW CREATE statement.

Programming Routines - Inside the Routines

Encapsulation of Commands (BEGIN-END)

Every procedure or function that consists of more than one SQL command must be introduced with handler and cursor.)

Within the BEGIN-END block a particular order must be adhered to:

BEGIN
DECLARE variables;
DECLARE cursors;
DECLARE conditions;
DECLARE handler;
other SQL commands;
END;

Before the BEGIN, an optional label can be given. The same name must then appear after the END. A Named block may be exited early with LEAVE:

blockname: BEGIN
commands;
IF condition THEN LEAVE blockname; END IF;
further commands;
END blockname;

Semicolons separate individual commands within an SP.

Using Variables

There are two types of variables to consider in MySQL programming:

  • Ordinary SQL variables: Such variables are prefixed with the @ character. These variables can be used in SPs just as they are in ordinary SQL commands. They maintain their contents until the termination of the connection to the MySQL server.
  • Local variables and parameters: These variables are used without the @ character. They must be declared with DECLARE before they are used. The content of a local variable is lost as soon as the procedure or function terminates.

Variables are local within the BEGIN-END group in which they are defined. This means that within a procedure several like-named variables can coexist within different levels of definition as shown int following example).

To avoid side effects, local variables are generally preferable.

The declaration of local variables must take place within a BEGIN-END group and before other commands in the group. The syntax of variable declaration looks like this:

DECLARE varname1, varname2, ...  datatype [DEFAULT value];

As you can see, you must provide the data type for all local variables. Local variables contain NULL by default, unless you initialize them with another value.

Warning: Be very careful not to give your variables names that coincide with those of columns or tables that you use in your SP. It is allowed syntactically, but it frequently leads to hard-to-detect errors (which can be revealed, for example, in that a variable, despite an assignment otherwise, always contains NULL).

DELIMITER ;

DROP PROCEDURE IF EXISTS `3blocks`;
DELIMITER $$

CREATE PROCEDURE `3blocks`(n INT)
BEGIN
DECLARE newn INT DEFAULT n;
BEGIN
DECLARE newn INT DEFAULT n * 2;
IF TRUE THEN
BEGIN
DECLARE newn INT DEFAULT n * 3;
SELECT n 'Orig', 3 'Run', newn 'New Factor';
END;
END IF;
SELECT n 'Orig', 2 'Run', newn 'New Factor';
END;
SELECT n 'Orig', 1 'Run', newn 'New Factor';
END$$

DELIMITER ;

CALL `3blocks`(10);
Code Explanation

There are three variables named same as newn.

Variables are declared in three levels of the code and are independent of one another.

The procedure returns three results, multiplying given parameters by 1, 2 and 3.

Variable Assignment

For variable assignments, use either SET or SELECT INTO. The latter is a variant of SELECT, in which the command ends with INTO varname.This variant is allowed only for a singleton SELECTs that return a single row.

In functions, only SET can be used, since there the use of SELECT and various other SQL commands is not allowed.

DELIMITER ;

DROP PROCEDURE IF EXISTS `assign_vars`;
DELIMITER $$

CREATE PROCEDURE `assign_vars`(n INT)
BEGIN

DECLARE varlength SMALLINT;
DECLARE pgcount SMALLINT;
DECLARE var1 SMALLINT;
DECLARE var2 SMALLINT;
DECLARE var3 SMALLINT;

SET var1 = n * 5, var2 = n * 10, var3 = n * 15;

SELECT @var := CONCAT_WS( ',', var1, var2, var3 );

SELECT CHAR_LENGTH(@var) INTO varlength;

SELECT COUNT(film_id) FROM film WHERE rating = 'PG' INTO pgcount;

SELECT @var, varlength 'Length of @var', pgcount 'PG Rated Film Count';

SELECT title, rating FROM film WHERE film_id = 101 INTO @vtitle, @vrating;

SELECT 101 'Film ID', @vtitle 'Title', @vrating 'Rated As';

END$$

DELIMITER ;

CALL `assign_vars`(10);

Code Explanation

Different variables are assigned different values.

Some are declared variables and others are SELECT-style variables.

General Programming Syntax Rules

These are the most important syntax rules for programming routines:

  • Semicolon:The commands in routines are separated by semicolons. Even control structures (branches and loops) must be terminated with a semicolon.
  • BEGIN-END: Several commands that do not fall between key words (for example, between THEN and END IF) must be placed between BEGIN and END. The code block of every routine with multiple statements must start with a BEGIN and terminated by END.
  • Spacing: White space is for readability purposes.
  • Variables: Local (internal) variables and parameters are used without @ prefix. The ordinary SQL variables are used with the @ prefix.
  • Case distinction: The definition and invocation of routines are case insensitive.
  • A function and a procedure can have the same name.
  • Special characters:MySQL allows a lot of flexibility for special characters in routine names. ,but external programming and administrative tools may cause problems.
  • Comments:Comments start with a double hyphen (-) and run to the end of line.

Invoking Procedures

Procedures must be called with CALL. You can return as result a table (as with a SELECT command). t is impossible to link procedures to SQL commands in any other way.

The following examples use the procedures get_title and half:

DROP PROCEDURE IF EXISTS oper;

DELIMITER $$

CREATE PROCEDURE oper(IN a FLOAT, INOUT b FLOAT, IN oper VARCHAR(1))
BEGIN

CASE oper
WHEN '+' THEN SET b := a + b;
WHEN '-' THEN SET b := a - b;
WHEN '*' THEN SET b := a * b;
WHEN '/' THEN SET b := a / b;
WHEN '%' THEN SET b := a % b;
WHEN '^' THEN SET b := POW(a, b);
ELSE SET b := a;
END CASE;

SELECT b;

END$$

DELIMITER ;

SET @b := 5;
CALL oper( 15, @b, '+' );
SELECT @b;
Code Explanation

A Case statement is used to use operators.

Three parameters are supplied - two floats and an operator string.

The second float is an out parameter and comes initialized with the result of the operation.

Tip: To execute an SP in another database, prefix the database name as in CALL dbname.spname().

Parameters to Procedures

Procedures are created by the command CREATE PROCEDURE. A parameter list is optional. Note, however, that you must provide a pair of parentheses even if there are no parameters.

CREATE PROCEDURE name ([parameterlist])
[options] sqlcode

If there is more than one parameter, they must be separated by commas. Each parameter is specified as follows:

[IN or OUT or INOUT] parametername datatype

The keywords IN, OUT, and INOUT determine whether the parameter is to be used only for input, only for output, or for data transport in both directions. (The default is IN.)

All data types from MySQL are allowed, such as INT and VARCHAR(n) and DOUBLE. However, in contrast to the definition of table columns, it is impossible to provide additional attributes to the data type such as NULL and NOT NULL. MySQL currently does not attempt to do type checking when it passes parameters, but that may change in future versions.

Be careful to give your parameters names that differ from those of your columns and tables. Otherwise, ambiguity can enter into the formulation of SQL commands in procedure code.

Function Parameters

The CREATE command for creating new functions looks very much like that for procedures:

Syntax
CREATE FUNCTION name 
([parameterlist]) RETURNS datatype
[options] sqlcode

A significant difference, however, is that functions do not support reference parameters. For this reason, the keywords IN, OUT, and INOUT are not permitted in the parameter list.

Note: We execute the script using source filename as usual, but since source requires the commands to be separated by semicolons, and it does not accept the command delimiter, you may have some trouble running a script.

Results of Procedures (SELECT)

Unlike functions, procedures do not return a single value. In procedures, however, one may use ordinary SELECT commands. It is even allowed for a procedure to execute several SELECT commands in sequence. The procedure then returns several result tables. However, only those programming languages that support the MULTI_RESULT mode are capable of evaluating several results. If you are working with PHP, you must use the method multi_query of the mysqli interface.

Function Results

Functions can return a value with the command RETURN, which also terminates the function execution. RETURN can be used only in functions, not in procedures. The data type of the return value must be specified in the parameter list with RETURNS.

Procedures and Functions differ in a number of details that will be discussed in this lesson.

Invoking Functions

Functions, like the predefined SQL functions, are integrated into ordinary SQL commands. For example, the reduce function from can be called as:

SELECT reduce("abcdefghijklmnopqrstuvwxyz", 15);

SELECT title, reduce(description, 25) 'ShortDesc'
FROM film_detail
WHERE rating = 'PG'
AND category_name = 'Children'
ORDER BY title;
Code Explanation

The first usage simply "reduce"s a constant string.

The second usage "reduce"s a database column description from film_detail.

IF-THEN-ELSE Branching

The SQL syntax for IF branching looks like this:

Syntax
IF condition THEN
commands;
[ELSE IF condition THEN
commands;]
[ELSE
commands;]
END IF;

The use of blocks marked with BEGIN and END within a control structure is not necessary. The condition can be formulated as with WHERE or HAVING in SELECT queries.

Tip: Instead of the IF structure presented here, in simple cases you may use the IF() function.

The following function is an example of several IF conditions. The function returns a string enclosed with given strings.

DELIMITER ;

DROP FUNCTION IF EXISTS enclose;
DELIMITER $$

CREATE FUNCTION enclose(str VARCHAR(255), leftstr VARCHAR(3), rightstr VARCHAR(3) )
RETURNS VARCHAR(255)
BEGIN

DECLARE returnstr VARCHAR(255) DEFAULT '';

IF ISNULL(str) THEN RETURN NULL; END IF;
IF leftstr IS NULL OR rightstr IS NULL OR CHAR_LENGTH( str ) = 0 THEN
 RETURN str;
END IF;


IF LEFT( str, CHAR_LENGTH( leftstr ) ) != leftstr THEN
SET returnstr = concat( leftstr, str );
END IF;

IF RIGHT( str, CHAR_LENGTH( rightstr ) ) != rightstr THEN
SET returnstr = concat( str, rightstr );
END IF;

IF returnstr = '' THEN SET returnstr = str; END IF;

RETURN returnstr;
END$$

DELIMITER ;

SELECT enclose( 'abc', '[', ']' );
SELECT enclose( '(abc', '(', ')' );
SELECT enclose( 'abc)', '(', ')' );
SELECT enclose( '(abc)', '(', ')' );
SELECT enclose( '[abc)', '(', ')' );
Code Explanation

Three parameters is passed to the function str: string to enlose, left enclosure, right enclosure.

The return type is a string (data type VARCHAR(255)).

Some preliminary parameter validation is performed.

The function checks IF the string already has the given left or right enclosures, if not present, the enclosures are added.

Branching Using CASE

CASE is a syntactic variant of IF that is particularly useful when all the branch decisions depend on the value of a single expression.

CASE expression
WHEN value1 THEN
commands;
[WHEN value2 THEN
commands;]
[ELSE
commands;]
END CASE;

REPEAT-UNTIL Loop

The code block between REPEAT and UNTIL is executed until the condition becomes true. The condition is evaluated only at the end of the loop body, at least one pass through the loop.

The loop can be given an optional label. The same loop name must then appear at the end of the loop. Naming a loop can be useful if you exit the loop prematurely with LEAVE or wish to repeat a loop iteration with ITERATE.

[loopname:] REPEAT
commands;
UNTIL condition
END REPEAT [loopname];

The following function is an example of a REPEAT-UNTIL loop. The function returns a string containing the + character n times.

DELIMITER ;

DROP FUNCTION IF EXISTS repeat_plus;
DELIMITER $$

CREATE FUNCTION repeat_plus(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE returnstr VARCHAR(255) DEFAULT '';
DECLARE c VARCHAR(1) DEFAULT '+';

char_add_loop: REPEAT
SET i = i+1;
SET returnstr = CONCAT( returnstr, c );
UNTIL i >= n END REPEAT;

RETURN returnstr;

END$$

DELIMITER ;

SELECT repeat_plus(5);
Code Explanation

One parameter is passed to the function repeat_plus(): an integer n to indicate how many pluses (+) to add.

The return type is a string (data type VARCHAR(255)).

Some preliminary parameter validation is performed.

The output string contains + repeated n times.

WHILE

The instructions between DO and END WHILE are executed as long as the condition is satisfied. Since the condition is evaluated at the beginning of the loop, it can happen that the loop is not executed even once (namely, when the condition is not satisfied on its initial evaluation). If you wish to use LEAVE or ITERATE, you must provide the loop with a label.

Syntax
[loopname:] WHILE condition DO
commands;
END WHILE [loopname];

LOOP

The instructions LOOP and END LOOP are executed until the loop is exited with LEAVE loopname. It is not syntactically required that the loop be given a name, but in practice, it is usually done (unless you wish to create an infinite loop).

Syntax
loopname: LOOP
commands;
END LOOP loopname;

Here is an example of a LOOP, to cut a given number.

DELIMITER ;

DROP FUNCTION IF EXISTS cut_number;
DELIMITER $$

CREATE FUNCTION cut_number(startnum INT, cutby INT, times INT)
RETURNS INT
BEGIN

DECLARE i INT DEFAULT 0;
DECLARE s TEXT DEFAULT '';
DECLARE cut INT DEFAULT 0;

SET cut = startnum;

cutloop: LOOP

IF i >= times THEN LEAVE cutloop; END IF;

SET cut = cut - cutby;
SET i = i + 1;

END LOOP cutloop;

RETURN cut;
END$$

DELIMITER ;

SELECT cut_number( 80, 16, 4 );
SELECT cut_number( 100, 20, 5 );
SELECT cut_number( 120, 4, 6 );
Code Explanation

Three parameters are passed to the function cut_number(): a starting number, what number to cut and how many times.

The return type is a reduced number.

LEAVE and ITERATE

As seen above, LEAVE loopname exits the loop block. LEAVE can also be used to exit a BEGIN-END block out-of-turn.

ITERATE loopname has the effect that the remaining part of the loop body is ignored and the loop is executed again. ITERATE only works with loops and cannot be used with a regular BEGIN-END block.

Here is an example of a WHILE loop to cut a given number, but ignore the cut every third run.

DELIMITER ;

DROP FUNCTION IF EXISTS cut_number_except3;
DELIMITER $$

CREATE FUNCTION cut_number_except3(startnum INT, cutby INT, times INT)
RETURNS INT NO SQL
BEGIN

DECLARE i INT DEFAULT 0;
DECLARE s TEXT DEFAULT '';
DECLARE cut INT DEFAULT 0;

SET cut = startnum;

cutloop: WHILE ( i < times ) DO

SET i = i + 1;
IF i % 3 = 0 THEN ITERATE cutloop; END IF;

SET cut = cut - cutby;

END WHILE;

RETURN cut;
END$$

DELIMITER ;

SELECT cut_number_except3( 60, 10, 6 );
SELECT cut_number_except3( 80, 16, 4 );
SELECT cut_number_except3( 100, 20, 5 );
SELECT cut_number_except3( 120, 4, 6 );
Code Explanation

As before, three parameters are passed to the function cut_number(): a starting number, what number to cut and how many times.

The return type is a reduced number.

ITERATE is used to continue the loop every third run.

Security

Only users with Create Routine and Alter Routine privileges may create, change, or delete SPs. The privilege Execute determines which MySQL users are allowed to execute SPs.

As SPs may contain several DML operations, some sensitive and critical, such as updating an employee's salary. So even if a user is permitted to execute SPs, some control of permitted database operations (that are defined in the SP) is critical. For example, can a table such as salary be updated via a particular SP invocation?.

MySQL identifies two distinct situations:

  • SQL SECURITY INVOKER: Routines with this option have the same access privileges as the MySQL user executing the routine. So, an SP can delete records from table(s) if the user running that SP has the GRANT to do so.
  • SQL SECURITY DEFINER: SPs with this option have the same privileges as the MySQL user who defined the SP. This security mode holds by default; that is, if the option SQL SECURITY INVOKER is not taken, then DEFINER holds.

Warning: Routines defined by privileged users such as admin or root have unrestricted privileges in all databases, and can cause a lot of hidden damage to data.

Using MySQL Query Browser

One can also use the MySQL Query Browser.

Defining an SP: First select the database mylibrary via FILE | SELECT SCHEMA and then execute the command SCRIPT | CREATE STORED PROCEDURE. After you have specified the desired name and type (procedure or function), the Query Browser presents a template for you to complete with code. When you are finished, store the new SP with EXECUTE. If you have committed a syntax error, you must halt execution with STOP. Correct the error and try again.

Testing the SP: To try out your new function, execute FILE | NEW QUERY TAB , give the command SELECT reduce(description, 20), title FROM film, execute it with EXECUTE.

Editing an SP: While you have the dialog for defining the function reduce open, you can easily edit it. (Remember to EXECUTE.)

Error Handling via Handlers

It is likely that SP will encounter errors may occur during the execution of SQL commands. MySQL therefore provides a limited mechanism via handlers to react to such errors.

A handler must be defined after the declaration of variables, cursors, and conditions but before the SQL commands of a BEGIN-END block, as shown:

Syntax
DECLARE type HANDLER FOR
condition1, condition2, condition3, ... statement;
  • type: Presently, types CONTINUE and EXIT are allowed. The CONTINUE specifies that procedure execution continues with the next command after the occurrence of an error. EXIT means that the BEGIN-END block is exited and the program continued after the block.
  • condition(s): The conditions are what cause the handler to be invoked. There are several ways to specify the error conditions:
    • SQLSTATE 'errorcode': Specifies a particular error code.
    • SQLWARNING: Covers all 01nnn SQLSTATEs.
    • NOT FOUND: Covers all other errors, where SQLSTATEs that do not begin with 01 or 02.
    • mysql-error-number: MySQL error number instead of the SQLSTATE code.
    • condition-name: Refers to a condition that was named with DECLARE CONDITION .
  • statement: Statement to be executed when conditions are matched. The statement must be specified for a EXIT-type HANDLER as well, however simple it may be.

A list of all MySQL error codes and the associated SQLSTATE values can be found in the online MySQL documentation at http://dev.mysql.com/doc/mysql/en/error-handling.html.

Named Conditions

Naming Conditions make cryptic error codes somewhat more readble. A condition must be declared before a handler as the name of the condition can then be used in the handler:

Syntax
DECLARE name CONDITION FOR condition;

The following example shows the declaration of a variable, a condition, and a handler for the error duplicate key.

DECLARE error_msg VARCHAR(200);
DECLAREerror_condition CONDITION FOR SQLSTATE '23000';
DECLARE CONTINUE HANDLER FOR error_condtion SET error_msg = 'An Errror has occurred.';

Here is a detailed example to show error handling:

DELIMITER ;
DROP TABLE IF EXISTS error_log;
CREATE TABLE IF NOT EXISTS error_log (error_code VARCHAR(200), error_message
VARCHAR(4000));

DROP PROCEDURE IF EXISTS `delete_category`;
DELIMITER $$

CREATE PROCEDURE `delete_category`(p_category_id INT)
BEGIN

DECLARE `CategoryInUse` CONDITION FOR 1451;

DECLARE EXIT HANDLER FOR `CategoryInUse`
INSERT INTO error_log(error_code,error_message) VALUES(
'1451',
CONCAT( 'Time:', current_date,
':*** Error: Category has references, cannot delete Category ID = ',
 p_category_id ) );

START TRANSACTION;
BEGIN
DELETE FROM category WHERE category_id = p_category_id;
COMMIT;
END;
ROLLBACK;

END$$

DELIMITER ;

CALL `delete_category`(10);

SELECT * FROM category WHERE category_id = 10;
SELECT * FROM error_log;
Code Explanation

Change delimiting to $$ to allow for semicolon use inside of procedure: delimiter $$

A category ID is are passed to the procedure.

An attempt is made to delete category with the given ID.

An error condition and an error handler have been defined in the procedure.

The error handler records an error condition in an error_log table.

Triggers

Triggers are automatic execution of SQL commands or a stored procedure automatically after or before INSERT, UPDATE, or DELETE commands. For example, you can test every UPDATE operation to see whether the altered data conform to a particular set of rules. Other possible applications include changes to logging and updating variables or columns in other tables.

Since trigger code is automatically executed at each change in the table, complex triggers can sharply reduce data throughput performance. This is particularly true when a command changes a large number of records (UPDATE table SET columnA = columnA + 1); the trigger code must be executed for each data record.

Note: The trigger implementation in MySQL 5.0 is unfortunately not very complete. Triggers will probably not be of practical use until version 5.1.

Creating a Trigger

You create a new trigger with the command CREATE TRIGGER. Only MySQL users with the Super privilege can execute this command.

Syntax
CREATE TRIGGER name BEFORE|AFTER INSERT|UPDATE|DELETE
ON tablename FOR EACH ROW sql-code

You can define up to six triggers for each table, whose code is to be executed before or after each INSERT, UPDATE, or DELETE command. If the command encompasses more than one record, the trigger will be executed for each record.

Currently (MySQL 5.0.3), each trigger must have a unique name within a table. In the future, trigger names will be valid for the entire database.

There are similar syntax rules for trigger code as for SPs. In particular, the code must be introduced with BEGIN and terminated with END if it contains more than one command. All SP language elements are available for triggers. However, there are many restrictions as to which SQL commands may be used in trigger code (more on this below).

Within the trigger code you can access the columns of the current record:

  • OLD.columnname returns the content of an existing record before it is changed or deleted (UPDATE, DELETE).
  • NEW.columnname returns the content of a new or altered record (INSERT, UPDATE).
  • You may change NEW.columnname in BEFORE INSERT triggers and BEFORE UPDATE triggers.

Deleting a Trigger

To delete a trigger, execute DROP TRIGGER. You need to specify the table and trigger names.

DROP TRIGGER tablename.triggername
\T trig_name.log

DROP TABLE IF EXISTS trig_film;

CREATE TABLE trig_film(film_id SMALLINT NOT NULL PRIMARY KEY,
 title VARCHAR(255), rating VARCHAR(25), category VARCHAR(25),
 price FLOAT);

DROP TRIGGER IF EXISTS film_before_insert;
DROP TRIGGER IF EXISTS film_before_update;

DROP TRIGGER film_before_insert;
DROP TRIGGER film_before_update;

DELIMITER $$

CREATE TRIGGER film_before_insert
BEFORE INSERT ON trig_film FOR EACH ROW
BEGIN
-- Force price to 1 for children movies
IF NEW.rating = 'PG' AND NEW.category = 'Children' THEN
SET NEW.price = ROUND(NEW.price / 2, 2);
END IF;
IF NEW.price > 4.0 THEN
SET NEW.price = 4.0;
END IF;

-- Notch up ratings if category is foreign
IF NEW.category = 'Foreign' THEN
IF NEW.rating = 'PG' THEN
SET NEW.rating = 'PG-13';
ELSEIF NEW.rating = 'PG-13' THEN
SET NEW.rating = 'NC-17';
END IF;
END IF;
END$$

CREATE TRIGGER film_before_update
BEFORE UPDATE ON trig_film FOR EACH ROW
BEGIN
-- Force price to 1 for children movies
IF NEW.rating = 'PG' AND NEW.category = 'Children' THEN
SET NEW.price = ROUND(NEW.price / 2, 2);
END IF;
IF NEW.price > 4.0 THEN
SET NEW.price = 4.0;
END IF;

-- Notch up ratings if category is foreign
IF NEW.category = 'Foreign' THEN
IF NEW.rating = 'PG' THEN
SET NEW.rating = 'PG-13';
ELSEIF NEW.rating = 'PG-13' THEN
SET NEW.rating = 'NC-17';
END IF;
END IF;
END$$

DELIMITER ;

INSERT INTO trig_film(film_id, title, rating, category, price)
SELECT film_id, title, rating, category_name, rental_rate
FROM film_detail;

SELECT title, price FROM trig_film WHERE rating = 'PG'
 AND category = 'Children'
 LIMIT 20;
SELECT title, rating FROM trig_film WHERE category = 'Foreign'
 LIMIT 20;

UPDATE trig_film SET price = 3.99 WHERE rating = 'PG'
AND category = 'Children'
AND price >= 1.5;
UPDATE trig_film SET price = 4.99 WHERE category = 'Horror'
 AND price >= 3.99;
UPDATE trig_film SET rating = 'PG' WHERE category = 'Foreign'
 AND rating = 'PG-13';
UPDATE trig_film SET rating = 'PG-13' WHERE category = 'Foreign'
 AND rating = 'NC-17';

SELECT title, price FROM trig_film WHERE rating = 'PG'
AND category = 'Children' LIMIT 20;
SELECT category, title, rating FROM trig_film
WHERE category = 'Foreign' LIMIT 20;
SELECT category, title, price FROM trig_film
WHERE category = 'Horror' LIMIT 20;

\t
Code Explanation

The update and insert triggers ensure that:

  • Price is made half for PG/Children films
  • Price cannot be set over 4 bucks
  • Ratings are adjusted for Foreign films, 'PG' becomes 'PG-13', 'PG-13' becomes 'NC-17'

The script also show dropping of triggers.

Implementation Details and Administrative Assistance

Currently (MySQL 5.0.3), MySQL trigger code is stored in the text file tablenname.TRG in the database directory.

Furthermore, there is currently is no real user interface/administrative help for Triggers: MySQL Query Browser, MySQL Administrator, phpMyAdmin, etc., know nothing about triggers. To CREATE TRIGGERs, one must use the command interpreter mysql.

To backup defined triggers, back up the .TRG files in the database directory.

The option IF EXISTS is supported in later releases of MySQL 5.0.

There may not be any administrative commands like ALTER TRIGGER, SHOW CREATE TRIGGER, or SHOW TRIGGER STATUS.

SHOW TRIGGERS is available in later releases of MySQL 5.0 onwards.

SHOW TRIGGERS;
  • One cannot access tables in trigger code, not even the table for which the trigger was defined. As with SP functions, the commands SELECT, UPDATE, INSERT, etc., are unavailable.
  • There are no commands or language elements to cancel the DELETE, UPDATE, or INSERT command from within a trigger or to raise an error.
  • Trigger code cannot call an SP with CALL.
  • No transaction commands can be called in trigger code.
DROP TABLE IF EXISTS trig_test;

CREATE TABLE trig_test (idcol SERIAL, rate FLOAT, status
ENUM('low','high','orig')
DEFAULT 'orig' );

DELIMITER $$

DROP TRIGGER IF EXISTS trig_test.trig_test_before_insert;
DROP TRIGGER IF EXISTS trig_test.trig_test_before_update;

CREATE TRIGGER trig_test_before_insert
BEFORE INSERT ON trig_test FOR EACH ROW
BEGIN
IF NEW.rate < 0.0 THEN
SET NEW.rate = 0.0;
SET NEW.status = 'low';
ELSEIF NEW.rate > 5.0 THEN
SET NEW.rate = 5.0;
SET NEW.status = 'high';
END IF;
END$$

CREATE TRIGGER trig_test_before_update
BEFORE UPDATE ON trig_test FOR EACH ROW
BEGIN
IF NEW.rate < 0.0 THEN
SET NEW.rate = 0.0;
SET NEW.status = 'low';
ELSEIF NEW.rate > 5.0 THEN
SET NEW.rate = 5.0;
SET NEW.status = 'high';
END IF;
END$$
DELIMITER ;

INSERT INTO trig_test (rate) VALUES (-1), (0.3), (5.5), (3.5), (-0.2), (4.2);
SELECT * FROM trig_test;

UPDATE trig_test SET rate = 1.7 WHERE idcol = 2;
SELECT * FROM trig_test;
Code Explanation
  1. Create a table with an ID, rate and an status enumeration.
  2. The status enumeration can take values as 'low', 'high' or 'orig'.
  3. Create two triggers, one for update and another for insert.
  4. Validate rate in triggers, ensure the lowest value allowed is (and set to) 0 and the highest value allowed to 5.
  5. While in the trigger, record any trigger-based adjustment made to rate in the status field

Programming with MySQL Conclusion

This lesson introduced you to programming elements of MySQL.

To continue to learn MySQL go to the top of this page and click on the next lesson in this MySQL Tutorial's Table of Contents.
 
mysql tools