MySQL stored procedures

#第1个存储过程, experience "encapsulating SQL"
Delimiter $
drop procedure if exists p1;$ #删除存储过程
CREATE PROCEDURE P1 () #创建存储过程
Begin
SELECT * from Rooms;
end;$
Call P1 (); $ #调用存储过程
#第2个存储过程, realize "parameters"
Delimiter $
drop procedure if exists p2;$
CREATE PROCEDURE P2 (n bigint (20))
Begin
SELECT * FROM rooms where id>n;
end;$
Call P2 (10050); $
#第3个存储过程, experience "control structure"
Delimiter $
drop procedure if exists p3;$
CREATE PROCEDURE p3 (n bigint (k), J char (1))
Begin
If j= ' h ' then #存储过程的If判断语句
SELECT * FROM rooms where id>n;
Else
SELECT * FROM rooms where id<n;
End If; #这里必须要用end if ends
end;$
Call P3 (10050, ' H '); $
Call P3 (10050, ' d ');
#第4个储存过程, experience "looping statements" (calculation of 1-n and)
Delimiter $
drop procedure if exists p4;$
CREATE PROCEDURE P4 (n smallint)
Begin
declare i int; #存储过程定义变量
declare s int;
Set I=1; #存储过程给变量初始值
Set s=0;
While I<=n do
Set s=s+i; #这里存储过程赋值也必须要用set, cannot use + =
Set i=i+1;
End While; #这里循环也必须要用end结束
Select S;
end;$
Call P4 (200); $

This article is from the "wind up with the Wind Stop" blog, please be sure to keep this source http://hww9011.blog.51cto.com/3545422/1602549

MySQL stored procedures