Stored Procedures in MySQL
Database vendors use a variety of programming languages and syntax for building and
managing stored procedures. Many of these databases share a set of core SQL commands,
but most of them have added extensions to facilitate the level of complexity that can be
attained within the stored procedure. Oracle procedures are written in PL/SQL. Microsoft SQL
Server 2000 procedures are written in Transact-SQL (T-SQL). To write procedures for PostgreSQL,
you use PL/psSQL. Each implementation includes some common commands, and then an
extended syntax for accomplishing more advanced logic.
MySQL developers have taken the expected approach in their implementation of stored
procedures. A database focused on simplicity and maximum performance would likely imple-
ment a simple set of features that supply the most amount of control to users wanting to move
logic into the database. MySQL has done this by implementing the SQL:2003 standard for
stored procedures and has added minimal MySQL-specific syntax. In the cases where MySQL
provides an extended use of a statement, the MySQL documentation (and this book) notes the
extension to the standard.
■Note The official standard for stored procedures is ISO/IEC 9075-x:2003, where x is a range of numbers
between 1 and 14 that indicate many different parts of the standard. For short, the standard is often referred
to as SQL:2003, SQL-2003, or SQL 2003. We refer to the standard a SQL:2003, since the official specifica-
tion uses the : as a separator, and MySQL documentation uses this format. The standard can be found on
the ISO web site () by doing a search for 9075. The standard is available for a fee.
The SQL:2003 standard provides a basic set of commands for building multiple-statement
interactions with the database. SQL:2003 was published in 2003 as the replacement for the pre-
vious SQL standard, SQL:1999. These standards include specifications for syntax and behavior
for SQL commands that are used to build, create, and maintain stored procedures. MySQL’s
choice to stick to the SQL:2003 standard means that stored procedures created in MySQL can be
seamlessly used in other databases that support this standard. Currently, IBM’s DB2 and Oracle
Database 10g are compliant with SQL:2003. The success of moving a stored procedure from Ora-
cle or DB2 into MySQL will depend on whether any of the vendor extensions have been used.
Even if the vendor supports SQL:2003, if a stored procedure uses vendor-specific syntax, MySQL
will fail on an unrecognized command when attempting to create the procedure.
The MySQL implementation provides a wide array of controls for processing data and

logic in the database. It doesn’t have the extended syntax bells and whistles of other database
systems, but it does provide a rich set of basic commands that can create some incredibly
powerful procedures.
CHAPTER 9 ■ STORED PROCEDURES 353
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 353
Stored procedures are processed by the MySQL server, and they are independent of the
storage engine used to store your data. If you use a feature of a particular storage engine in
your stored procedure statement, you will need to continue to use that table type to use the
stored procedure. MySQL stores the data for stored procedures in the proc table in the mysql
database. Even though procedures are all stored in one place, they are created and called by
either using the current database or by prepending a database name onto the various proce-
dure statements.
In the rest of this chapter, we’ll cover how to create, manage, and call MySQL stored
procedures.
Building Stored Procedures
SQL:2003 sets forth a set of commands to create procedures; declare variables, handlers, and
conditions; and set up cursors and constructs for flow control.
In its simplest form, you can create a stored procedure with a CREATE statement, procedure
name, and a single SQL statement. Listing 9-1 shows just how simple this can be.
Listing 9-1. Creating a Single-Statement Procedure
mysql> create procedure get_customers ()
SELECT customer_id,name FROM customer;
■Caution The stored procedure shown in Listing 9-1 has a SELECT statement as the last thing processed
in the procedure, which returns a resultset to the caller.This is really convenient, but it is a MySQL extension
to the SQL:2003 standard. The standard says you must put results into a variable or use a cursor to process
a set of results.
However frivolous Listing 9-1 may appear, it contains the required parts: a CREATE state-
ment with a procedure name and a SQL statement. Calling the stored procedure to get the
results is simple, as demonstrated in Listing 9-2.
Listing 9-2. Calling a Single-Statement Procedure

mysql> call get_customers ();
+ + +
| customer_id | name |
+ + +
| 1 | Mike |
| 2 | Jay |
| 3 | Johanna |
| 4 | Michael |
| 5 | Heidi |
| 6 | Ezra |
+ + +
6 rows in set (0.00 sec)
CHAPTER 9 ■ STORED PROCEDURES354
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 354
Other than abstracting the syntax of the query from the caller, this example doesn’t really
justify creating a procedure. The same result is just as easily available with a single query from
your application.
As a more realistic example, let’s consider the scenario of merging duplicate accounts in
your online ordering system. Your online store allows a user to create an account, with a user-
defined login and password, to use for placing orders. Suppose user Mike places an order or
two, and then doesn’t visit your site for a while. Then he returns and signs up again, inadver-
tently creating a second account. He places a few more orders. At some point, he realizes that
he has two accounts and puts in a request to have the old account removed. He says that he
would prefer to keep all the old orders on the newer account.
This means that in your database, you’ll need to find all the information associated with
the old account, move it into the new account, and delete the old account. The new account
record probably has core pieces of information like name, address, and phone, which won’t
need to change. The data to be moved may include address book and payment information,
as well as Mike’s orders. Anywhere in your system where a table has a relationship with your
customer, you’ll need to make a change. Of course, you should check for the existence of the

