mysql 游标循环_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 |

+----+

0

顶

3

踩

分享到：

2012-06-24 19:01

浏览 18055

分类:数据库

评论