If you’ve not worked with stored procedures before, it may seem like we’ve just thrown lot of information at you, so let’s look at a couple of examples that may help to clear things up a bit. A very simple one would be a stored procedure like the one shown here, which has only a single output parameter:
You’ll notice that before we entered the definition for the stored procedure, we used the DELIMITER command to change the character used to terminate a query from a semicolon to a pipe character. This is because MySQL needs some way to distinguish the semicolon used to end SQL statements inside the body of the procedure from what’s used to terminate the CREATE PROCEDURE statement itself. Once we’ve done so, we simply use DELIMITER again like so:
DELIMITER ; |
This tells MySQL that we want to use the semicolon to terminate commands once again. You don’t necessarily have to use the pipe character as your delimiter in such cases, but since it’s not often used in queries, it’s a good choice.
Once a procedure has been created, it’s invoked using the CALL command followed by the number of parameters required in the procedure’s definition. When the procedure is called, it sets values for any output parameters by means of the SELECT ... INTO ... statement. Just as with the parameters for a function or method in programming languages such as PHP, Perl, and so on, we don’t have to use the same variable names as used in the procedure definition; we can use whatever names we like, and we can use these just like we would any other user variables. In other words, which values get inserted into the variables we use in the CALL statement depends on their order in the procedure definition.
We just showed a simple SELECT query, but we’re not limited in what sort of query we use this value in:
One other point we need to mention before we continue concerns how the procedures are associated with databases. Normally, a procedure is associated with the database selected at the time the CREATE PROCEDURE or CREATE FUNCTION statement is executed. If we wish to refer to this procedure later after selecting a different database, it’s necessary to qualify its name with the name of the database to which it belongs, using dbname.procedurename notation. In addition, any tables referred to within a procedure are assumed to be in the current database. The USE statement normally employed for selecting a particular database isn’t permitted inside procedures, and since a procedure might be called at any time, no matter which database (if any) is currently selected, MySQL must “know” which database to use. However, this tends to make procedures much more flexible as a result.
In other words, suppose we have selected the database named mydb, and we create a procedure named myproc, which selects records from a table named mytable. Unless you use dbname.tablename notation to specify that mytable is part of some other database, MySQL assumes that it should try to find mytable in mydb, and an error will result if it can’t find a table by that name in that database. In addition, if you select a different database and then try to call myproc using CALL myproc(); , an error will result; in that case, you must fully qualify the name of the procedure:
Let’s turn now to an example of a stored procedure that uses several parameters. First, as before, let’s change the statement delimiter to the pipe character and then define the stored procedure get_prod_info, as shown here:
This procedure takes the ID for a product and returns the name of the product, the name of the category to which it belongs, and the price of the product. It has one input parameter and three output parameters having the names and datatypes shown here:
CREATE PROCEDURE get_product_info
The body of this procedure consists of a single SELECT query, with an INTO clause that stores the three column values in the output parameters, and that uses the prodid parameter value in the WHERE clause:
SELECT c.name, p.name, p.price
Now we reset the semicolon as the delimiter character, then call the procedure using an integer value for prodid and user variable names for the output parameters. Then we can select these three user variables in order to view the result:
As we’ve already said, you’re not limited to using SELECT queries in stored procedures. Let’s create a stored procedure for transferring money between two bank accounts, using the same accounts table and initial data as we used for the Python transactions example in Chapter 5:
CREATE TABLE accounts (
In this example, we’ll assume that we’re using a MyISAM table and therefore not enforcing transaction compliance. However, you should note that you’ll want to use START TRANSACTION rather than BEGIN or BEGIN WORK inside a stored procedure in order to avoid clashing with the BEGIN keyword as used to demarcate the procedure’s body. We’ll commence by defining a stored procedure named transfer_funds as shown here:
The procedure has three input parameters and no output parameters. The parameters amt, from_acct, and to_acct are intended to pass (in order) the amount to be transferred, the ID number of the account from which the funds are to be taken, and the number of the account that will receive the transferred funds. We’ll check the initial state of the data with a simple select query, then call the stored procedure and verify the result with another SELECT * FROM accounts;:
As you can see, stored procedures will have a lot to offer users of MySQL when they’re finally available in a production release.
blog comments powered by Disqus