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:
[click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here].
9.1. 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 2. Many of the examples in this particular
chapter involve the activities of a fictitious college.
| ALTER EVENT |
ALTER EVENT |
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 [click here]
statement to delete an existing event and then create it again with a
new name using [click here]. You can use the [click here] 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 [click here] 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 [click here]
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.
| ALTER FUNCTION |
ALTER FUNCTION stored_procedure |
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 [click here] and create a new procedure with [click here]. See the description of [click here] 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 [click here] 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 [click here] statement
is altered:
ALTER FUNCTION date_reformatted
SQL SECURITY INVOKER
COMMENT "Converts a string date like 'Dec. 7, 2007' to standard format.";
| ALTER PROCEDURE |
ALTER PROCEDURE stored_procedure |
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 [click here] and create a new procedure with [click here]. See the description of [click here] 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 [click here] 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 [click here]
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.
| ALTER TRIGGER |
There is not an ALTER TRIGGER statement at
this time. Instead, use the [click here] statement
and then [click here] again with the new, adjusted
trigger.
| BEGIN...END |
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 [click here] and [click here] 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 [click here] and [click here] statements later in this chapter.
| CALL |
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 [click here]
statement later in this chapter.
| CLOSE |
CLOSE cursor |
This statement closes a cursor that has been declared within
the current routine and has been opened using the [click here] statement. See the descriptions of the [click here] and [click here] statements
later in this chapter for more information on cursors.
| CREATE EVENT |
CREATE [DEFINER = {'user'@'host'|CURRENT_USER}] EVENT |
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 [click here] 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 [click here]
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 [click here] is executed for the
event.
Here is an example using this statement. It schedules a
procedure that is created in the example under the [click here] 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 [click here] to change an event and [click here] to get a list of events.
| CREATE FUNCTION |
CREATE |
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 [click here] statement. The [click here]
statement removes a user-defined function. You cannot change standard,
built-in functions.
| CREATE PROCEDURE |
CREATE |
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
[click here] 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 [click here] statement. The [click here]
statement removes a procedure.
| CREATE TRIGGER |
CREATE |
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 using [click here], which is described earlier in this
chapter.
There is no ALTER TRIGGER statement at this
time. Instead, use the [click here] statement and
then reissue [click here] 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
[click here] 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.
| DECLARE |
DECLARE variable data_type [DEFAULT value] |
This statement declares local variables and other items
related to routines. It must be used within a [click here] 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 [click here] 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 [click here] 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 [click here] statement. When finished, use the [click here] 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 [click here] 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.
| DELIMITER |
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 the [click here] or [click here] 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 [click here] and [click here] statements earlier in this chapter.
| DROP EVENT |
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 the
EVENT privilege.
| DROP FUNCTION |
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.
| DROP PREPARE |
{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 [click here] statement later in this chapter.
| DROP PROCEDURE |
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.
| DROP TRIGGER |
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.
| EXECUTE |
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 the SET statement
to set the value of a variable. See the [click here]
statement later in this chapter for an example of the
EXECUTE statement's use.
| FETCH |
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 [click here] statement. Then you use the [click here] statement to initialize the cursor. The [click here] 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 [click here] 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 [click here] statement and end
fetches based on the condition. Use the [click here]
statement to close a cursor.
| OPEN |
OPEN cursor |
This statement opens a cursor that has been declared within
the current routine. Data selected with the cursor is accessed with
the [click here] statement. The cursor is closed with
the [click here] statement. See the descriptions of
the [click here] and [click here]
statements earlier in this chapter for more information on
cursors.
| PREPARE |
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 [click here] 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 [click here] 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 the [click here] statement. The results will
probably be different, of course. To remove a prepared statement from
the cache, use the [click here] statement.
| SHOW CREATE EVENT |
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 [click here] statement.
Here is an example showing an event that was created with the
[click here] 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()
| SHOW CREATE FUNCTION |
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 [click here] 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')
| SHOW CREATE PROCEDURE |
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
[click here] 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
| SHOW EVENTS |
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 [click here] 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
| SHOW FUNCTION CODE |
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.
| SHOW FUNCTION STATUS |
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:
Code View:
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.
| SHOW PROCEDURE CODE |
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.
| SHOW PROCEDURE STATUS |
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.
| SHOW TRIGGERS |
SHOW TRIGGERS STATUS [FROM database] |
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 [click here] earlier in this chapter for
more information on triggers and to see how the trigger shown was
created.
No comments:
Post a Comment