How to Implement Stored Procedures on an Alibaba Cloud MySQL Server

This article discusses how to create, execute, and implement stored procedures on a MySQL server hosted on an Alibaba Cloud ECS or ApsaraDB RDS for MySQL instance.

By Francis Ndungu, Alibaba Cloud Community Blog author.

Stored procedures (SP) are user-defined functions created on a MySQL server and executed on-demand to run specific tasks.

There are two methods of executing queries on a MySQL database. In the first method, MySQL queries are executed from the client side. Alternatively MySQL statements may be stored and executed from the MySQL server. The second method forms the basis of stored procedures.

Generally, if your workflow and business logic can permit the use of stored procedures, then, this approach is recommended. This is because, stored procedures help to reduce network traffic, improve database performance, aid in code interoperability and increase database security. We will see these benefits later in details.

This article focuses on how to create, execute and implement stored procedures in a MySQL server hosted on an Alibaba Cloud ApsaraDB RDS for MySQL or Elastic Compute Service (ECS) instance.

Prerequisites

An Alibaba Cloud account. Sign up now and get up to $1200 worth of free trial credit.
An ECS instance or an ApsaraDB instance running MySQL server
Root access for the MySQL server.

Procedure

Step 1: Structure of MySQL Stored Procedures

Below is the basic syntax for creating MySQL stored procedure:

DELIMITER $$
   CREATE PROCEDURE [Procedure_Name]()
   BEGIN
       [SQL statement];
   END $$
 DELIMITER ;

Before we start creating our first procedure, let us first go over the syntax in details.

DELIMITER: By default, a MySQL server will recognize semicolons as a delimiter or boundary for each SQL statement. Because we want to pass the whole stored procedure instead of separated bits, we must first of all change the delimiter to a different value like '$$'. As such, we can notify the server when we are through writing the SP.
CREATE PROCEDURE: This statement allows us to define the name of our new procedure. Please note, the parenthesis following shortly after the name of our procedure.
BEGIN: The SQL statement following the BEGIN statement is the body of the named stored procedure.
END: The END statement signifies the ending of the body.

If you are just creating a single stored procedure, it is always a good practice to switch the delimiter back to a semicolon and this is why we have added the command below at the end of our statement.

DELIMITER ;

Step 2: Creating Sample Data

Now that we have established a basic understanding of how stored procedures are written, we can go ahead and create sample data for testing purposes.

First, log in to your MySQL server using the root account for the database. If you are using a terminal command, the basic syntax is shown below:

$ mysql -uroot -p

Enter your password when prompted and hit Enter to continue.

We are going to create a sample database named 'sample_store';

mysql> create database sample_store;

Switch to the database:

mysql> use sample_store;

Then, create a table to hold the customers' data.

mysql> create table 
customers(
customer_id BIGINT NOT NULL, 
customer_name VARCHAR(50), 
Balance DOUBLE, 
PRIMARY KEY (customer_id)
 ) ENGINE=InnoDB;

To ensure you have the right table schema, run the describe statement followed by the name of the table we have created:

mysql>Describe customers;

The output is as follows:

+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| customer_id   | bigint(20)  | NO   | PRI | NULL    |       |
| customer_name | varchar(50) | YES  |     | NULL    |       |
| Balance       | double      | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+

Next, we are going to add some sample data to the customers' table. To do this, run the SQL commands below one by one:

mysql>insert into customers (customer_id, customer_name, balance) VALUES ('1','JOHN DOE', '40000');
mysql>insert into customers (customer_id, customer_name, balance) VALUES ('2','MARY SMITH', '720');
mysql>insert into customers (customer_id, customer_name, balance) VALUES ('3','JANIE ROE', '2855');

Ensure the data was inserted correctly:

mysql>select * from customers;

The output is as follows:

+-------------+---------------+---------+
| customer_id | customer_name | Balance |
+-------------+---------------+---------+
|           1 | JOHN DOE      |   40000 |
|           2 | MARY SMITH    |     720 |
|           3 | JANIE ROE     |    2855 |
+-------------+---------------+---------+

Step 3: Creating a Stored Procedure on MySQL server

Now let's go ahead and start off with a sample procedure that will calculate the total amount owed by customers based on the sum of the balance column.

