Stored Procedure In MySQL

In this article you will learn about stored procedure in MySQL.

Introduction

In MySQL, a stored procedure is a methodology to encapsulate repetitive tasks.A stored procedure that calls itself is known as a Recursive stored procedure. The database can grant suitable permissions to applications that access stored procedures in the database without giving any permission on the basic database tables. A stored procedure can be also invoked by triggers.

        
 
 

Advantages of Stored Procedure:

  • Once created stored procedures are compiled and they can be stored in the database.
  • It is secure to use stored procedures.
  • Stored procedures are reusable and transparent to any applications.
  • stored procedures help an increasing the performance of the applications.

Steps to create a Stored Procedure:

I have an existing table with table name : “Tech”, as seen in below image. 
 
  

Now I am creating a Stored Procedure using SELECT statement with name : “ tech_proc”. 

 
How to call an existing Stored Procedure:

Query: call tech_proc(5);

Example:

 
 
How to create a Stored Procedure using INSERT statement:

Example: Follow the below image with Insert Statement in Stored Procedure named “tech_procedure”. 

Read more articles on MySQL: