mysql中游标学习笔记

因为之前的某些原因，逃了一次搞基数据库的课程.趁着这次失眠就把游标的知识学习一遍吧...

首先,mysql游标是什么?

游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。

可理解为“游动的光标...

其次,游标的使用步骤

声明/定义一个游标

declare 声明;declare 游标名 cursor for select_statement;

打开一个游标

open 打开;open 游标名

操作游标/取值

fetch 取值;fetch 游标名 into var1,var2[,...]

关闭游标

close 关闭;close 游标名;

NOT FOUND条件处理函数

declare continue handler for NOT FOUND +需要执行的语句

这次以一个简单的案例来说下步骤?

用存储过程游标为student_info表统计各个学生总分(total)字段值

总分=数学成绩+语文成绩+音乐成绩+历史成绩

CREATE DATABASE IF NOT EXISTS mydb;
USE mydb;

DROP TABLE IF EXISTS `student_info`;
CREATE TABLE `student_info` (
  `sn` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(45) NOT NULL,
  `math` int(10) unsigned NOT NULL,
  `chinese` int(10) unsigned NOT NULL,
  `music` int(10) unsigned NOT NULL,
  `history` int(10) unsigned NOT NULL,
  `total` int(10) unsigned default NULL,
  PRIMARY KEY  (`sn`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

INSERT INTO `student_info` (`sn`,`name`,`math`,`chinese`,`music`,`history`,`total`) VALUES 
 (1,'张小明',75,66,61,72,0),
 (2,'刘齐清',68,77,73,63,0),
 (3,'梁代年',80,64,60,69,0),
 (4,'许志锋',62,69,78,72,0),
 (5,'王平丰',78,81,75,91,0),
 (6,'何静静',65,79,80,73,0),
 (7,'苗青青',88,90,81,88,0),
 (8,'路小斌',72,71,68,83,0);

首先我们先建一个mysql存储过程（未使用游标）

delimiter //
drop procedure if exists work4_demo //
create procedure work4_demo()
begin

select * from student_info;

end
//
delimiter ;

执行结果：

使用游标：

delimiter //
drop procedure if exists work4_demo //
create procedure work4_demo()
begin
//将score放到score_all里面暂时储存
declare score_all int;
//基于总行下次用
declare cur_row int;
//定义游标
declare cur_work cursor for select (math+chinese+music+history) as score 
from student_info;
//打开游标
open cur_work;
//取值
fetch cur_work into score_all;
select score_all;
//关闭游标
close cur_work;
end
//
delimiter ;

执行结果：

这时候你会发现我们只得到了一个查询结果,这是因为游标每次只能返回一次，所以我改了下代码

delimiter //
drop procedure if exists work4_demo //
create procedure work4_demo()
begin
declare score_all int;
declare cur_row int;
declare cur_work cursor for select (math+chinese+music+history) as score 
from student_info;
open cur_work;
fetch cur_work into score_all;
select score_all;
fetch cur_work into score_all;
select score_all;
fetch cur_work into score_all;
select score_all;
fetch cur_work into score_all;
select score_all;
fetch cur_work into score_all;
select score_all;
fetch cur_work into score_all;
select score_all;
fetch cur_work into score_all;
select score_all;
fetch cur_work into score_all;
--select score_all;
--fetch cur_work into score_all;
---ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
select score_all;
close cur_work;
end
//
delimiter ;

结果：

发现什么了吗？相同的语句，我们每取一次就往后游一次，有几次就游几次，直到你把游完所有标识

但是当你超出了你所需要遍历的值时,会出现

---ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

这个错误告诉我们游标已经走完了...

所以我又换了一个方法,用select count(*)计算所有行的数量

delimiter //
drop procedure if exists work4_demo //
create procedure work4_demo()
begin
declare score_all int;
//定义总行数
declare cur_row int default 0;
declare i int default 0;
declare cur_work cursor for select (math+chinese+music+history) as score 
from student_info;
//计算得出的总行数查询后赋给cur_row变量
select count(*) into cur_row from student_info;
open cur_work;
repeat
set i=i+1;
fetch cur_work into score_all;

select score_all;

until i>=cur_row end repeat;
close cur_work;
end
//
delimiter ;

结果:

但是这个方法是我们自己打出来的，而mysql 有repeat---until,我们可以结合NOT FOUND条件处理

delimiter //
drop procedure if exists work4_demo //
create procedure work4_demo()
begin
declare score_all int;
declare cur_end int default 0;//声明一个变量表明还有数据可遍历
declare cur_work cursor for select (math+chinese+music+history) as score 
from student_info;
declare continue handler for NOT FOUND set cur_end=1;
open cur_work;
repeat
fetch cur_work into score_all;

select score_all;

until cur_end=1 end repeat;
close cur_work;
end
//
delimiter ;

结果:

你会发现最后一个出现了两次...

解决方案：声明处理的hanlder不再是continue，而是exit即可达到目的。

即：declare exit handler for NOT FOUND set cur_end=1;

到这里为止...上面的作业也很容易做出来了

delimiter //
drop procedure if exists work4_demo //
create procedure work4_demo()
begin
declare score_all int;
declare i int default 0;
declare cur_end int default 0;
declare cur_work cursor for select (math+chinese+music+history) as score 
from student_info;
declare continue handler for NOT FOUND set cur_end=1;
open cur_work;
repeat
set i=i+1;
fetch cur_work into score_all;
UPDATE student_info set total = score_all where sn=i;

until cur_end=1 end repeat;
close cur_work;
end
//
delimiter ;