/*
Defining variables
Mode 1:set @ variable name = value;
Mode 2:select value into @ variable name;
Method 3:declare The default value of the variable name type (string type plus range);
The value of the in parameter entry will only work in the stored procedure
The value of the Out parameter entry is set to NULL, and the value computed in the store affects the value that is referenced outside the variable.
The value of the InOut parameter entry is not set to NULL, and the value computed in the store affects the value that is referenced outside the variable.
*/
Use MySQL;
/* Create 1 stored procedures */
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 (Ten) DEFAULT ' this is INNERP1 ';
DECLARE Innerp2 VARCHAR (Ten) 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 a ';
When ' B ' then
Select ' P3 is B ';
When ' C ' then
Select ' P3 is C ';
Else
Select P3;
End case;
/* Condition not satisfied 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;
/* Conditional gratification 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;
Introduction to MySQL Process Control and stored procedures