How To Use Stored Procedures in MySQL

Typically, when working with a relational database, you issue individual Structured Query Language (SQL) queries to retrieve or manipulate data, like SELECT, INSERT, UPDATE or DELETE, directly from within your application code. Those statements work on and manipulate underlying database tables directly. If the same statements or group of statements are used within multiple applications accessing the same database, they are often duplicated in individual applications.

MySQL, similar to many other relational database management systems, supports the use of stored procedures. Stored procedures help group one or multiple SQL statements for reuse under a common name, encapsulating common business logic within the database itself. Such a procedure can be called from the application that accesses the database to retrieve or manipulate data in a consistent way.

Using stored procedures, you can create reusable routines for common tasks to be used across multiple applications, provide data validation, or deliver an additional layer of data access security by restricting database users from accessing the underlying tables directly and issuing arbitrary queries.

In this tutorial, you’ll learn what stored procedures are and how to create basic stored procedures that return data and use both input and output parameters.

Connecting to MySQL and Setting up a Sample Database

In this section, you will connect to a MySQL server and create a sample database so that you can follow the examples in this guide.

For this guide, you’ll use an imaginary car collection. You’ll store details about currently owned cars, with their make, model, build year, and value.

If your SQL database system runs on a remote server, SSH into your server from your local machine:

ssh sammy@your_server_ip

Then open up the MySQL server prompt, replacingsammywith the name of your MySQL user account:

mysql -u sammy-p

Create a database namedprocedures:

CREATE DATABASEprocedures;

If the database was created successfully, you’ll receive output like this:

OutputQuery OK, 1 row affected (0.01 sec)

To select the procedures database, run the following USE statement:

USEprocedures;

You will receive the following output:

ssh sammy@your_server_ip

After selecting the database, you can create sample tables within it. The table cars will contain simplified data about cars in the database. It will hold the following columns:

• make: This column holds the make for each owned car, expressed using the varchar data type with a maximum of 100 characters.
• model: This column holds the car model name, expressed using the varchar data type with a maximum of 100 characters.
• year: This column stores the car’s build year with int data type to hold numerical values.
• value: This column stores the car’s value using the decimal data type with a maximum of 10 digits and 2 digits after the decimal point.

Create the sample table with the following command:

mysql -u sammy-p

If the following output prints, the table has been created:

CREATE DATABASEprocedures;

Following that, load the cars table with some sample data by running the following INSERT INTO operation:

OutputQuery OK, 1 row affected (0.01 sec)

The INSERT INTO operation will add ten sample sports cars to the table, with five Porsche and five Ferrari models. The following output indicates that all five rows have been added:

ssh sammy@your_server_ip

With that, you’re ready to follow the rest of the guide and begin using stored procedures in SQL.

Introduction to Stored Procedures

Stored procedures in MySQL and in many other relational database systems are named objects that contain one or more instructions laid out and then executed by the database in a sequence when called. In the most basic example, a stored procedure can save a common statement under a reusable routine, such as retrieving data from the database with often-used filters. For example, you could create a stored procedure to retrieve online store customers who made orders within the last given number of months. In the most complex scenarios, stored procedures can represent extensive programs describing intricate business logic for robust applications.

The set of instructions in a stored procedure can include common SQL statements, such as SELECT or INSERT queries, that return or manipulate data. Additionally, stored procedures can make use of:

• Parameters passed to the stored procedure or returned through it.
• Declared variables to process retrieved data directly within the procedure code.
• Conditional statements, which allow the execution of parts of the stored procedure code depending on certain conditions, such as IF or CASE instructions.
• Loops, such as WHILE, LOOP, and REPEAT, allow executing parts of the code multiple times, such as for each row in a retrieved data set.
• Error handling instructions, such as returning error messages to the database users accessing the procedure.
• Calls to other stored procedures in the database.

When the procedure is called by its name, the database engine executes it as defined, instruction by instruction.

The database user must have the appropriate permissions to execute the given procedure. This permissions requirement provides a layer of security, disallowing direct database access while giving users access to individual procedures that are guaranteed safe to execute.

