Mysql必知必会:游标的使用
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=true
。SQLSTATE '02000'
是一个未找到条件,当 REPEAT
由于没有更多的行供循环而不能继续时,出现这个条件。
游标的优缺点
优点:游标是面向集合与面向行的设计思想之间的一种桥梁,因为游标是针对行操作的,所以对从数据库中 SELECT
查询得到的每一行可以进行分开的独立的相同或不同的操作,是一种分离的思想。可以满足对某个结果行进行特殊的操作。如基于游标位置的增删改查能力。
缺点:
- 速度较慢
- 会产生死锁现象
- 内存大
- 点赞
- 收藏
- 关注作者
评论(0)