Stored procedure syntax:
CREATE PROCEDURE ProcedureName
Begin
SQL 1
end$
Show all stored procedures Show procedure status;
Calling the stored procedure call procedurename ();
Contains an if statement that has been saved:
CREATE PROCEDURE ProcedureName ()
Begin
declare yy int
If
Sql1
Else
Sql2
End If;
end$
Case:
CREATE PROCEDURE procedurename (width int,height int)
Begin
Select Concat (' Your area is ', width*height) as areas;
If width >height Then
Select ' You are quite fat ';
ElseIf width Select ' You are quite thin ';
Else
Select ' You are pretty square ';
End If;
end$
Programming three steps
Sequential selection Loops
The While loop statement has been saved:
Ask 1 to 100 of the and;
Delimiter $
CREATE PROCEDURE ProcedureName ()
Begin
DECLARE total int default 0;
DECLARE num2 int default 0;
While num2<=100 do
Set total:=total+num2;
Set num2:=num2+1;
End while;
Select Total;
end$
In/out/inout
--in Incoming parameters
CREATE PROCEDURE name (in num int)
Begin
DECLARE total int default 0;
DECLARE num2 int default 0;
While Num2<=num do
Set total:=total+num2;
Set num2:=num2+1;
End while;
Select Total;
end$
--in/out
delimiter$
CREATE PROCEDURE P8 (in num int,out total int)
Begin
DECLARE num2 int default 0;
Set total: = 0;
While Num2<=num do
Set total:=total+num2;
Set num2:=num2+1;
End while;
Select Total;
end$
Call Method:
Call P8 (@tt)
Select @tt
--inout
delimiter$
CREATE PROCEDURE P9 (inout age int)
Begin
Set Age: =age+20;
end$
Call Method:
Set @currentage =18
Call P9 (@currentage)
Select @currentage
--case
delimiter$
CREATE PROCEDURE P10 ()
Begin
declare POS int default 0;
Set Pos:=floor (4*rand ());
Case POS
When 1 then select ' Happy ';
When 2 then select ' Cry ';
When 3 then select ' Angry ';
Else select ' None ';
End case;
end$
--repeat Cycle
delimiter$
CREATE PROCEDURE P11 ()
Begin
DECLARE total int default 0;
declare i int default 0;
Repeat
Set i:=i+1;
Set total: =total+i;
Until i>=100 end repeat;
Select Total;
end$
[MySQL] Stored procedures