游标详解
1.游标是啥玩意?
简单的说:游标(cursor)就是游动的标识,啥意思呢,通俗的这么说,一条sql取出对应n条结果资源的接口/句柄,就是游标,沿着游标可以一次取出一行。
2.怎么使用游标?
//1.声明/定义一个游标
declare 声明;declare 游标名 cursor for select_statement;
//2.打开一个游标
open 打开;open 游标名
//3.取值
fetch 取值;fetch 游标名 into var1,var2[,…]
//4.关闭一个游标
close 关闭;close 游标名;
3.游标实战
未使用游标:
create procedure p1()
begin
select * from category;
end$
call p1();
我们如何循环游标来取出所有行?
思路:
1.计算所有行select count(*)
DELIMITER $$
DROP PROCEDURE IF EXISTS `p2`$$
CREATE PROCEDURE p2()
BEGIN
DECLARE row_id INT;
DECLARE row_name VARCHAR(90);
DECLARE row_amount INT;
DECLARE cnt INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT id,NAME,amount FROM test;
SELECT COUNT(*) INTO cnt FROM test;
OPEN cur;
REPEAT
SET i:=i+1;
FETCH cur INTO row_id,row_name,row_amount;
SELECT row_id,row_name,row_amount;
UNTIL i>=cnt END REPEAT;
CLOSE cur;
END$$
call p2();
一次显示一条结果,如图
2.给游标定义一个越界的标识
//在mysql游标(cursor)中,可以定义continue handler来操作一个越界标识,使用语法:
declare continue handler for NOT FOUND statemet
(当没数据的时候要执行的语句)
//这句话的意思是说,我要声明一个句柄事件,你往后取,一旦发生NOT FOUND 事件就会出发set done=true 这个语句
DELIMITER $$
DROP PROCEDURE IF EXISTS `p_zhh`$$
CREATE PROCEDURE `p_zhh`(IN v_amount INT)
BEGIN
DECLARE t_id INT;
DECLARE t_amount INT;
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT id, amount FROM test WHERE amount = v_amount;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
emp: LOOP
FETCH cur INTO t_id, t_amount;
IF done THEN
LEAVE emp;
END IF;
IF t_amount = v_amount THEN
UPDATE test t SET t.amount = t.amount + 1 WHERE t.id = t_id;
END IF;
END LOOP emp;
CLOSE cur;
END$$
DELIMITER ;
call p_zhh(0);
如果为0 ,数量加1,可以继续
call p_zhh(1);
测试用的数据表及数据:
/*Table structure for table `test` */
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(8) NOT NULL,
`name` varchar(128) DEFAULT NULL,
`amount` int(8) DEFAULT NULL,
`type` smallint(2) DEFAULT NULL COMMENT '1,手机;2,充值卡;3,其他',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `test` */
insert into `test`(`id`,`name`,`amount`,`type`) values (1,'手机类型',427,1),(2,'CDMA手机',1,1),(3,'GSM手机',1,1),(4,'3G手机',1,1),(5,'双模手机',1,3),(6,'手机配件',265,3),(7,'充电器',6,3),(8,'耳机',6,3),(9,'电池',6,3),(11,'读卡器和内存卡',6,3),(12,'小灵通充值卡',47,2),(13,'固话充值卡',12,2),(14,'移动手机充值卡',12,2),(15,'联通手机充值卡',12,2);