Introduction to MySQL Stored Procedure – A Complete Guide

By /
Introduction To Stored Procedure In Mysql

In this article, we will understand what a stored procedure is in MySQL and further understand it using the syntax. This is going to be an introductory tutorial about MySQL stored procedures where we will discuss what is stored procedure, its syntax, advantages and disadvantages.

There are a bunch of different topics in MySQL stored procedures, such as delimiter, alter procedures, show procedure, drop procedure, etc. We will see them one by one in separate articles as an individual tutorial. So, stay tuned with us.

Before We Start

Before we start, let me tell you that even if you don’t have any knowledge of stored functions or stored procedures in MySQL, you shouldn’t worry because we will cover everything about the stored procedure here.

Moreover, we will clear out the theory first so that you will understand the topic later when we dive into real examples.

What is Stored Procedure?

By definition, the stored procedure is a set of declarative SQL statements stored in the MySQL server. It is something like a function in any programming language.

When you create a function body in a programming language, you can call that function anywhere just using its name without writing the whole code again. Similar to it, a stored procedure is a set of statements that perform a certain task and you can call that stored procedure whenever you want just by using the name.

Let’s take an example again. Suppose you have a complex and long query that you use in your application more than two or three times. It is not good practice to write the same long query multiple times. Instead, you can create a stored procedure and inside that procedure, you write those SQL statements. So whenever you want to fire that query, you can simply call the stored procedure using its name.

Now, let’s see how we can create the stored procedure.

Syntax to Create the Stored Procedure

We can create a stored procedure using the CREATE PROCEDURE statement. Following is the complete syntax-

CREATE PROCEDURE [IF NOT EXISTS] proc_name([parameters])
[characteristics]
procedure_body


parameters:
[ IN | OUT | INOUT ] param_name type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

procedure_body:
    Valid SQL routine statementCode language: SQL (Structured Query Language) (sql)

Where,

  • CREATE PROCEDURE – It is a statement to create a stored procedure
  • IF NOT EXISTS – It will create a new stored procedure only if the current procedure name doesn’t exist already in the database.
  • proc_name – Any name you can give to the procedure.
  • parameters – There are three types of parameters – IN, OUT, INOUT. The IN parameter specifies the input value that you are passing to the procedure from the procedure call. The OUT parameter is the value that the procedure will return. On the other hand, the INOUT performs combined work of the IN and OUT parameters.
  • Characteristics – You can choose any of the characteristics from the above-mentioned set. Read the official documentation for more information.
  • procedure_body – In the body, you write valid SQL statements. You can write a simple statement such as SELECT or INSERT, or you can write multiple statements using a BEGIN END block.

This way you can create a stored procedure. To invoke the stored procedure, the CALL statement is used. Following is the syntax to call the procedure.

CALL proc_name([parameters])Code language: SQL (Structured Query Language) (sql)

Remember to pass the correct arguments to the procedure call. If you specify the parameters in the procedure definition but don’t mention them in the procedure call then you will get an error.

Why Should We Use Stored Procedures?

There are numerous advantages of using stored procedures in your application. Below we have listed some top pros of stored procedure.

  • Stored procedures help reduce the network traffic between an application and a MySQL server. It is because the application sends only the procedure name to the MySQL server instead of sending lengthy queries/statements.
  • Instead of writing the same logic multiple times in your application, you can write it in the stored procedure as a centralized function and you can call that procedure again and again without duplication. This makes your database more consistent
  • The stored procedure makes the database more secure by granting only required privileges to the application.

Disadvantages of Stored Procedure

Along with the pros, stored procedures come with cons as well. Following are the disadvantages of stored procedures.

  • If you write a stored procedure for every smaller task, it will result in resource over-usage. Besides, too much usage of logical operations in the stored procedure will increase CPU usage.
  • Stored procedures are difficult to troubleshoot and it is not easy to find an error when you create a stored procedure.
  • Developing and maintaining stored procedures necessitates a unique set of skills that not all application developers possess. This might cause issues with application development as well as maintenance.

Conclusion

So, in this tutorial, we have seen everything about the MySQL stored procedure. To create a stored procedure, you should know the maximum information about it that we explained above. I hope you have understood the topic effectively. In the next tutorial, we will see how to create a stored procedure with multiple examples.

References

MySQL official documentation on the stored procedure.