Working with MySQL Stored Procedures

The series so far:

1. Getting started with MySQL
2. Working with MySQL tables
3. Working with MySQL views

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:

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:

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:

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.

Figure 1. Adding a stored procedure through the Workbench GUI

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.)

Figure 2. Viewing the stored procedure in Navigator

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.

Figure 3. Viewing the procedure definition on the Stored Procedure tab

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:

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:

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.

Figure 4. Examining the stored procedure’s routine body in Viewer

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:

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.

Figure 5. Viewing the results after calling the stored procedure

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:

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:

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:

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:

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.

Figure 6. Calling a stored procedure with an input parameter

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:

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:

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:

SELECT @out_id, @out_name, @plane_count, @avg_wingspan, @avg_length;

Figure 7 shows the results returned by the SELECT statement.

Figure 7. Viewing the routine’s output parameter values for Beechcraft planes

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.

Figure 8. Viewing the routine’s output parameter values for Airbus planes

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:

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.

Figure 9. Viewing the procedure definition on the Stored Procedure tab

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.

 

The post Working with MySQL Stored Procedures appeared first on Simple Talk.

from Simple Talk https://ift.tt/7WCkFZi
via