accounts, and the employee who makes that change may want to have a report of how many
records were changed.
Creating the series of statements to process this data merge in your code is possible, but
using a procedure to handle it would simplify your application. Listing 9-3 demonstrates how
a stored procedure might solve the requirements of this merge account request.
Listing 9-3. Creating a Multistatement Stored Procedure
DELIMITER //
CREATE PROCEDURE merge_customers
(IN old_id INT, IN new_id INT, OUT error VARCHAR(100))
SQL SECURITY DEFINER
COMMENT 'merge customer accounts'
BEGIN
DECLARE old_count INT DEFAULT 0;
DECLARE new_count INT DEFAULT 0;
DECLARE addresses_changed INT DEFAULT 0;
DECLARE payments_changed INT DEFAULT 0;
DECLARE orders_changed INT DEFAULT 0;
## check to make sure the old_id and new_id exists
SELECT count(*) INTO old_count FROM customer WHERE customer_id = old_id;
SELECT count(*) INTO new_count FROM customer WHERE customer_id = new_id;
IF !old_count THEN
SET error = 'old id does not exist';
ELSEIF !new_count THEN
SET error = 'new id does not exist';
ELSE
UPDATE address SET customer_id = new_id WHERE customer_id = old_id;
SELECT row_count() INTO addresses_changed;
CHAPTER 9 ■ STORED PROCEDURES 355
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 355
UPDATE payment SET customer_id = new_id WHERE customer_id = old_id;

SELECT row_count() INTO payments_changed;
UPDATE cust_order SET customer_id = new_id WHERE customer_id = old_id;
SELECT row_count() INTO orders_changed;
DELETE FROM customer WHERE customer_id = old_id;
SELECT addresses_changed,payments_changed,orders_changed;
END IF;
END
//
DELIMITER ;
When entering multiple statement blocks into MySQL, you need to first change the
default delimiter to something other than a semicolon (;), so MySQL will allow you to enter
a ; without having the client process the input. Listing 9-3 begins by using the delimiter
statement: DELIMITER //, which changes the delimiter to //. When you’re ready to have your
procedure created, type //, and the client will process your entire procedure. When you’re
finished working on your procedures, change the delimiter back to the standard semicolon
with: DELIMITER ;, as you can see at the end of Listing 9-3. We’ll explain the other parts of this
listing in detail shortly.
Listing 9-4 shows how to call this procedure with the required parameters and get the
results from the procedure. We’ll look at the details of executing stored procedures in the
“Using Stored Procedures” section later in this chapter.
Listing 9-4. Calling the Stored Procedure
mysql> call merge_customers (1,4,@error);
+ + + +
| addresses_changed | payments_changed | orders_changed |
+ + + +
| 2 | 2 | 2 |
+ + + +
1 row in set (0.23 sec)
Now, let’s step through each part of the stored procedure to see how it’s constructed and
what options are available.

CHAPTER 9 ■ STORED PROCEDURES356
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 356
The CREATE Statement
You create a stored procedure using the CREATE statement, which takes a procedure name, fol-
lowed by parameters in parentheses, followed by procedure characteristics, and ending with
the series of statements to be run when the procedure is called. Here is the syntax:
mysql> CREATE PROCEDURE [database.]<name> ([<parameters>]) [<characteristics>]
<body statements>
The name may be prefixed with a database name, and it must be followed by parentheses.
If the database is not provided, MySQL creates the procedure in the current database or gives
a No database selected error if a database is not active. Procedure names can be up to 64
characters long.
■Caution Avoid conflicts with built-in functions by not using built-in function names for your procedure.
If you must have a procedure with the same name as a MySQL function, putting a space between the name
and the parentheses will help MySQL differentiate between the two. For example, a build in function for get-
ting all uppercase text is
upper().We suggest you don’t, but if you must create a stored procedure with the
same name, use
upper () (note the space between the name and the opening parenthesis) to distinguish it
from the built-in function.
You can set parameters for a stored procedure using the following syntax:
[IN|OUT|INOUT] <name> <data type>
If you don’t specify IN, OUT, or INOUT for the parameter, it will default to IN. These three
types of parameters work as follows:
• An IN parameter is set and passed into the stored procedure to use internally in its
processing.
• An OUT parameter is set within the procedure, but accessed by the caller.
• An INOUT parameter is passed into the procedure for internal use, but is also available to
the caller after the procedure has completed.
The name and data type of the parameter are used in the stored procedure for referencing

and setting values going in and out of the procedure. The data type can be any valid data type
for MySQL, and it specifies what type of data will be stored in the parameter. You’ll see a detailed
example of passing arguments in and out of a procedure in the “Using Stored Procedures” sec-
tion (Listings 9-13 and 9-16) later in this chapter.
CHAPTER 9 ■ STORED PROCEDURES 357
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 357
The stored procedure characteristics include a number of options for how the stored pro-
cedure behaves. Table 9-1 lists the available options with a description of how they affect the
stored procedure.
Table 9-1. Characteristics Used to Create a Stored Procedure
Characteristic Value Description
LANGUAGE SQL This is the language that was used to write the stored
procedure. While MySQL intends to implement other
languages with external procedures, currently SQL is
the only valid option.
SQL SECURITY DEFINER or INVOKER The SQL SECURITY characteristic tells MySQL which user
to use for permissions when running the procedure. If
it’s set to
DEFINER, the stored procedure will be run using
the privileges of the user who created the procedure. If
INVOKER is specified, the user calling the procedure will
be used for obtaining access to the tables. The default, if
not specified, is
DEFINER.
COMMENT The COMMENT characteristic is a place to enter notes
about a stored procedure. The comment is displayed
in SHOW CREATE PROCEDURE commands.
■Caution The COMMENT characteristic is an extension to SQL:2003, which means that procedures with a
comment in the definition may not easily move to another SQL:2003-compliant database.
The Procedure Body

