MySQL 游标 循环

MySQL游标的使用

A simple cursor of mysql

step1:
新建表tb_test;

create table tb_test(
	id int
);

insert into tb_test values ('1');
insert into tb_test values ('2');
insert into tb_test values ('3');

step2:
声明存储过程sp_test;

delimiter $$  
create procedure sp_test()   
BEGIN  
    declare tmp int default 0;
    declare sum int default 0;
    declare done int default -1;
	
	/* 声明游标 */
	declare myCursor cursor for select id from tb_test;
	
	/* 当游标到达尾部时，mysql自动设置done=1 */   
	declare continue handler for not found set done=1;
	
	/* 打开游标 */
	open myCursor;
	
	/* 循环开始 */
	myLoop: LOOP
	
		/* 移动游标并赋值 */
		fetch myCursor into tmp;
		
		if done = 1 then 
		leave myLoop;
		end if;
		
		/* do something */
		set sum = sum + tmp;
	
	/* 循环结束 */
	end loop myLoop;
	
	insert into tb_test values(sum);
	
	/* 关闭游标 */
	close myCursor;
END  
$$  
delimiter ; 

step3:
call sp_test();
select * from tb_test();
+----+  
| id |  
+----+  
|  1 |  
|  2 |  
|  3 |
|  6 |  
+----+