Stored Procedures in MySql

The previous lesson of MySql Lessons Series was about MySql Upsert Query and in this lesson we will learn to create and use stored procedures in MySql.



A stored procedure is a sub-routine stored in the database which can be invoked by CALL or Execute statement.

Creating a stored procedure

A MySql Stored Procedure is nothing new it is just a MySql query pre-stored in a database and all we need to do is call it to run it. Basic syntax of creating a stored procedure is given below:

DELIMITER $$
CREATE PROCEDURE `Procedure_Name` 
(IN Parameter1 VARCHAR(20), IN Parameter2 VARCHAR(20))
BEGIN
  MySql Query;
END $$
DELIMITER ;

Let us say we have a table `marks` given in the image below and we want to write a stored procedure to insert data in the table `marks` whenever the procedure is called.



A stored procedure that inserts data in table `marks` is given below:

DELIMITER $$
CREATE PROCEDURE `Insert_Marks` 
(IN studentid INT(11), IN English VARCHAR(20), IN Science VARCHAR(20), 
IN Maths VARCHAR(20), IN Philosophy VARCHAR(20))
BEGIN
  INSERT INTO `marks` 
VALUES (`studentid`, `English`, `Science`, `Maths`, `Philosophy`);
END $$
DELIMITER ;

Calling a stored procedure

A stored procedure can be invoked by executing Call Procedure or Execute Procedure statements. Basic syntax of calling a stored procedure is given below:

CALL ProcedureName('parameters');

or

Execute ProcedureName('parameters');

We can call the procedure `Insert_Marks` created above like this:

CALL `Insert_Marks`('', 45, 73, 32, 89);

How to see all the stored procedures in a database?

Execute the database query given below to see all the stored procedures in a database.

SHOW PROCEDURE STATUS;

How to drop a stored procedure?

DROP PROCEDURE IF EXISTS ProcedureName;

To drop the stored procedure `Insert_Marks`, execute the query given below:

DROP PROCEDURE IF EXISTS `Insert_Marks`;

Why use stored procedures?

Stored procedures greatly increases the performance of a system as a client query has to pass through various levels of database like parsing, analyzing, optimizing each time the query is executed while a stored procedure may be residing in a pre-compiled form in a database. Stored procedures reduces the dependency on programming language as it totally relies on database server. Stored procedures are more secure.

Stay tuned for more lessons...