存储过程加游标一起使用的基本操作

  1 -- 存储过程  加  游标
  2 
  3 -- 建表
  4 create table student(
  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 PROCEDURE proc1()
 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 from student;
 38 -- 2、
 39 open student_cur1;
 40 -- 3、
 41 fetch student_cur1 into cur_sid,cur_sname,cur_sex,cur_age,cur_classno;
 42 select cur_sid,cur_sname,cur_sex,cur_age,cur_classno;
 43 -- 4、
 44 close student_cur1;
 45 END
 46 
 47 -- 执行存储过程
 48 call proc1();
 49 -- 删除存储过程
 50 drop procedure if exists proc1
 51 
 52 
 53 
 54 -- 游标的循环遍历‘
 55 create PROCEDURE proc2()
 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 from student;
 68 -- 2、
 69 open student_cur1;
 70 -- 3、 最简单的  while 遍历方法
 71 select count(sid) into sum from student;
 72 while i<sum DO
 73     fetch student_cur1 into cur_sid,cur_sname,cur_sex,cur_age,cur_classno;
 74     select cur_sid,cur_sname,cur_sex,cur_age,cur_classno;
 75     set i=i+1;
 76 end while;
 77 -- 4、
 78 close student_cur1;
 79 END
 80 
 81 -- 执行存储过程
 82 call proc2();
 83 -- 删除存储过程
 84 drop procedure if exists proc2
 85 
 86 
 87 -- 使用 loop 遍历游标
 88 create PROCEDURE proc3()
 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 from student;
100 -- 2、
101 open student_cur1;
102 -- 3、 loop 遍历游标
103 cur_loop:loop -- 循环开始
104 -- 循环开始的时候提取一次
105         fetch student_cur1 into cur_sid,cur_sname,cur_sex,cur_age,cur_classno;
106         select cur_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 close student_cur1;
114 END
115 
116 -- 执行存储过程
117 call proc3()
118 -- 删除存储过程
119 drop procedure if exists proc3
120 
121 
122 
123 
124 /*
125 fetch是获取游标当前指向的数据行，并将指针指向下一行，当游标已经指向最后一行时继续执行会造成游标溢出。
126 使用loop循环游标时，他本身是不会监控是否到最后一条数据了，像下面代码这种写法，就会造成死循环；
127 
128 read_loop:loop  
129 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 then  
141     leave read_loop;    --跳出游标循环  
142 end if;  
143 如果done的值是true，就结束循环。继续执行下面的代码。
144 
145 */
146 
147 
148 
149 
150 
151 
152 
153 
154 -- 性别翻转
155 
156 create PROCEDURE proc4()
157 BEGIN
158 declare cur_sid int;
159 declare cur_sex varchar(1);
160 
161 declare state int default false;
162 -- 1、
163 declare sex_cur cursor for select sid,sex from student;
164 -- 
165 declare CONTINUE HANDLER for not found set state=true;
166 -- 2、
167 open sex_cur;
168 -- 3、
169 sex_loop:LOOP -- 循环开始
170     fetch sex_cur into cur_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 close sex_cur;
183 END
184 
185 
186 
187 
188 
189 
190 -- 执行存储过程
191 call proc4()
192 -- 删除存储过程
193 drop procedure if exists proc4
194 
195 
196 
197 
198 
199 -- 表的查询
200 select * from student

 

转载于:https://www.cnblogs.com/Lvhengshuai/p/6826198.html