SQL Stored Procedure in MySQL

A stored procedure is a segment of declarative SQL code, which is stored in the database catalog. A stored procedure can be invoked by a program, a trigger or even another stored procedure. A stored procedure which calls itself is recursive stored procedure. Almost RDMBS supports recursive stored procedure but MySQL does not support it well. You should check your version of MySQL database before implementing recursive stored procedures.
MySQL is known as the most popular open source RDBMS which is widely used by both community and enterprise. However during the first decade of its existence, it did not support stored procedure, trigger, event…etc. Since MySQL version 5.0, those features have been added to MySQL database engine to allow MySQL to be more flexible and powerful.
Before starting the tutorial series about stored procedure, it is required that you have MySQL version  5.x+  installed in your computer or server.

The advantages of using Stored Procedure

1. Programming a modular.
2. Program execution is faster.
3. Reduce network traffic.
4. Can be used for security mechanisms.

format :

CREATE [DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body
     
CREATE FUNCTION sp_name
[DEFINER = { user | CURRENT_USER }]
([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body
     
proc_parameter:
[ IN | OUT | INOUT ] param_name type
     
func_parameter:
param_name type
     
type:
Any valid MySQL data type
     
characteristic:
LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'
     
routine_body:
Valid SQL procedure statement