The body of a stored procedure contains the collection of SQL statements that make up the
actual procedure. In addition to the typical SQL statements you use to interact with data in
your database, the SQL:2003 specification includes a number of additional commands to store
variables, make decisions, and loop over sets of records.
■Note MySQL allows you to put Data Definition Language (DDL) statements (CREATE, ALTER, and so on) in
the body of a stored procedure. This is part of the SQL:2003 standard, but it is labeled as an optional feature
and may not be supported in other databases that comply with the standard.
CHAPTER 9 ■ STORED PROCEDURES358
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 358
BEGIN and END Statements
You use the BEGIN and END statements to group statements in procedures with more than one
SQL statement. Declarations can be made only within a BEGIN . . . END block.
You can define a label for the block to clarify your code, as shown here:
customer: BEGIN
<SQL statement>;
<SQL statement>;
END customer
The labels must match exactly.
The DECLARE Statement
The DECLARE statement is used to create local variables, conditions, handlers, and cursors within
the procedure. You can use DECLARE only as the first statements immediately within a BEGIN
block. The declarations must occur with variables first, cursors second, and handlers last.
A common declaration is the local variable, which is done with a variable name and type:
DECLARE <name> <data type> [DEFAULT];
Variable declarations can use any valid data type for MySQL, and may include an optional
default value. In Listing 9-3, several declarations are made, including a number of variables for
counting items as the statements in the procedure are processed:
DECLARE new_count INT DEFAULT 0;
Here, we’ll look at how to declare variables, conditions, and handlers. Cursors are covered
in more detail in Chapter 11.

Variables
Stored procedures can access and set local, session, and global variables. Local variables are
either passed in as parameters or created using the DECLARE statement, and they are used in
the stored procedure by referencing the name of the parameter or declared variable.
You can set variables in several ways. Using the DECLARE statement with a DEFAULT will set
the value of a local variable:
DECLARE customer_count INT DEFAULT 0;
You can assign values to local, session, and global variables using the SET statement:
SET customer_count = 5;
MySQL’s SET statement includes an extension to the SQL:2003 standard that permits
setting multiple variables in one statement:
SET customer_count = 5, order_count = 50;
CHAPTER 9 ■ STORED PROCEDURES 359
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 359
■Caution Setting multiple variables in a single statement is a MySQL extension to the SQL:2003 standard.
Using this syntax may make your procedures less portable.
Using SELECT . . . INTO is another method for setting variables within your stored pro-
cedure. This allows you to query a table and push the results into a variable as a part of the
query. SELECT . . . INTO works only if you are selecting a single row of data:
SELECT COUNT(*) INTO customer_count FROM customer;
You can also select multiple values into multiple variables:
SELECT customer_id,name INTO new_id,new_name FROM customer LIMIT 1;
■Caution Use caution when creating variables in stored procedures. If variable names are the same as
field names in a table, you may encounter unexpected results. You might want to define a naming convention
for all variables in stored procedures to avoid conflicts with other items in the namespace.
Conditions and Handlers
When making declarations in your stored procedure, your list of declarations can include
statements to indicate special handling when certain conditions arise. When you have a col-
lection of statements being processed, being able to detect the outcome of those statements
and proactively do something to help the procedure be successful can be important to your

caller.
Suppose one of the stored procedures created for your online store included a statement
to update the customer’s name. The column for the customer’s name is CHAR(10), which is
smaller than you would like, but is the most your legacy order-entry system can handle. The
normal behavior for MySQL when updating a record is to truncate the inserted value to a
length that fits the column. For numerous reasons, this is unacceptable to you. Fortunately,
when MySQL does a truncation, it issues a warning and returns an error, and also sets the
SQLSTATE to indicate that during the query, the data was truncated.
■Note More than 2,000 error numbers can be raised as errors or warnings from MySQL. Each MySQL
error number has a message and a corresponding
SQLSTATE value. For the details of each error number and
its meaning, see />Handlers are designed to detect when certain errors or warnings have been triggered by
statements and allow you to take action. A handler is declared with a handler type, condition,
and statement:
DECLARE <handler type> HANDLER FOR <condition> <statement>;
CHAPTER 9 ■ STORED PROCEDURES360
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 360
Handler Types
The handler type is either CONTINUE or EXIT.
3
CONTINUE means that when a certain error or
warning is issued, MySQL will run the provided statement and continue running the state-
ments in the procedure. The EXIT handler type tells MySQL that when the condition is met,
it should run the statement and exit the current BEGIN . . . END block.
Here’s a handler statement with an EXIT handler type:
DECLARE EXIT HANDLER FOR truncated_name
UPDATE customer SET name = old_name WHERE customer_id = cust_id;
In this statement, the EXIT handler type tells the procedure to execute the statement, and then
exit when a truncation occurs.
Conditions

The handler condition is what triggers the handler to act. You can define your own conditions
and reference them by name, or choose from a set of conditions that are provided by default
in MySQL. Table 9-2 shows the MySQL handler conditions.
Table 9-2. MySQL Handler Conditions
Condition Description
SQLSTATE '<number>' A specific warning or error number, which is described in the
MySQL documentation. The number must be enclosed in
quotes (typically single).
<self-defined condition name> The name of the self-defined condition you created using the
DECLARE . . . CONDITION statement.
SQLWARNING Matches any SQLSTATE that begins with 01. Using this
condition will allow you to catch a wide range of states.
NOT FOUND Matches any SQLSTATE beginning with 02. Using this state lets
you catch any instance where the query references a missing
table, database, and so on.
SQLEXCEPTION Matches every SQLSTATE except those beginning with 01 or 02.
<MySQL error> Using a specific error will cause the handler to execute for the
specific MySQL error.
■Tip Creating self-defined conditions improves readability of your code. Rather than using the MySQL error
or
SQLSTATE number, you are assigning a name to that state, which will be more understandable than just
having the number.
CHAPTER 9 ■ STORED PROCEDURES 361
3. The UNDO handler type, which is part of the SQL:2003 specification, is not currently supported in
MySQL.
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 361
To create a self-defined condition, use a condition declaration with a name and a value:
DECLARE <condition name> CONDITION FOR <condition value>;
The condition name will be used in a DECLARE . . . HANDLER definition. The condition value can
be either a MySQL error number or a SQLSTATE code. For example, to catch when some data has

