Mysql Stored Procedure example
/*
Define Variables
Method 1: set @ variable name = value;
Method 2: select value into @ variable name;
Method 3: default Value of the declare variable name type (string type plus range;
The value of the in parameter will only play a role in the stored procedure.
The value of the out parameter is set to null, and the value calculated in the storage will affect the value of the variable referenced outside.
The value of the inout parameter is not set to null. The value calculated in the storage will affect the value of the variable referenced outside.
*/
Use mysql;
/* Create a stored procedure */
Delimiter $
Drop procedure if exists porc_person_02;
Create procedure porc_person_02 (IN p1 INT, OUT p2 INT, INOUT p3 VARCHAR (20 ))
BEGIN
DECLARE innerp1 VARCHAR (10) DEFAULT 'this is innerp1 ';
DECLARE innerp2 VARCHAR (10) DEFAULT 'this is innerp2 ';
SET p1 = 10;
SET p2 = 20;
SET p3 = 'this is string ';
If p1 = 10 then
Select 'p1 is 10 ';
End if;
If p1 = p2 then
Select 'p1 = p2 ';
Else
Select p1, p2, p3;
End if;
Case p3
When 'A' then
Select 'p3 is ';
When 'B' then
Select 'p3 is B ';
When 'C' then
Select 'p3 is C ';
Else
Select p3;
End case;
/* If the condition is not met, it will be terminated */
While p1> 4
Do
Set p1 = p1-1;
End while;
Select p1;
Checka: loop
Set p1 = p1 + 1;
If p1 = 14 then
Leave checka;
End if;
End loop;
Select p1;
/* If conditions are met, the service will be terminated */
Repeat
Set p1 = p1-1;
Until p1 = 6
End repeat;
Select p1;
END;
$
Set @ p_in = 3;
Set @ p_out = 2;
Set @ p_inout = 'B ';
Select 'check procedure 'into @ p4;
Call porc_person_02 (@ p_in, @ p_out, @ p_inout );
Select @ p_in, @ p_out, @ p_inout, @ p4;