{"id":93857,"date":"2022-04-15T16:19:47","date_gmt":"2022-04-15T16:19:47","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=93857"},"modified":"2022-04-15T16:23:23","modified_gmt":"2022-04-15T16:23:23","slug":"working-with-mysql-stored-procedures","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/working-with-mysql-stored-procedures\/","title":{"rendered":"Working with MySQL Stored Procedures"},"content":{"rendered":"

This article is part of Robert Sheldon's continuing series on Learning MySQL. To see all of the items in the series, click here<\/a>.<\/strong><\/p>\n\n

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.\u00a0<\/p>\n

A procedure\u2019s 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.<\/p>\n

Stored procedures enable SQL code to be reused as often as needed, helping to simplify application development and reduce statement errors. Developers don\u2019t have to write complex queries for each application request, and QA teams don\u2019t need to spend as much time verifying queries when testing applications.<\/p>\n

The ability to reuse code also reduces network traffic because a stored procedure can be invoked with a single CALL<\/code> 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.<\/p>\n

In this article, I demonstrate how to create and update stored procedures, as well as invoke them with a CALL<\/code> statement. You\u2019ll 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.<\/p>\n

Preparing your MySQL environment<\/h2>\n

The examples in this article are based on the travel<\/code> database, which is the same database I used for the previous article on MySQL views<\/a>. 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<\/code> 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:<\/p>\n

DROP DATABASE IF EXISTS travel;\r\nCREATE DATABASE travel;\r\nUSE travel;\r\nCREATE TABLE manufacturers (\r\n  manufacturer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,\r\n  manufacturer VARCHAR(50) NOT NULL,\r\n  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\r\n  last_update TIMESTAMP NOT NULL \r\n    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\r\n  PRIMARY KEY (manufacturer_id) ) \r\nENGINE=InnoDB AUTO_INCREMENT=1001;\r\nCREATE TABLE airplanes (\r\n  plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT,\r\n  plane VARCHAR(50) NOT NULL,\r\n  manufacturer_id INT UNSIGNED NOT NULL,\r\n  engine_type VARCHAR(50) NOT NULL,\r\n  engine_count TINYINT NOT NULL,\r\n  max_weight MEDIUMINT UNSIGNED NOT NULL,\r\n  wingspan DECIMAL(5,2) NOT NULL,\r\n  plane_length DECIMAL(5,2) NOT NULL,\r\n  parking_area INT GENERATED ALWAYS AS \r\n              ((wingspan * plane_length)) STORED,\r\n  icao_code CHAR(4) NOT NULL,\r\n  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\r\n  last_update TIMESTAMP NOT NULL \r\n    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\r\n  PRIMARY KEY (plane_id),\r\n  CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id) \r\n    REFERENCES manufacturers (manufacturer_id) ) \r\nENGINE=InnoDB AUTO_INCREMENT=101;<\/pre>\n
The airplanes<\/code> table includes a foreign key that references the manufacturers<\/code> 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\u2019ll be able to test your stored procedure. To populate the table, run the following INSERT<\/code> statements:<\/p>\n
INSERT INTO manufacturers (manufacturer)\r\nVALUES ('Airbus'), ('Beechcraft'), ('Piper');\r\nINSERT INTO airplanes \r\n  (plane, manufacturer_id, engine_type, engine_count, \r\n    max_weight, wingspan, plane_length, icao_code)\r\nVALUES \r\n  ('A380-800', 1001, 'jet', 4, 1267658, 261.65, 238.62, 'A388'),\r\n  ('A319neo Sharklet', 1001, 'jet', 2, 166449, 117.45, 111.02, 'A319'),\r\n  ('ACJ320neo (Corporate Jet version)', 1001, 'jet', 2, 174165, 117.45, 123.27, 'A320'),\r\n  ('A300-200 (A300-C4-200, F4-200)', 1001, 'jet', 2, 363760, 147.08, 175.50, 'A30B'),\r\n  ('Beech 390 Premier I, IA, II (Raytheon Premier I)', 1002, 'jet', 2, 12500, 44.50, 46.00, 'PRM1'),\r\n  ('Beechjet 400 (from\/same as MU-300-10 Diamond II)', 1002, 'jet', 2, 15780, 43.50, 48.42, 'BE40'),\r\n  ('1900D', 1002, 'Turboprop', 2,17120,  57.75, 57.67, 'B190'),\r\n  ('PA-24-400 Comanche', 1003, 'piston', 1, 3600, 36.00, 24.79, 'PA24'),\r\n  ('PA-46-600TP Malibu Meridian, M600', 1003, 'Turboprop', 1, 6000, 43.17, 29.60, 'P46T'),\r\n  ('J-3 Cub', 1003, 'piston', 1, 1220, 38.00, 22.42, 'J3');<\/pre>\n
As with the CREATE<\/code> TABLE<\/code> statements, you should run the INSERT<\/code> statements in the order specified here so you don\u2019t violate the foreign key defined on the airplanes<\/code> table.<\/p>\n
Creating a stored procedure in MySQL<\/h2>\n
To build a stored procedure in MySQL, you must use a CREATE<\/code> PROCEDURE<\/code> 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<\/code> statement.) For this example, you\u2019ll use the travel<\/code> database.<\/p>\n
When building your CREATE<\/code> PROCEDURE<\/code> 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<\/code> or UPDATE<\/code>, or it can be a compound statement. A compound statement is one that uses the BEGIN\u2026END<\/code> 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.<\/p>\n
Most stored procedures use a compound statement even if they include only a single SQL statement. For example, the routine in the following CREATE<\/code> PROCEDURE<\/code> statement includes a compound statement with only one SELECT<\/code> statement:<\/p>\n
DELIMITER \/\/\r\nCREATE PROCEDURE get_plane_info()\r\nBEGIN\r\n  SELECT a.manufacturer_id, m.manufacturer, \r\n    COUNT(*) AS plane_count,\r\n    ROUND(AVG(a.wingspan), 2) AS avg_span, \r\n    ROUND(AVG(a.plane_length), 2) AS avg_length\r\n  FROM airplanes a INNER JOIN manufacturers m\r\n    ON a.manufacturer_id = m.manufacturer_id\r\n  GROUP BY a.manufacturer_id\r\n  ORDER BY m.manufacturer;\r\nEND\/\/\r\n  \r\nDELIMITER ;<\/pre>\n
The example creates a procedure named get_plane_info<\/code>. 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\u2019ll cover later in the article). If you don\u2019t include parameters, you must still provide the parentheses.<\/p>\n
The compound statement is defined by the BEGIN\u2026END<\/code> syntax, which encloses the single SELECT<\/code> statement. The SELECT<\/code> statement itself joins the airplanes<\/code> and manufacturers<\/code> tables, groups the data by the manufacturer_id<\/code> column in the airplanes<\/code> table, and calculates the average wingspan<\/code> and plane_length<\/code> values for each manufacturer. The statement also orders the results by manufacturer and provides the total number of plane models for each one. (We\u2019ll be covering all these statement elements in more detail later in this series.)<\/p>\n
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<\/code> statements that surround the procedure definition.<\/p>\n
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\u2019s final delimiter, and all these delimiters can cause confusion when passing the CREATE<\/code> PROCEDURE<\/code> statement from a client to the server.<\/p>\n
To get around this issue, MySQL supports the use of the DELIMITER<\/code> 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<\/code> statement changes the delimiter to double forward slashes (\/\/), and the second DELIMITER<\/code> statement changes the delimiter back to a semi-colon. The temporary delimiter is then used at the end of the CREATE<\/code> PROCEDURE<\/code> statement (after the END<\/code> keyword), but the SELECT<\/code> statement itself is still terminated with the semi-colon delimiter.<\/p>\n
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<\/code> PROCEDURE<\/code> statement but leaves it up to you to fill in the details. Figure 1 shows the Stored Procedure<\/em> tab as it appears when you first launch it in Workbench.<\/p>\n
\"The<\/p>\n
Figure 1. Adding a stored procedure through the Workbench GUI<\/p>\n
To launch the Stored Procedure<\/em> 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<\/em>.) When the Stored Procedure<\/em> tab appears, you can start building your statement. After you finish, click Apply.<\/em> MySQL will then add a few statement components that are necessary to create the procedure. Review the final script, click Apply<\/em> once more, and then click Finish<\/em>. The stored procedure will be added to the target database.<\/p>\n
The Stored Procedure<\/em> tab can be useful for creating and editing a stored procedure, so I wanted to be sure you knew it\u2019s available. However, I prefer to use a query tab when working with a stored procedure because I think it\u2019s easier and saves steps, so this is the approach I take for the examples in this article.<\/p>\n
Verifying a newly created stored procedure<\/h2>\n
After you run the CREATE<\/code> PROCEDURE<\/code> statement, you can verify that it\u2019s been added to the travel<\/code> database by viewing it in Navigator, as shown in Figure 2. (You might need to refresh Navigator to see the new procedure.)<\/p>\n
\"Image<\/p>\n
Figure 2. Viewing the stored procedure in Navigator<\/p>\n
From Navigator, you can open the procedure definition in the Stored Procedure<\/em> 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<\/code> clause after the CREATE<\/code> keyword.<\/p>\n
\"Image<\/p>\n
Figure 3. Viewing the procedure definition on the Stored Procedure<\/em> tab<\/p>\n
The DEFINER<\/code> clause specifies which account has been designated as the procedure creator. Because I created the stored procedure under the root<\/code> 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<\/code> PROCEDURE<\/code> statement, but you can specify a different account as long as it\u2019s been granted adequate permissions.<\/p>\n
Other than the DEFINER<\/code> clause, your stored procedure definition should look much like what you created, except that there are no DELIMITER<\/code> statements or custom delimiter. However, if you were to update the definition on the Stored Procedure<\/em> tab and click Apply,<\/em> Workbench would add those elements for you.<\/p>\n
Another way to verify whether your stored procedure has been created is to query the routines<\/code> view in the INFORMATION_SCHEMA<\/code> database:<\/p>\n
SELECT * FROM information_schema.routines\r\nWHERE routine_schema = 'travel';<\/pre>\n
The statement includes a WHERE<\/code> clause that limits the results to the travel<\/code> database. Any routines (stored procedures or functions) that have been created in the database will be returned by this query.<\/p>\n
You can limit the results further by also specifying the procedure name in the WHERE<\/code> clause and by specifying which column or columns to return. For example, the following SELECT<\/code> statement limits the results to the routine_definition<\/code> column and the get_plane_info<\/code> routine in the travel<\/code> database:<\/p>\n
SELECT routine_definition\r\nFROM information_schema.routines\r\nWHERE routine_schema = 'travel'\r\n  AND routine_name = 'get_plane_info';<\/pre>\n
Although the statement returns only a single value, it can still be difficult to read, especially if it\u2019s 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<\/em>. Select Text if it\u2019s not already selected. MySQL launches a separate window that displays the value, as shown in Figure 4.<\/p>\n
\"Image<\/p>\n
Figure 4. Examining the stored procedure\u2019s routine body in Viewer<\/p>\n
Of course, verifying that the stored procedure exists doesn\u2019t 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<\/code> statement that specifies the procedure name, as shown in the following example:<\/p>\n
CALL get_plane_info;<\/pre>\n
When you call the procedure, MySQL runs the stored routine and returns the statement results, which are shown in Figure 5.<\/p>\n
\"Image<\/p>\n
Figure 5. Viewing the results after calling the stored procedure<\/p>\n
As expected, the CALL<\/code> statement returns the aggregated airplane data, grouped by manufacturer. These are the same results that you would have received if you ran the routine\u2019s SELECT<\/code> statement directly. However, the statement is now persisted to the database, eliminating the need to write the statement at the application level.<\/p>\n
Adding an input parameter to a stored procedure<\/h2>\n
The get_plane_info<\/code> 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.<\/p>\n
In this section, I demonstrate how to add an input parameter to the procedure definition. (I\u2019ll 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\u2019s characteristics, but nothing more. To make any significant updates, you must drop the procedure and then re-create it, incorporating any new elements.<\/p>\n
To drop a stored procedure, you can use a DROP<\/code> PROCEDURE<\/code> statement, as shown in the following example:<\/p>\n
DROP PROCEDURE IF EXISTS get_plane_info;<\/pre>\n
The IF<\/code> EXISTS<\/code> 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<\/code> view in the INFORMATION_SCHEMA<\/code> database:<\/p>\n
SELECT * FROM information_schema.routines\r\nWHERE routine_schema = 'travel';<\/pre>\n
The statement should now return an empty result set, unless you created other stored procedures or functions.<\/p>\n
After you delete the get_plane_info<\/code> stored procedure, you can update your CREATE<\/code> PROCEDURE<\/code> statement to include an input parameter. For each parameter, you should specify the parameter type, the parameter name, and the parameter\u2019s data type. MySQL supports three parameter types:<\/p>\n
    \n
  • IN.<\/strong> Input parameter that passes a value from the caller into the procedure\u2019s routine.<\/li>\n
  • OUT.<\/strong> Output parameter that passes a value from the routine back to the caller.<\/li>\n
  • INOUT.<\/strong> Parameter that can be initialized by the caller, updated by the routine, and then returned to the caller with its new value.<\/li>\n<\/ul>\n
    The following CREATE<\/code> PROCEDURE<\/code> statement includes one input parameter, which is named in_name<\/code> and defined with the VARCHAR(50)<\/code> data type:<\/p>\n
    DELIMITER \/\/\r\nCREATE PROCEDURE get_plane_info(\r\n  IN in_name VARCHAR(50))\r\nCOMMENT 'retrieves aggregated airplane information'\r\nBEGIN\r\n  SELECT a.manufacturer_id, m.manufacturer, \r\n    COUNT(*) AS plane_count,\r\n    ROUND(AVG(a.wingspan), 2) AS avg_span, \r\n    ROUND(AVG(a.plane_length), 2) AS avg_length\r\n  FROM airplanes a INNER JOIN manufacturers m\r\n    ON a.manufacturer_id = m.manufacturer_id\r\n  WHERE m.manufacturer = in_name;\r\nEND\/\/\r\nDELIMITER ;<\/pre>\n
    The parameter definition is enclosed in parentheses and includes the IN<\/code> keyword, parameter name, and data type. I also updated the SELECT<\/code> statement to reflect the use of the parameter. It no longer includes the GROUP<\/code> BY<\/code> and ORDER<\/code> BY<\/code> clauses but now includes a WHERE<\/code> clause that compares the in_name<\/code> parameter to the manufacturer<\/code> column. In this way, the caller can specify the manufacturer on which to base the query.<\/p>\n
    The CREATE<\/code> PROCEDURE<\/code> statement also includes the COMMENT<\/code> characteristic, which appends a comment to the procedure definition. You can include one or more characteristics in a CREATE<\/code> PROCEDURE<\/code> 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\u2019s nature.<\/p>\n
    When calling a stored procedure that takes an input parameter, you must include the parameter value in parentheses. If it\u2019s a character value, you must enclose it in single quotes. For example, the following CALL<\/code> statement specifies piper<\/code> as the value for the procedure\u2019s input parameter:<\/p>\n
    CALL get_plane_info ('piper');<\/pre>\n
    When MySQL runs the procedure\u2019s routine, it substitutes the piper<\/code> value for the in_name<\/code> parameter specified in the WHERE<\/code> clause. Figure 6 shows the results now returned by the stored procedure.<\/p>\n
    \"Image<\/p>\n
    Figure 6. Calling a stored procedure with an input parameter<\/p>\n
    When defining your stored procedure, you can include multiple IN<\/code> 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<\/code> parameters or INOUT<\/code> parameters alongside the input parameters.<\/p>\n
    Adding output parameters to a stored procedure<\/h2>\n
    Now let\u2019s look at how to add multiple OUT<\/code> parameters to the get_plane_info<\/code> 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\u2019ll add five output parameters, which will correspond to the columns specified in the routine\u2019s SELECT<\/code> list.<\/p>\n
    To add the parameters, you\u2019ll need to again drop the procedure and then run an updated CREATE<\/code> PROCEDURE<\/code> statement. The output parameters are specified within the same parentheses as the input parameter, as shown in the following script:<\/p>\n
    DROP PROCEDURE IF EXISTS get_plane_info;\r\nDELIMITER \/\/\r\nCREATE PROCEDURE get_plane_info(\r\n  IN in_name VARCHAR(50),\r\n  OUT out_id INT UNSIGNED,\r\n  OUT out_name VARCHAR(50),\r\n  OUT plane_count SMALLINT UNSIGNED,\r\n  OUT avg_wingspan DECIMAL(5,2), \r\n  OUT avg_length DECIMAL(5,2))\r\nCOMMENT 'retrieves aggregated airplane information'\r\nBEGIN\r\n  SELECT a.manufacturer_id, m.manufacturer, \r\n    COUNT(*),\r\n    ROUND(AVG(a.wingspan), 2), \r\n    ROUND(AVG(a.plane_length), 2)\r\n  INTO out_id, out_name, plane_count, avg_wingspan, avg_length\r\n  FROM airplanes a INNER JOIN manufacturers m\r\n    ON a.manufacturer_id = m.manufacturer_id\r\n  WHERE m.manufacturer = in_name;\r\nEND\/\/\r\nDELIMITER ;<\/pre>\n
    For each output parameter, you must specify the OUT<\/code> keyword, the parameter name, and the parameter\u2019s data type. In addition, you must add an INTO<\/code> clause after the SELECT<\/code> list that returns the results to the output parameters. I\u2019ve also removed the column aliases from the SELECT<\/code> list because they\u2019re no longer needed.<\/p>\n
    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<\/code> statement:<\/p>\n
    CALL get_plane_info ('beechcraft', @out_id, @out_name, \r\n  @plane_count, @avg_wingspan, @avg_length);<\/pre>\n
    The CALL<\/code> statement specifies beechcraft<\/code> 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<\/code> statement, the returned parameter values are assigned to the variables.<\/p>\n
    The exact way in which you\u2019ll handle output parameters in your application will depend on the programming language that you\u2019re using. In the meantime, you can then verify that your variables contain the expected values by running a SELECT<\/code> statement similar to the following:<\/p>\n
    SELECT @out_id, @out_name, @plane_count, @avg_wingspan, @avg_length;<\/pre>\n
    Figure 7 shows the results returned by the SELECT<\/code> statement.<\/p>\n
    \"Image<\/p>\n
    Figure 7. Viewing the routine\u2019s output parameter values for Beechcraft planes<\/p>\n
    The figure shows the results when you specify beechcraft<\/code> as the input value when calling the stored procedure. If you were to specify another value, such as airbus<\/code>, your SELECT<\/code> statement would return much different results, as shown in Figure 8.<\/p>\n
    \"Image<\/p>\n
    Figure 8. Viewing the routine\u2019s output parameter values for Airbus planes<\/p>\n
    Both IN<\/code> and OUT<\/code> 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<\/code> parameter. For example, you might create a stored procedure that includes some type of counter. You can use an INOUT<\/code> parameter to set the counter\u2019s initial value and then return the new counter value based on the routine\u2019s output.<\/p>\n
    Altering a stored procedure in MySQL<\/h2>\n
    MySQL supports the ALTER<\/code> PROCEDURE<\/code> statement for updating a procedure\u2019s 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<\/code> PROCEDURE<\/code> statement adds two characteristics to the procedure definition, but the rest of the procedure definition will remain unchanged:<\/p>\n
    ALTER PROCEDURE get_plane_info\r\nREADS SQL DATA\r\nSQL SECURITY INVOKER;<\/pre>\n
    The READS<\/code> SQL<\/code> DATA<\/code> 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<\/code> SECURITY<\/code> INVOKER<\/code> characteristic indicates that the routine should run under the security context of the user account that invokes the routine rather than the definer account.<\/p>\n
    After you run the ALTER<\/code> PROCEDURE<\/code> statement, you can verify that the characteristics have been added by viewing the procedure definition on the Stored Procedure<\/em> tab, which is shown in Figure 9.<\/p>\n
    \"Image<\/p>\n
    Figure 9. Viewing the procedure definition on the Stored Procedure<\/em> tab<\/p>\n
    Notice that the CREATE<\/code> PROCEDURE<\/code> statement now includes three characteristics: the two you just added and the original COMMENT<\/code> characteristic that you added earlier.<\/p>\n
    Working with stored procedures in MySQL<\/h2>\n
    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<\/code> statement, but you can build routines that define far more complex logic. Later in the series, I\u2019ll be demonstrating how to create more robust compound statements that you can build into your stored procedures or use for other types of queries.<\/p>\n
     <\/p>\n","protected":false},"excerpt":{"rendered":"
    MySQL stored procedures are objects containing one or more SQL statements for reuse. They often contain business logic. Robert Sheldon demonstrates how to create and call stored procedures.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,145792],"tags":[95506],"coauthors":[6779],"class_list":["post-93857","post","type-post","status-publish","format-standard","hentry","category-featured","category-mysql","tag-automate"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93857","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=93857"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93857\/revisions"}],"predecessor-version":[{"id":93873,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93857\/revisions\/93873"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=93857"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=93857"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=93857"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=93857"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}