mysql存储过程-积分计算

create procedure scoreRule()
begin
declare v_count int default 0 ;
declare has_row int  default 0;
declare v_agency_id int default 0;
declare v_static_time varchar(20);
declare v_oper_type int default 1;
declare v_money float default 0;
declare v_rate float ;
declare v_score_rule int;
declare v_create_time datetime ;
declare v_score int default 0;
declare v_total_score int default 0;
declare v_old_score int default 0;
declare vc_agency_id int default 0;
declare vc_create_time varchar(10);
declare vc_consume_type int default 0;
declare vc_area_id int default 0;
declare cur_agency_trans cursor for select min(f.agency_id)  as agencyID 
       from t_ebiz_fund_transaction r ,t_ebiz_fund f where r.fund_inst_id = f.fund_inst_id and f.fund_type =2 and r.oper_type in ('1','2','7' ) and
       DATE_FORMAT(r.create_Time,'%Y%m%d') =DATE_FORMAT(STR_TO_DATE(vc_create_time,'%Y%m%d'),'%Y%m%d') group by f.agency_id order by f.agency_id desc;
declare continue handler for not found set has_row = 1;
set v_create_time = now() ;
set vc_create_time =DATE_FORMAT(date_add(now(),interval -1 day),'%Y%m%d');
-- set vc_create_time='20120320';
/*遍历代理商钱包流水表的代理商钱包数据，往表t_ebiz_score_info中插入数据*/
open cur_agency_trans ;
    agency_rec:loop
    fetch cur_agency_trans into vc_agency_id ;
    /*  如果没有找到记录，就跳出循环*/
if has_row =1 then
leave agency_rec;
end if;
     /*计算下一个代理商，重置总积分和网点最近日期的积分值*/
      set v_total_score = 0;
      set v_old_score =0;
       /*根据代理商iID，获取代理商的地市ID，增加地市ID选取积分规则*/
      select t.area_id into vc_area_id from t_ebiz_agency_info t where t.agency_id = vc_agency_id ;
      begin
       declare has_detail_row int default 0;
       declare cur_detail_trans cursor for select min(r.oper_type) as operType,-1*sum(r.amount) as money
       from t_ebiz_fund_transaction r ,t_ebiz_fund f where r.fund_inst_id = f.fund_inst_id and f.fund_type =2 and r.oper_type in ('1','2','7' ) and f.agency_id = vc_agency_id and DATE_FORMAT(r.create_Time,'%Y%m%d') = vc_create_time group by     
       DATE_FORMAT(r.create_Time,'%Y%m%d'),f.agency_id ,r.oper_type;
      declare continue handler for not found set has_detail_row =1;
       /*  通过指定的代理商和日期，遍历代理商钱包流水记录表,往获取积分明细表插入数据*/
open cur_detail_trans;
detail_rec:loop
fetch cur_detail_trans into v_oper_type,v_money;
      /*  如果没有找到记录，就跳出循环*/
if has_detail_row =1 then
leave detail_rec;
end if;
set  v_count = v_count + 1;
       /*根据钱包的类型，获取积分计算规则的类型*/
       if v_oper_type =1 || v_oper_type =7 then
         set vc_consume_type = 1 ;
       end if;
       if v_oper_type =2 then
        set vc_consume_type =2;
       end if;
/*获取积分比率和积分规则ID，地市id根据网点的地市id为先，要是网点对应的地市没有配置规则，就用默认的地市规则area_id =1 */
select  r.rate,r.score_rule_seq into v_rate,v_score_rule from t_ebiz_score_rule r where r.consume_type = vc_consume_type and( abs(v_money)/100 between r.section_start and r.section_end) and r.area_id in( vc_area_id,'1') order by r.area_id desc limit 1;
       set v_score = v_money * v_rate / 100;
insert into t_ebiz_get_score_detail (score_rule_seq,agency_id,create_time,create_staff,consume,score_amount)VALUES  (v_score_rule,vc_agency_id,v_create_time,"system",v_money,v_score);
set v_total_score = v_total_score + v_score;
        commit; 
end loop detail_rec;
close cur_detail_trans;
      end;
      /*往表代理商积分信息表中插入记录  其中网点的积分值=网点最近日期的积分值 + 当天获取的积分值*/
     select t.current_score into v_old_score from t_ebiz_score_info t where t.agency_id = vc_agency_id  order by t.static_time desc limit 1;
     insert into t_ebiz_score_info(agency_id,static_time,total_score,current_score) values(vc_agency_id,v_create_time,v_total_score,v_total_score+v_old_score);
     commit;
end loop agency_rec;
close cur_agency_trans;
end