MySQL存储过程02

这次接着说MySQL存储过程：

我们先看它的多分支控制结构case:

case的语句很简单： 

case 变量名

when 条件1 then 输出结果1;

when 条件2 then 输出结果2;

......

end case;

那我们就来建立一个存储过程实现它：

create procedure p10()
begin
declare pos int default 0;
set pos:= floor(5*rand());
case pos
when 1  then select'我会飞';
when 2 then select'我掉到海里';
when 3 then select'我在小岛';
else select'我不知道我在哪';
end case;
end$

由于我们设置的是随机数，所以它会根据我们生成的数来输出结果

接着看一下repeat循环：

repeat的语法：

repeat

sql statement;

sql statement;

...

until condition end repeat;

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$

游标：

一条sql，对应N条资源，取出资源的接口/句柄，就是游标

沿着游标，可以一次取出一行。

建立游标的语法：

declare 游标名 cursor for sql语句;

open 游标名

fetch 游标名 into 变量1，变量2，...变量N；

close 游标名

create procedure p12()
begin
declare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare getgoods cursor for select gid,num,name from goods;
open getgoods;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
close getgoods;
end$

 

那么我们看到这个输出结果没有什么特别的地方，那么如果我们把游标改一改？

create procedure p13()
begin
declare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare getgoods cursor for select gid,num,name from goods;
open getgoods;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
close getgoods;
end$

我们看到在输出了三行之后报错，那么我们可以把循环和游标结合起来：

create procedure p14()
begin
declare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare cnt int default 0; #声明一个变量用来计数
declare i int default 0;
declare getgoods cursor for select gid,num,name from goods;
select count(*) into cnt from goods; #将总行数赋给cnt
open getgoods;
repeat
set i:=i+1;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
until i>=cnt end repeat;
close getgoods;
end$

其实游标在越界时我们可以用declare continue handler 来操作一个越界标志

declare continue handler for NOT FOUND 可执行语句;

create procedure p15()
begin
declare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare you int default 1;
declare getgoods cursor for select gid,num,name from goods;
declare continue handler for NOT FOUND set you:=0;
open getgoods;
repeat
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
until you=0 end repeat;
close getgoods;
end$

在上面的结果中我们发现最后一行被取了两次，这是为什么？

答：因为我们声明的是continue型handler，那么它在把you 这个变量改为0后还会接着执行后面的sql语句，我们把continue改为exit就不会出现这种情况了：

create procedure p15()
begin
declare row_gid int default 0;
declare row_num int default 0;
declare row_name varchar(20);
declare you int default 1;
declare getgoods cursor for select gid,num,name from goods;
declare exit handler for NOT FOUND set you:=0;
open getgoods;
repeat
fetch getgoods into row_gid,row_num,row_name;
select row_name,row_num;
until you=0 end repeat;
close getgoods;
end$

那么其实我们还有另一种方法来修改：我们使逻辑上更加通顺

create procedure p17()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare you int default 1;
declare getgoods cursor for select gid,num,name from goods;
declare continue handler for NOT FOUND set you:=0;
open getgoods;
fetch getgoods into row_gid,row_num,row_name;
repeat
select row_name,row_num;
fetch getgoods into row_gid,row_num,row_name;
until you=0 end repeat;
close getgoods;
end$

或者是将repeat改为while循环，在这里我就不列举了。