A MySQL stored procedure contains a set of SQL statements stored separately in the MySQL database. It's a powerful feature of MySQL that enables developers to write complex SQL queries and perform repetitive tasks in a more efficient and structured manner. Stored procedures are similar to functions in programming languages and can be called and executed from other SQL statements or programs.
In terms of performance, stored procedures offer several benefits. Since the code is executed on the server side, stored procedures can reduce the amount of network traffic between the application and the database server. This can result in faster execution times and lower network latency. Additionally, stored procedures can be optimized for performance by using efficient algorithms and data structures. They can also be cached in memory, which can further improve execution times.
Storing the procedures inside the MySQL database is more secure than using ad hoc SQL queries. This is because ad hoc queries are placed inside the application file. As any unauthorized person who has access to the application source code can see the queries and spot the original table and column names, this could cause potential security issues. You can avoid this with stored procedures, since you can grant database users permission to access the stored procedures without giving them access to the tables.
In this article, we'll walk through how to create and execute stored procedures using MySQL. We'll also learn how to create a stored procedure with various parameter options. Finally, we'll see how to automate some repetitive tasks such as backup, user creation, and user permission management using MySQL stored procedures.
MySQL stored procedure syntax
Learning how to set up a stored procedure is relatively straightforward since it combines standard SQL queries and executes them together. In simple terms, we assign a name to multiple SQL queries, and when we call the stored procedure, these multiple queries are executed as a single unit.
The following is the syntax for creating a stored procedure in MySQL:
In the above code:
CREATE PROCEDUREis the standard command for creating procedures.
procedure_name()represents the name of the stored procedure. When naming it, we must make sure we use the naming rules of MySQL, where names are case-sensitive, need to start with a letter, and can't have any spaces.
- Parameters are optional, and we can use them to pass values to the procedure.
- All SQL queries need to be written between
Note that a semicolon (
;) is the default delimiter used to separate multiple SQL queries between
END. However, MySQL won't consider the stored procedure as a single statement if it encounters a semicolon and will try to execute queries immediately instead of saving the procedure. In other words, MySQL will try to execute the code whenever it encounters a semicolon, leading to an error when executing the stored procedure. To avoid this behavior, you can use
DELIMITER $$ to instruct MySQL to consider
$$ as the delimiter for the purposes of the stored procedure, then use
DELIMITER ; to reset to the default delimiter after the procedure.
Creating a simple stored procedure
Using the above syntax, we can create a simple stored procedure. The following code will create a stored procedure that prints "Hello World" as an output:
In this code,
sayHello() is the procedure name. The
SELECT command with the single quote prints the output.
To execute the stored procedure, use the
CALL command with the procedure name:
Viewing and dropping stored procedures
We can view any existing stored procedures by querying the
routines table in MySQL's
Use the following query to obtain a list of stored procedures for a specific MySQL database:
This query will return the names of the stored procedures along with their associated metadata, such as the routine type, definer, creation date, security type, and SQL data access.
If necessary, we can use the following command to drop a stored procedure:
Creating a parameterized stored procedure
In order to create a dynamic query, we need to define a placeholder within the query. This placeholder will be assigned a value when we call the stored procedure. A parameter is used to accomplish this. For instance, if we need to change a user's password, we would pass the new password as an input parameter to the stored procedure.
A MySQL stored procedure has three modes for a parameter:
IN: If a parameter is declared as
IN, it's used to accept input values, and we cannot assign a value to this parameter inside the stored procedure. We can use the
INparameter in the
WHEREcondition to filter data. It can also be used with the
CREATEcommand. If we don't specify the parameter mode, the default mode is
OUT: If a parameter is declared as
OUT, it's used to assign values to the parameter inside the stored procedure. We can access the assigned value of this parameter outside of the stored procedure.
INOUT: If a parameter is declared as
INOUT, we can use both input and output values for it.
The following is the syntax for adding parameters to the stored procedure:
Using that syntax, the following code will create a stored procedure that changes the password of a user and includes parameters using the
In this code, the stored procedure is
ALTER command will change the password for the given username.
To execute the stored procedure, use the
CALL command with the procedure name:
In this example, the stored procedure
changePassword() takes two parameters: a username (
'erp_user1') and a new password (
Automating repetitive tasks using stored procedures
Repetitive tasks such as creating users, changing user passwords, making backups of table data, managing user permissions, and moving old data to secondary tables often require the same set of standard SQL queries. However, manually changing the input values every time we execute these queries can be time-consuming and tedious. To simplify these tasks, we can create stored procedures with parameters that allow us to automate these processes by passing input values to the queries at runtime. This can save us time and effort in the long run. This section highlights a few examples of repetitive tasks that can be automated by stored procedures.
Backing up a table
To back up an entire table's data in MySQL, we can use the
SELECT … INTO OUTFILE command, which writes the selected data to a file. The following is the syntax for this command:
Before writing the stored procedure, we must tell MySQL where to store the output file. The default file location is
var/lib/mysql-files. However, this location can be modified using the
mysql.ini file if needed.
Note that MySQL will not allow us to write to the output file if the location is not properly configured. To check the configured output file location, we can use the following command:
The following example creates a stored procedure to back up table data using
SELECT … INTO OUTFILE:
Execute this stored procedure using the
Creating or removing a user
Creating and removing users in the MySQL database can also be automated using stored procedures.
We can create a user and assign it to the database in MySQL using the following command:
The following code creates a stored procedure to automate this task:
When using stored procedure parameters, we cannot use them directly inside Data Definition Language (DDL) and Data Control Language (DCL) commands like
GRANT, and others. This is because DDL and DCL commands cannot directly consume parameter values. To work around this, we can use the
CONCAT function to generate the query string with the parameter values included. We can then execute the generated query using the
To use the
createUser() stored procedure, pass in the username, password, and database name, and execute it using the
Similarly, we can create a stored procedure to remove a user:
Pass the username as input and execute the stored procedure to remove the user from the database:
Granting and revoking permissions
We can also create a stored procedure to help reduce the workload around managing user permissions.
The following code creates a stored procedure to add or remove user permissions:
grantPermission() stored procedure by running the following command:
The following code will create a stored procedure to remove user permission:
revokePermission() stored procedure by running the following command:
And that's how use MySQL stored procedure. Although MySQL stored procedure has many benefits, it can be difficult to maintain and challenging to debug. Luckily, there is an alternative platform available for automating repetitive tasks easily.
Introducing Airplane: An easy way to automate repetitive tasks
Author: Bharathiraja Shanmugam