认识游标(cursor), 按字面意思可理解为, 游动的标识, 或者叫做"光标", 这样更容易理解. 就好比现有一张表存储了n行记录, 然后我想每次取出一行, 进行为所欲为, 这时候就用到了游标cursor, 数据的搬运工, 搬运完当前数据(游标指向当前), 然后又移动到下一条数据的位置. "移动", 和 "指向" 这两个词很重要, 跟C的指针有点类似, 举一个链表的例子吧. a -> b -> c -> d, 需求是求计算链表的长度. 则我们需要定义一个游标变量cursor, 默认定位到"a" 节点位置, 还需定义一个技术变量count 默认为0, 然后移动游标cursor (Python里 "=" 即表示"指向"), 沿着 a->b->c->d, 每移动一次, 则游标指向当前节点, 计数器加1.... 应用场景, 在于, 好比一条 select xxx where xxx ; 返回的是一个查询集, 但我不想啪啪啪一顿返回一堆数据, 我要自己逐行检查和写逻辑判断, 这时候就需要cursor. 即, 一条sql, 对应N条数据, 取出(依次 or 自定义顺序) 数据的接口(interface) / 句柄, 就是游标, 沿着游标方向, 依次可以一次取出1行.
语法
-- 文档说明 -- 游标声明必须在procedure 数据处理之前, 和在变量声明之后. DECLARE cursor_name CURSOR FOR select_statement; OPEN cursor_name; -- 打开游标(当前块,唯一命名) FETCH cursor_name INTO var_name, [, var_name] ... -- 读取游标数据, 并前进指针 CLOSE cursor_name; -- 如果不close, 则会在其被声明的复合语句末尾被关闭 案例用之前的goods, 表操作一波. -- 查看一下数据 -- out mysql> select * from goods; +-----+------+-----+ | gid | name | num | +-----+------+-----+ | 1 | cat | 37 | | 2 | dog | 72 | | 3 | pig | 18 | +-----+------+-----+ 3 rows in set (0.09 sec) -- 更新一波 cat 的数量吧 mysql> update goods set num = 100 where gid=1; Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from goods; +-----+------+-----+ | gid | name | num | +-----+------+-----+ | 1 | cat | 100 | | 2 | dog | 72 | | 3 | pig | 18 | +-----+------+-----+ 3 rows in set (0.11 sec) 需求是要逐条取出每行数据, 而不是一下子都给我. -- cursor: 依次获取每行数据 drop procedure if exists cur1; delimiter // create procedure cur1() begin -- select * from goods; 每行有3个值, gid, name, num -- 首先要定义变量来存储 -- 声明和打开游标 -- fetch 每行数据 -- 处理逻辑 -- 关闭游标 end // delimiter ; 具体实现 drop procedure if exists cur1; delimiter // create procedure cur1() begin -- select * from goods; 每行有3个值, gid, name, num -- 首先就为每行数据, 定义相应临时变量来存储 declare tmp_gid int; declare tmp_name varchar(20); declare tmp_num int; -- 声明和打开游标 declare getGoods cursor for select gid, name, num from goods; open getGoods; -- fetch 每行数据, 要一一有对应的变量来接收哦 fetch getGoods into tmp_gid, tmp_name, tmp_num; -- 处理逻辑(这里只打印一下) select tmp_gid, tmp_name, tmp_num; -- 关闭游标 close getGoods; end // delimiter ; -- out mysql> call cur1(); +---------+----------+---------+ | tmp_gid | tmp_name | tmp_num | +---------+----------+---------+ | 1 | cat | 100 | +---------+----------+---------+ 1 row in set (0.14 sec) 与之前直接取出一行的区别在于, 控制权在我们手里, 真的可以为所欲为, 进一步可以进行判断,取值等各种编程操作, 真的可以为所欲为. 如何fetch多行呢? -- fetch 多行 及 游标到尾(没有数据了, 不会报错) drop procedure if exists cur1; delimiter // create procedure cur1() begin declare tmp_gid int; declare tmp_name varchar(20); declare tmp_num int; declare getGoods cursor for select gid, name, num from goods; open getGoods; -- fetch and into val1, var2..... -- fetch 多行 fetch getGoods into tmp_gid, tmp_name, tmp_num; select tmp_gid, tmp_name, tmp_num; fetch getGoods into tmp_gid, tmp_name, tmp_num; select tmp_gid, tmp_name, tmp_num; fetch getGoods into tmp_gid, tmp_name, tmp_num; select tmp_gid, tmp_name, tmp_num; fetch getGoods into tmp_gid, tmp_name, tmp_num; select tmp_gid, tmp_name, tmp_num; -- cursor 即便到尾了(没有data, 也不会报错哦) fetch getGoods into tmp_gid, tmp_name, tmp_num; select tmp_gid, tmp_name, tmp_num; -- close close getGoods; end // delimiter ; -- out mysql> call cur1(); +---------+----------+---------+ | tmp_gid | tmp_name | tmp_num | +---------+----------+---------+ | 1 | cat | 100 | +---------+----------+---------+ 1 row in set (0.20 sec) +---------+----------+---------+ | tmp_gid | tmp_name | tmp_num | +---------+----------+---------+ | 2 | dog | 72 | +---------+----------+---------+ 1 row in set (0.37 sec) +---------+----------+---------+ | tmp_gid | tmp_name | tmp_num | +---------+----------+---------+ | 3 | pig | 18 | +---------+----------+---------+ 1 row in set (0.54 sec) mysql> select * from goods; +-----+------+-----+ | gid | name | num | +-----+------+-----+ | 1 | cat | 100 | | 2 | dog | 72 | | 3 | pig | 18 | +-----+------+-----+ 3 rows in set (0.18 sec) 可以看出, 游标的特点是, 每fetch一次, 就往后游动一次, 即称为游标嘛. 现在, 要采用循环与游标相配合 取出每条数据. 思路可以是先查询到表的行数rows_num;作为循环的退出条件, 然后循环fetch即可.(while, repeat都行). -- 通过cursor, 循环取出每行数据 drop procedure if exists curRepeat; delimiter // create procedure curRepeat() begin declare getGoods cursor for select gid, name, num from goods; open getGoods; -- 循环fetch repeat -- 这里需要一大波的定义变量哦. fetch getGoods into xxx, xxx, ...; select xxx, xxx ...; until i > rows_num end repeat; end // delimiter ; 详细repeat 实现 -- 通过cursor, 循环取出每行数据 drop procedure if exists curRepeat; delimiter // create procedure curRepeat() begin declare tmp_gid int; declare tmp_name varchar(20); declare tmp_num int; declare i int default 0; -- 自增变量 declare rows_num int default 0; -- 存储查询集的行数(循环的退出条件) declare getGoods cursor for select gid, name, num from goods; -- 获取查询集的行数 rows_num, 注意顺序, 操作要放在 declare 之后哦 -- 错误语法: set rows_num := select count(*) from goods; select count(*) into rows_num from goods; open getGoods; -- 循环fetch repeat fetch getGoods into tmp_gid, tmp_name, tmp_num; -- 业务逻辑处理(这里只是简单打印), 游标的作用就在于此, 这里可以为所欲为. select tmp_gid, tmp_name, tmp_num; set i := i + 1; until i > rows_num end repeat; -- 别忘了close close getGoods; end // delimiter ; -- out call curRepeat(); Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +---------+----------+---------+ | tmp_gid | tmp_name | tmp_num | +---------+----------+---------+ | 1 | cat | 100 | +---------+----------+---------+ 1 row in set (0.12 sec) +---------+----------+---------+ | tmp_gid | tmp_name | tmp_num | +---------+----------+---------+ | 2 | dog | 72 | +---------+----------+---------+ 1 row in set (0.26 sec) +---------+----------+---------+ | tmp_gid | tmp_name | tmp_num | +---------+----------+---------+ | 3 | pig | 18 | +---------+----------+---------+ 1 row in set (0.41 sec) 思路二: 游标取值越界时, 利用越界标识判断, 用到DECLARE ... HANDLER... 处理程序
DECLARE handler_type HANDLER FOR condtion_value ... sp_statement. -- cursor 越界标识来退出循环 drop procedure if exists curBorder; delimiter // create procedure curBorder() begin declare tmp_gid int; declare tmp_name varchar(20); declare tmp_num int; -- 游标遍历数据结束的标志 declare done boolean default false; declare getGoods cursor for select gid, name, num from goods; -- 退出的 handler 标记, not found 时执行 -- declare continue handler for NOT FOUND set done := True; -- 解决continue 多取一行的问题, 用 EXit 即可 declare EXIT handler for NOT FOUND set done := True; open getGoods; -- 循环取每行值 repeat fetch getGoods into tmp_gid, tmp_name, tmp_num; -- 业务逻辑处理 select tmp_gid, tmp_name, tmp_num; until done=True end repeat; -- 总是忘了最后关闭游标呀 close getGoods; end // delimiter ; -- out mysql> call curBorder(); +---------+----------+---------+ | tmp_gid | tmp_name | tmp_num | +---------+----------+---------+ | 1 | cat | 100 | +---------+----------+---------+ 1 row in set (0.12 sec) +---------+----------+---------+ | tmp_gid | tmp_name | tmp_num | +---------+----------+---------+ | 2 | dog | 72 | +---------+----------+---------+ 1 row in set (0.27 sec) +---------+----------+---------+ | tmp_gid | tmp_name | tmp_num | +---------+----------+---------+ | 3 | pig | 18 | +---------+----------+---------+ 1 row in set (0.38 sec) Query OK, 0 rows affected (0.00 sec) 小结 handler 类型
从逻辑上, 就用continue handler 来取出数据 -- 坚持用contine取出所有行,并考虑特殊情况 drop procedure if exists curContinue; delimiter // create procedure curContinue() begin declare tmp_gid int; declare tmp_name varchar(20); declare tmp_num int; declare done boolean default False; declare getGoods cursor for select gid, name, num from goods; declare continue handler for not found set done := True; open getGoods; -- 先fetch 一行出来 fetch getGoods into tmp_gid, tmp_name, tmp_num; repeat -- 先取出一条来出来(不论是0,1或多), 再继续 fetch select tmp_gid, tmp_name, tmp_num; fetch getGoods into tmp_gid, tmp_name, tmp_num; until done = True end repeat; -- colse close getGoods; end // delimiter ; -- out mysql> call curContinue(); +---------+----------+---------+ | tmp_gid | tmp_name | tmp_num | +---------+----------+---------+ | 1 | cat | 100 | +---------+----------+---------+ 1 row in set (0.14 sec) +---------+----------+---------+ | tmp_gid | tmp_name | tmp_num | +---------+----------+---------+ | 2 | dog | 72 | +---------+----------+---------+ 1 row in set (0.28 sec) +---------+----------+---------+ | tmp_gid | tmp_name | tmp_num | +---------+----------+---------+ | 3 | pig | 18 | +---------+----------+---------+ 1 row in set (0.39 sec) Query OK, 0 rows affected (0.00 sec) 同样, 换成while循环也是一样的. drop procedure if exists cur_while; delimiter // create procedure cur_while() begin -- 声明临时变量来存储fetch每行值和一状态变量 declare tmp_gid int; declare tmp_name varchar(20); declare tmp_num int; declare done boolean default True; -- 声明游标对应的查询集 declare getGoods cursor for select gid, name, num from goods; -- 声明退出条件的 handler declare continue handler for not found set done := False; open getGoods; -- 先取一行 fetch getGoods into tmp_gid, tmp_name, tmp_num; -- while 循环 来取数据 while done do -- 处理每行的业务逻辑 select tmp_gid, tmp_name, tmp_num; -- 继续往后fetch fetch getGoods into tmp_gid, tmp_name, tmp_num; end while; close getGoods; end // delimiter ; -- out mysql> call cur_while(); +---------+----------+---------+ | tmp_gid | tmp_name | tmp_num | +---------+----------+---------+ | 1 | cat | 100 | +---------+----------+---------+ 1 row in set (0.12 sec) +---------+----------+---------+ | tmp_gid | tmp_name | tmp_num | +---------+----------+---------+ | 2 | dog | 72 | +---------+----------+---------+ 1 row in set (0.27 sec) +---------+----------+---------+ | tmp_gid | tmp_name | tmp_num | +---------+----------+---------+ | 3 | pig | 18 | +---------+----------+---------+ 1 row in set (0.38 sec) Query OK, 0 rows affected (0.00 sec) 小结游标
|
|