CREATE DEFINER=root@% FUNCTION test() RETURNS int(11)
BEGIN

-- 是否达到记录的末尾 
declare bOF int default 0;    

-- 定义变量
-- 投资人id
declare investor_id1 int;
-- 收益表 实际金额 合计值
declare actual_amount1 decimal(10,2);
    
declare investor_id2 int;
declare actual_amount2 decimal(10,2);
-- 定义游标（获取每个投资人的累计收益）
DECLARE cur_sub CURSOR FOR select investor_id,sum(actual_amount) from mrxdtp_profit_082022 where  project_id = '77'  GROUP BY investor_id ORDER BY investor_id DESC;
-- 没有数据时，设置 bOF
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bOF = 1;
OPEN cur_sub;

FETCH cur_sub INTO investor_id1, actual_amount1; /*获取第一条记录*/

while bOF <> 1 do

    -- 更新认购表 累计金额
    update mrxdtp_subscription_test set cumulative_income = actual_amount1 where project_id = 77 and investor_id = investor_id1;
    -- 测试        
    #insert into Test(investor_id,cumulative_income,project_id) values(investor_id1,actual_amount1,77);
    -- 获取下一条数据
    FETCH cur_sub INTO investor_id1, actual_amount1;
end while;
    
close cur_sub;

RETURN 0;

END

存储方法的调用:select test();

存储过程创建

CREATE PROCEDURE p_next_id(kind_name VARCHAR(30), i_length int,currentSeqNo VARCHAR(3),OUT o_result INT)
BEGIN

 SET @a= NULL;
 SET @b= NULL;
 SELECT id INTO @a FROM t_seq WHERE number= currentSeqNo and length= i_length ;          IF (@a is null ) THEN
        select min(id) into @a FROM t_seq where length = i_length;
        select number  INTO @b FROM t_seq WHERE id = @a;ELSE
    select number  INTO @b FROM t_seq WHERE id = @a+1;        
 END IF;        
 SELECT @b INTO o_result;     

END

存储过程调用

call p_next_id('t_factory',2,'0',@result); -- 上面的存储过程含有四个参数，所以这里调用的时候，也需要传递4个参数：输入参数填写值，输出参数用变量表示@result
select @result; -- 这句话是在控制台显示变量值