been truncated, the condition declaration with the MySQL error number looks like this:
DECLARE truncated_name CONDITION FOR 1265;
Or if you wanted to use the SQLSTATE number, you would write the same statement like
this:
DECLARE truncated_name CONDITION FOR SQLSTATE '01000';
■Caution A single SQLSTATE value can be assigned to multiple MySQL error numbers, meaning that if
you use the
SQLSTATE numbers, you may have different errors that generate the same SQLSTATE. This can
help or hinder the effectiveness of your handler. In some cases, you want to match all occurrences of a cer-
tain type of error, which are grouped under a certain
SQLSTATE. In the example, we want to find a very
specific error, so it makes more sense to use the MySQL error code.
Statements
The last piece of the handler declaration is a statement, which will be run before the stored
procedure either continues or exits, depending on the handler type you chose. For example, to
catch a case where the name had been truncated, your stored procedure might look like the
one shown in Listing 9-5.
Listing 9-5. Declaring a Condition and Handler
DELIMITER //
CREATE PROCEDURE update_name (IN cust_id INT, IN new_name VARCHAR(20))
BEGIN
DECLARE old_name VARCHAR(10);
DECLARE truncated_name CONDITION for 1265;
DECLARE EXIT HANDLER FOR truncated_name
UPDATE customer SET name = old_name WHERE customer_id = cust_id;
SELECT name INTO old_name FROM customer WHERE customer_id = cust_id;
UPDATE customer SET name = new_name WHERE customer_id = cust_id;
SELECT customer_id,name FROM customer WHERE customer_id = cust_id;
END
//

DELIMITER ;
The update_name procedure accepts a customer ID (cust_id) and a new name (new_name).
The first two statements declare a variable to store the old name and a condition named
CHAPTER 9 ■ STORED PROCEDURES362
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 362
truncated_name, which specifies MySQL error 1265 as the condition. MySQL error 1265 indi-
cates that a field in the statement was truncated when the statement was processed. The third
declaration is a handler statement that tells the procedure that if the truncated_name state is
reached, to update the customer record to the old name and exit.
The stored procedure runs the declarations first, and then selects the current name for
that customer into the old_name variable. On the following UPDATE statement, depending on
the length of the name to be inserted, the query result may be a MySQL error 1265. If so, the
handler for truncated_name runs the statement associated with the handler:
UPDATE customer SET name = old_name WHERE customer_id = cust_id;
This query sets the name back to the original value. The procedure then exits, and no
record is returned to the client.
■Note The handler example demonstration here is really just an elaborate rollback mechanism. The
SQL:2003 standard contains specifications for an
UNDO handler type, which would roll back the transaction
block if a particular condition is met. MySQL doesn’t currently support the
UNDO handler type, but promises it
is coming.
Flow Controls
SQL:2003 flow constructs give you a number of statements to control and organize your state-
ment processing. MySQL supports IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, and WHILE, but does
not currently support the FOR statement.
IF
The IF statement behaves as you would expect if you’ve written code in another language. It
checks a condition, running the statements in the block if the condition is true. You can add
ELSEIF statements to continue attempting to match conditions and also, if desired, include a

final ELSE statement.
Listing 9-6 shows a piece of a procedure where the shipping cost is being calculated based
on the number of days the customer is willing to wait for delivery. delivery_day is an integer
parameter passed into the procedure.
Listing 9-6. IF Statement
IF delivery_day = 1 THEN
SET shipping = 20;
ELSEIF delivery_day = 2 THEN
SET shipping = 15;
ELSEIF delivery_day = 3 THEN
SET shipping = 10;
ELSE
SET shipping = 5;
END IF;
CHAPTER 9 ■ STORED PROCEDURES 363
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 363
CASE
If you’re checking a uniform condition, such a continual check for number of shipping days,
you might be better off using the CASE construct. Listing 9-7 shows how this same logic
demonstrated in Listing 9-6 could be processed using the CASE statement. Not only does it
seem to improve the readability of the code, but the code in Listing 9-7 runs at least twice as
fast as the code in Listing 9-6. delivery_day is an integer parameter passed into the procedure.
Listing 9-7. CASE Statement
CASE delivery_day
WHEN 1 THEN
SET shipping = 20;
WHEN 2 THEN
SET shipping = 15;
WHEN 3 THEN
SET shipping = 10;

ELSE
SET shipping = 5;
END case;
The CASE control can also operate without an initial case value, evaluating a condition on
each WHEN block. Listing 9-8 shows the shipping calculator using this syntax. As with Listing 9-7,
Listing 9-8 runs significantly faster than the IF-based logic in Listing 9-6.
Listing 9-8. CASE Statement with Condition Checks
CASE
WHEN delivery_day = 1 THEN
SET shipping = 20;
WHEN delivery_day = 2 THEN
SET shipping = 15;
WHEN delivery_day = 3 THEN
SET shipping = 10;
ELSE
SET shipping = 5;
END CASE;
Now that you are up to speed with checking values, we’ll turn our attention to the con-
structs for repeating. The LOOP, LEAVE, ITERATE, REPEAT, and WHILE statements provide methods
to work through a given number of conditions.
LOOP and LEAVE
The LOOP statement creates an ongoing loop that will run until the LEAVE statement is invoked.
Optional to the LOOP is a label, which is a name and a colon prefixed to the LOOP statement,
with the identical name appended to the END LOOP statement. Listing 9-9 demonstrates a LOOP
and LEAVE construct.
CHAPTER 9 ■ STORED PROCEDURES364
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 364
Listing 9-9. LOOP Statement with LEAVE
increment: LOOP
SET count = count + 1;

