简单表结构如下:
要求的的话,也就是通过将test中的value值累加(ID升序排列),类似于贪心算法,尽可能多加,但是和得小于450,那么将所有满足的行ID打印出来
(注意:会多直到值大于450停止,也就是会多添加一个ID,需要调用程序进行相关控制)。
-- 初始化
drop procedure if exists useCursor;
-- 建立 存储过程 create
CREATE PROCEDURE useCursor()
BEGIN
-- 局部变量的定义 declare
declare total INT default 0 ;
declare tmpid INT default 0 ;
declare tmp INT default 0 ;
declare allId varchar(255) default '' ;
declare cur1 CURSOR FOR SELECT value FROM test.test ORDER BY id ASC ;
declare cur2 CURSOR FOR SELECT id FROM test.test ORDER BY id ASC;
-- 开游标
OPEN cur1;
OPEN cur2;
-- 游标向下走一步
FETCH cur1 INTO tmp;
FETCH cur2 INTO tmpid;
repeat
set total = total + tmp;
set allId = CONCAT(allId ,CONCAT(tmpid ,";")) ;
-- 游标向下走一步
FETCH cur1 INTO tmp;
FETCH cur2 INTO tmpid;
until total > 450
end repeat;
/*
-- 循环体
WHILE ( total <= 450 ) DO
set total = total + tmp;
set tmpName = CONCAT(tmpid ,";") ;
set allName = CONCAT(allName ,tmpName) ;
-- 游标向下走一步
FETCH cur1 INTO tmp;
FETCH cur2 INTO tmpid;
END WHILE;
*/
CLOSE cur1;
CLOSE cur2;
select allId ;
END;
call useCursor()