Mysql必知必会:游标的使用

举报
Python新视野 发表于 2022/04/28 19:03:23 2022/04/28
【摘要】 SQL 检索操作返回结果集,如果简单地使用 SELECT 语句,没有办法得到第一行、下一行或前 10 行。有时,需要在检索出来的行中前进或后退一行或多行,这就是游标的用途所在。游标(cursor)是一个存储在 DBMS 服务器上的数据库查询, 它不是一条 SELECT 语句,而是被该语句检索出来的结果集。在存储了 游标之后,应用程序可以根据需要滚动或浏览其中的数据。 使用游标游标使用的步骤:...

SQL 检索操作返回结果集,如果简单地使用 SELECT 语句,没有办法得到第一行、下一行或前 10 行。有时,需要在检索出来的行中前进或后退一行或多行,这就是游标的用途所在。游标(cursor)是一个存储在 DBMS 服务器上的数据库查询, 它不是一条 SELECT 语句,而是被该语句检索出来的结果集。在存储了 游标之后,应用程序可以根据需要滚动或浏览其中的数据。

使用游标

游标使用的步骤:

  • 在使用游标前,必须声明(定义)它。这个过程实际上没有检索数据, 它只是定义要使用的 SELECT 语句和游标选项。
  • 一旦声明,就必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来。
  • 对于填有数据的游标,根据需要取出(检索)各行。
  • 在结束游标使用时,必须关闭游标,可能的话,释放游标。

声明游标后,可以根据需要频繁地 打开或关闭 游标。在游标打开时,可根据需要频繁地执行 操作。

注意:不像多数DBMS,MySQL游标只能用于存储过程(和函数)。


创建游标

使用 DECLEAR 来创建游标,DECLARE 命名游标,并定义相应的 SELECT 语句,根据需要带 WHERE 和 其他子句。

下面的语句定义了名为 ordernumbers 的游标,使用了可以检索所有订单的 SELECT 语句。

Order表中的信息:

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
	-- 定义游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;

这个存储过程中,使用 DECLARE 语句用来定义和命名游标。存储过程处理完成后,游标就消失(因为它局限于存储过程)。


使用游标数据

使用 OPEN 语句来打开游标,CLOSE 语句关闭游标,在一个游标被打开后,可以使用 FETCH 语句分别访问它的每一行。FETCH 指定检索的数据(所需的列),数据存储的位置(定义的变量)。 它还向前移动游标中的内部行指针,使下一条 FETCH 语句检索下一行(不重复读取同一行)。

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
    -- 定义局部变量
    DECLARE num INT;
    -- 定义游标
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    -- 打开游标
    OPEN ordernumbers;
    -- 获取第一行数据
    FETCH ordernumbers INTO num;
    -- 查询结果
    SELECT num;
    -- 关闭游标
    CLOSE ordernumbers;
END;
CALL processorder();

其中 FETCH 用来检索当前行的 order_num 列(将自动从第一行开始)到一个名为 num 的局部变量中,并将查询 num 的结果。由于只检索到第一行,所以 num 的值为 ‘20005’。

下面,循环检索数据,从第一行到最后一行。

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
	-- 定义局部变量
	DECLARE done BOOLEAN DEFAULT false;
	DECLARE num INT;
	-- 定义游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	-- 定义CONTINUE HANDLER
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=true;
	-- 打开游标
	OPEN ordernumbers;
	-- 循环所有行
	REPEAT
		-- 获取第一行数据
		FETCH ordernumbers INTO num;
	-- 结束循环
	UNTIL done END REPEAT;
	-- 查询结果
	SELECT num;
	-- 关闭游标
	CLOSE ordernumbers;
END;
CALL processorder();

循环了结果集的所有行,所以 num 的值是最后一行的数据。

与上一个例子不同之处是,这个例子的 FETCH 是在 REPEAT 内,因此它反复执行直到 done 为真。

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=true;

这条语句定义了一个 CONTINUE HANDLER,它是在条件出现时被执行的代码。这里,它指出当 SQLSTATE '02000' 出现时,SET done=trueSQLSTATE '02000'是一个未找到条件,当 REPEAT 由于没有更多的行供循环而不能继续时,出现这个条件。


游标的优缺点

优点:游标是面向集合与面向行的设计思想之间的一种桥梁,因为游标是针对行操作的,所以对从数据库中 SELECT 查询得到的每一行可以进行分开的独立的相同或不同的操作,是一种分离的思想。可以满足对某个结果行进行特殊的操作。如基于游标位置的增删改查能力。

缺点

  • 速度较慢
  • 会产生死锁现象
  • 内存大

【版权声明】本文为华为云社区用户原创内容,未经允许不得转载,如需转载请自行联系原作者进行授权。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。