Introduction to Mysql stored procedure :

Stored procedure are very similar to functions of Python or any programming language. The Story at high level remains the same, it may take some argument(s) and return some data.

In this article we shall explore how to create different types of stored procedure inside mysql docker containers. If you have missed the previous article, I would like to request you to have look. Here  you will find the series of commands that will fire up mysql docker container.

Why it is important:

In the majority of the situations where multiple softwares are placed at different computers. Therefore, computers heavily use network to exchange data. So, when we use stored procedures, network only carries the parameters from the source computer to destination computer, which reduces the network traffic. Hence, Stored procedures speed up the data retrival time.

There is one more reason that is very similar to the above reason. Let’s say, we know that a very similar complex query is called repeated times. Therefore, it is very convenient to create a stored procedure and call it multiple times.

The basic structure of stored procedure :

DELIMITER $$
CREATE PROCEDURE procedureName()
BEGIN
   some sql query;
END $$
DELIMITER ;

Explaination:

DELIMITER $$ —>we changing the default delimiter of mysql to $$ from ;(semicolon)

CREATE PROCEDURE procedureName() —> creating a new procedure, use a meaningfull name to indentify it easily.

BEGIN … END $$ –> whatever written inside it, is the actually mysql query to be executed.

DELIMITER; —> Again reseting the delimter to semicolon.

Creating a procedure without any arguments:

DELIMITER $$
CREATE PROCEDURE getFirstFiftyCustomers()
BEGIN
  select * from employees;
END $$
DELIMITER ;

How to call a store procedure:

call getFirstFiftyCustomers();

How to drop/delete a stored procedure:

drop procedure getFirstFiftyCustomers;

Creating mysql stored prosedure with input parameter:

DELIMITER $$
CREATE PROCEDURE findEmployee(in ID INT)
BEGIN
    select *
    from employees
    where emp_no = ID;
END $$
DELIMITER ;

Calling the above procedure:

call findEmployee(10004);

Creating stored procedure with in out parameters:

DELIMITER $$
CREATE PROCEDURE getGenderofEmployee(in ID INT,out genderOut enum("M","F"))
BEGIN
    select gender into genderOut
    from employees
   where emp_no = ID;
END $$
DELIMITER ;

Caution : The variable name of the out parameter in the procedure definition header must be same as the variable name after into phrase,example gen

Calling the procedure :

call getGenderofEmployee(10004,@genderOut);

Extracting the value from the Out parameter:

select @genderOut;

How to list all the existing procedure :

SHOW PROCEDURE STATUS WHERE Db = 'sakila';

How to delete a procedure :

Drop procedure genGender;

Conclusion:

In this article we explored how a mysql stored procedure works and the different operations associated with it.  These are one of the most usefull tools in any database. Therefore, for backend developers importance of the procedures are very usefull as it will reduced much of complexity in our driving program.

Please watch the youtube video for visual explaination!