mysql存储过程 游标 循环使用介绍
程序员文章站
2024-02-18 16:11:22
mysql的存储过程是从版本5才开始支持的，所以目前一般使用的都可以用到存储过程。今天分享下自己对于mysql存储过程的认识与了解。一些简单的调用以及语法规则这里就不在赘述...
mysql的存储过程是从版本5才开始支持的，所以目前一般使用的都可以用到存储过程。今天分享下自己对于mysql存储过程的认识与了解。
一些简单的调用以及语法规则这里就不在赘述，网上有许多例子。这里主要说说大家常用的游标加循环的嵌套使用。
首先先介绍循环的分类：
(1)while ... end while
(2)loop ... end loop
(3)repeat ... end repeat
(4)goto
这里有三种标准的循环方式：while循环，loop循环以及repeat循环。还有一种非标准的循环方式：goto(不做介绍)。
(1)while ... end while
create procedure p14()
begin
declare v int;
set v = 0;
while v < 5 do
insert into t values (v);
set v = v + 1;
end while;
end;
这是while循环的方式。它跟if语句相似，使用"set v = 0;"语句使为了防止一个常见的错误，如果没有初始化，默认变量值为null，而null和任何值操作结果都为null。
(2)repeat ... end repeat
create procedure p15 ()
begin
declare v int;
set v = 0;
repeat
insert into t values (v);
set v = v + 1;
until v >= 5
end repeat;
end;
这是repeat循环的例子，功能和前面while循环一样。区别在于它在执行后检查结果，而while则是执行前检查。类似于do while语句。注意到until语句后面没有分号，在这里可以不写分号，当然你加上额外的分号更好。
(3)loop ... end loop
create procedure p16 ()
begin
declare v int;
set v = 0;
loop_label: loop
insert into t values (v);
set v = v + 1;
if v >= 5 then
leave loop_label;
end if;
end loop;
end;
以上是loop循环的例子。loop循环不需要初始条件，这点和while循环相似，同时它又和repeat循环一样也不需要结束条件。
iterate 迭代
如果目标是iterate（迭代）语句的话，就必须用到leave语句
create procedure p20 ()
begin
declare v int;
set v = 0;
loop_label: loop
if v = 3 then
set v = v + 1;
iterate loop_label;
end if;
insert into t values (v);
set v = v + 1;
if v >= 5 then
leave loop_label;
end if;
end loop;
end;
iterate（迭代）语句和leave语句一样也是在循环内部的循环引用， 它有点像c语言中 的“continue”，同样它可以出现在复合语句中，引用复合语句标号，iterate（迭代）意思 是重新开始复合语句。
以上是对于循环的几种情况的介绍。接着就是介绍一个带游标的例子来详细解释。
begin
declare p_feecode varchar(20);
declare p_feename varchar(20);
declare p_billmoney float(12);
declare p_schememoney float(12);
declare allmoney float(10);
declare allusedmoney float(10);
declare p_year varchar(50);
declare p_totalcompeleterate float(12);
declare done int(10);
declare flag int(2);
declare feecodecursor cursor for select feecode from fee;//申明一个游标变量
declare continue handler for not found set done=1;//申明循环结束的标志位
set done=0;
select date_format(now(),'%y') into p_year;
open feecodecursor;//打开游标
loop_label:loop
fetch feecodecursor into p_feecode;//将游标插入申明的变量
if done = 1 then
leave loop_label;
else
set flag = 0;
end if;
set p_schememoney=0;
set p_billmoney = 0;
select feename into p_feename from fee where feecode=p_feecode;
select sum(billmoney) into p_billmoney from bill_data where feecode=p_feecode and billdate like concat(p_year, '%');
select schememoney into p_schememoney from total_scheme where feecode=p_feecode and schemedate like concat(p_year, '%') limit 1;
if flag = 0 then
set done = 0;
end if;
if p_schememoney=0 then
set p_totalcompeleterate=-1.0;
else
set p_totalcompeleterate=(1.0*p_billmoney)/p_schememoney;
end if;
insert into total_summary values(p_feecode,p_feename,p_year,p_billmoney,p_totalcompeleterate);
commit;
end loop;
close feecodecursor;//循环结束后需要关闭游标
end
以上只是一个简单的例子来说明如何使用，大家不需要关注具体业务逻辑，只需要关注的是其中标志位值的修改情况，已经循环何时离开。以及游标如何声明，如何使用，至于里面具体的操作和普通的sql语句没有太大区别。此处是用一层循环，至于复杂业务需要需要两层三层，可以继续用同样的方法继续嵌套。以下给出双层嵌套循环的，同样大家只需要关注嵌套结构即可。
begin
declare p_projectid varchar(20);
declare p_projectname varchar(20);
declare p_feecode varchar(20);
declare p_feename varchar(20);
declare p_projectschememoney float(10);
declare p_projectmoney float(10);
declare p_billmoney float(10);
declare p_year varchar(50);
declare p_projectfeecompeleterate float(10);
declare done1 int(10);
declare done2 int(10);
declare flag int(2);
declare feecodecursor cursor for select feecode from fee;
declare continue handler for not found set done1=1;
set done1=0;
select date_format(now(),'%y') into p_year;
delete from project_fee_summary;
open feecodecursor;
repeat //第一层嵌套开始
fetch feecodecursor into p_feecode;
select feename into p_feename from fee where feecode=p_feecode;
if not done1 then
begin
declare projectidcursor cursor for select projectid from project;
declare continue handler for not found set done2 = 1;
set done2=0;
open projectidcursor;
loop_label:loop//第二层嵌套开始
fetch projectidcursor into p_projectid;
select projectname into p_projectname from project where projectid=p_projectid;
if done2 = 1 then
leave loop_label;
else
set flag = 0;
end if;
if not done2 then
set p_projectschememoney=0;
select sum(billmoney) into p_billmoney from bill_data where feecode=p_feecode and projectid=p_projectid and billdate like concat(p_year, '%');
select projectschememoney into p_projectschememoney from project_scheme where feecode=p_feecode and projectid=p_projectid;
if flag = 0 then
set done2 = 0;
end if;
if p_projectschememoney=0 then
set p_projectfeecompeleterate=-1;
else
set p_projectfeecompeleterate=(1.0*p_billmoney)/p_projectschememoney;
end if;
insert into project_fee_summary values(p_feecode,p_projectid,p_projectname,p_feename,p_year,p_billmoney,p_projectfeecompeleterate,p_projectfeecompeleterate);
end if;
end loop;
select sum(billmoney) into p_projectmoney from bill_data where feecode=p_feecode and billdate like concat(p_year, '%');
set p_projectfeecompeleterate=(1.0*p_projectmoney)/p_projectschememoney;
insert into project_fee_summary values(p_feecode,"total","total",p_feename,p_year,p_projectmoney,p_projectfeecompeleterate,p_projectfeecompeleterate);
close projectidcursor;
end;
end if;
until done1
end repeat;
close feecodecursor;
end
一些简单的调用以及语法规则这里就不在赘述，网上有许多例子。这里主要说说大家常用的游标加循环的嵌套使用。
首先先介绍循环的分类：
(1)while ... end while
(2)loop ... end loop
(3)repeat ... end repeat
(4)goto
这里有三种标准的循环方式：while循环，loop循环以及repeat循环。还有一种非标准的循环方式：goto(不做介绍)。
(1)while ... end while
复制代码 代码如下:
create procedure p14()
begin
declare v int;
set v = 0;
while v < 5 do
insert into t values (v);
set v = v + 1;
end while;
end;
这是while循环的方式。它跟if语句相似，使用"set v = 0;"语句使为了防止一个常见的错误，如果没有初始化，默认变量值为null，而null和任何值操作结果都为null。
(2)repeat ... end repeat
复制代码 代码如下:
create procedure p15 ()
begin
declare v int;
set v = 0;
repeat
insert into t values (v);
set v = v + 1;
until v >= 5
end repeat;
end;
这是repeat循环的例子，功能和前面while循环一样。区别在于它在执行后检查结果，而while则是执行前检查。类似于do while语句。注意到until语句后面没有分号，在这里可以不写分号，当然你加上额外的分号更好。
(3)loop ... end loop
复制代码 代码如下:
create procedure p16 ()
begin
declare v int;
set v = 0;
loop_label: loop
insert into t values (v);
set v = v + 1;
if v >= 5 then
leave loop_label;
end if;
end loop;
end;
以上是loop循环的例子。loop循环不需要初始条件，这点和while循环相似，同时它又和repeat循环一样也不需要结束条件。
iterate 迭代
如果目标是iterate（迭代）语句的话，就必须用到leave语句
复制代码 代码如下:
create procedure p20 ()
begin
declare v int;
set v = 0;
loop_label: loop
if v = 3 then
set v = v + 1;
iterate loop_label;
end if;
insert into t values (v);
set v = v + 1;
if v >= 5 then
leave loop_label;
end if;
end loop;
end;
iterate（迭代）语句和leave语句一样也是在循环内部的循环引用， 它有点像c语言中 的“continue”，同样它可以出现在复合语句中，引用复合语句标号，iterate（迭代）意思 是重新开始复合语句。
以上是对于循环的几种情况的介绍。接着就是介绍一个带游标的例子来详细解释。
复制代码 代码如下:
begin
declare p_feecode varchar(20);
declare p_feename varchar(20);
declare p_billmoney float(12);
declare p_schememoney float(12);
declare allmoney float(10);
declare allusedmoney float(10);
declare p_year varchar(50);
declare p_totalcompeleterate float(12);
declare done int(10);
declare flag int(2);
declare feecodecursor cursor for select feecode from fee;//申明一个游标变量
declare continue handler for not found set done=1;//申明循环结束的标志位
set done=0;
select date_format(now(),'%y') into p_year;
open feecodecursor;//打开游标
loop_label:loop
fetch feecodecursor into p_feecode;//将游标插入申明的变量
if done = 1 then
leave loop_label;
else
set flag = 0;
end if;
set p_schememoney=0;
set p_billmoney = 0;
select feename into p_feename from fee where feecode=p_feecode;
select sum(billmoney) into p_billmoney from bill_data where feecode=p_feecode and billdate like concat(p_year, '%');
select schememoney into p_schememoney from total_scheme where feecode=p_feecode and schemedate like concat(p_year, '%') limit 1;
if flag = 0 then
set done = 0;
end if;
if p_schememoney=0 then
set p_totalcompeleterate=-1.0;
else
set p_totalcompeleterate=(1.0*p_billmoney)/p_schememoney;
end if;
insert into total_summary values(p_feecode,p_feename,p_year,p_billmoney,p_totalcompeleterate);
commit;
end loop;
close feecodecursor;//循环结束后需要关闭游标
end
以上只是一个简单的例子来说明如何使用，大家不需要关注具体业务逻辑，只需要关注的是其中标志位值的修改情况，已经循环何时离开。以及游标如何声明，如何使用，至于里面具体的操作和普通的sql语句没有太大区别。此处是用一层循环，至于复杂业务需要需要两层三层，可以继续用同样的方法继续嵌套。以下给出双层嵌套循环的，同样大家只需要关注嵌套结构即可。
复制代码 代码如下:
begin
declare p_projectid varchar(20);
declare p_projectname varchar(20);
declare p_feecode varchar(20);
declare p_feename varchar(20);
declare p_projectschememoney float(10);
declare p_projectmoney float(10);
declare p_billmoney float(10);
declare p_year varchar(50);
declare p_projectfeecompeleterate float(10);
declare done1 int(10);
declare done2 int(10);
declare flag int(2);
declare feecodecursor cursor for select feecode from fee;
declare continue handler for not found set done1=1;
set done1=0;
select date_format(now(),'%y') into p_year;
delete from project_fee_summary;
open feecodecursor;
repeat //第一层嵌套开始
fetch feecodecursor into p_feecode;
select feename into p_feename from fee where feecode=p_feecode;
if not done1 then
begin
declare projectidcursor cursor for select projectid from project;
declare continue handler for not found set done2 = 1;
set done2=0;
open projectidcursor;
loop_label:loop//第二层嵌套开始
fetch projectidcursor into p_projectid;
select projectname into p_projectname from project where projectid=p_projectid;
if done2 = 1 then
leave loop_label;
else
set flag = 0;
end if;
if not done2 then
set p_projectschememoney=0;
select sum(billmoney) into p_billmoney from bill_data where feecode=p_feecode and projectid=p_projectid and billdate like concat(p_year, '%');
select projectschememoney into p_projectschememoney from project_scheme where feecode=p_feecode and projectid=p_projectid;
if flag = 0 then
set done2 = 0;
end if;
if p_projectschememoney=0 then
set p_projectfeecompeleterate=-1;
else
set p_projectfeecompeleterate=(1.0*p_billmoney)/p_projectschememoney;
end if;
insert into project_fee_summary values(p_feecode,p_projectid,p_projectname,p_feename,p_year,p_billmoney,p_projectfeecompeleterate,p_projectfeecompeleterate);
end if;
end loop;
select sum(billmoney) into p_projectmoney from bill_data where feecode=p_feecode and billdate like concat(p_year, '%');
set p_projectfeecompeleterate=(1.0*p_projectmoney)/p_projectschememoney;
insert into project_fee_summary values(p_feecode,"total","total",p_feename,p_year,p_projectmoney,p_projectfeecompeleterate,p_projectfeecompeleterate);
close projectidcursor;
end;
end if;
until done1
end repeat;
close feecodecursor;
end