MySql游标
一、什么是游标
个人觉得就是一个cursor,就是一个标识,用来标识数据取到什么地方了。你也可以把它理解成数组中的下标或者句柄。
二、游标的特性
1、只读的:cursor本身不提供修改数据的操作,只能fetch columns into variables。(当然你可以把数据拿出来以后,再用update语句更新。但是有坑,第三点说明)。
2、不能滚动的:只能向一个方向遍历数据,不能回滚,不能随意进退,不能跳过某些记录。
3、数据敏感的(大坑):游标分为两类,一类为数据敏感类型的游标,一类为非数据敏感类型的游标。
- 数据敏感类型游标:游标指向实际的数据,遍历数据时,如果其他session修改了当前cursor遍历的数据集合,会影响当前游标的行为。
- 非数据敏感类型游标:游标查询的数据集合放入到一个临时表中,游标指向临时表的记录集合。
- mysql cursor: 是数据敏感类型游标。官方英文说明:The server may or may not make a copy of its result table.
- 基于以上说明: 不要更新mysql游标打开表的数据。(也有部分资料说:不要更新游标声明语句中select statement的where条件字段)
游标(cursor)必须在声明处理程序之前被声明,并且变量和条件必须在声明游标或处理程序之前被声明。
三、使用游标
1、声明游标
DECLARE cursor_name CURSOR FOR select_statement
2、打开游标
OPEN cursor_name
3、使用游标
FETCH cursor_name INTO var_name [, var_name] ...
4、关闭游标
CLOSE cursor_name
四、应用举例
1、测试表和数据
1 mysql> show create table salgrade\G; 2 *************************** 1. row *************************** 3 Table: salgrade 4 Create Table: CREATE TABLE `salgrade` ( 5 `grade` mediumint(8) unsigned NOT NULL DEFAULT '0', 6 `losal` decimal(17,2) NOT NULL, 7 `hisal` decimal(17,2) NOT NULL 8 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 9 1 row in set (0.00 sec) 10 11 ERROR: 12 No query specified 13 14 mysql> select * from salgrade; 15 +-------+---------+---------+ 16 | grade | losal | hisal | 17 +-------+---------+---------+ 18 | 1 | 700.00 | 1200.00 | 19 | 2 | 1201.00 | 1400.00 | 20 | 3 | 1401.00 | 2000.00 | 21 | 4 | 2001.00 | 3000.00 | 22 | 5 | 3001.00 | 9999.00 | 23 +-------+---------+---------+ 24 5 rows in set (0.00 sec)
2、测试存储过程
1 drop procedure if exists `will_cursor`; 2 delimiter $$ 3 create procedure will_cursor(in param int, out result varchar(512)) 4 begin 5 declare p_grade int;//定义的变量名不能与列名重复 6 declare p_losal decimal(17,2); 7 declare p_hisal decimal(17,2); 8 declare done int default 0; 9 10 declare will_cur CURSOR for select grade,losal,hisal from salgrade; 11 declare continue handler FOR SQLSTATE '02000' SET done = 1;//数据取完,结束的标识 12 13 if(param = 1 || param = 2) then 14 select concat_ws('_',grade,losal,hisal) into result from salgrade where grade=param; 15 else 16 open will_cur; 17 fetch will_cur into p_grade,p_losal,p_hisal; 18 while done = 0 do 19 select concat_ws('_',result,p_grade,p_losal,p_hisal) into result; 20 fetch will_cur into p_grade,p_losal,p_hisal; 21 end while; 22 close will_cur; 23 end if; 24 end$$ 25 delimiter ;
3、测试结果
1 mysql> call will_cursor(1, @test); 2 Query OK, 1 row affected (0.00 sec) 3 4 mysql> select @test; 5 +------------------+ 6 | @test | 7 +------------------+ 8 | 1_700.00_1200.00 | 9 +------------------+ 10 1 row in set (0.00 sec) 11 12 mysql> call will_cursor(0, @test); 13 Query OK, 0 rows affected, 1 warning (0.00 sec) 14 15 mysql> select @test; 16 +------------------------------------------------------------------------------- 17 -----------+ 18 | @test 19 | 20 +------------------------------------------------------------------------------- 21 -----------+ 22 | 1_700.00_1200.00_2_1201.00_1400.00_3_1401.00_2000.00_4_2001.00_3000.00_5_3001.00_9999.00 | 23 +------------------------------------------------------------------------------- 24 -----------+ 25 1 row in set (0.00 sec)