1 --存储过程 加 游标
2
3 --建表
4 create tablestudent(5 sid varchar(8) primary key,6 sname varchar(10),7 sex varchar(2),8 age int,9 classno varchar(6)10 );11 --表中的数据
12 insert into student values
13 ('20170101','张石瑞','男','19','201701'),14 ('20170201','李佛','女','20','201702'),15 ('20170202','王法无','男','19','201702')16
17 /*创建游标和关闭游标的四个步骤18 -- 1、创建游标 (cur_name 游标名字)19 declare cur_name cursor for select 语句;20 -- 2、打开游标21 open cur_name;22 -- 3、提取游标数据23 fetch cur_name [into 变量1,变量2,、、、、、];24 -- 4、关闭(释放)游标25 close cur_name;26 */
27 --游标的基本提取操作
28 create PROCEDUREproc1()29 BEGIN
30 declare cur_sid varchar(20);31 declare cur_sname varchar(20);32 declare cur_sex varchar(20);33 declare cur_age varchar(20);34 declare cur_classno varchar(20);35 --1、
36 declare student_cur1 CURSOR for
37 select sid,sname,sex,age,classno fromstudent;38 --2、
39 openstudent_cur1;40 --3、
41 fetch student_cur1 intocur_sid,cur_sname,cur_sex,cur_age,cur_classno;42 selectcur_sid,cur_sname,cur_sex,cur_age,cur_classno;43 --4、
44 closestudent_cur1;45 END
46
47 --执行存储过程
48 call proc1();49 --删除存储过程
50 drop procedure if existsproc151
52
53
54 --游标的循环遍历‘
55 create PROCEDUREproc2()56 BEGIN
57 declare cur_sid varchar(20);58 declare cur_sname varchar(20);59 declare cur_sex varchar(20);60 declare cur_age varchar(20);61 declare cur_classno varchar(20);62
63 declare sum int default 0;64 declare i int default 0;65 --1、
66 declare student_cur1 CURSOR for
67 select sid,sname,sex,age,classno fromstudent;68 --2、
69 openstudent_cur1;70 --3、 最简单的 while 遍历方法
71 select count(sid) into sum fromstudent;72 while i
78 closestudent_cur1;79 END
80
81 --执行存储过程
82 call proc2();83 --删除存储过程
84 drop procedure if existsproc285
86
87 --使用 loop 遍历游标
88 create PROCEDUREproc3()89 BEGIN
90 declare cur_sid varchar(20);91 declare cur_sname varchar(20);92 declare cur_sex varchar(20);93 declare cur_age varchar(20);94 declare cur_classno varchar(20);95
96 declare state int default false; --定义表示用于判断游标是否溢出
97 --1、
98 declare student_cur1 CURSOR for
99 select sid,sname,sex,age,classno fromstudent;100 --2、
101 openstudent_cur1;102 --3、 loop 遍历游标
103 cur_loop:loop --循环开始
104 --循环开始的时候提取一次
105 fetch student_cur1 intocur_sid,cur_sname,cur_sex,cur_age,cur_classno;106 selectcur_sid,cur_sname,cur_sex,cur_age,cur_classno;107
108 if state then
109 leave cur_loop;110 end if;111 end loop; --循环结束
112 --4、
113 closestudent_cur1;114 END
115
116 --执行存储过程
117 call proc3()118 --删除存储过程
119 drop procedure if existsproc3120
121
122
123
124 /*
125 fetch是获取游标当前指向的数据行,并将指针指向下一行,当游标已经指向最后一行时继续执行会造成游标溢出。126 使用loop循环游标时,他本身是不会监控是否到最后一条数据了,像下面代码这种写法,就会造成死循环;127
128 read_loop:loop129 fetch cur into n,c;130 set total = total+c;131 end loop;132 在MySql中,造成游标溢出时会引发mysql预定义的NOT FOUND错误,所以在上面使用下面的代码指定了当引发not found错误时定义一个continue 的事件,指定这个事件发生时修改done变量的值。133
134 declare continue HANDLER for not found set done = true;135 也有这样写的136 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set done = true;137 所以在循环时加上了下面这句代码:138
139 --判断游标的循环是否结束140 if done then141 leave read_loop; --跳出游标循环142 end if;143 如果done的值是true,就结束循环。继续执行下面的代码。144
145 */
146
147
148
149
150
151
152
153
154 --性别翻转
155
156 create PROCEDUREproc4()157 BEGIN
158 declare cur_sid int;159 declare cur_sex varchar(1);160
161 declare state int defaultfalse;162 --1、
163 declare sex_cur cursor for select sid,sex fromstudent;164 --
165 declare CONTINUE HANDLER for not found set state=true;166 --2、
167 opensex_cur;168 --3、
169 sex_loop:LOOP --循环开始
170 fetch sex_cur intocur_sid,cur_sex;171 if state then
172 leave sex_loop;173 end if;174 if cur_sex='男' then
175 update student set sex='女' where sid=cur_sid;176 end if;177 if cur_sex='女' then
178 update student set sex='男' where sid=cur_sid;179 end if;180 end loop; --循环结束
181 --4、
182 closesex_cur;183 END
184
185
186
187
188
189
190 --执行存储过程
191 call proc4()192 --删除存储过程
193 drop procedure if existsproc4194
195
196
197
198
199 --表的查询
200 select * from student