10、游标（Cursor）的定义及使⽤
在 MySQL 中，存储过程或函数中的查询有时会返回多条记录，⽽使⽤简单的 SELECT 语句，没有办法得到第⼀⾏、下⼀⾏或前⼗⾏的数据，这时可以使⽤游标来逐条读取查询结果集中的记录。游标在部分资料中也被称为光标。
关系数据库管理系统实质是⾯向集合的，在 MySQL 中并没有⼀种描述表中单⼀记录的表达形式，除⾮使⽤ WHERE ⼦句来限制只有⼀条记录被选中。所以有时我们必须借助于游标来进⾏单条记录的数据处理。
⼀般通过游标定位到结果集的某⼀⾏进⾏数据修改。
结果集是符合 SQL 语句的所有记录的集合。
个⼈理解游标就是⼀个标识，⽤来标识数据取到了什么地⽅，如果你了解编程语⾔，可以把他理解成数组中的下标。
不像多数 DBMS，MySQL 游标只能⽤于存储过程和函数。
下⾯介绍游标的使⽤，主要包括游标的声明、打开、使⽤和关闭。
1. 声明游标（游标只存在于存储过程中）
MySQL 中使⽤ DECLARE 关键字来声明游标，并定义相应的 SELECT 语句，根据需要添加 WHERE 和其它⼦句。其语法的基本形式如下：
DECLARE cursor_name CURSOR FOR select_statement;
其中，cursor_name 表⽰游标的名称；
select_statement 表⽰ SELECT 语句，可以返回⼀⾏或多⾏数据。
例 1
下⾯声明⼀个名为 nameCursor 的游标，代码如下：
mysql>DELIMITER//
mysql>CREATE PROCEDURE processnames()创建存储过程
->BEGIN
->DECLARE nameCursor CURSOR声明游标
->FOR
->SELECT name FROM tb_student;
->END//
以上语句定义了 nameCursor 游标，游标只局限于存储过程中，存储过程处理完成后，游标就消失了。
2. 打开游标
声明游标之后，要想从游标中提取数据，必须⾸先打开游标。在 MySQL 中，打开游标通过 OPEN 关键字来实现，其语法格式如下：OPEN cursor_name;
其中，cursor_name 表⽰所要打开游标的名称。需要注意的是，打开⼀个游标时，游标并不指向第⼀条记录，⽽是指向第⼀条记录的前边。
在程序中，⼀个游标可以打开多次。⽤户打开游标后，其他⽤户或程序可能正在更新数据表，所以有时会导致⽤户每次打开游标后，显⽰的结果都不同。
3. 使⽤游标
游标顺利打开后，可以使⽤ INTO 语句来读取数据，其语法形式如下：
FETCH cursor_name INTO var_name [,var_name]...
上述语句中，将游标 cursor_name 中 SELECT 语句的执⾏结果保存到变量参数 var_name 中。变量参数 var_name 必须在游标使⽤之前定义。使⽤游标类似⾼级语⾔中的数组遍历，当第⼀次使⽤游标时，此时游标指向结果集的第⼀条记录。
MySQL 的游标是只读的，也就是说，你只能顺序地从开始往后读取结果集，不能从后往前，也不能直接跳到中间的记录。
4. 关闭游标
游标使⽤完毕后，要及时关闭，在 MySQL 中，使⽤ CLOSE 关键字关闭游标，其语法格式如下：
CLOSE cursor_name;
CLOSE 释放游标使⽤的所有内部内存和资源，因此每个游标不再需要时都应该关闭。
在⼀个游标关闭后，如果没有重新打开，则不能使⽤它。但是，使⽤声明过的游标不需要再次声明，
⽤ OPEN 语句打开它就可以了。如果你不明确关闭游标，MySQL 将会在到达 END 语句时⾃动关闭它。游标关闭之后，不能使⽤ FETCH 来使⽤该游标。
例 2
创建 student 数据表，并插⼊数据，SQL 语句和运⾏结果如下：
create table student(
stuId int primary key auto_increment,
stuName varchar(20),
stuSex varchar(2),
stuAge int
)default charset=utf8;
insert into student(stuName,stuSex,stuAge)values
('⼩明','男',20),
('⼩花','⼥',19),
('⼤⾚','男',20),
('可乐','男',19),
('莹莹','⼥',19);
创建存储过程 test_cursor，并创建游标 cur_test，查询 users 数据表中的第 3 条记录，SQL 语句和执⾏过程如下：
delimiter//
create procedure p1()
begin
declare id int;
declare name varchar(100)character set utf8;
declare done int default0;
-- 声明游标
declare mc cursor for select stuId,stuName from student where stuAge >19;
declare continue handler for not found set done =1;
-- 打开游标
open mc;
-- 获取结果
fetch mc into id,name;
-- 这⾥是为了显⽰获取结果
select id,name;
-- 关闭游标
close mc;
end//
delimiter;
试使⽤ 三种⽅式 使⽤游标创建⼀个存储过程，统计年龄⼤于19的记录的数量
Loop循环
-- 定义语法结束符号
delimiter//
-- 创建⼀个名称为 p2 的存储过程
create procedure p2()
begin
-- 创建⽤于接收游标值的变量
declare id,age,total int;
-
- 注意接收游标值为中⽂时需要给变量指定字符集为utf8
declare name,sex varchar(20)character set utf8;
-- 游标结束的标志
declare done int default0;
-- 声明游标
declare cur cursor for select stuId,stuName,stuSex,stuAge from student where stuAge >19; -- 指定游标循环结束时的返回值
declare continue handler for not found set done =1;
-- 打开游标
open cur;
-- 初始化变量
set total =0;
-- loop 循环
xxx:loop
-- 根据游标当前指向的⼀条数据
fetch cur into id,name,sex,age;
-- 当游标的返回值为 1 时退出 loop循环
if done =1then
leave xxx;
end if;
-- 累计
set total = total +1;
end loop;
-
- 关闭游标
close cur;
-- 输出累计的结果
select total;
end//
fetch最佳用法delimiter;
while 循环
delimiter /-- 创建⼀个 名称为 p3 的存储过程create procedure p3()
-- 创建⽤于接收游标值的变量
declare id,age,total int;
-- 注意接收游标值为中⽂时需要给变量指定字符集为utf8
declare name,sex varchar(20)character set utf8;
-- 游标结束的标志
declare done int default0;
-- 声明游标
declare cur cursor for select stuId,stuName,stuSex,stuAge from student where stuAge >19; -- 指定游标循环结束时的返回值
declare continue handler for not found set done =1;
-- 打开游标
open cur;
-- 初始化变量
set total =0;
-- while 循环
while done !=1do
fetch cur into id,name,sex,age;
if done !=1then
set total = total +1;
end if;
end while;
-- 关闭游标
close cur;
-- 输出累计的结果
select total;
end//
delimiter;
repeat 循环
-- 创建⽤于接收游标值的变量
declare id,age,total int;
-- 注意接收游标值为中⽂时需要给变量指定字符集为utf8
declare name,sex varchar(20)character set utf8;
-- 游标结束的标志
declare done int default0;
-- 声明游标
declare cur cursor for select stuId,stuName,stuSex,stuAge from student where stuAge >19; -- 指定游标循环结束时的返回值
declare continue handler for not found set done =1;
-- 打开游标
open cur;
-- 初始化变量
set total =0;
-- repeat 循环
repeat
fetch cur into id,name,sex,age;
if done !=1then
set total = total +1;
end if;
until done =1
end repeat;
-- 关闭游标
close cur;
-- 输出累计的结果
select total;
end//
delimiter;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论