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;

...

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循环，在这里我就不列举了。

巴特西

MySQL存储过程02

最新文章

热门文章