Introduction:
Stored procedures are a powerful feature of MySQL that can significantly enhance your database management and development capabilities. In this comprehensive guide, we’ll dive deep into the world of stored procedures, exploring what they are, why they are essential, and how to create and use them effectively in MySQL.
How to create Stored procedures in MYSQL :
to create Stored procedures we use this simple syntax :
delimiter $$
create procedure name_procedure([parameter_list])
begin
// CODE SQL ;
end $$
delimiter ;We use this Syntax for executing a stored procedure :
call nom_procedure([paramter_list])Parameters IN , OUT , INOUT
MySQL stored procedures have three directions in which a parameter can be defined:
Parameter IN
The value is only passed to the stored procedure. It is used as part of the procedure. This amounts to providing input to the stored procedure.
Parameter OUT
The value is only passed out of the stored procedure, any external variables that have been assigned to this position will take the passed value. This is similar to returning values from a stored procedure.
Parameter INOUT
A variable and its value (ExtVal) are passed to the stored procedure (IntVal) and can be there modified. When the stored procedure is finished, the external value (ExtVal) will be equal to the value modified (IntVal)
Example 1 IN
delimiter $$
create procedure client_adresse( IN var1 varchar(25)
begin
select * from clients
where UPPER(adresse)=UPPER(var1);
end $$
== >
call client_adresse("rabat")Example 2 OUT
delimiter $$
create procedure client_adresse( IN var1 varchar(25) , OUT count_clients int )
begin
select COUNT(*) INTO count_clients From
clients where
UPPER(adresse)=UPPER(var1);
end $$
delimiter ;
==> call this function
call client_adresse( "casablanca",@Total);
// @Total is a session variable
SELECT @Total ;Example 3 INOUT
delimiter $$
create procedure SetCounter( INOUT counter INT, IN inc INT )
begin
SET counter = counter + inc ;
end $$
delimiter ;
== > call this function
SET @counter = 1;
call SetCounter(@counter,1) ;
call SetCounter(@counter,1) ;
call SetCounter(@counter,5) ;
SELECT @counter ;How to create function in MYSQL
How to create function in MYSQL