Stored procedures are executed directly on the database server, performing all computations locally and returning results to the calling user only when finished.

If you want to change the procedure behavior, you can update the procedure in the database, and the applications that are using it will automatically pick up the new version. All users will immediately start using the new procedure code without needing to adjust their applications.

Here is the general structure of the SQL code used to create a stored procedure:

mysql -u sammy-p

The first and last instructions in this code fragment are DELIMITER // and DELIMITER ;. Usually, MySQL uses the semicolon symbol (;) to delimit statements and indicate when they start and end. If you execute multiple statements in the MySQL console separated with semicolons, they will be treated as separate commands and executed independently, one after another. However, the stored procedure can enclose multiple commands that will be executed sequentially when it gets called. This poses a difficulty when trying to tell MySQL to create a new procedure. The database engine would encounter the semicolon sign in the stored procedure body and think it should stop executing the statement. In this situation, the intended statement is the whole procedure creation code, not a single instruction within the procedure itself, so MySQL would misinterpret your intentions.

To work around this limitation, you use the DELIMITER command to temporarily change the delimiter from ; to // for the duration of the CREATE PROCEDURE call. Then, all semicolons inside the stored procedure body will be passed to the server as-is. After the whole procedure is finished, the delimiter is changed back to ; with the last DELIMITER ;.

The heart of the code to create a new procedure is the CREATE PROCEDURE call followed by the name of the procedure: procedure_name in the example. The procedure name is followed by an optional list of parameters the procedure will accept. The last part is the procedure body, enclosed in BEGIN and END statements. Inside is the procedure code, which can contain a single SQL statement such as a SELECT query or more complex code.

The END command ends with //, a temporary delimiter, instead of a typical semicolon.

In the next section, you’ll create a basic stored procedure with no parameters enclosing a single query.

Creating a Stored Procedure Without Parameters

In this section, you’ll create your first stored procedure encapsulating a single SQL SELECT statement to return the list of owned cars ordered by their make and value in descending order.

Start by executing the SELECT statement that you’re going to use:

ssh sammy@your_server_ip

The database will return the list of cars from the cars table, first ordered by make and then, within a single make, by value in descending order:

mysql -u sammy-p

The most valuable Ferrari is at the top of the list, and the least valuable Porsche appears at the bottom.

Assume this query will be used frequently in multiple applications or by multiple users and assume you want to ensure everyone will use the exact same way of ordering the results. To do so, you want to create a stored procedure that will save that statement under a reusable named procedure.

To create this stored procedure, execute the following code fragment:

CREATE DATABASEprocedures;

As described in the previous section, the first and last commands (DELIMITER // and DELIMITER ;) tell MySQL to stop treating the semicolon character as the statement delimiter for the duration of procedure creation.

The CREATE PROCEDURE SQL command is followed by the procedure nameget_all_cars, which you can define to best describe what the procedure does. After the procedure name, there is a pair of parentheses () where you can add parameters. In this example, the procedure doesn’t use parameters, so the parentheses are empty. Then, between the BEGIN and END commands defining the beginning and end of the procedure code block, the previously used SELECT statement is written verbatim.

The database will respond with a success message:

ssh sammy@your_server_ip

Theget_all_carsprocedure is now saved in the database, and when called, it will execute the saved statement as is.

To execute saved stored procedures, you can use the CALL SQL command followed by the procedure name. Try running the newly created procedure like so:

mysql -u sammy-p

The procedure name,get_all_cars, is all you need to use the procedure. You no longer need to manually type any part of the SELECT statement you used previously. The database will display the results just like the output from the SELECT statement run before:

CREATE DATABASEprocedures;

You have now successfully created a stored procedure without any parameters that return all cars from the cars table ordered in a particular way. You can use the procedure across multiple applications.

In the next section, you will create a procedure that accepts parameters to change the procedure behavior depending on user input.

The above is the detailed content of How To Use Stored Procedures in MySQL. For more information, please follow other related articles on the PHP Chinese website!