MySQL in a Nutshell (2008)
Part II. SQL Statements and Functions
Chapter 9. Stored Routines Statements
MySQL allows sets of SQL statements, known as routines, to be stored in the database for easier and more consistent use. You can create your own functions based on existing SQL statements and built-in functions, allowing a user to pass values to these user-defined functions as well as receive values in return. This can make complex tasks simpler for end users, as well as allow database administrators to control or enhance the functions available to users. Additionally, MySQL provides SQL statements related to events. Events are internal methods to schedule the execution of SQL statements or stored procedures. These are the SQL statements covered in this chapter:
ALTER EVENT, ALTER FUNCTION, ALTER PROCEDURE, ALTER TRIGGER, BEGIN...END, CALL, CLOSE, CREATE EVENT, CREATE FUNCTION, CREATE PROCEDURE, CREATE TRIGGER, DECLARE, DELIMITER, DROP EVENT, DROP FUNCTION, DROP PREPARE, DROP PROCEDURE, DROP TRIGGER, EXECUTE, FETCH, OPEN, PREPARE, SHOW CREATE EVENT, SHOW CREATE FUNCTION, SHOW CREATE PROCEDURE, SHOW EVENTS, SHOW FUNCTION CODE, SHOW FUNCTION STATUS, SHOW PROCEDURE CODE, SHOW PROCEDURE STATUS, SHOW TRIGGERS.
Statements in Alphabetical Order
This section is an alphabetical listing of MySQL statements related to events, stored procedures, triggers, and user-defined functions. For an explanation of the method of presenting syntax and describing the SQL statements, as well as for information related to examples, please see the introduction to Part II. Many of the examples in this particular chapter involve the activities of a fictitious college.
Name
ALTER EVENT
Synopsis
ALTER EVENT
[DEFINER = {'user'@'host'|CURRENT_USER}]
event
ON SCHEDULE
AT timestamp [+ INTERVAL count interval] |
EVERY count interval
[STARTS timestamp [+ INTERVAL count interval]]
[ENDS timestamp [+ INTERVAL count interval]]
[ON COMPLETION [NOT] PRESERVE]
[ENABLE|DISABLE|DISABLE ON SLAVE]
[COMMENT 'comment']
DO statement
Use this statement to alter an existing scheduled MySQL event. The statement can be used to change the time when the scheduled SQL statement will execute or other aspects of its upcoming execution. The event parameter has to be the name of an event that was already scheduled but has not yet been completed, or was completed but preserved by the server. It isn’t possible within MySQL to change the name of an event. Instead, use the DROP EVENT statement to delete an existing event and then create it again with a new name using CREATE EVENT. You can use theSHOW CREATE EVENT statement to be sure that all other parameters are the same.
To change the MySQL user and host through which MySQL executes the event, use the DEFINER clause. As of version 5.1.12 of MySQL, a user that has EVENT privilege can change an event. Unless the definer is specified with the DEFINER clause, the user that changes an event becomes the new definer.
To change the time and date that form the basis for running the event, use the ON SCHEDULE AT clause and give the new time in the timestamp format (yyyy-mm-dd hh:mm:ss). The time given can be a string, a time function, or just CURRENT_TIMESTAMP. You can also specify a time relative to the timestamp given by adding a plus sign followed by the keyword INTERVAL, the number of intervals (e.g., 1), and then the interval increment (e.g., HOUR). For interval, use one of the allowable intervals shown in the description of the CREATE EVENT statement later in this chapter.
To make the event a recurring one, add the EVERY clause, using the same syntax and format. You can also give starting and ending times for a recurring event with the STARTS and ENDS clauses.
If an event is not yet completed, you can keep the server from dropping it by adding the ON COMPLETION clause with the PRESERVE keyword. If you already did this when you created the event, you can change your mind and set the server to NOT PRESERVE the event.
If you created an event that you need to temporarily disable for some reason, you can do so with this statement by using the DISABLE keyword. An event that has been disabled can be enabled with the ENABLE keyword. The DISABLE ON SLAVE keyword prevents the event from running on slave servers.
With the COMMENT clause, you can add or change a comment describing the event for future reference. The DO clause can include any SQL statement to be executed. A stored procedure can be used to easily execute a set of SQL statements.
Here is an example using this statement to change a periodic event:
ALTER EVENT students_copy
ON SCHEDULE EVERY 1 DAY
STARTS '2007-12-10 01:30:00'
ON COMPLETION PRESERVE;
If you look at the example for CREATE EVENT later in this chapter, you’ll see that our only change is to move the time from 2:30 A.M. to 1:30 A.M. here. The starting time and date given are not only for the time we want, but since this statement is run on December 9, the date of December 10 is given. When an event’s time is altered or when an event is first created, it must be for a future time. The EVERY clause is included because STARTS is part of it and not a separate clause of its own. So that the ON COMPLETION PRESERVE isn’t set back to the default of ON COMPLETION NOT PRESERVE, we stipulate it again here.
Name
ALTER FUNCTION
Synopsis
ALTER FUNCTION stored_procedure
[{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA} |
SQL SECURITY {DEFINER|INVOKER} |
COMMENT 'string']
This statement changes the characteristics of an existing user-defined function. You cannot change the function itself with it. To do that, you need to delete the function with DROP FUNCTION and create a new procedure with CREATE FUNCTION. See the description of CREATE FUNCTION later in this chapter for an explanation of each characteristic.
There are three types of characteristics you can set or change with this statement: the types of interaction with the server, the user recognized for SQL security, and a comment. Each type may be given in a space-separated list, in any order. See CREATE FUNCTION later in this chapter for a discussion of the characteristics. The COMMENT clause replaces any existing comment. To clear a comment without inserting another, give two quotes with nothing between them.
This statement requires the CREATE ROUTINE privilege. The ALTER ROUTINE and EXECUTE privileges are granted to the user and host account that creates or alters a function, by default.
Here is an example using this statement, in which a function shown in the example for the CREATE FUNCTION statement is altered:
ALTER FUNCTION date_reformatted
SQL SECURITY INVOKER
COMMENT "Converts a string date like 'Dec. 7, 2007' to standard format.";
Name
ALTER PROCEDURE
Synopsis
ALTER PROCEDURE stored_procedure
[{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}]
[SQL SECURITY {DEFINER|INVOKER}]
[COMMENT 'string']
This statement changes the characteristics of an existing stored procedure. You cannot change the procedure itself with it. To do that, you need to delete the procedure with DROP PROCEDURE and create a new procedure with CREATE PROCEDURE. See the description of CREATE PROCEDURE later in this chapter for an explanation of each characteristic.
There are three types of characteristics that you can set or change with this statement: the types of interaction with the server, the user recognized for SQL security, and a comment. Each type may be given in a space-separated list, in any order. See CREATE PROCEDURE later in this chapter for a discussion of the characteristics. The COMMENT clause replaces any existing comment. To clear a comment without inserting another, give two quotes with nothing between them.
This statement requires the CREATE ROUTINE privilege. The ALTER ROUTINE and EXECUTE privileges are granted to the user and host account that creates or alters a stored procedure, by default.
Here is an example of this statement:
ALTER PROCEDURE students_copy_proc
SQL SECURITY INVOKER
COMMENT 'Copies data from students table to students_backup.
Add a comment with @ref_note.'
If you look at the example for CREATE PROCEDURE later in this chapter, you’ll see that the example here is changing the procedure created in that example. We’re only adding that the user account to be used for executing the procedure will be the invoker, and we’re adding a comment about the procedure—we didn’t include one when we created the procedure.
Name
ALTER TRIGGER
Synopsis
There is not an ALTER TRIGGER statement at this time. Instead, use the DROP TRIGGER statement and then CREATE TRIGGER again with the new, adjusted trigger.
Name
BEGIN...END
Synopsis
BEGIN...END
Use this combination of statements to start and end the steps that are part of a stored procedure or trigger. In essence, BEGIN marks the beginning of a compound SQL statement and END marks the end of it. Multiple SQL statements can be included between them.
Traditionally, as you know from using the mysql client, each SQL statement must end with a semicolon. However, semicolons must be used within CREATE PROCEDURE and CREATE TRIGGER statements to separate the internal statements that form the procedure or trigger. So as not to confuse the parser in the client and server, include a DELIMITER command to change the default delimiter to another character before entering BEGIN, and then to set it back to a semicolon again after entering END. For examples of these statements, see the CREATE PROCEDURE andCREATE TRIGGER statements later in this chapter.
Name
CALL
Synopsis
CALL stored_procedure[([parameter[, ...]])]
Use this statement to call a stored procedure. Parameters to be passed to the stored procedure may be given within the parentheses. If the keyword of INOUT is used, values may be given to the stored procedure and returned to the SQL statement that called it. For an example of this statement, see the CREATE PROCEDURE statement later in this chapter.
Name
CLOSE
Synopsis
CLOSE cursor
This statement closes a cursor that has been declared within the current routine and has been opened using the OPEN statement. See the descriptions of the DECLARE and FETCH statements later in this chapter for more information on cursors.
Name
CREATE EVENT
Synopsis
CREATE [DEFINER = {'user'@'host'|CURRENT_USER}] EVENT
[IF NOT EXISTS] event
ON SCHEDULE
AT timestamp [+ INTERVAL count interval] |
EVERY count interval
[STARTS timestamp [+ INTERVAL count interval]]
[ENDS timestamp [+ INTERVAL count interval]]
[ON COMPLETION [NOT] PRESERVE]
[ENABLE|DISABLE|DISABLE ON SLAVE]
[COMMENT 'comment']
DO statement
Use this statement to schedule the execution of an SQL statement at a specific time and date. Events may also be recurring. Although there are many options, the basic syntax is:
CREATE EVENT event ON SCHEDULE AT timestamp DO statement
The event name you give may be any nonreserved word and is case-insensitive. The DO clause can include any SQL statement to be executed. A stored procedure can be passed here to conveniently execute a set of SQL statements.
With the DEFINER clause, you can specify the MySQL user and host to be used by MySQL for the event. This means that the event may be created by a user with SUPER privileges but executed by another user account in which privileges are limited for security reasons. The IF NOT EXISTS clause may be given to prevent errors from being returned if the event has already been created.
For the required ON SCHEDULE AT clause, include a specific time and date in the timestamp format (yyyy-mm-dd hh:mm:ss). The time given can be a string, a time function, or just CURRENT_TIMESTAMP. You can also specify a time relative to the timestamp given by adding a plus sign followed by the keyword INTERVAL, the number of intervals (e.g., 1), and then the interval increment (e.g., HOUR). For interval, use one of the allowable intervals: SECOND, MINUTE, MINUTE_SECOND, HOUR, HOUR_SECOND, HOUR_MINUTE, DAY, DAY_SECOND, DAY_MINUTE, DAY_HOUR,WEEK, MONTH, QUARTER, YEAR, or YEAR_MONTH.
To make the event a recurring one, add the EVERY clause, using the same syntax and format. You can also give starting and ending times for a repeating event with the STARTS and ENDS clauses.
Once an event is completed, it will be dropped automatically. However, you can drop it manually before completion with the DROP EVENT statement. You can also keep the server from dropping an event by adding the ON COMPLETION clause with the PRESERVE keyword. The NOT PRESERVE keyword instructs the server not to retain the event when completed; this is the server’s default behavior.
When creating an event, you may want to create it with the DISABLE parameter so that it won’t begin to execute until you enable it. Then use the ALTER EVENT statement to enable it later. The DISABLE ON SLAVE keyword will disable the event from running on slave servers. By default, an event runs on the master and all slaves.
With the COMMENT clause, you can add a comment describing the event for future reference. This comment is displayed only when SHOW CREATE EVENT is executed for the event.
Here is an example using this statement. It schedules a procedure that is created in the example under the CREATE PROCEDURE statement later in this chapter:
CREATE EVENT students_copy
ON SCHEDULE EVERY 1 DAY
STARTS '2007-11-27 02:30:00'
ON COMPLETION PRESERVE
COMMENT 'Daily copy of students table to students_backup'
DO CALL students_copy_proc();
In this example, the event will be run once a day starting from the time given and then every day afterward at the same time (2:30 A.M.). It’s set to be recurring, but in case someone ever changes that aspect of it, MySQL will preserve the event upon completion. We’ve added a comment to explain the purpose of the event. Use ALTER EVENT to change an event and SHOW EVENTS to get a list of events.
Name
CREATE FUNCTION
Synopsis
CREATE
[DEFINER = {'user'@'host'|CURRENT_USER}]
FUNCTION function ([parameter data_type[,...]])
RETURNS data_type
[LANGUAGE SQL]
[[NOT] DETERMINISTIC]
[{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}]
[COMMENT 'string']
[SQL SECURITY {DEFINER|INVOKER}]
RETURN routine
A user-defined function is essentially a set of SQL statements that may be called as a unit, processing any data it’s given in its parameters and returning a value to the caller of the function. This is similar to a stored procedure, except that a function returns a value and a stored procedure does not. A stored procedure normally places the values it generates in user variables that can then be retrieved in various ways.
The basic, minimum syntax is something like this:
CREATE FUNCTION function_name (parameter) RETURNS INT RETURN routine
The function name given can be any nonreserved name; don’t use the name of a built-in function. The name is case-insensitive. Within parentheses, give a comma-separated list of the parameters. For each parameter, specify the data type to be used (INT, CHAR, etc.). The keyword RETURNS is followed by the data type of the value that will be returned by the function. At the end comes the keyword RETURN followed by the routine to perform.
You may provide special parameters to indicate the characteristics of the function. Several may be given in any order, in a space-separated list. You can specify the language used as SQL with the LANGUAGE SQL parameter, but this is the default and usually unnecessary.
A function that returns the same results each time for the same given parameters is considered deterministic. You can save processing time on the server by specifying this property through the DETERMINISTIC parameter. NOT DETERMINISTIC is the default.
The following keywords may be used to tell the server how the function will interact with it, allowing the server to optimize the function. The server does not enforce the restrictions on the function, however:
CONTAINS SQL
The function executes SQL statements, but does not read from or write to a table; one example is a function that queries server status. This is the default.
NO SQL
The function does not contain any SQL statements.
READS SQL DATA
The function might read data from at least one table, but it doesn’t write data to any tables.
MODIFIES SQL DATA
The function might write data to at least one table, as well as potentially read data from tables.
With the COMMENT clause, you can add a comment describing the function for future reference.
This statement requires the CREATE ROUTINE privilege. The ALTER ROUTINE and EXECUTE privileges are granted to the user and host account that creates or alters a routine, by default. With the DEFINER clause, you can specify the MySQL user and host to be used by MySQL for the function. Related to this clause is SQL SECURITY keyword, which instructs MySQL to use either the user account of the creator (DEFINER) of the function or the account that’s calling the function (INVOKER). This can help to prevent some users from accessing restricted functions.
Here is an example using this statement:
CREATE FUNCTION date_reformatted (new_date VARCHAR(13))
RETURNS DATE
RETURN STR_TO_DATE(REPLACE(new_date, '.', ''), '%b %d, %Y');
SELECT date_reformatted('Dec. 7, 2007')
AS proper_date;
+-------------+
| proper_date |
+-------------+
| 2007-12-07 |
+-------------+
This function simply uses the STR_TO_DATE() function to convert a string to a particular date format (i.e., yyyy-mm-dd) based on a common string that users may give. It expects the data given to be no more than 13 characters long. Because some users may include a period after the abbreviated month name and some may not, the function uses the REPLACE() function to remove the period. A function like this one can be used in any type of statement (e.g., an UPDATE statement to set a column value).
To change an existing user-defined function, use the ALTER FUNCTION statement. The DROP FUNCTION statement removes a user-defined function. You cannot change standard, built-in functions.
Name
CREATE PROCEDURE
Synopsis
CREATE
[DEFINER = {'user'@'host'|CURRENT_USER}]
PROCEDURE stored_procedure ([[IN|OUT|INOUT] parameter data_type[,...]])
[LANGUAGE SQL]
[NOT] DETERMINISTIC]
[{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}]
[COMMENT 'string']
[SQL SECURITY {DEFINER|INVOKER}]
routine
A procedure, also known as a stored procedure, is a set of SQL statements stored on the server and called as a unit, processing any data it’s given in its parameters. A procedure may communicate results back to the user by placing the values it generates in user variables that can then be retrieved in various ways.
The basic, minimum syntax is something like this:
CREATE PROCEDURE procedure_name (IN parameter INT) SQL_statements
The procedure name given can be any nonreserved name, and is case-insensitive. Within parentheses, give a comma-separated list of the parameters that will take data in (IN), return data (OUT), or do both (INOUT). For each parameter, specify the data type to be used (INT, CHAR, etc.).
You may provide special parameters to indicate the characteristics of the stored procedure. Several may be given in any order, in a space-separated list. You can specify the language used as SQL with the LANGUAGE SQL parameter, but this is the default and usually unnecessary.
A procedure that returns the same results each time for the same given parameters is considered deterministic. You can save processing time on the server by specifying this property through the DETERMINISTIC parameter. NOT DETERMINISTIC is the default.
The following keywords may be used to tell the server how the procedure will interact with it, allowing the server to optimize the procedure. The server does not enforce the restrictions on the procedure, however:
CONTAINS SQL
The procedure executes SQL statements, but does not read from or write to a table; one example is a procedure that queries server status. This is the default.
NO SQL
The procedure does not contain any SQL statements.
READS SQL DATA
The procedure might read data from at least one table, but it doesn’t write data to any tables.
MODIFIES SQL DATA
The procedure might write data to at least one table, as well as potentially read data from tables.
With the COMMENT clause, you can add a comment describing the procedure for future reference.
This statement requires the CREATE ROUTINE privilege. The ALTER ROUTINE and EXECUTE privileges are granted to the user and host account that creates or alters a routine, by default. With the DEFINER clause, you can specify the MySQL user and host to be used by MySQL for the procedure. Related to this clause is the SQL SECURITY keyword, which instructs MySQL to use either the user account of the creator (DEFINER) of the procedure or the account that’s executing the procedure (INVOKER). This can help prevent some users from accessing restricted procedures.
In the following example, we create a simple procedure that copies all of the data from the students table to a backup table with the same schema. The table also includes an extra column in which the user can add a comment or reference note:
DELIMITER |
CREATE PROCEDURE students_copy_proc (IN ref_note VARCHAR(255))
BEGIN
REPLACE INTO students_backup
SELECT *, ref_note FROM students;
END|
DELIMITER ;
SET @ref_note = '2008 Spring Roster';
CALL students_copy_proc(@ref_note);
The first statement changes the terminating character for an SQL statement from its default, a semicolon, to a vertical bar. See the BEGIN...END statement earlier in this chapter for the reasons this is necessary.
Inside the procedure, the REPLACE statement selects all columns from students along with the value of the ref_note variable. Thus, every row of students is inserted, along with the value of the variable, into a new row in students_backup.
After the procedure is defined and the delimiter is changed back to a semicolon, the example sets a variable called ref_note that contains a note the user wants added to each row of data in the new table. This variable is passed to the CALL statement that runs the procedure.
To change an existing stored procedure, use the ALTER PROCEDURE statement. The DROP PROCEDURE statement removes a procedure.
Name
CREATE TRIGGER
Synopsis
CREATE
[DEFINER = {'user'@'host'|CURRENT_USER}]
TRIGGER trigger {AFTER|BEFORE}
{DELETE|INSERT|UPDATE}
ON table FOR EACH ROW statement
Only one of each trigger timing and trigger event combination is allowed for each table. For example, a table cannot have two BEFORE INSERT triggers, but it can have a BEFORE INSERT and an AFTER INSERT trigger.
To specify that the trigger be executed immediately before the associated user statement, use the parameter BEFORE; to indicate that the trigger should be executed immediately afterward, use AFTER.
At this time, only three types of SQL statements can cause the server to execute a trigger: insertions, deletions, and updates. Specifying INSERT, however, applies the trigger to INSERT statements, LOAD DATA statements, and REPLACE statements—all statements that are designed to insert data into a table. Similarly, specifying DELETE includes both DELETE and REPLACE statements because REPLACE potentially deletes rows as well as inserting them.
Triggers are actions to be taken when a user requests a change to data. Each trigger is associated with a particular table and includes definitions related to timing and event. A trigger timing indicates when a trigger is to be performed (i.e., BEFORE or AFTER). A trigger event is the action that causes the trigger to be executed (i.e., a DELETE, INSERT, or UPDATE on a specified table).
After specifying the trigger event, give the keyword ON followed by the table name. This is followed by FOR EACH ROW and the SQL statement to be executed when the trigger event occurs. Multiple SQL statements to execute may be given in the form of a compound statement usingBEGIN...END, which is described earlier in this chapter.
There is no ALTER TRIGGER statement at this time. Instead, use the DROP TRIGGER statement and then reissue CREATE TRIGGER with the new trigger.
To show how a trigger may be created, suppose that for a college database, whenever a student record is deleted from the students table, we want to write the data to another table to preserve that information. Here is an example of how that might be done with a trigger:
DELIMITER |
CREATE TRIGGER students_deletion
BEFORE DELETE
ON students FOR EACH ROW
BEGIN
INSERT INTO students_deleted
(student_id, name_first, name_last)
VALUES(OLD.student_id, OLD.name_first, OLD.name_last);
END|
DELIMITER ;
The first statement changes the terminating character for an SQL statement from its default, a semicolon, to a vertical bar. See the BEGIN...END statement earlier in this chapter for the reasons this is necessary.
Next, we create a trigger to stipulate that, before making a deletion in the students table, the server must perform the compound SQL statement given. The statements between BEGIN and END will write the data to be deleted to another table with the same schema.
To capture that data and pass it to the INSERT statement, we use the OLD table alias provided by MySQL coupled with the column names of the table where the row is to be deleted. OLD refers to the table in the trigger’s ON clause, before any changes are made by the trigger or the statement causing the trigger. To save space, in this example we’re capturing the data from only three of the columns. OLD.* is not allowed, so we have to specify each column. To specify the columns after they are inserted or updated, use NEW as the table alias.
The statement to be executed by the trigger in the previous example is a compound statement. It starts with BEGIN and ends with END and is followed by the vertical bar (|) that we specified as the delimiter. The delimiter is then reset in the last line back to a semicolon.
Name
DECLARE
Synopsis
DECLARE variable data_type [DEFAULT value]
DECLARE condition CONDITION FOR
{SQLSTATE [VALUE] value | error_code]
DECLARE cursor CURSOR FOR SELECT...
DECLARE {CONTINUE|EXIT|UNDO} HANDLER FOR
{[SQLSTATE [VALUE] value]
[SQLWARNING]
[NOT FOUND]
[SQLEXCEPTION]
[error_code]
[condition]}
SQL_statement
This statement declares local variables and other items related to routines. It must be used within a BEGIN...END compound statement of a routine, after BEGIN and before any other SQL statements. There are four basic uses for DECLARE: to declare local variables, conditions, cursors, and handlers. Within a BEGIN...END block, variables and conditions must be declared before cursors and handlers, and cursors must be declared before handlers.
The first syntax shows how to declare variables. It includes the data type and, optionally, default values. A variable declared with this statement is available only within the routine in which it is declared. If the default is a string, place it within quotes. If no default is declared, NULL is the default value.
A condition is generally either an SQLSTATE value or a MySQL error code number. The second syntax is used for declaring a condition and associating it with an SQLSTATE or an error code. When declaring a condition based on an SQLSTATE, give the SQLSTATE VALUE clause followed by the state. Otherwise, give the error code number.
The third syntax declares a cursor, which represents—within a procedure—a results set that is retrieved one row at a time. Give a unique, nonreserved word for the cursor’s name. This is followed by CURSOR FOR and then a SELECT statement. It must not have an INTO clause. To call or open a cursor, use the OPEN statement within the same routine in which the declaration was made. To retrieve data from a cursor, which is done one row at a time, use the FETCH statement. When finished, use the CLOSE statement to close an open cursor.
The last syntax for this statement declares a handler. With a handler, you can specify an SQL statement to be executed given a specific condition that occurs within a routine. Three types of handlers are allowed: CONTINUE, EXIT, and UNDO. Use CONTINUE to indicate that the routine is to continue after the SQL statement given is executed. The EXIT parameter indicates that the BEGIN...END compound statement that contains the declaration should be exited when the condition given is met. UNDO is meant to instruct MySQL to undo the compound statement for which it is given. However, this parameter is not yet supported by MySQL.
The handler’s FOR clause may contain multiple conditions in a comma-separated list. There are several related to the SQLSTATE: you can specify a single SQLSTATE code number, or you can list SQLWARNING to declare any SQLSTATE code starting with 01, NOT FOUND for any SQLSTATE code starting with 02, or SQLEXCEPTION for all states that don’t start with 01 or 02. Another condition you can give is a MySQL error code number. You can also specify the name of a condition you previously created with its own DECLARE statement.
Name
DELIMITER
Synopsis
DELIMITER character
This statement changes the delimiter (terminating character) of SQL statements from the default of a semicolon to another character. This is useful when creating a stored procedure or trigger, so that MySQL does not confuse a semicolon contained in the procedure or trigger as the end of theCREATE PROCEDURE or CREATE TRIGGER statement. This statement is also used to restore the default delimiter. Don’t use the backslash as the delimiter, as that is used to escape special characters. Examples of this statement appear in the CREATE PROCEDURE and CREATE TRIGGER statements earlier in this chapter.
Name
DROP EVENT
Synopsis
DROP EVENT [IF EXISTS] event
This statement deletes an event. The IF EXISTS keyword prevents error messages when the event doesn’t exist. Instead, a note will be generated, which can be displayed afterward by executing the SHOW WARNINGS statement. As of version 5.1.12 of MySQL, this statement requires theEVENT privilege.
Name
DROP FUNCTION
Synopsis
DROP FUNCTION [IF EXISTS] function
Use this statement to delete a user-defined function. The IF EXISTS keyword prevents error messages when the function doesn’t exist. Instead, a note will be generated, which can be displayed afterward by executing the SHOW WARNINGS statement. This statement requires the ALTER ROUTINE privilege for the function given, which is automatically granted to the creator of the function.
Name
DROP PREPARE
Synopsis
{DROP|DEALLOCATE} PREPARE statement_name
This statement deletes a prepared statement. The syntax of DROP PREPARE and DEALLOCATE PREPARE are synonymous. For an example, see the PREPARE statement later in this chapter.
Name
DROP PROCEDURE
Synopsis
DROP PROCEDURE [IF EXISTS] procedure
This statement deletes a stored procedure. The IF EXISTS keyword prevents error messages when the stored procedure doesn’t exist. Instead, a note will be generated, which can be displayed afterward by executing the SHOW WARNINGS statement. This statement requires the ALTER ROUTINE privilege for the stored procedure given, which is automatically granted to the creator of the stored procedure.
Name
DROP TRIGGER
Synopsis
DROP TRIGGER [IF EXISTS] [database.]trigger
This statement deletes a trigger. The IF EXISTS keyword prevents error messages when the trigger doesn’t exist. Instead, a note will be generated, which can be displayed afterward by executing the SHOW WARNINGS statement. You may specify the database or schema with which the trigger is associated. If not given, the current default database is assumed. As of version 5.1.6 of MySQL, this statement requires the TRIGGER privilege for the table related to the trigger given. Previously, it required SUPER privilege. When upgrading from version 5.0.10 or earlier of MySQL, be sure to drop all triggers because there’s a problem with using or dropping triggers from earlier versions.
Name
EXECUTE
Synopsis
EXECUTE statement_name [USING @variable[, ...] ...]
This statement executes a user-defined prepared statement. If the prepared statement contains placeholders so that you can pass parameters to it, these parameters must be given in the form of user-defined variables. Multiple variables may be given in a comma-separated list. You can use theSET statement to set the value of a variable. See the PREPARE statement later in this chapter for an example of the EXECUTE statement’s use.
Name
FETCH
Synopsis
FETCH cursor INTO variable[, ...]
A cursor is similar to a table or a view: it represents, within a procedure, a results set that is retrieved one row at a time using this statement. You first establish a cursor with the DECLARE statement. Then you use the OPEN statement to initialize the cursor. The FETCH statement retrieves the next row of the cursor and places the data retrieved into one or more variables. There should be the same number of variables as there are columns in the underlying SELECT statement of the cursor. Variables are given in a comma-separated list. Each execution of FETCH advances the pointer for the cursor by one row. Once all rows have been fetched, an SQLSTATE of 02000 is returned. You can tie a condition to this state through a DECLARE statement and end fetches based on the condition. Use the CLOSE statement to close a cursor.
Name
OPEN
Synopsis
OPEN cursor
This statement opens a cursor that has been declared within the current routine. Data selected with the cursor is accessed with the FETCH statement. The cursor is closed with the CLOSE statement. See the descriptions of the DECLARE and FETCH statements earlier in this chapter for more information on cursors.
Name
PREPARE
Synopsis
PREPARE statement_name FROM statement
This statement creates a prepared statement. A prepared statement is used to cache an SQL statement, so as to save processing time during multiple executions of the statement. This can potentially improve performance. Prepared statements are local to the user and session; they’re not global. The name given can be any nonreserved name and is case-insensitive. The statement given within quotes can be any type of SQL statement.
If you want to include a value that will be changed when the statement is executed, give a question mark as a placeholder within statement. When the prepared statement is executed later with the EXECUTE statement, the placeholders will be replaced with the values given. The values must be user variables (set with the SET statement) and must be passed to the EXECUTE statement in the order that the placeholders appear in the prepared statement. Here is a simple example using these statements:
PREPARE state_tally
FROM 'SELECT COUNT(*)
FROM students
WHERE home_city = ?';
SET @city = 'New Orleans';
EXECUTE state_tally USING @city;
SET @city = 'Boston';
EXECUTE state_tally USING @city;
In this example, the query within the prepared statement will return a count of the number of students from the city given. By setting the value of the user-defined variable @city to another city, we can execute the prepared statement state_tally again without having to reenter thePREPARE statement. The results will probably be different, of course. To remove a prepared statement from the cache, use the DROP PREPARE statement.
Name
SHOW CREATE EVENT
Synopsis
SHOW CREATE EVENT event
This statement displays an SQL statement that can be used to create an event like the one given. It’s mostly useful for displaying any comments associated with the event because they’re not included in the results of the SHOW EVENTS statement.
Here is an example showing an event that was created with the CREATE EVENT statement earlier in this chapter:
SHOW CREATE EVENT students_copy \G
*************************** 1. row ***************************
Event: students_copy
sql_mode:
Create Event: CREATE EVENT `students_copy` ON SCHEDULE
EVERY 1 DAY ON COMPLETION PRESERVE ENABLE
COMMENT 'Daily copy of students table to students_backup'
DO CALL students_copy_proc()
Name
SHOW CREATE FUNCTION
Synopsis
SHOW CREATE FUNCTION function
This statement displays an SQL statement that can be used to create a function like the one given. It’s useful for displaying the SQL statements that are performed by the function.
Here is an example of a function that was created with the CREATE FUNCTION statement earlier in this chapter:
SHOW CREATE FUNCTION date_reformatted \G
*************************** 1. row ***************************
Function: date_reformatted
sql_mode:
Create Function: CREATE DEFINER=`root`@`localhost`
FUNCTION `date_reformatted`(new_date VARCHAR(12))
RETURNS date
SQL SECURITY INVOKER
COMMENT 'Converts a string date like ''Dec. 7, 2007'' to standard format.'
RETURN STR_TO_DATE(REPLACE(new_date, '.', ''), '%b %d, %Y')
Name
SHOW CREATE PROCEDURE
Synopsis
SHOW CREATE PROCEDURE procedure
This statement displays an SQL statement that can be used to create a stored procedure like the one given. It’s useful for displaying the SQL statements that are performed by the stored procedure.
Here is an example of a procedure that was created with the CREATE PROCEDURE statement earlier in this chapter:
SHOW CREATE PROCEDURE students_copy_proc \G
*************************** 1. row ***************************
Procedure: students_copy_proc
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost`
PROCEDURE `students_copy_proc`(IN ref_note VARCHAR(255))
BEGIN
REPLACE INTO students_backup
SELECT *, ref_note FROM students;
END
Name
SHOW EVENTS
Synopsis
SHOW EVENTS [FROM database] [LIKE 'pattern'|WHERE expression]
This statement displays a list of scheduled events on the server. The results can also include events that have been completed but were preserved. The database to which events are related may be given in the FROM clause; the default is the current database. The LIKE or WHERE clauses can be used to list events based on a particular naming pattern. With the WHERE clause, you can use the names of fields in the results to create an expression that sets a condition determining the results returned. An example of this follows. See CREATE EVENT earlier in this chapter for more information on events:
SHOW EVENTS FROM college
WHERE Definer='russell@localhost' \G
*************************** 1. row ***************************
Db: college
Name: students_copy
Definer: russell@localhost
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: DAY
Starts: 2007-11-27 02:30:00
Ends: NULL
Status: ENABLED
Name
SHOW FUNCTION CODE
Synopsis
SHOW FUNCTION CODE function
This statement displays the internal code of a function. It requires that the MySQL server be built with debugging. This statement was introduced in version 5.1.3 of MySQL.
Name
SHOW FUNCTION STATUS
Synopsis
SHOW FUNCTION STATUS [LIKE 'pattern'|WHERE expression]
This statement displays information on user-defined functions. The LIKE or WHERE clauses can be used to list functions based on a particular naming pattern. With the WHERE clause, you can use the names of fields in the results to create an expression that sets a condition determining the results returned. Here is an example using this statement:
SHOW FUNCTION STATUS
WHERE Name='date_reformatted' \G
*************************** 1. row ***************************
Db: college
Name: date_reformatted
Type: FUNCTION
Definer: root@localhost
Modified: 2007-11-27 11:55:00
Created: 2007-11-27 11:47:37
Security_type: INVOKER
Comment: Converts a string date like 'Dec. 7, 2007' to standard format.
Name
SHOW PROCEDURE CODE
Synopsis
SHOW PROCEDURE CODE stored_procedure
This statement displays the internal code of a stored procedure. It requires that the MySQL server be built with debugging. This statement was introduced in version 5.1.3 of MySQL.
Name
SHOW PROCEDURE STATUS
Synopsis
SHOW PROCEDURE STATUS [LIKE 'pattern'|WHERE expression]
This statement displays information on stored procedures. The LIKE or WHERE clauses can be used to list stored procedures based on a particular naming pattern. With the WHERE clause, you can use the names of fields in the results to create an expression that sets a condition determining the results returned. Here is an example using this statement:
SHOW PROCEDURE STATUS
WHERE Name='students_copy_proc' \G
*************************** 1. row ***************************
Db: college
Name: students_copy_proc
Type: PROCEDURE
Definer: russell@localhost
Modified: 2007-11-27 09:27:42
Created: 2007-11-27 09:27:42
Security_type: DEFINER
Comment:
Note that for the WHERE clause we use the field name to get the specific stored procedure.
Name
SHOW TRIGGERS
Synopsis
SHOW TRIGGERS STATUS [FROM database]
[LIKE 'pattern'|WHERE expression]
This statement displays a list of triggers on the server. The database to which triggers are related may be given in the FROM clause; the default is the current database. The LIKE or WHERE clauses can be used to list triggers based on a particular naming pattern. The LIKE clause includes the name of the table with which the trigger is associated or a pattern for the table name that includes wildcards (%). With the WHERE clause, you can use the names of fields in the results to create an expression that sets a condition determining the results returned. Here is an example using this statement:
SHOW TRIGGERS LIKE 'students' \G
*************************** 1. row ***************************
Trigger: students_deletion
Event: DELETE
Table: students
Statement: BEGIN
INSERT INTO students_deleted
(student_id, name_first, name_last)
VALUES(OLD.student_id, OLD.name_first, OLD.name_last);
END
Timing: BEFORE
Created: NULL
sql_mode:
Definer: root@localhost
See CREATE TRIGGER earlier in this chapter for more information on triggers and to see how the trigger shown was created.