This article is part of Robert Sheldon's continuing series on Learning MySQL. To see all of the items in the series, click here.
Like most relational database management systems, MySQL supports the use of stored procedures that can be invoked on-demand by data-driven applications. Each stored procedure is a named database object that contains a routine made up of one or more SQL statements. When an application calls the stored procedure, MySQL executes those statements and returns the results to the application.
A procedure’s routine can include a wide range of statements, including data definition language (DDL) and data manipulation language (DML). MySQL stored procedures also support the use of input and output parameters, making them a highly flexible tool for encapsulating statement logic.
Stored procedures enable SQL code to be reused as often as needed, helping to simplify application development and reduce statement errors. Developers don’t have to write complex queries for each application request, and QA teams don’t need to spend as much time verifying queries when testing applications.
The ability to reuse code also reduces network traffic because a stored procedure can be invoked with a single CALL
statement, no matter how complex the underlying query. Stored procedures can also deliver a higher degree of security by abstracting the underlying database structure and eliminating ad hoc queries at the application level.
In this article, I demonstrate how to create and update stored procedures, as well as invoke them with a CALL
statement. You’ll learn how to build both basic and parameterized procedures that use input and output parameters. As with the previous articles in this series, I used the MySQL Community edition on a Windows computer to build the examples, which I created in MySQL Workbench, the graphical user interface (GUI) that comes with the Community edition.
Preparing your MySQL environment
The examples in this article are based on the travel
database, which is the same database I used for the previous article on MySQL views. This article uses the same tables and data to demonstrate how to work with stored procedures. If you tried the examples in the previous article, the travel
database might still be installed on your MySQL instance. If it is not, you can use the following SQL script to create the database and its tables:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
DROP DATABASE IF EXISTS travel; CREATE DATABASE travel; USE travel; CREATE TABLE manufacturers ( manufacturer_id INT UNSIGNED NOT NULL AUTO_INCREMENT, manufacturer VARCHAR(50) NOT NULL, create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (manufacturer_id) ) ENGINE=InnoDB AUTO_INCREMENT=1001; CREATE TABLE airplanes ( plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT, plane VARCHAR(50) NOT NULL, manufacturer_id INT UNSIGNED NOT NULL, engine_type VARCHAR(50) NOT NULL, engine_count TINYINT NOT NULL, max_weight MEDIUMINT UNSIGNED NOT NULL, wingspan DECIMAL(5,2) NOT NULL, plane_length DECIMAL(5,2) NOT NULL, parking_area INT GENERATED ALWAYS AS ((wingspan * plane_length)) STORED, icao_code CHAR(4) NOT NULL, create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (plane_id), CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id) REFERENCES manufacturers (manufacturer_id) ) ENGINE=InnoDB AUTO_INCREMENT=101; |
The airplanes
table includes a foreign key that references the manufacturers
table, so be sure to create the tables in the order shown here. After you create the tables, you can add sample data to them so you’ll be able to test your stored procedure. To populate the table, run the following INSERT
statements:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
INSERT INTO manufacturers (manufacturer) VALUES ('Airbus'), ('Beechcraft'), ('Piper'); INSERT INTO airplanes (plane, manufacturer_id, engine_type, engine_count, max_weight, wingspan, plane_length, icao_code) VALUES ('A380-800', 1001, 'jet', 4, 1267658, 261.65, 238.62, 'A388'), ('A319neo Sharklet', 1001, 'jet', 2, 166449, 117.45, 111.02, 'A319'), ('ACJ320neo (Corporate Jet version)', 1001, 'jet', 2, 174165, 117.45, 123.27, 'A320'), ('A300-200 (A300-C4-200, F4-200)', 1001, 'jet', 2, 363760, 147.08, 175.50, 'A30B'), ('Beech 390 Premier I, IA, II (Raytheon Premier I)', 1002, 'jet', 2, 12500, 44.50, 46.00, 'PRM1'), ('Beechjet 400 (from/same as MU-300-10 Diamond II)', 1002, 'jet', 2, 15780, 43.50, 48.42, 'BE40'), ('1900D', 1002, 'Turboprop', 2,17120, 57.75, 57.67, 'B190'), ('PA-24-400 Comanche', 1003, 'piston', 1, 3600, 36.00, 24.79, 'PA24'), ('PA-46-600TP Malibu Meridian, M600', 1003, 'Turboprop', 1, 6000, 43.17, 29.60, 'P46T'), ('J-3 Cub', 1003, 'piston', 1, 1220, 38.00, 22.42, 'J3'); |
As with the CREATE
TABLE
statements, you should run the INSERT
statements in the order specified here so you don’t violate the foreign key defined on the airplanes
table.
Creating a stored procedure in MySQL
To build a stored procedure in MySQL, you must use a CREATE
PROCEDURE
statement. To get started, open a new query window in Workbench and ensure that the target database is active. (To make a database active, double-click the database in Navigator or run a USE
statement.) For this example, you’ll use the travel
database.
When building your CREATE
PROCEDURE
statement, you must provide a name for the procedure and specify the SQL routine you want to persist to your database. The routine can include a single SQL statement such as SELECT
or UPDATE
, or it can be a compound statement. A compound statement is one that uses the BEGIN…END
syntax to enclose a block of one or more SQL statements. The block can include a wide range of SQL language elements, including DDL and DML statements, variable declarations, embedded blocks, or flow control constructs such as loops or conditional tests.
Most stored procedures use a compound statement even if they include only a single SQL statement. For example, the routine in the following CREATE
PROCEDURE
statement includes a compound statement with only one SELECT
statement:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DELIMITER // CREATE PROCEDURE get_plane_info() BEGIN SELECT a.manufacturer_id, m.manufacturer, COUNT(*) AS plane_count, ROUND(AVG(a.wingspan), 2) AS avg_span, ROUND(AVG(a.plane_length), 2) AS avg_length FROM airplanes a INNER JOIN manufacturers m ON a.manufacturer_id = m.manufacturer_id GROUP BY a.manufacturer_id ORDER BY m.manufacturer; END// DELIMITER ; |
The example creates a procedure named get_plane_info
. Notice that a set of parentheses follows the name. If the statement were to include input or output parameters, they would be defined within the parenthesis (which I’ll cover later in the article). If you don’t include parameters, you must still provide the parentheses.
The compound statement is defined by the BEGIN…END
syntax, which encloses the single SELECT
statement. The SELECT
statement itself joins the airplanes
and manufacturers
tables, groups the data by the manufacturer_id
column in the airplanes
table, and calculates the average wingspan
and plane_length
values for each manufacturer. The statement also orders the results by manufacturer and provides the total number of plane models for each one. (We’ll be covering all these statement elements in more detail later in this series.)
As you can see, creating a simple stored procedure is a fairly straightforward process. At a minimum, you must provide a name and the routine body. However, you no doubt noticed the inclusion of the two DELIMITER
statements that surround the procedure definition.
By default, MySQL uses the semi-colon (;) as a statement delimiter. This helps to ensure that a client sends a statement to the server in its entirety without confusing it with other statements. However, a compound statement within a stored procedure might include one or more delimiters, in addition to the definition’s final delimiter, and all these delimiters can cause confusion when passing the CREATE
PROCEDURE
statement from a client to the server.
To get around this issue, MySQL supports the use of the DELIMITER
statement, which lets you temporarily change the delimiter so you can pass the entire procedure definition to the server as a single statement. In the above example, the first DELIMITER
statement changes the delimiter to double forward slashes (//), and the second DELIMITER
statement changes the delimiter back to a semi-colon. The temporary delimiter is then used at the end of the CREATE
PROCEDURE
statement (after the END
keyword), but the SELECT
statement itself is still terminated with the semi-colon delimiter.
I also wanted to point out that MySQL Workbench provides a tool (in the form of a tab) for creating and editing stored procedures. The tool is similar to the one used for creating and editing views. It provides a stub for building a CREATE
PROCEDURE
statement but leaves it up to you to fill in the details. Figure 1 shows the Stored Procedure tab as it appears when you first launch it in Workbench.
To launch the Stored Procedure tab, select the target database in Navigator and then click the create stored procedure button on the Workbench toolbar. (The button includes the tooltip Create a new stored procedure in the active schema in the connected server.) When the Stored Procedure tab appears, you can start building your statement. After you finish, click Apply. MySQL will then add a few statement components that are necessary to create the procedure. Review the final script, click Apply once more, and then click Finish. The stored procedure will be added to the target database.
The Stored Procedure tab can be useful for creating and editing a stored procedure, so I wanted to be sure you knew it’s available. However, I prefer to use a query tab when working with a stored procedure because I think it’s easier and saves steps, so this is the approach I take for the examples in this article.
Verifying a newly created stored procedure
After you run the CREATE
PROCEDURE
statement, you can verify that it’s been added to the travel
database by viewing it in Navigator, as shown in Figure 2. (You might need to refresh Navigator to see the new procedure.)
From Navigator, you can open the procedure definition in the Stored Procedure tab by clicking the wrench icon next to the procedure name. Figure 3 shows the procedure definition as you created it, except for one difference. It now includes the DEFINER
clause after the CREATE
keyword.
The DEFINER
clause specifies which account has been designated as the procedure creator. Because I created the stored procedure under the root
account on my local MySQL instance, that is the username added to the definition. By default, MySQL uses the account of the user who executed the CREATE
PROCEDURE
statement, but you can specify a different account as long as it’s been granted adequate permissions.
Other than the DEFINER
clause, your stored procedure definition should look much like what you created, except that there are no DELIMITER
statements or custom delimiter. However, if you were to update the definition on the Stored Procedure tab and click Apply, Workbench would add those elements for you.
Another way to verify whether your stored procedure has been created is to query the routines
view in the INFORMATION_SCHEMA
database:
1 2 |
SELECT * FROM information_schema.routines WHERE routine_schema = 'travel'; |
The statement includes a WHERE
clause that limits the results to the travel
database. Any routines (stored procedures or functions) that have been created in the database will be returned by this query.
You can limit the results further by also specifying the procedure name in the WHERE
clause and by specifying which column or columns to return. For example, the following SELECT
statement limits the results to the routine_definition
column and the get_plane_info
routine in the travel
database:
1 2 3 4 |
SELECT routine_definition FROM information_schema.routines WHERE routine_schema = 'travel' AND routine_name = 'get_plane_info'; |
Although the statement returns only a single value, it can still be difficult to read, especially if it’s a complex compound statement. To view the statement in its entirety, right-click the value directly in the results and click Open Value in Viewer. Select Text if it’s not already selected. MySQL launches a separate window that displays the value, as shown in Figure 4.
Of course, verifying that the stored procedure exists doesn’t tell you whether it will work as expected. For this reason, you should also execute the procedure and see what sort of results it returns (in addition to running it through a proper QA cycle). For this, use a CALL
statement that specifies the procedure name, as shown in the following example:
1 |
CALL get_plane_info; |
When you call the procedure, MySQL runs the stored routine and returns the statement results, which are shown in Figure 5.
As expected, the CALL
statement returns the aggregated airplane data, grouped by manufacturer. These are the same results that you would have received if you ran the routine’s SELECT
statement directly. However, the statement is now persisted to the database, eliminating the need to write the statement at the application level.
Adding an input parameter to a stored procedure
The get_plane_info
stored procedure created in the previous section demonstrated most of the main components that go into a MySQL stored procedure. In a production environment, the compound statement will likely be more complex, but this example still provides most of the basics. That said, one of the most beneficial aspects of a stored procedure is its ability to support input and output parameters.
In this section, I demonstrate how to add an input parameter to the procedure definition. (I’ll cover output parameters in the next section.) Before I get into that, you should be aware that you cannot simply alter a procedure definition like you can a table or view definition. You can modify a procedure’s characteristics, but nothing more. To make any significant updates, you must drop the procedure and then re-create it, incorporating any new elements.
To drop a stored procedure, you can use a DROP
PROCEDURE
statement, as shown in the following example:
1 |
DROP PROCEDURE IF EXISTS get_plane_info; |
The IF
EXISTS
clause is optional, but it can help avoid unnecessary errors. After you run this statement, you can confirm that the procedure has been dropped by again querying the routines
view in the INFORMATION_SCHEMA
database:
1 2 |
SELECT * FROM information_schema.routines WHERE routine_schema = 'travel'; |
The statement should now return an empty result set, unless you created other stored procedures or functions.
After you delete the get_plane_info
stored procedure, you can update your CREATE
PROCEDURE
statement to include an input parameter. For each parameter, you should specify the parameter type, the parameter name, and the parameter’s data type. MySQL supports three parameter types:
- IN. Input parameter that passes a value from the caller into the procedure’s routine.
- OUT. Output parameter that passes a value from the routine back to the caller.
- INOUT. Parameter that can be initialized by the caller, updated by the routine, and then returned to the caller with its new value.
The following CREATE
PROCEDURE
statement includes one input parameter, which is named in_name
and defined with the VARCHAR(50)
data type:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DELIMITER // CREATE PROCEDURE get_plane_info( IN in_name VARCHAR(50)) COMMENT 'retrieves aggregated airplane information' BEGIN SELECT a.manufacturer_id, m.manufacturer, COUNT(*) AS plane_count, ROUND(AVG(a.wingspan), 2) AS avg_span, ROUND(AVG(a.plane_length), 2) AS avg_length FROM airplanes a INNER JOIN manufacturers m ON a.manufacturer_id = m.manufacturer_id WHERE m.manufacturer = in_name; END// DELIMITER ; |
The parameter definition is enclosed in parentheses and includes the IN
keyword, parameter name, and data type. I also updated the SELECT
statement to reflect the use of the parameter. It no longer includes the GROUP
BY
and ORDER
BY
clauses but now includes a WHERE
clause that compares the in_name
parameter to the manufacturer
column. In this way, the caller can specify the manufacturer on which to base the query.
The CREATE
PROCEDURE
statement also includes the COMMENT
characteristic, which appends a comment to the procedure definition. You can include one or more characteristics in a CREATE
PROCEDURE
statement after the parameter definitions. A characteristic is one of several options that can be added to a procedure definition. Each characteristic affects the procedure definition in a different way. For example, this characteristic adds a comment, but you can also use characteristics to indicate the routine language, specify whether the routine is deterministic, or define the routine’s nature.
When calling a stored procedure that takes an input parameter, you must include the parameter value in parentheses. If it’s a character value, you must enclose it in single quotes. For example, the following CALL
statement specifies piper
as the value for the procedure’s input parameter:
1 |
CALL get_plane_info ('piper'); |
When MySQL runs the procedure’s routine, it substitutes the piper
value for the in_name
parameter specified in the WHERE
clause. Figure 6 shows the results now returned by the stored procedure.
When defining your stored procedure, you can include multiple IN
parameters, separating them with commas. Then, when you call the procedure, you specify each parameter value within the parentheses, again separated with commas. You can also include OUT
parameters or INOUT
parameters alongside the input parameters.
Adding output parameters to a stored procedure
Now let’s look at how to add multiple OUT
parameters to the get_plane_info
stored procedure. Output parameters provide a mechanism for returning one or more values back to the caller, rather than returning a single result set. For this example, you’ll add five output parameters, which will correspond to the columns specified in the routine’s SELECT
list.
To add the parameters, you’ll need to again drop the procedure and then run an updated CREATE
PROCEDURE
statement. The output parameters are specified within the same parentheses as the input parameter, as shown in the following script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DROP PROCEDURE IF EXISTS get_plane_info; DELIMITER // CREATE PROCEDURE get_plane_info( IN in_name VARCHAR(50), OUT out_id INT UNSIGNED, OUT out_name VARCHAR(50), OUT plane_count SMALLINT UNSIGNED, OUT avg_wingspan DECIMAL(5,2), OUT avg_length DECIMAL(5,2)) COMMENT 'retrieves aggregated airplane information' BEGIN SELECT a.manufacturer_id, m.manufacturer, COUNT(*), ROUND(AVG(a.wingspan), 2), ROUND(AVG(a.plane_length), 2) INTO out_id, out_name, plane_count, avg_wingspan, avg_length FROM airplanes a INNER JOIN manufacturers m ON a.manufacturer_id = m.manufacturer_id WHERE m.manufacturer = in_name; END// DELIMITER ; |
For each output parameter, you must specify the OUT
keyword, the parameter name, and the parameter’s data type. In addition, you must add an INTO
clause after the SELECT
list that returns the results to the output parameters. I’ve also removed the column aliases from the SELECT
list because they’re no longer needed.
When you call a stored procedure that returns output parameters, you can capture those parameter values by passing in a user-defined variable for each output parameter to hold its value, as shown in the following CALL
statement:
1 2 |
CALL get_plane_info ('beechcraft', @out_id, @out_name, @plane_count, @avg_wingspan, @avg_length); |
The CALL
statement specifies beechcraft
as the input parameter value. This is followed by five user-defined variables, which correspond to the parameters specified in the stored procedure definition. When you run the CALL
statement, the returned parameter values are assigned to the variables.
The exact way in which you’ll handle output parameters in your application will depend on the programming language that you’re using. In the meantime, you can then verify that your variables contain the expected values by running a SELECT
statement similar to the following:
1 |
SELECT @out_id, @out_name, @plane_count, @avg_wingspan, @avg_length; |
Figure 7 shows the results returned by the SELECT
statement.
The figure shows the results when you specify beechcraft
as the input value when calling the stored procedure. If you were to specify another value, such as airbus
, your SELECT
statement would return much different results, as shown in Figure 8.
Both IN
and OUT
parameters can make stored procedures far more flexible when supporting data-driven applications. You might also encounter situations when you want to use an INOUT
parameter. For example, you might create a stored procedure that includes some type of counter. You can use an INOUT
parameter to set the counter’s initial value and then return the new counter value based on the routine’s output.
Altering a stored procedure in MySQL
MySQL supports the ALTER
PROCEDURE
statement for updating a procedure’s characteristics. You cannot use this statement to make any other changes to a procedure definition. You are limited to characteristics only. For example, the following ALTER
PROCEDURE
statement adds two characteristics to the procedure definition, but the rest of the procedure definition will remain unchanged:
1 2 3 |
ALTER PROCEDURE get_plane_info READS SQL DATA SQL SECURITY INVOKER; |
The READS
SQL
DATA
characteristic indicates that the routine includes statements that read data. This type of characteristic is advisory only and does not constrain the routine in any way. The SQL
SECURITY
INVOKER
characteristic indicates that the routine should run under the security context of the user account that invokes the routine rather than the definer account.
After you run the ALTER
PROCEDURE
statement, you can verify that the characteristics have been added by viewing the procedure definition on the Stored Procedure tab, which is shown in Figure 9.
Notice that the CREATE
PROCEDURE
statement now includes three characteristics: the two you just added and the original COMMENT
characteristic that you added earlier.
Working with stored procedures in MySQL
Stored procedures can offer a great deal of flexibility, while helping to streamline application development. However, a procedure is only as effective as its underlying routine and the SQL statements it contains. In this exercise, I showed you how to create a stored procedure whose routine contained a single SELECT
statement, but you can build routines that define far more complex logic. Later in the series, I’ll be demonstrating how to create more robust compound statements that you can build into your stored procedures or use for other types of queries.
Load comments