2020 Mysql 存储过程

最新推荐文章于 2024-04-15 10:15:00 发布

超级大蚂蚁

最新推荐文章于 2024-04-15 10:15:00 发布

阅读量108

点赞数

分类专栏：笔记

本文链接：https://blog.csdn.net/jintaocccq/article/details/107744105

版权

笔记专栏收录该内容

41 篇文章 3 订阅

订阅专栏

DROP  procedure IF EXISTS t_from1_to_from2_1;
CREATE  procedure t_from1_to_from2_1()

BEGIN
	-- 定义变量
	DECLARE i INT DEFAULT 0; 
	DECLARE fid varchar(255);
	DECLARE sum INT DEFAULT 0; 
	-- 结果集
	DECLARE report CURSOR FOR 	select t.fid ,sum(t.sum) sum from (
	select f1.fid ,  f1.count * f2.price 	 as sum 
	from from1 f1
  LEFT JOIN from2 f2 on f1.cid = f2.cid
	) t  GROUP BY t.fid ;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET i = 1; 
	-- 打开游标
	open report;
				out_loop:
					LOOP
						FETCH report INTO fid,sum;
						IF (i =1) THEN
							LEAVE out_loop;
						END IF;
						
						if (sum is null or sum = 0) then
							set @flag = (select sum(t.sum) sum from (
									select f1.fid ,  f1.count * f2.price 	 as sum 
									from from1 f1
									LEFT JOIN from2 f2 on f1.cid = f2.cid
									) t where t.fid = fid  GROUP BY t.fid )
								update from2 set price = @flag  where cid = fid;
							SET i = 0;
						ELSE
							update from2 set price = sum  where cid = fid;
							SET i = 0;
						end if;
						
				END LOOP out_loop;
 
  -- 关闭游标
	close report;

END


call t_from1_to_from2_1();