mysql 使用游标聚合表数据

DROP PROCEDURE IF EXISTS pro_tpss_daily_packg_hu;  
DELIMITER //
CREATE PROCEDURE pro_tpss_daily_packg_hu(IN p_in VARCHAR(200)) 


BEGIN 
DECLARE  no_more_products, quantity_in_stock INT DEFAULT 0; 
declare v_sql varchar(5000);
DECLARE group_sql VARCHAR(5000);
DECLARE mvn_code VARCHAR(50);
DECLARE detail_temp_table VARCHAR(200);
DECLARE detail_total_table VARCHAR(200);

DECLARE  cur_product CURSOR FOR  SELECT DISTINCT mvno_code FROM t_tpss_daily_dim_hu t where t.task_status=0; 
DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  no_more_products = 1;

OPEN  cur_product;

REPEAT 
FETCH  cur_product INTO mvn_code;

-- 创建表名称
set detail_total_table = concat('daily_total_',mvn_code ,'_',p_in);
set detail_temp_table = concat('daily_temp_',mvn_code);

update t_tpss_daily_dim_hu t
set t.task_status=1
where t.mvno_code=mvn_code;

set v_sql=concat('DROP TABLE IF EXISTS ',detail_temp_table );


set @v_sql=v_sql; 
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;


-- 聚合sql
set group_sql = concat('select userName, mvnoCode, orgCode, month, day, pkgId, mcc, visitCountry, imei, SUM(flowSize) as flowSize 
from m_flow_',mvn_code ,'_',p_in,'
group by userName, visitCountry, orgCode, day, pkgId
order by orgCode,day,visitCountry,flowSize desc');

set v_sql=concat('CREATE table ',detail_temp_table,' as ', group_sql);
set @v_sql=v_sql; 
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;

set v_sql=concat('DROP TABLE IF EXISTS ',detail_total_table );
set @v_sql=v_sql; 
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;

-- 关联表数据
set group_sql = concat('select d.userName, d.mvnoCode, d.orgCode, d.month, d.day, d.pkgId, d.mcc, d.visitCountry, d.imei, d.flowSize/1024 as flowSize, t.fee as money, t.threshold as threshold
from ',detail_temp_table,
' d left join t_tpss_daily_dim_hu t 
on d.mvnoCode=t.mvno_code and (t.org_code like CONCAT("%",d.orgCode,"%") or (t.org_code=-1 and t.ex_org_code not like CONCAT("%",d.orgCode,"%")))
and (t.country like CONCAT("%",d.visitCountry,"%") or (t.country=-1 and t.ex_country not like CONCAT("%",d.visitCountry,"%")))');

-- 创建统计表
set v_sql=concat('CREATE table ',detail_total_table,' as ', group_sql);

set @v_sql=v_sql; 
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;

-- 包天没有超过阀值的用户不收钱
set v_sql=concat('update ',detail_total_table,' t set t.money=0 where t.flowSize<t.threshold');

set @v_sql=v_sql; 
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;

update t_tpss_daily_dim_hu t
set t.task_status=2
where t.mvno_code=mvn_code;

UNTIL no_more_products  END REPEAT;  
CLOSE cur_product;
END //

DELIMITER