Stored Procedure Examples - MySQL

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.

---

NOTE  Logging out of MySQL and then back in again resets the delimiter character to the semicolon. However, if you lose the connection for some reason, or even if the MySQL server crashes, the delimiter will remain the same, so long as the client continues to run; this information is stored in the client, and not on the server.

---

The procedure itself consists of the bare minimum that’s required following the output parameter—the BEGIN keyword followed by an SQL block containing just one statement followed by END and the delimiter character we specified before writing the CREATE PROCEDURE command.

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:

CALL mydb.myproc();

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
(
  IN catid INT,
  OUT catname VARCHAR(50),
  OUT prodname VARCHAR(50),
  OUT prodprice DECIMAL(5,2)
)

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
  INTO catname, prodname, prodprice
FROM products p
JOIN categories c
ON c.id = p.category_id
WHERE p.id = prodid;

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:

---

NOTE  In the rest of the examples covering stored procedures and stored functions, we’ll assume that the delimiter character has been set to “|” (pipe character) and left that way for the duration of the session, rather than taking up space by showing you a continuous switching back and forth when it’s not really necessary.

---

Although output parameters can contain only scalar values, you can still obtain result sets using stored procedures, simply by not using output parameters for the result of the SELECT. Here’s a simple example, which also shows you what the output of a SHOW CREATE PROCEDURE statement looks like:

                       

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 (
  account_number int(11) NOT NULL default '0',
  firstname varchar(50) NOT NULL default '',
  lastname varchar(50) NOT NULL default '',
  balance decimal(8,2) NOT NULL default '0.00',
  PRIMARY KEY (account_number)
);
INSERT INTO accounts VALUES (6557, 'Gerald', 'Roberts', '1602.92');
INSERT INTO accounts VALUES (8510, 'Morris', 'Johnson', '2176.21');

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;:

---

NOTE  Stored procedures and stored functions in MySQL 5.0+ require the proc table to be present in the mysql database, and will not work without it. If you’re upgrading an existing installation, be sure to run the mysql_fix_privilege_tables script included with the MySQL distribution files in order to add the proc table to your grant tables. (An early [alpha] version did not add this table, so make sure that you obtain and install the latest version from http://dev.mysql.com/.)
We also recommend that you take a look at the output of a SHOW CREATE TABLE mysql.proc; command—you should find it most instructive.

---