IF count > in_count THEN LEAVE increment;
END IF;
END LOOP increment;
The LEAVE statement is designed to exit from any flow control. The LEAVE statement must
be accompanied by a label.
ITERATE
You can use ITERATE in a LOOP, WHILE, or REPEAT control to indicate that the control should
iterate through the statements in the loop again. Listing 9-10 shows ITERATE added to the
increment example in Listing 9-9. Adding the IF condition to check if the count is less than 20,
and if so iterating, means that the value of count, when the loop is complete, will never be less
than 20, because the ITERATE statement ensures that the addition statement is run repeatedly
until the count reaches 20.
Listing 9-10. Loop with ITERATE Statement
DELIMITER //
CREATE PROCEDURE increment (IN in_count INT)
BEGIN
DECLARE count INT default 0;
increment: LOOP
SET count = count + 1;
IF count < 20 THEN ITERATE increment; END IF;
IF count > in_count THEN LEAVE increment;
END IF;
END LOOP increment;
SELECT count;
END
//
DELIMITER ;
WHILE
The WHILE statement is another mechanism to loop over a set of statements until a condition
is true. Unlike LOOP, where the condition is met within the loop, the WHILE statement requires

specification of the condition when defining the statement. As with loops, you can add a
name to give a name to the WHILE construct. Listing 9-11 shows a simple use of this statement.
Listing 9-11. WHILE Statement
WHILE count < 10 DO
SET count = count + 1;
END WHILE;
CHAPTER 9 ■ STORED PROCEDURES 365
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 365
REPEAT
To loop over a set of statements until a post-statement condition is met, use the REPEAT state-
ment. Listing 9-12 shows a simple use. The check_count label is optional, as is the label with
other constructs.
Listing 9-12. REPEAT Statement
check_count: REPEAT
SET count = count + 1;
UNTIL count > 10
END REPEAT check_count;
Using Stored Procedures
If you’ve gone to all the trouble of creating a procedure, you probably want to put it to use.
You may be calling the procedures directly from the MySQL command-line client or from a
program written in PHP, Java, Perl, Python, or another language. Here, we’ll look at how to call
procedures from the command line and from PHP, just to demonstrate calling procedures from
a program. Check the documentation for the specific language you’re using to see which driv-
ers are needed and how the interface for procedures and parameters work in that language.
Calling Procedures from the MySQL Client
From the MySQL client, you use the CALL statement to execute a procedure, providing the
procedure name and correct number of arguments.
CALL [database.]<procedure name> ([<parameter>, <parameter>, …]);
Calling a simple procedure without any parameters is fairly straightforward, as you saw
earlier in the chapter, when we demonstrated calling the get_customer procedure (Listing 9-2).

Listing 9-13 shows an example of calling a stored procedure that requires three arguments:
an old customer ID as the first IN argument, a new customer ID as the second IN argument, and
an OUT argument used in the procedure for setting an error message. Once the stored procedure
has been executed, the @error variable contains a string set inside the stored procedure.
Listing 9-13. Calling a Stored Procedure with IN and OUT Parameters
mysql> CALL merge_customers (8,9,@error);
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT @error;
+ +
| @error |
+ +
| old id does not exist |
+ +
1 row in set (0.30 sec)
CHAPTER 9 ■ STORED PROCEDURES366
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 366
If you call a procedure with the wrong number of arguments, MySQL gives an error:
mysql> CALL shop.merge_customers (1,2);
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE merge_customers; \
expected 3, got 2
Calling Procedures from PHP
In PHP, stored procedures must be called using PHP’s mysqli extensions. This requires your
PHP code to be compiled with the with-mysqli option. Listing 9-14 shows how you would
call the get_customers procedure from PHP and report the results.
Listing 9-14. Calling a Stored Procedure from PHP
<?
$mysqli = mysqli_connect("localhost","mkruck","ProMySQL","shop");
if (mysqli_connect_errno()) {
printf("Failed to connect: %s\n", mysqli_connect_error());
exit();

}
if ($result = $mysqli->query("CALL get_customers ()")) {
printf("%d records found\n",$result->num_rows);
while ($row = $result->fetch_row()) {
printf("%d - %s\n",$row[0],$row[1]);
}
}
else {
echo $mysqli->error,"\n";
}
$mysqli->close();
?>
This script makes a connection to the database (checking for failure), calls the stored pro-
cedure, and then prints the number of rows that were returned along with a line for each piece
of the data. If the CALL statement fails, the error is printed.
Running the PHP script in Listing 9-14 generates the output shown in Listing 9-15.
Listing 9-15. Output from a Stored Procedure Called in PHP
6 records found
1 - Mike
2 - Jay
3 - Johanna
4 - Michael
5 - Heidi
6 - Ezra
The output from Listing 9-15 shows that six records were returned from the get_customers
procedure.
CHAPTER 9 ■ STORED PROCEDURES 367
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 367
To merge two of these customers by calling the merge_customers procedure (created in
Listing 9-3) adds a little more complexity because you must pass IN and OUT parameters to the

procedure. A simple way to do this is shown in Listing 9-16.
Listing 9-16. Calling a Stored Procedure with Parameters from PHP
<?
$mysqli = mysqli_connect("localhost","mkruck","ProMySQL","shop");
if (mysqli_connect_errno()) {
printf("Failed to connect: %s\n", mysqli_connect_error());
exit();
}
$old_customer = 1;
$new_customer = 4;
$mysqli->query("CALL merge_customers ($old_customer,$new_customer,@error)");
$result = $mysqli->query("SELECT @error");
if ($result->num_rows) {
while ($row = $result->fetch_row()) {
printf("%s\n",$row[0]);
}
}
else {
print "Customer merge successful";
}
$mysqli->close();
?>
This PHP script will print a success message if the call to the procedure didn’t set the
@error:
Customer merge successful
But if the procedure encountered a problem, such as that one of the records couldn’t be
found, and sets the @error variable with an error message, the PHP script will print that error.
Running the PHP script again, after customer records 1 and 4 have already been merged,
results in the PHP script printing the error message from the procedure:
old id does not exist

