Database-stored procedures are sets of pre-compiled SQL statements created in the server, called and executed by database applications. It is very simple and the same result can be archived by SQL query.
Stored procedures advantages
Stored procedures increase the performance of an application. Once created, stored procedure is compiled and stored in the database catalog. It runs faster than an uncompiled SQL commands which are sent from application
Stored procedure reduces the traffic between application and database server because instead of sending multiple uncompiled long SQL commands statement, application has only to send the stored procedure name and get the result back.
Stored procedure is reusable and transparent to any application which wants to use it. Stored procedure exposes the database interface to all applications so developer doesn’t have to program the functions which are already supported in stored procedure in all programs.
Stored procedure is secured. Database administrator can grant the right to application which to access which stored procedures in database catalog without granting any permission on the underlying database table.
Stored procedures disadvantages
Stored procedure make the database server high load in both memory and processors. Instead of being focused on the storing and retrieving data, you could be asking the database server to perform a number of logical operations or a complex of business logic which is not the role of it.
Stored procedure only contains declarative SQL so it is very difficult to write a procedure with complexity of business like other languages in application layer such as Java, C#, C++…
You cannot debug stored procedure in almost RDMBSs and in MySQL also. There are some workarounds on this problem but it still not good enough to do so.
Writing and maintain stored procedure usually required specialized skill set that not all developers possess. This introduced the problem in both application development and maintain phrase.
Stored procedure in MySQL
MySQL certainly is the most open source RDBMS which is widely used by both community and enterprise but during the first decade of its existence, it did not support stored procedure. Since MySQL version 5.0, Stored Procedure has been added to MySQL database engine.
A procedure can exist with or without a return value and with or without parameters.
There are three options for parameterized stored procedures:
IN parameter— You can only send values to the stored procedure. In the stored procedure body, you can execute queries based on input value. This is the default parameter, and keyword IN is optional. If a parameter type is not explicitly defined, by default it is treated as an IN parameter type.
OUT parameter— You can only receive values from the procedure. Your cannot pass values to OUT parameters in a stored procedure call. After successful execution of the stored procedure, all the OUT parameters values will be assigned to their respective bound variables.
INOUT parameter— You can send and receive values from the procedure. This provides the benefits of both OUT and IN parameters using the single bound variable.
Create Stored Procedure “select_procedure” without parameters
Create Stored Procedure “insert_procedure_in” with IN parameters
Call Stored Procedure “insert_procedure”
Create Stored Procedure “out_procedure” with OUT parameter
Call Stored Procedure
Create Stored Procedure “inout_procedure” with INOUT parameter
Call Stored Procedure
Alter Procedure Syntax
This statement can be used to change the characteristics of a stored procedure. More than one change may be specified in an ALTER PROCEDURE statement. However, you cannot change the parameters or body of a stored procedure using this statement; to make such changes, you must drop and re-create the procedure using DROP PROCEDURE and CREATE PROCEDURE.
DROP PROCEDURE IF EXISTS sample_procedure;
List All Procedures:
SHOW PROCEDURE STATUS
Show Procedure Code:
SHOW CREATE PROCEDURE sample_procedure
Delimiters other than the default ; are typically used when defining functions, stored procedures, and triggers wherein you must define multiple statements. You define a different delimiter like $$ which is used to define the end of the entire procedure, but inside it, individual statements are each terminated by ;. That way, when the code is run in the mysql client, the client can tell where the entire procedure ends and execute it as a unit rather than executing the individual statements inside.
Note that the DELIMITER keyword is a function of the command line mysql client only and not a regular language feature. It won’t work if you tried to pass it through a programming language API to MySQL. Some other clients like PHPMyAdmin have other methods to specify a non-default delimiter.
This tutorial would have helped in gaining certain facts and information about the Stored Procedures in MySQL in an easy and straightforward approach. Do share your thoughts and feedback on the same.