tored procedures and functions are a new feature of MySQL 5.0. A stored procedure is a pre-built procedure containing one or more SQL statements stored in the database server.
This article shows how to create a few basic stored procedure and function examples, and call MySQL stored procedures and functions from PHP with the help of some database extensions.
Advantages of Using Stored Procedures
Stored procedures can provide improved performance because they can be precompiled, and because the client needs to send only a name and required parameters to the server to run a stored procedure rather than having to send the entire procedure code. In addition, stored procedures provide these other advantages:
- They simplify complex operations by encapsulating processes into a single easy-to-use unit.
- They help avoid errors because you can use a single well-tested stored procedure in many applications.
- A stored procedure runs the same way from all languages/environments. Because stored procedures reside on the database server, it makes no difference what application environment you use to call them—the stored procedure itself remains consistent.
- They reduce the risk of data damage by limiting access to the data.
- They can reduce network traffic. Complex, repetitive tasks may require getting some data, applying some logic to the retrieved values, and using the results to retrieve more data. When this multi-step process takes place completely on the database server, as in a stored procedure, it can eliminate the need to send result sets and new queries back and forth from to the database server.
Creating Stored Procedures in MySQL
MySQL 5.0 finally introduces functionality for stored procedures. In this implementation, each stored procedure or function is associated with a particular database, which has the following implications:
- When you call a stored procedure or function, the database issues an implicit USE db_name command, which remains in effect until the stored procedure terminates.
- You can create a stored procedure name for a given database name only if that name is unique in the current database. For example, to invoke a stored procedure named proc or function named func associated with the book database, you can write CALL book.proc() or CALL book.func().
- When you drop a database, MySQL drops all stored procedures and functions associated with that database as well.
Defining procedures or functions is a two-step process:
- Define the name of the procedure or function, and set its parameters.
- Define the body of the procedure or function between BEGIN and END statements.
Here's the basic syntax:
CREATE PROCEDURE procedure_name ([procedure_parameter[,...]])
The procedure_parameter is a list of parameters and their directions, composed using the following arguments:
- IN: Passes a value into a procedure. The procedure can modify the value, but the modification is not visible to the caller when the procedure returns.
- OUT: Passes a value from the procedure back to the caller. The parameter's initial value in the procedure is NULL; the procedure usually changes that value, and the final value is visible to the caller when the procedure returns.
- INOUT: The caller initializes an INOUT parameter, but the procedure can modify the value, and the final value is visible to the caller when the procedure returns.
|Author's Note Specifying a parameter as IN, OUT, or INOUT is valid only for a procedure. In contrast, function parameters are always IN parameters.|
Calling a Stored Procedure in MySQL
Within MySQL, you call a stored procedure using the call method, for example:
Creating Stored Functions in MySQL
There are a few key differences between creating a stored procedure and creating a function:
- The keyword function replaces the procedure keyword.
- You don't need to specify parameter direction, because all parameters are IN.
- The RETURNS keyword after the parameter list specifies the return value type.
- You don't need to use a BEGIN...END block.
- To call a function, use the syntax select function(parameter_list).
Here's the function creation syntax:
CREATE FUNCTION function_name ([function_parameter[,...]])
Here's a simple stored function example that calculates and returns an int:
CREATE FUNCTION simple_operation (price int) RETURNS int
To call it from SQL Server, use:
The return value in this case is 5000.