Our SP is as follows:

MySQL> DELIMITER $$
   CREATE PROCEDURE total_debt()
   BEGIN
       select ifnull(sum(balance),0) as amount_owed from  customers ;
   END $$
 DELIMITER ;

Once the command is run, MySQL server will run and catalog the SP. If there are no errors, then that means that you have created your first stored procedure.

Step 4: Calling MySQL Stored Procedure

To get the results or to run a function written in a stored procedure, use the call statement.

CALL [Procedure Name];

To call our total_debt procedure, we should run the command below:

CALL total_debt;

The output is as follows:

+-------------+
| amount_owed |
+-------------+
|       43575 |
+-------------+

Benefits of MySQL Stored Procedures

As mentioned earlier in this tutorial, stored procedures have a lot of advantages. Let's discuss them in details:

Reducing Network Traffic

Stored procedures can help developers to create systems that can run tons of code scripts as a batch. The entire business logic is compiled into a nice, handy package of code that is saved on the database server. The results of these stored procedures are then retrieved using a single CALL function.

This reduces network traffic and saves bandwidth cost. The waiting time from client to server is also reduced.

Improving Database Performance

Another benefit of utilizing SP is improved database performance. This benefit is realized through the fact that the code is run directly from the server. This is different from running the same statement over a client through database drivers or an API. So this runs the program a little bit faster and gives applications a strong performance boost.

Aiding Code Interoperability and Portability

SP can also be helpful in centralizing business logic from the database side. So, if you want to change the client-side programming language, you can do so very easily without rewriting lots of code because the entire logic of your application resides in the database. Also, any change made to the SP is directly reflected on the client machines without tweaking the application from each client. From a software maintenance perspective, this is a big plus.

Increasing Database Security

MySQL has different levels of privileges that limit what users can do on the database. By using stored procedures, you may decide to grant EXECUTE permissions to users and lock other privileges to avoid exposing the entire database object.

To put this into perspective, we can decide that regular users on our 'sample_store' database are only allowed to view the total balance of the customers and not any other information about individual customers. This will help us conform to the privacy policies that we have agreed with our customers.

To achieve this, we should only give users EXECUTE permissions. To demonstrate, this, let us create hypothetical user named 'james' and grant him execute permissions to our 'customers' table.

To do this, run the command below as root:

mysql> CREATE USER 'james'@'localhost' IDENTIFIED BY 'jamespass';
mysql> GRANT EXECUTE ON sample_store.* TO 'james'@'localhost';

Once you create the user, login with their details:

$ mysql -ujames -p

Then, try to view the entire customer's data using the SELECT statement.

mysql> use sample_store;
mysql> select * from customers;

Output:

ERROR 1142 (42000): SELECT command denied to user 'james'@'localhost' for table 'customers'

As you can see above, user 'james' can not view the classified information about customers. However, since we have granted him EXECUTE privileges, he can get the balances of all customers by running our stored procedure

CALL total_debt;

Output:

+-------------+
| amount_owed |
+-------------+
|       43575 |
+-------------+

Thus, if well crafted, stored procedures can be used to safeguard the database and make private information even more secure.

Conclusion

This article is a walkover on creating stored procedures on MySQL servers hosted on Alibaba Cloud ApsaraDB for RDS or ECS instances. We believe you will use the syntax and examples in this guide to make better database applications that run smoothly.

Remember, if you are new to Alibaba, you can sign up now and get free trial credit of up to $1200 to test the MySQL stored procedures and over 40 other cloud products on Alibaba Cloud.

Community

How to Implement Stored Procedures on an Alibaba Cloud MySQL Server

Prerequisites

Procedure

Step 1: Structure of MySQL Stored Procedures

Step 2: Creating Sample Data

Step 3: Creating a Stored Procedure on MySQL server

Step 4: Calling MySQL Stored Procedure

Benefits of MySQL Stored Procedures

Reducing Network Traffic

Improving Database Performance

Aiding Code Interoperability and Portability

Increasing Database Security

Conclusion

Read previous post:

Read next post:

francisndungu

You may also like

Comments

francisndungu

Related Products

Database Overview

PolarDB for MySQL

AnalyticDB for MySQL

Database for FinTech Solution