CHAPTER 32

Stored Routines

Throughout this book you've seen quite a few examples where the MySQL queries were embedded directly into the PHP script. Indeed, for smaller applications this is fine; however, as application complexity and size increase, continuing this practice could be the source of some grief. For instance, what if you have to deploy two similar applications, one desktop-based and the other Web-based, that use the MySQL database and perform many of the same tasks? On the occasion a query changed, you'd need to make modifications wherever that query appeared not in one application but in two!

Another challenge that arises when working with complex applications, particularly in a team environment, involves affording each member the opportunity to contribute his expertise without necessarily stepping on the toes of others. Typically the individual responsible for database development and maintenance is particularly knowledgeable in writing efficient and secure queries. But how can this individual write and maintain these queries without interfering with the application developer if the queries are embedded in the code? Furthermore, how can the database architect be confident that the developer wasn't "improving" upon the queries, potentially opening security holes in the process?

One of the most common solutions to these challenges comes in the form of a database feature known as a stored routine. A stored routine is a set of SQL statements stored in the database server and executed by calling an assigned name within a query, much like a function encapsulates a set of commands that is executed when the function name is invoked. The stored routine can then be maintained from the secure confines of the database server, without ever having to touch the application code.

As of version 5.0, MySQL supports this long-awaited feature. This chapter tells you all about how MySQL implements stored routines, both by discussing the syntax and by showing you how to create, manage, and execute stored routines. You'll also learn how to incorporate stored routines into your Web applications via PHP scripts. To begin, take a moment to review a more formal summary of their advantages and disadvantages.

Should You Use Stored Routines?

Rather than blindly jumping onto the stored routine bandwagon, it's worth taking a moment to consider their advantages and disadvantages, particularly because their utility is a hotly debated topic in the database community. This section summarizes the pros and cons of incorporating stored routines into your development strategy.

Stored Routine Advantages

Stored routines have a number of advantages, the most prominent of which are highlighted here:

• Consistency: When multiple applications written in different languages are performing the same database tasks, consolidating these like functions within stored routines decreases otherwise redundant development processes.
• Performance: A competent database administrator likely is the most knowledgeable member of the team regarding how to write optimized queries. Therefore, it may make sense to leave the creation of particularly complex database-related operations to this individual by maintaining them as stored routines.
• Security: When working in particularly sensitive environments such as finance, health care, and defense, it's sometimes mandated that access to data is severely restricted. Using stored routines is a great way to ensure that developers have access only to the information necessary to carry out their tasks.
• Architecture: Although it's out of the scope of this book to discuss the advantages of multitier architectures, using stored routines in conjunction with a data layer can further facilitate manageability of large applications. Search the Web for n-tier architecture for more information about this topic.

Stored Routine Disadvantages

Although the preceding advantages may have you convinced that stored routines are the way to go, take a moment to ponder the following drawbacks:

• Performance: Many would argue that the sole purpose of a database is to store data and maintain data relationships, not to execute code that could otherwise be executed by the application. In addition to detracting from what many consider the database's sole role, executing such logic within the database will consume additional processor and memory resources.
• Capability: As you'll soon learn, the SQL language constructs do offer a fair amount of capability and flexibility; however, most developers find that building these routines is both easier and more comfortable using a full-featured language such as PHP.
• Maintainability: Although you can use GUI-based utilities such as MySQL Query Browser (see Chapter 27) to manage stored routines, coding and debugging them is considerably more difficult than writing PHP-based functions using a capable IDE.
• Portability: Because stored routines often use database-specific syntax, portability issues will surely arise should you need to use the application in conjunction with another database product.

So, even after reviewing the advantages and disadvantages, you may still be wondering whether stored routines are for you. Perhaps the best advice one could give regarding making this determination is to read on and experiment with the numerous examples provided throughout this chapter.

How MySQL Implements Stored Routines

Although the term stored procedures is commonly bandied about, MySQL actually implements two procedural variants, which are collectively referred to as stored routines:

• Stored procedures: Stored procedures support execution of SQL commands such as SELECT, INSERT, UPDATE, and DELETE. They also can set parameters that can be referenced later from outside of the procedure.
• Stored functions: Stored functions support execution only of the SELECT command, accept only input parameters, and must return one and only one value. Furthermore, you can embed a stored function directly into a SQL command just like you might do with standard MySQL functions such as count() and date_format().

Generally speaking, you use stored routines when you need to work with data found in the database, perhaps to retrieve rows or insert, update, and delete values, whereas you use stored functions to manipulate that data or perform special calculations. In fact, the syntax presented throughout this chapter is practically identical for both variations, except that the term "procedure" is swapped out for "function." For example, the command DROP PROCEDURE procedure_name is used to delete an existing stored procedure, while DROP FUNCTION function_name is used to delete an existing stored function.

Stored Routine Privilege Tables

Those of you who have used MySQL for some time are aware that several new tables exist in the mysql database. Two of these tables, proc and procs_priv, are used to manage stored routines and the privileges required to create, execute, alter, and delete them.

The proc Table

The proc table stores information regarding the stored routine, including its syntax, creation date, parameter list, and more. Its structure is presented in Table 32-1.

Table 32-1. The proc Table

Column	Datatype	Null	Default
db	char(64)	Yes	No default
name	char(64)	No	No default
type	enumtype	No	No default
specific_name	char(64)	No	No default
language	enum('SQL')	No	SQL
sql_data_access	enumdataaccess	No	CONTAINS_SQL
is_deterministic	enum('YES', 'NO')	No	NO
security_type	enumsecurity	No	DEFINER
param_list	blob	No	No default
returns	char(64)	No	No default
body	longblob	No	No default
definer	char(77)	No	No default
created	timestamp	Yes	Current timestamp
modified	timestamp	Yes	0000-00-00 00:00:00
sql_mode	setsqlmode	No	No default
comment	char(64)	No	No default

Because of space limitations, the term enumtype is used as a placeholder for enum('FUNCTION', 'PROCEDURE'), which determines whether the routine is a function or procedure. The term enumdataaccess is used as a placeholder for enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA'). The term enumsecurity is a placeholder for enum('INVOKER', 'DEFINER'). Finally, setsqlmode is a placeholder for a rather lengthy list of options that I won't itemize here but will instead just say the column stores the SQL_MODE setting in place at the time the stored routine was created. A discussion of SQL_MODE is outside the scope of this book; for those interested, see the MySQL manual.

These columns are introduced in further detail in the section "Creating a Stored Routine."

The procs_priv Table

The procs_priv table stores privilege information pertinent to which users are allowed to interact with the routines defined in the proc table. The procs_priv table structure is defined in Table 32-2.

Table 32-2. The procs_priv Table

Column	Datatype	Null	Default
Host	char(60)	No	No default
Db	char(64)	No	No default
User	char(16)	No	No default
Routine_name	char(64)	No	No default
Routine_type	enumroutine	No	No default
Grantor	char(77)	No	No default
Proc_priv	procset	No	No default
Timestamp	timestamp	Yes	Current timestamp

Because of space limitations, the term enumroutine is used as a placeholder for enum('FUNCTION', 'PROCEDURE'), which determines whether the routine is a function or procedure. You might think it odd that this information is repeated in this table; after all, it's already found in the proc table. Couldn't MySQL just look to that table to retrieve this information? The reason for repeating this information is that it's possible for stored procedures and stored functions to share the same name, so MySQL requires this information to determine which is under review for a given user.

The term procset is used as a placeholder for set('Execute', 'Alter Routine', 'Grant'). Execute must be set in order for the user to execute the specified routine, Alter Routine must be set for the user to alter or drop the routine, and Grant must be set for the user to grant privileges for this routine to other users.

Creating a Stored Routine

The following syntax is available for creating a stored routine:

CREATE
   [DEFINER = { user | CURRENT_USER }
   PROCEDURE procedure_name ([parameter[, ...]])
   [characteristics, ...] routine_body

whereas the following is used to create a stored function:

CREATE
   [DEFINER = { user | CURRENT_USER }
   FUNCTION function_name ([parameter[, ...]])
   RETURNS type
   [characteristics, ...] routine_body

For example, the following creates a simple stored procedure that returns a static string:

mysql>CREATE PROCEDURE get_inventory()
    ->SELECT 45 AS inventory;

That's it. Now execute the procedure using the following command:

mysql>CALL get_inventory();

Executing this procedure returns the following output:

---

+---------------+
| inventory     |
+---------------+
|        45     |
+---------------+

---

Of course, this is about the simplest example that could possibly be provided. Read on to learn more about all the options at your disposal for creating more complex (and useful) stored routines.

Setting Security Privileges

The DEFINER clause determines which user account will be consulted to determine whether appropriate privileges are available to execute the queries defined by the stored routine. If you use the DEFINER clause, you'll need to specify both the username and hostname using 'user@host' syntax (for example, 'jason@localhost'). If CURRENT_USER is used (the default), then the privileges of whichever account has caused the routine to execute are consulted. Only users having the SUPER privilege are able to assign DEFINER to another user.

Setting Input and Return Parameters

Stored procedures can both accept input parameters and return parameters back to the caller. However, for each parameter, you need to declare the name, datatype, and whether it will be used to pass information into the procedure, pass information back out of the procedure, or perform both duties.

---

Note This section applies only to stored procedures. Although stored functions can accept parameters, they support only input parameters and must return one and only one value. Therefore, when declaring input parameters for stored functions, be sure to include just the name and type.

---

Perhaps not surprisingly, the datatype corresponds to those supported by MySQL. Therefore, you're free to declare a parameter to be of any datatype you might use when creating a table.

To declare the parameter's purpose, use one of the following three keywords:

• IN:IN parameters are intended solely to pass information into the procedure.
• OUT:OUT parameters are intended solely to pass information back out of the procedure.
• INOUT:INOUT parameters can pass information into the procedure, have its value changed, and then pass information back out of the procedure.

For any parameter declared as OUT or INOUT, you need to preface its name with the @ symbol when calling the stored procedure, so that the parameter can then be called from outside of the procedure. Consider an example that specifies a procedure named get_inventory, which accepts two parameters, productid, an IN parameter that determines the product you're interested in, and count, an OUT parameter that returns the value back to the caller's scope:

CREATE PROCEDURE get_inventory(IN product CHAR(8), OUT count INT)
   ...Statement Body

This procedure can then be called like so:

CALL get_inventory("ZXY83393", @count);

and the count parameter can be accessed like so:

SELECT @count;

Characteristics

Several attributes known as characteristics allow you to further tweak the nature of the stored procedure. The complete range of characteristics is presented below, followed by an introduction to each:

LANGUAGE SQL
|[NOT] DETERMINISTIC
|{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
|SQL SECURITY {DEFINER | INVOKER}
|COMMENT 'string'

LANGUAGE SQL

At present, SQL is the only supported stored procedure language, but there are plans to introduce a framework for supporting other languages in the future. This framework will be made public, meaning any willing and able programmer will be free to add support for his favorite language. For example, it's quite likely that you'll be able to create stored procedures using languages such as PHP, Perl, and Python, meaning the capabilities of the procedures will be limited only by the boundaries of the language being used.

[NOT] DETERMINISTIC

Only used with stored functions, any function declared as DETERMINISTIC will return the same value every time, provided the same set of parameters is passed in. Declaring a function DETERMINISTIC helps MySQL optimize execution of the stored function and aids in replication scenarios.

CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA

This setting indicates what type of task the stored procedure will do. The default, CONTAINS SQL, specifies that SQL is present but will not read or write data. NO SQL indicates that no SQL is present in the procedure. READS SQL DATA indicates that the SQL will only retrieve data. Finally, MODIFIES SQL DATA indicates that the SQL will modify data. At the time of writing, this characteristic had no bearing on what the stored procedure was capable of doing.

SQL SECURITY {DEFINER | INVOKER}

If the SQL SECURITY characteristic is set to DEFINER, then the procedure will be executed in accordance with the privileges of the user who defined the procedure. If it's set to INVOKER, it will execute according to the privileges of the user executing the procedure.

You might think the DEFINER setting is a tad strange, and perhaps insecure. After all, why would anyone want to allow a user to execute procedures using another user's privileges? This is actually a great way to enforce, rather than abandon, security of your system, because it allows you to create users that have absolutely no rights to the database other than to execute these procedures.

COMMENT 'string'

You can add some descriptive information about the procedure by using the COMMENT characteristic.

Declaring and Setting Variables

Local variables are often required to serve as temporary placeholders when carrying out tasks within a stored routine. However, unlike PHP, MySQL requires you to specify the type of the variables and explicitly declare them. This section shows you how to both declare and set variables.

Declaring Variables

Unlike PHP, MySQL requires you to declare local variables within a stored routine before using them, specifying their type by using one of MySQL's supported datatypes. Variable declaration is acknowledged with the DECLARE statement, and its prototype looks like this:

DECLARE variable_name type [DEFAULT value]

For example, suppose a stored procedure named calculate_bonus was created to calculate an employee's yearly bonus. It might require a variable named salary, another named bonus, and a third named total. They would be declared like so:

DECLARE salary DECIMAL(8,2);
DECLARE bonus DECIMAL(4,2);
DECLARE total DECIMAL(9,2);

When declaring variables, the declaration must take place within a BEGIN/END block. Furthermore, the declarations must take place before executing any other statements in that block. Also note that variable scope is limited to the block in which it's declared, an important point because it's possible to have several BEGIN/END blocks in a routine.

The DECLARE keyword is also used for declaring certain conditions and handlers. This matter is discussed in further detail in the later section, "Conditions and Handlers."

Setting Variables

The SET statement is used to set the value of a declared stored routine variable. Its prototype looks like this:

SET variable_name = value [, variable_name = value]

The following example illustrates the process of declaring and setting a variable titled inv:

DECLARE inv INT;
SET inv = 155;

It's also possible to set variables using a SELECT...INTO statement. For example, the inv variable can also be set like this:

DECLARE inv INT;
SELECT inventory INTO inv FROM product WHERE productid="MZC38373";

Of course, this variable is local in scope to the BEGIN/END block from within which it was declared. If you want to use this variable from outside of the routine, you need to pass it in as an OUT variable, like so:

mysql>DELIMITER //
mysql>CREATE PROCEDURE get_inventory(OUT inv INT)
->SELECT 45 INTO inv;
->//
Query OK, 0 rows affected (0.08 sec)
mysql>DELIMITER ;
mysql>CALL get_inventory(@inv);
mysql>SELECT @inv;

This returns the following:

---

+-------------+
| @inv        |
+-------------+
| 45          |
+-------------+

---

You may be wondering about the DELIMITER statement, though. By default, MySQL uses the semicolon to determine when a statement has concluded. However, when creating a multistatement stored routine, you need to write several statements, but you don't want MySQL to do anything until you've finished writing the stored routine. Therefore, you must change the delimiter to another character string. It doesn't have to be //. You can choose whatever you please, ||| or ^^, for instance.

Executing a Stored Routine

Executing a stored routine is accomplished by referencing the stored routine in conjunction with the CALL statement. For example, executing the previously created get_inventory procedure is accomplished like so:

mysql>CALL get_inventory(@inv);
mysql>SELECT @inv;

Executing get_inventory will return:

---

+-------------+
| @inv        |
+-------------+
| 45          |
+-------------+

---

Creating and Using Multistatement Stored Routines

Single-statement stored routines are quite useful, but stored routines' real power lies in their ability to encapsulate and execute several statements. In fact, an entire language is at your disposal, enabling you to perform rather complex tasks such as conditional evaluation and iteration. For instance, suppose your company's revenues are driven by a sales staff. To coax the staff into meeting its lofty goals, bonuses are tacked onto their monthly paychecks, with the size of the bonus proportional to the revenues attributed to the employee. The company handles its payroll internally, using a custom Java program to calculate and print the bonus checks at the conclusion of each year; however, a Web-based interface is provided to the sales staff so that it can monitor its progress (and bonus size) in real time. Because both applications would require the ability to calculate the bonus amount, this task seems like an ideal candidate for a stored function. The syntax for creating this stored function looks like this:

DELIMITER //
CREATE FUNCTION calculate_bonus
(emp_id CHAR(8)) RETURNS DECIMAL(10,2)
COMMENT 'Calculate employee bonus'
BEGIN
   DECLARE total DECIMAL(10,2);
   DECLARE bonus DECIMAL(10,2);
   SELECT SUM(revenue) INTO total FROM sales WHERE employee_id = emp_id;
   SET bonus = total * .05;
   RETURN bonus;
END;
//
DELIMITER ;

The calculate_bonus function would then be called like this:

mysql>SELECT calculate_bonus("35558ZHU");

This function returns something similar to this:

---

+-----------------------------+
| calculate_bonus("35558ZHU") |
+-----------------------------+
|                      295.02 |
+-----------------------------+

---

Even though this example includes some new syntax (all of which will soon be introduced), it should be rather straightforward.

The remainder of this section is devoted to coverage of the syntax commonly used when creating multistatement stored routines.

The BEGIN and END Block

When creating multistatement stored routines, you need to enclose the statements in a BEGIN/END block. The block prototype looks like this:

BEGIN
   statement 1;
   statement 2;
   ...
   statement N;
END

Note that each statement in the block must end with a semicolon.

Conditionals

Basing task execution on run-time information is key for wielding tight control over its outcome. Stored routine syntax offers two well-known constructs for performing conditional evaluation: the IF-ELSEIF-ELSE statement and the CASE statement. Both are introduced in this section.

IF-ELSEIF-ELSE

The IF-ELSEIF-ELSE statement is one of the most common means for evaluating conditional statements. In fact, even if you're a novice programmer, you've likely already used it on numerous occasions. Therefore, this introduction should be quite familiar. The prototype looks like this:

IF condition THEN statement_list
   [ELSEIF condition THEN statement_list] . . .
   [ELSE statement_list]
END IF

For example, suppose you modified the previously created calculate_bonus stored procedure to determine the bonus percentage based on not only sales but also the number of years the salesperson has been employed at the company:

IF years_employed < 5 THEN
   SET bonus = total * .05;
ELSEIF years_employed >= 5 and years_employed < 10 THEN
   SET bonus = total * .06;
ELSEIF years_employed >=10 THEN
   SET bonus = total * .07;
END IF

CASE

The CASE statement is useful when you need to compare a value against an array of possibilities. While doing so is certainly possible using an IF statement, the code readability improves considerably by using the CASE statement. Its prototype looks like this:

CASE
   WHEN condition THEN statement_list
   [WHEN condition THEN statement_list] . . .
   [ELSE statement_list]
END CASE

Consider the following example, which sets a variable containing the appropriate sales tax rate by comparing a customer's state to a list of values:

CASE
   WHEN state="AL" THEN:
      SET tax_rate = .04;
   WHEN state="AK" THEN:
      SET tax_rate = .00;
   ...
   WHEN state="WY" THEN:
      SET tax_rate = .04;
END CASE;

Alternatively, you can save some typing by using the following variation:

CASE state
   WHEN "AL" THEN:
      SET tax_rate = .04;
   WHEN "AK" THEN:
      SET tax_rate = .00;
   ...
   WHEN "WY" THEN:
      SET tax_rate = .04;
END CASE;

Iteration

Some tasks, such as inserting a number of new rows into a table, require the ability to repeatedly execute over a set of statements. This section introduces the various methods available for iterating and exiting loops.

ITERATE

Executing the ITERATE statement causes the LOOP, REPEAT, or WHILE block within which it's embedded to return to the top and execute again. Its prototype looks like this:

ITERATE label

Consider an example. The following stored procedure will increase every employee's salary by 5 percent, except for those assigned the employee category of 0:

DELIMITER //

DROP PROCEDURE IF EXISTS `corporate`.`calc_bonus`//
CREATE PROCEDURE `corporate`.`calc_bonus` ()
BEGIN

DECLARE empID INT;
DECLARE emp_cat INT;
DECLARE sal DECIMAL(8,2);
DECLARE finished INTEGER DEFAULT 0;

DECLARE emp_cur CURSOR FOR
   SELECT employee_id, salary FROM employees ORDER BY employee_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished=1;

OPEN emp_cur;

calcloop: LOOP

   FETCH emp_cur INTO empID, emp_cat, sal;

   IF finished=1 THEN
      LEAVE calcloop;
   END IF;

   IF emp_cat=0 THEN
      ITERATE calcloop;
   END IF;

   UPDATE employees SET salary = sal + sal * 0.05 WHERE employee_id=empID;

END LOOP calcloop;

CLOSE emp_cur;

END//


DELIMITER ;

You might have noticed that in this example a cursor was used to iterate through each row of the result set. If you're not familiar with this feature, see Chapter 35.

LEAVE

Pending the value of a variable or outcome of a particular task, you may want to immediately exit a loop or a BEGIN/END block by using the LEAVE command. Its prototype follows:

LEAVE label

An example of LEAVE in action is provided in the introduction to LOOP, next. You'll also find an example in the previous example.

LOOP

The LOOP statement will continue iterating over a set of statements defined in its block until the LEAVE statement is encountered. Its prototype follows:

[begin_label:] LOOP
   statement_list
END LOOP [end_label]

MySQL stored routines are unable to accept arrays as input parameters, but you can mimic the behavior by passing in and parsing a delimited string. For example, suppose you provide clients with an interface for choosing among an array of ten corporate services they'd like to learn more about. The interface might be presented as a multiple-select box, checkboxes, or some other mechanism; which one you use is not important, because ultimately the array of values would be condensed into a string (using PHP's implode() function, for instance) before being passed to the stored routine. For instance, the string might look like this, with each number representing the numerical identifier of a desired service:

1,3,4,7,8,9,10

The stored procedure created to parse this string and insert the values into the database might look like this:

DELIMITER //

CREATE PROCEDURE service_info
(client_id INT, services varchar(20))

   BEGIN
            DECLARE comma_pos INT;
      DECLARE current_id INT;

      svcs: LOOP
  
         SET comma_pos = LOCATE(',', services);
         SET current_id = SUBSTR(services, 1, comma_pos);

         IF current_id <> 0 THEN
            SET services = SUBSTR(services, comma_pos+1);
         ELSE
            SET current_id = services;
         END IF;

         INSERT INTO request_info VALUES(NULL, client_id, current_id);

         IF comma_pos = 0 OR current_id = '' THEN
            LEAVE svcs;
         END IF;

      END LOOP;

   END//


DELIMITER ;

Now call service_info, like so:

call service_info("45","1,4,6");

Once executed, the request_info table will contain the following three rows:

---

+-------+----------+----------+
| row_id | client_id | service |
+-------+----------+----------+
|     1  |       45 |       1  |
|     2  |       45 |       4  |
|     3  |       45 |       6  |
+-------+----------+----------+

---

REPEAT

The REPEAT statement operates almost identically to WHILE, looping over a designated statement or set of statements for as long as a certain condition is true. However, unlike WHILE, REPEAT evaluates the conditional after each iteration rather than before, making it akin to PHP's DO...WHILE construct. Its prototype follows:

[begin_label:] REPEAT
   statement_list
UNTIL condition
END REPEAT [end_label]

For example, suppose you were testing a new set of applications and wanted to build a stored procedure that would fill a table with a given number of test rows. The procedure follows:

DELIMITER //
CREATE PROCEDURE test_data
(rows INT)
BEGIN

   DECLARE val1 FLOAT;
   DECLARE val2 FLOAT;

   REPEAT
      SELECT RAND() INTO val1;
      SELECT RAND() INTO val2;
      INSERT INTO analysis VALUES(NULL, val1, val2);
      SET rows = rows - 1;
   UNTIL rows = 0
   END REPEAT;

END//

DELIMITER ;

Executing this procedure passing in a rows parameter of 5 produces the following result:

---

+--------+-----------+----------+
| row_id | val1      | val2     |
+--------+-----------+----------+
|     1  | 0.0632789 | 0.980422 |
|     2  |  0.712274 | 0.620106 |
|     3  |  0.963705 | 0.958209 |
|     4  |  0.899929 | 0.625017 |
|     5  |  0.425301 | 0.251453 |
+--------+-----------+----------+

---

WHILE

The WHILE statement is common among many, if not all, modern programming languages, iterating one or several statements for as long as a particular condition or set of conditions remains true. Its prototype follows:

[begin_label:] WHILE condition DO
   statement_list
END WHILE [end_label]

The test_data procedure first created in the above introduction to REPEAT has been rewritten, this time using a WHILE loop:

DELIMITER //
CREATE PROCEDURE test_data
(rows INT)
BEGIN

   DECLARE val1 FLOAT;
   DECLARE val2 FLOAT;

   WHILE rows > 0 DO
      SELECT RAND() INTO val1;
      SELECT RAND() INTO val2;
      INSERT INTO analysis VALUES(NULL, val1, val2);
      SET rows = rows - 1;
   END WHILE;

END//

DELIMITER ;

Executing this procedure produces similar results to those shown in the REPEAT section.

Calling a Routine from Within Another Routine

It's possible to call a routine from within another routine, saving you the inconvenience of having to repeat logic unnecessarily. An example follows:

DELIMITER //
CREATE PROCEDURE process_logs()
BEGIN
   SELECT "Processing Logs";
END//

CREATE PROCEDURE process_users()
BEGIN
   SELECT "Processing Users";
END//

CREATE PROCEDURE maintenance()
BEGIN
   CALL process_logs();
   CALL process_users();
END//

DELIMITER ;

Executing the maintenance() procedure produces the following:

---

+-----------------+
| Processing Logs |
+-----------------+
| Processing Logs |
+-----------------+
1 row in set (0.00 sec)
+------------------+
| Processing Users |
+------------------+
| Processing Users |
+------------------+
1 row in set (0.00 sec)

---

Modifying a Stored Routine

At present MySQL only offers the ability to modify stored routine characteristics, via the ALTER statement. Its prototype follows:

ALTER (PROCEDURE | FUNCTION) routine_name [characteristic ...]

For example, suppose you want to change the SQL SECURITY characteristic of the calculate_bonus method from the default of DEFINER to INVOKER:

ALTER PROCEDURE calculate_bonus SQL SECURITY invoker;

Deleting a Stored Routine

To delete a stored routine, execute the DROP statement. Its prototype follows:

DROP (PROCEDURE | FUNCTION) [IF EXISTS] sp_name

For example, to drop the calculate_bonus stored procedure, execute the following command:

mysql>DROP PROCEDURE calculate_bonus;

As of version 5.0.3, you'll need the ALTER ROUTINE privilege to execute DROP.

Viewing a Routine's Status

On occasion you may be interested to learn more about who created a particular routine, the routine's creation or modification time, or to what database the routine applies. This is easily accomplished with the SHOW STATUS statement. Its prototype looks like this:

SHOW (PROCEDURE | FUNCTION) STATUS [LIKE 'pattern']

For example, suppose you want to learn more about a previously created get_products() stored procedure:

mysql>SHOW PROCEDURE STATUS LIKE 'get_products'G

Executing this command produces the following output:

---

*************************** 1. row ***************************
           Db: corporate
         Name: get_products
         Type: PROCEDURE
      Definer: root@localhost
      Modified: 2008-03-12 19:07:34
      Created: 2008-03-12 19:07:34
      Security_type: DEFINER
      Comment:
      character_set_client: latin1          
      collation_connection: latin1_swedish_ci
      Database Collation: latin1_swedish_ci
1 row in set (0.01 sec)

---

Note that the G option was used to display the output in vertical rather than horizontal format. Neglecting to include G produces the results horizontally, which can be difficult to read.

It's also possible to use a wildcard if you want to view information regarding several stored routines simultaneously. For instance, suppose another stored routine named get_employees() was available:

mysql>SHOW PROCEDURE STATUS LIKE 'get_%'G

This would produce:

---

*************************** 1. row ***************************
           Db: corporate
         Name: get_employees
         Type: PROCEDURE
      Definer: jason@localhost
      Modified: 2008-03-12 23:05:28
      Created: 2008-03-12 23:05:28
      Security_type: DEFINER
      Comment:
      character_set_client: latin1          
      collation_connection: latin1_swedish_ci
      Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
           Db: corporate
         Name: get_products
         Type: PROCEDURE
      Definer: root@localhost
      Modified: 2008-03-12 19:07:34
      Created: 2008-03-12 19:07:34
      Security_type: DEFINER
      Comment:
      character_set_client: latin1          
      collation_connection: latin1_swedish_ci
      Database Collation: latin1_swedish_ci
2 rows in set (0.02 sec)

---

Viewing a Routine's Creation Syntax

It's possible to review the syntax used to create a particular routine, by using the SHOW CREATE statement. Its prototype follows:

SHOW CREATE (PROCEDURE | FUNCTION) dbname.spname

For example, the following statement will re-create the syntax used to create the get_products() procedure:

SHOW CREATE PROCEDURE corporate.maintenanceG

Executing this command produces the following output (slightly formatted for readability):

---

*************************** 1. row ***************************
           Procedure: maintenance
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `maintenance`()
    BEGIN
        CALL process_logs();
        CALL process_users();
    END
    character_set_client: latin1
    collation_connection: latin1_swedish_ci
    Database Collation: latin1_swedish_ci

---

Handling Conditions

Earlier, this chapter mentioned that the DECLARE statement can also specify handlers that can execute should a particular situation, or condition, occur. For instance, earlier in this chapter a handler was used in the calc_bonus procedure to determine when the iteration of a result set had completed. Two declarations were required, a variable named finished and a handler for the NOT FOUND condition:

DECLARE finished INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished=1;

Once the iteration loop was entered, finished was checked with each iteration, and if it was set to 1, the loop would be exited:

IF finished=1 THEN
   LEAVE calcloop;
END IF;

MySQL supports numerous conditions that can be reacted to as necessary. See the MySQL documentation for more details.

Integrating Routines into Web Applications

Thus far, all the examples have been demonstrated by way of the MySQL client. While this is certainly an efficient means for testing examples, the utility of stored routines is drastically increased by the ability to incorporate them into your application. This section demonstrates just how easy it is to integrate stored routines into your PHP-driven Web application.

Creating the Employee Bonus Interface

Returning to the multistatement stored function example involving the calculation of employee bonuses, it was mentioned that a Web-based interface was offered to enable employees to track their yearly bonus in real time. This example demonstrates just how easily this is accomplished using the calculate_bonus() stored function.

Listing 32-1 presents the simple HTML form used to prompt for the employee ID. Of course, in a real-world situation, such a form would also request a password; however, for the purposes of this example an ID is sufficient.

Listing 32-2 receives the information provided by login.php, using the provided employee ID and calculate_bonus() stored function to calculate and display the bonus information.

Executing this example produces output similar to this:

---

Your bonus is $295.02

---

Retrieving Multiple Rows

Although the above example should suffice for understanding how multiple rows are returned from a stored routine, the following brief example makes it abundantly clear. Suppose you create a stored procedure that retrieves information regarding company employees:

CREATE PROCEDURE get_employees()
   SELECT employee_id, name, position FROM employees ORDER by name;

This procedure can then be called from within a PHP script like so:

<?php
  // Instantiate the mysqli class
  $db = new mysqli("localhost", "root", "jason", "corporate");

  // Execute the stored procedure
  $result = $db->query("CALL get_employees()");

  // Loop through the results
  while (list($employee_id, $name, $position) = $result->fetch_row()) {
     echo "$employee_id, $name, $position <br />";
  }

?>

Executing this script produces output similar to the following:

---

EMP12388, Clint Eastwood, Director
EMP76777, John Wayne, Actor
EMP87824, Miles Davis, Musician

---

Summary

This chapter introduced stored routines. You learned about the advantages and disadvantages to consider when determining whether this feature should be incorporated into your development strategy, and all about MySQL's specific implementation and syntax. Finally, you learned how easy it is to incorporate both stored functions and stored procedures into your PHP applications.

The next chapter introduces another feature new to MySQL 5: triggers.