mysql游标和存储过程一起使用方法_存储过程加游标一起使用的基本操作

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