■Tip The mysqli extension allows significantly more complex database interaction, such as creating
prepared statements, binding parameters, and so on. For more information, see the PHP documentation at
/>CHAPTER 9 ■ STORED PROCEDURES368
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 368
Managing Stored Procedures
Most of the work in an environment where stored procedures are used is in creating the stored
procedure. However, at some point, you will need to manage the procedures in your database.
MySQL provides a set of commands for this purpose.
Viewing Stored Procedures
You have several options when viewing information about stored procedures. To get a summary
of the procedures across all databases in your system, use SHOW PROCEDURE STATUS, which will give
you a summary of information about all the stored procedures in your system. Listing 9-17 shows
the output for three procedures used for listings in this chapter. Using the \G option outputs in
rows instead of columns.
Listing 9-17. Output of SHOW PROCEDURE STATUS
mysql> SHOW PROCEDURE STATUS\G
*************************** 1. row ***************************
Db: shop
Name: get_customers
Type: PROCEDURE
Definer: mkruck01@localhost
Modified: 2005-01-10 23:23:20
Created: 2005-01-10 23:23:20
Security_type: DEFINER
Comment:
*************************** 2. row ***************************
Db: shop
Name: get_shipping_cost
Type: PROCEDURE
Definer: mkruck01@localhost

Modified: 2005-01-10 22:45:57
Created: 2005-01-10 22:45:57
Security_type: DEFINER
Comment:
*************************** 3. row ***************************
Db: shop
Name: merge_customers
Type: PROCEDURE
Definer: mkruck01@localhost
Modified: 2005-01-10 23:23:20
Created: 2005-01-10 23:23:20
Security_type: DEFINER
Comment: get rid of unnecessary data
This command can be limited by appending a LIKE clause, in this case limiting the output
to just returning the merge_customer procedure.
mysql> SHOW PROCEDURE STATUS LIKE 'merge%'\G
CHAPTER 9 ■ STORED PROCEDURES 369
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 369
The SHOW PROCEDURE STATUS statement gives you a nice summary view of all the procedures
in the databases on your machine. To get more details on a stored procedure, use the
SHOW CREATE PROCEDURE statement:
SHOW CREATE PROCEDURE [<database>.]<procedure name>;
This statement shows you the name and the CREATE statement. Listing 9-18 shows an example
of the output for the get_shipping_cost procedure.
Listing 9-18. Output of SHOW CREATE PROCEDURE
mysql> SHOW CREATE PROCEDURE shop.get_shipping_cost\G
*************************** 1. row ***************************
Procedure: get_shipping_cost
sql_mode:
Create Procedure: CREATE PROCEDURE `shop`.`get_shipping_cost`(IN delivery_day INT)

COMMENT 'determine shipping cost based on day of delivery'
BEGIN
declare shipping INT;
case delivery_day
when 1 then set shipping = 20;
when 2 then set shipping = 15;
when 3 then set shipping = 10;
else set shipping = 5;
end case;
select shipping;
END
1 row in set (0.12 sec)
Neither of the views we’ve discussed thus far shows you everything there is to know about
a procedure. The summary provides only a few pieces of summary information, and SHOW
➥
CREATE PROCEDURE shows the name, along with the body as a large, unreadable CREATE statement.
If you have SELECT access on the proc table in the mysql database, a SELECT statement will show
you everything there is to know about all procedures or a particular procedure. Listing 9-19
shows the output from a SELECT of the get_shipping_cost procedure, which shows the proce-
dure’s database, name, language, security type, parameter list, body, definer, comment, and
other information.
Listing 9-19. Output of SELECT from the mysql.proc Table
mysql> SELECT * FROM mysql.proc WHERE name = 'get_shipping_cost'\G
*************************** 1. row ***************************
db: shop
name: get_shipping_cost
type: PROCEDURE
specific_name: get_shipping_cost
language: SQL
sql_data_access: CONTAINS_SQL

is_deterministic: NO
CHAPTER 9 ■ STORED PROCEDURES370
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 370
security_type: DEFINER
param_list: IN delivery_day INT
returns:
body: BEGIN
declare shipping INT;
case delivery_day
when 1 then set shipping = 20;
when 2 then set shipping = 15;
when 3 then set shipping = 10;
else set shipping = 5;
end case;
select shipping;
END
definer: mkruck01@localhost
created: 2005-01-11 00:01:47
modified: 2005-01-11 00:01:47
sql_mode:
comment: determine shipping cost based on day of delivery
1 row in set (0.12 sec)
As you can see, if you want to view everything there is to know about a procedure, the
direct SELECT on the mysql.proc table will provide the most information.
Altering and Removing Stored Procedures
The ALTER statement lets you change the characteristics of a stored procedure. It has the fol-
lowing syntax:
ALTER PROCEDURE [<database>.]<procedure name> <characteristics>
The ALTER statement can change any of the characteristics used to create the procedure,
as shown earlier in Table 9-1. For example, to change the SQL SECURITY and COMMENT on the

