//建立表结构
create table person(name varchar(10));

insert into person values(¹a¹);
insert into person values(¹b¹);
insert into person values(¹c¹);
insert into person values(¹d¹);
insert into person values(¹e¹);

//初始化
drop procedure if exists cursorTest

//建立
CREATE PROCEDURE cursorTest()
BEGIN

//定义变量
declare name1 varchar(10) default ¹¹ ;
declare name2 varchar(100) default ¹¹ ;

declare mycursor CURSOR FOR select name from person;

declare CONTINUE HANDLER FOR SQLSTATE ¹02000¹ SET tmpname = null;

//打开游标
OPEN mycursor;

//遍历游标
FETCH mycursor INTO name1;

//把游标查询出的 name 都加起并用 ; 号隔开
WHILE( name1 is not null) DO
set name1 = CONCAT(name1, ";") ;
set name2 = CONCAT(name2, name1) ;
FETCH mycursor INTO name1;
END WHILE;
CLOSE mycursor;

select mycursor;
END;

//调用游标
call mycursor()

-- 定义本地变量

DECLARE o varchar(128);

-- 定义游标

DECLARE ordernumbers CURSOR

FOR

SELECT callee_name FROM account_tbl where acct_timeduration=10800;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;

SET no_more_departments=0;

-- 打开游标

OPEN ordernumbers;

-- 循环所有的行

REPEAT

-- Get order number

FETCH ordernumbers INTO o;

update account set allMoney=allMoney+72,lastMonthConsume=lastMonthConsume-72 where NumTg=@o;


-- 循环结束

UNTIL no_more_departments
END REPEAT;

-- 关闭游标

CLOSE ordernumbers;