--存储过程 加 游标
--建表
create tablestudent(
sidvarchar(8) primary key,
snamevarchar(10),
sexvarchar(2),
ageint,
classnovarchar(6)
);--表中的数据
insert into student values('20170101','张石瑞','男','19','201701'),
('20170201','李佛','女','20','201702'),
('20170202','王法无','男','19','201702')
/创建游标和关闭游标的四个步骤
-- 1、创建游标 (cur_name 游标名字)
declare cur_name cursor for select 语句;
-- 2、打开游标
open cur_name;
-- 3、提取游标数据
fetch cur_name [into 变量1,变量2,、、、、、];
-- 4、关闭(释放)游标
close cur_name;/
--游标的基本提取操作
create PROCEDUREproc1()BEGIN
declare cur_sid varchar(20);declare cur_sname varchar(20);declare cur_sex varchar(20);declare cur_age varchar(20);declare cur_classno varchar(20);--1、
declare student_cur1 CURSOR for
select sid,sname,sex,age,classno fromstudent;--2、
openstudent_cur1;--3、
fetch student_cur1 intocur_sid,cur_sname,cur_sex,cur_age,cur_classno;selectcur_sid,cur_sname,cur_sex,cur_age,cur_classno;--4、
closestudent_cur1;END
--执行存储过程
call proc1();--删除存储过程
drop procedure if existsproc1
--游标的循环遍历‘
create PROCEDUREproc2()BEGIN
declare cur_sid varchar(20);declare cur_sname varchar(20);declare cur_sex varchar(20);declare cur_age varchar(20);declare cur_classno varchar(20);
declare sum int default 0;declare i int default 0;--1、
declare student_cur1 CURSOR for
select sid,sname,sex,age,classno fromstudent;--2、
openstudent_cur1;--3、 最简单的 while 遍历方法
select count(sid) into sum fromstudent;while i
closestudent_cur1;END
--执行存储过程
call proc2();--删除存储过程
drop procedure if existsproc2
--使用 loop 遍历游标
create PROCEDUREproc3()BEGIN
declare cur_sid varchar(20);declare cur_sname varchar(20);declare cur_sex varchar(20);declare cur_age varchar(20);declare cur_classno varchar(20);
declare state int default false; --定义表示用于判断游标是否溢出--1、
declare student_cur1 CURSOR for
select sid,sname,sex,age,classno fromstudent;--2、
openstudent_cur1;--3、 loop 遍历游标
cur_loop:loop --循环开始--循环开始的时候提取一次
fetch student_cur1 intocur_sid,cur_sname,cur_sex,cur_age,cur_classno;selectcur_sid,cur_sname,cur_sex,cur_age,cur_classno;
if state then
leave cur_loop;
end if;
end loop; --循环结束--4、
closestudent_cur1;END
--执行存储过程
call proc3()--删除存储过程
drop procedure if existsproc3
/*fetch是获取游标当前指向的数据行,并将指针指向下一行,当游标已经指向最后一行时继续执行会造成游标溢出。
使用loop循环游标时,他本身是不会监控是否到最后一条数据了,像下面代码这种写法,就会造成死循环;
read_loop:loop
fetch cur into n,c;
set total = total+c;
end loop;
在MySql中,造成游标溢出时会引发mysql预定义的NOT FOUND错误,所以在上面使用下面的代码指定了当引发not found错误时定义一个continue 的事件,指定这个事件发生时修改done变量的值。
declare continue HANDLER for not found set done = true;
也有这样写的
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set done = true;
所以在循环时加上了下面这句代码:
--判断游标的循环是否结束
if done then
leave read_loop; --跳出游标循环
end if;
如果done的值是true,就结束循环。继续执行下面的代码。
*/
--性别翻转
create PROCEDUREproc4()BEGIN
declare cur_sid int;declare cur_sex varchar(1);
declare state int defaultfalse;--1、
declare sex_cur cursor for select sid,sex fromstudent;--
declare CONTINUE HANDLER for not found set state=true;--2、
opensex_cur;--3、
sex_loop:LOOP --循环开始
fetch sex_cur intocur_sid,cur_sex;if state thenleave sex_loop;end if;if cur_sex='男' then
update student set sex='女' where sid=cur_sid;end if;if cur_sex='女' then
update student set sex='男' where sid=cur_sid;end if;end loop; --循环结束--4、
closesex_cur;END
--执行存储过程
call proc4()--删除存储过程
drop procedure if existsproc4
--表的查询
select * from student