get_customers procedure, you would use the following ALTER statement:
mysql> ALTER PROCEDURE get_customers SQL SECURITY INVOKER
COMMENT 'show all customers';
To remove a stored procedures, use the DROP statement, which has the following syntax:
DROP PROCEDURE [database.]<procedure name>
Editing Stored Procedures
Editing stored procedures doesn’t happen interactively with the database, as with the SHOW,
ALTER, or DROP statements. The process of editing a stored procedure means opening it in an
editor, making the necessary changes, and replacing the existing procedure in the database
with the new one using a DROP and then a CREATE statement.
Choosing an environment for editing stored procedures is similar to finding one for any
kind of programming. If you prefer to work in a text editor like Emacs, vi, or Notepad, you’ll
probably be most comfortable doing the same when working on your procedures. A GUI tool
will make more sense if that’s where you find most of your other development happens.
CHAPTER 9 ■ STORED PROCEDURES 371
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 371
Regardless of your tool for editing stored procedures, you should use a versioning system
like Subversion or CVS to store and keep track of changes in your stored procedures. Stored
procedures should be treated like any other piece of code in this respect—you’ve spent time
developing it and should take measures to protect your time and effort.
■Tip If you prefer working in a GUI, you might try MySQL Query Browser, a GUI tool for Windows and Linux
that has en excellent interface for editing procedures. The tool will allow you to update the existing proce-
dure with a
DROP and CREATE from a button on the interface. More information on the freely available
MySQL Query Browser is available at />Stored Procedure Permissions
For permissions to create and call stored procedures, MySQL relies on the existing permissions
scheme, which is covered in Chapter 15. Specific to procedures, the MySQL permissions scheme
has the CREATE ROUTINE, ALTER ROUTINE, and EXECUTE privilege.
The permissions required for working with stored procedures are as follows:
Viewing permissions: To view stored procedures with SHOW PROCEDURE STATUS, you must

have SELECT access to the mysql.proc table. To be able to use the SHOW CREATE PROCEDURE,
you must have either SELECT access to the mysql.proc table or the ALTER ROUTINE privilege
for that particular procedure. Both SHOW PROCEDURE STATUS and SHOW CREATE PROCEDURE
were covered earlier in this chapter.
Calling permissions: To call a stored procedure, you need the ability to connect to the
server and have the EXECUTE permission for the procedure. EXECUTE permissions can be
granted globally (in the mysql.user table), at the database level (in the mysql.db table),
or for a specific routine (in the mysql.procs_priv table).
Creating and altering permissions: To govern creating and altering a stored procedure,
MySQL uses the CREATE ROUTINE and ALTER ROUTINE privilege. As with the EXECUTE privi-
lege, permissions for creating or changing procedures can be granted globally (in the
mysql.user table), at the database level (in the mysql.db table), or for a specific routine
(in the mysql.procs_priv table).
Dropping permissions: To drop a procedure, you must have the ALTER ROUTINE privilege.
Permissions for dropping procedures can be granted globally (in the mysql.user table),
at the database level (in the mysql.db table), or for a specific routine (in the mysql.
procs_priv table).
The success of a stored procedure call is also affected by the procedure’s SQL SECURITY
characteristic. If set to DEFINER, the procedure will be run with the permissions of the user
who created the procedure. Procedures will be run as the calling user if SQL SECURITY is set
to INVOKER. In either case, the INVOKER or DEFINER must have appropriate access to the tables
used in the stored procedure or calling the procedure will result in a permission error.
CHAPTER 9 ■ STORED PROCEDURES372
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 372
Having the option to run procedures with the permissions of the creator means that you
can create a set of procedures by a user with access to all of the tables, and allow a user who
has no permissions in the tables but does have the ability to connect to the server and execute
the procedure, to run it. This can be a simple, but excellent, way to simplify and enforce secu-
rity in your database.
WHAT’S MISSING IN MYSQL STORED PROCEDURES?

The MySQL AB developers continue to develop stored procedure features in MySQL. As of version 5.0.6, a
few documented statements are still missing from the syntax:
• SIGNAL: Used in a handler to return a SQLSTATE and message text.
• RESIGNAL: Allows you to indicate that a handler should send a SQLSTATE other than the one originally
caught.
• UNDO: Used in defining a handler. This handler type specifies that if a certain condition is reached, the
database should undo the statements previously run within the BEGIN . . . END block.
• FOR: Used to loop over a set of instructions a given number of times.
Summary
Stored procedures in MySQL are a welcome and exciting addition to the 5.0 release. While
there’s a lot of power, and perhaps some efficiency, in moving logic into your database, it’s
important to consider if and how procedures fit into your existing application. Hasty decisions
based on excitement to use cool technology usually lead to problems down the road.
As mentioned in the chapter, users should exercise caution in adopting the stored
procedure functionality until the stability of the 5.0 server matches their environment
requirements. For most users, waiting for the stable release is probably the best choice.
MySQL’s choice of SQL:2003 provides a good set of statements for developing procedures
and a standard for potential inter-database procedure exchange. MySQL provides a good set
of tools for creating, altering, dropping, and viewing procedures.
As MySQL developers continue to develop and flush out their implementation of stored
procedures, we look forward to further developments of the stored procedure functionality
and anxiously await the stable release of the 5.0 branch of MySQL.
In the next chapter, we’ll look at stored functions, another technology available in MySQL
versions 5.0 and later.
CHAPTER 9 ■ STORED PROCEDURES 373
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 373
505x_Ch09_FINAL.qxd 6/27/05 3:30 PM Page 374
Functions
More than likely, you’re already familiar with database functions. If you haven’t defined
them yourself in a database that allowed user-defined functions, you’ve probably used one

