MySQL Stored Procedures

Recently we were working on a project where we needed to categorize or manipulate a large dataset for further processing. The data needed to be filtered based on certain criteria where we would check the relationship of the data from multiple tables and then store in different tables based on the conditions matched.

We were skeptical about using any scripting language as it would only had increased the toll of data transition between the application and MySQL Server as it takes a very long time to transfer a large dataset and the scripting language could only process one record at a time. So we thought of implementing our database logic in MySQL itself using MySQL Stored Procedures.

Why are Stored Procedures?

A stored procedure in MySQL is a subroutine like a subprogram which is stored in database. A procedure has a name, a parameter list, and SQL statement(s).

Why Use Them?

• Provides a common interface: MySQL stored procedures lets you introduce APIs for your database server. It provides a common interface across multiple applications and servers which makes it easy to manage, maintain and document your database.
• Added Security: It also adds an extra layer to the security of your database as it allows you to encapsulate your data queries into procedures and prevents the user from directly accessing them.
  MySQL stored procedures are also safe from sql injections.
• Faster than regular MySQL queries: Stored procedures are performance friendly as well. Once defined procedures are stored in the cache memory of MySQL in already compiled state which saves time in creating the execution plan every time the queries are run.
• Saves network traffic: Stored procedures generate very less network traffic as compared to regular queries as multiple queries within the procedure can be run with a single instruction to the server.
• Separation of concerns: Stored procedures lets you separate your database logic from your business logic. Which eases the testing and the maintenance of the database.

Writing a Stored Procedure

A typical MySQL stored procedure would look like the below code:

DELIMITER //CREATE PROCEDURE get_order_for_customer (IN customer_id INT(11))BEGIN  ** Some queries go here **END //DELIMITER ;

In the above code block, we are creating a stored procedure to get orders for a customer from the database which takes one parameter i.e. the id of the customer. To create a procedure CREATE PROCEDURE statement is used. The body of the procedure is defined between BEGIN and END statement. You can write your queries to be executed within the body.

The above stored procedure can be called with the below statement:

CALL get_order_for_customer(20)

To update a stored procedure in MySQl , you need to drop the existing definition and then redefine the updated one. You can drop an existing procedure in MySQL by using the following statement:

If you want to check if the procedure exists before dropping it.

DROP PROCEDURE IF EXISTS get_order_for_customer;

Now, any database user can retrieve order for a customer by using a simple statement get_order_for_customer without worrying about the relationship behind it and it also will be consistent across the system. Imagine you need to change the relationship between orders and customers, you would only change the procedure definition and it will reflect across all the systems.

Things to Keep In Mind

Before you proceed further with the stored procedures keep following things in mind:

• MySQL is not a scripting language that is why it is not designed to handle complex logics well. So, try to put only database related logics in the stored procedures and keep your business logic separate.
• Query syntax of MySQL is not so elegant for scripting so try to keep your code short to make it readable.
• Error handling in the stored procedure can be quite hard sometimes.
• Always run a query at the end of the procedure as some implementations throw errors when no results are returned.

So, if used carefully stored procedures in MySQL provide a great deal to your database infrastructure. It improves the overall maintainability and scalability of your system while keeping it consistent.