In this blog we are going to discuss about mysql stored procedures, its variables, loops, and Input – Output parameters.
What is stored procedure?
It is a subroutine like a subprogram or in a simple language a function that are stored in the database. It has a name, parameters list, and SQL statements that execute on demand.
Why we need stored procedures?
We need it to optimize the program execution time of backend in case of multiple database calls. For example, if your application makes multiple database request which is dependent in nature like you have users table in which company and incremental employee id are saved. Suppose you want to add a new employee then in this case you have to get the last employee id, and on that basis, you will increase employee id by one and save to database. And the Same for the new company, you will get the last company id, increase the value by one and same it.
In both cases, you will see there are two database calls, one after another. Now if you have some network latency then it will consume time. So, to optimize it, we can do this operation in one database call by putting our logic inside the stored procedure.
Basic structure of stored procedure
CREATE PROCEDURE `your_stored_procedure_name` () BEGIN #Your logic here END
Working with variables
To declare a variable inside a stored procedure, you can use
DECLARE statement. These variables have their own scope that defines its lifetime.
DECLARE your_variable_name datatype(size) DEFAULT default_value;
For multiple variables
DECLARE var1, var2 datatype(size) DEFAULT default_value;
SET your_variable_name = value;
Session variables are the variables whose name begins with the @ sign. It is available and accessible until the session ends.
SET @variable_name := value;
if-elseif-else in stored procedure
if exists (Your_statement_here) then #your_code elseif exists (your_second_statement_here) then #your_second_code else #your_third_code end if;
Check the value is null
IF(NAME_OF_THE_VAR IS NULL) THEN #your_code END IF;