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$

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

MySQL存储过程02

 

接着看一下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$

MySQL存储过程02

 

游标:

一条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$

 MySQL存储过程02

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

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$

MySQL存储过程02

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

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$

MySQL存储过程02

其实游标在越界时我们可以用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$

MySQL存储过程02

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

答:因为我们声明的是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$

MySQL存储过程02

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

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$

MySQL存储过程02

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

上一篇:mysql 存储过程demo


下一篇:mysql5.6.0 的存储过程 与 定时器 实例