What Are Stored Procedures?

Simply put, a stored procedure is a procedure in SQL that is stored in the database server rather than the web server. You may be thinking that you don't store any procedures on the web server, but in fact you doany script that contains SQL queries counts as a procedure stored on the web server. For example, every query in your application that selects, deletes, updates, or inserts data into tableswhich you will have painstakingly coded in your scriptscould be stored in the database as a stored procedure and referenced as such in your scripts.

Proponents of using stored procedures in code point to performance and maintenance as key reasons for doing so:

• Better performance Stored procedures exist as precompiled SQL in the database, so a typical two-step process (compile and execute) becomes a single-step process (execute).

• Ease of maintenance Maintaining one statement in one place (the database) is significantly less time-consuming than maintaining one statement in numerous places, such as all through scripts on your web server. Additionally, storing all your statements in the database as opposed to actual text files in your web server document root is one more line of defense should someone gain access to the files on your web serverall they'll have are queries that call stored procedures instead of the logic of the procedure itself.

In the next section, you'll see a simple example of creating and using a stored procedure.

Basic Syntax of Stored Procedures

An example of a useful stored procedure would be the SQL query used to generate a report of some sortbe it financial data, sales inventory, or otherwisejust imagine a complex query that involves a lot of processing. Creating a stored procedure out of this type of query goes along with the performance benefits of stored procedures. If you had a simple query used frequently throughout your application, creating a stored procedure for it would go along with the maintenance benefits of stored procedures. Regardless of the simplicity or complexity of the stored procedure, creating and using it follows the same basic process.

The following table will be used in this stored procedure example:

mysql> DESC testSP; +------------+-------------+------+-----+---------+----------------+ | Field      | Type        | Null | Key | Default | Extra          | +------------+-------------+------+-----+---------+----------------+ | id         | int(11)     | NO   | PRI |         | auto_increment | | field_name | varchar(25) | YES  |     |         |                | +------------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)

Next, you must ensure that MySQL knows the delimiter character you'll be using in your stored procedures. This example uses "//" as the delimiter, so the following query was issued:

mysql> DELIMITER //

The syntax for creating a basic stored procedure is

CREATE PROCEDURE procedure_name () query //

For this example, the stored procedure simply selects all data from the test SP table, and we'll name the stored procedure sp1:

mysql> CREATE PROCEDURE sp1 () SELECT * FROM testSP // Query OK, 0 rows affected (0.11 sec)

To call the stored procedure, use the CALL command:

mysql> CALL sp1 ()// +----+------------+ | id | field_name | +----+------------+ | 1  | test1      | | 2  | test2      | | 3  | test3      | +----+------------+ 3 rows in set (0.44 sec) Query OK, 0 rows affected (0.44 sec)

In Chapter 18 you'll learn the process for issuing these SQL queries using PHP.

Clearly, these few pages do not even begin to scratch the surface of working with stored procedures; this section is intended only to introduce you to the concept. Additional recommended reading includes the MySQL AB informational publication (70 pages) at http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.