or more of the functions built into your database. If you’ve ever stuck a LENGTH() function in
a query to find the number of bytes in a string, or used an UPPER() function to make the data
you select return in all uppercase letters, you’re familiar with at least the use of functions.
You may have encountered situations where, within a query, you’ve wanted to use one
of the built-in database functions to perform some simple calculation or manipulation on
a piece of data, but found that the database didn’t offer a function suitable to your needs. Or
maybe the database had a function that looked like it would work, but when you tried it, that
function didn’t give you the kind of results you wanted.
Stored functions, available in MySQL versions 5.0 and later, are the solution many need
for encapsulating pieces of logic. In this chapter, we’ll cover the following topics related to
stored functions:
•Uses of database functions
•Database functions compared with other database tools
•MySQL’s implementation of stored functions
• How to create stored functions
• An example of using functions
• How to view, change, and remove stored functions
•Stored function permissions
•Benchmarks to determine the overhead in using functions
375
CHAPTER 10
■ ■ ■
505x_Ch10_FINAL.qxd 6/27/05 3:31 PM Page 375
Database Function Uses
To illustrate the usefulness of stored functions, let’s look at how they might offer a solution
for a problem in the online store application we’ve used in previous chapters. Part of your
system is a table full of customer records. The customer records are used on your web site to
customize the users’ pages by displaying their name when they are at the site. In addition, the
data is used for mailing periodic promotional flyers and for invoicing wholesale customers.
Users create their own accounts on the site, and they can update their contact information

if it changes. The self-service account management leads to variations in the format of the
records. Many users use mixed uppercase and lowercase characters, but some users enter
data in all uppercase or all lowercase.
For your web site and mailings, you’re particularly interested in having the customer’s
first and last name look professional, with the first letter uppercase and the remainder lower-
case. To ensure the name is formatted correctly, you want to handle this as part of the queries
that pull data from the database, as opposed to in the code for the site or mailing list. MySQL
has built-in UPPER() and LOWER() functions, but a thorough review of the string functions
reveals nothing that will achieve the formatting you need. What you need is a function that
will take a string and return the string with the first character converted to uppercase and the
remainder in lowercase.
Stored functions to the rescue. MySQL versions 5.0 and later offer a means for defining
functions to be used in standard SQL statements for performing an endless number of tasks,
including calculations, data validation, data formatting, and data manipulation.
■Note As we write this chapter, MySQL has released version 5.0.6, which is labeled a beta release. While
the database is stable enough to test and document the functionality of stored functions, production users
are encouraged to wait until a release of the 5.0.x branch that is labeled production.
Database functions are a method for encapsulating logic that can be performed with any
number of input arguments, and require that one, and only one, value be returned. Database
functions are called within SELECT, INSERT, or UPDATE statements, generating values on the fly
to be used within the query to change data being saved into a table or returned in a set of
results. A function always returns a single, predefined type set in the definition of the function.
Examining MySQL’s built-in functions, you might conclude that a function is intended to
perform some calculation or manipulation of one or more values to return a value for output
from a SELECT statement or storage in the database (think of the LENGTH() function). When
building your own functions, you can also use other pieces of data (like a session variable)
as a part of the SQL statements that make up the function body. For example, if you created a
ucasefirst() function to solve the problem of customer name formatting, you would use it in
a SQL statement like this:
SELECT user_id, ucasefirst(firstname), ucasefirst(lastname),

email_address FROM user;
We’ll return to this sample ucasefirst() function at the very end of this chapter, after
we’ve covered the details of creating functions.
CHAPTER 10 ■ FUNCTIONS376
505x_Ch10_FINAL.qxd 6/27/05 3:31 PM Page 376
Functions Compared with Other Database Tools
Database functions can be used in many ways, so you need to consider how to best use them
for your applications. Throughout this book, we continue to emphasize careful consideration
of the various database technologies as you design and implement your database. As you con-
sider the possible uses for functions in your application and database, you should be thinking
about how functions fit into the bigger picture and if a stored function in the database is the
best choice for the required logic. To help you figure this out, let’s take a look at how functions
compare with some other database tools for manipulating data: stored procedures, views, and
triggers.
While we can’t provide definitive answers as to where each tool fits best, we do suggest
that you think carefully about your overall database and application architecture, and keep
your use of the various database tools consistent and well documented.
Stored Functions vs. Stored Procedures
The syntax for defining the body of stored functions includes the same set of statements
defined for stored procedures, covered in Chapter 9. As we’ve discussed, MySQL’s stored
procedures provide a rich set of syntax to perform logic operations in the database. Like the
body of a procedure, the function body can include things like variables and flow constructs
to encapsulate both small and large pieces of functionality.
So why not just use stored procedures then? While you can do a lot with stored proce-
dures, they aren’t always the best fit for encapsulating pieces of logic. Furthermore, creating a
stored procedure for each piece of logic can be overkill, and needing to call and then process
the results from a procedure is sometimes more work that it’s worth if you need only a small
piece of data to use in another query.
A function can be used directly from within a SELECT, INSERT, or UPDATE statement, and
the result of that function is either saved in the table or returned with the output (depending

on whether you’re getting or saving data). Stored procedures may not return any results, or
they might return a large set of records for further processing and presentation. In contrast, a
stored function always returns a single value.
1
The required single-value return makes a func-
tion perfect for logic needed within an existing query.
In summary, the main difference between stored procedures and database functions is
the way they are called and what they return. A stored procedure is executed with an explicit
statement: the CALL command. Stored procedures don’t necessarily return any value, but can
set OUT values and can return one or more data records. A stored procedure can also execute
without returning any data to the client.
Note that the debate surrounding the use of stored procedures, discussed in Chapter 9,
also applies to using stored functions (as well as views and triggers) in the database. Many of
the arguments for and against stored procedures also pertain to using functions in your data-
base, and you should be aware of these arguments when assessing the merits of incorporating
such features into your application.
CHAPTER 10 ■ FUNCTIONS 377
1. The returned value can be NULL, if there is nothing for the function to return.
505x_Ch10_FINAL.qxd 6/27/05 3:31 PM Page 377