mysql数据存储过程代码_mysql数据库存储过程

CREATE DEFINER = 'root'@'localhost'

PROCEDURE myhouse.calculate_wtht_server_fee(IN store_Id VARCHAR(255), IN startDate VARCHAR(30), IN endDate VARCHAR(30))

BEGIN

DECLARE i int DEFAULT 1;

DECLARE v_heTongBian varchar(50);

DECLARE v_wordType varchar(1);

DECLARE v_albsz varchar(1);

DECLARE v_bcChoinceTwo varchar(30);

DECLARE v_serviceMiddleMoney int DEFAULT 0;

DECLARE v_zhuanRangPriceT int DEFAULT 0;

DECLARE v_bcChoinceNote11 varchar(10);

DECLARE v_jdr varchar(10);

DECLARE temp_count int;

declare tmp_sum int default 0;

DECLARE lp_flag boolean DEFAULT TRUE;

DECLARE wtht_cursor CURSOR FOR SELECT heTongBian,wordType,albsz,bcChoinceTwo,serviceMiddleMoney,zhuanRangPriceT,bcChoinceNote11,jdr FROM wtht WHERE jdr in (select xm from sys_yh where find_in_set(StoreID, store_Id) and (isDel <> -1 or isDel is null)) AND creatTime >= startDate AND creatTime < endDate and wordType<>6 and wordType<>5 and (flag=3 or flag=-3) order by heTongBian,wordType desc;

-- handler 句柄

DECLARE continue handler for NOT FOUND set lp_flag = false;

drop table if exists temp_wtht_fee;

-- 创建临时表收集数据

CREATE temporary TABLE `temp_wtht_fee` (

`agentName` varchar(20) COMMENT '经纪人姓名',

`secondContractTotal` int COMMENT '二手买卖签单业绩',

`secondSignNum` int COMMENT '签单数量'

) ENGINE=InnoDB;

OPEN wtht_cursor;

WHILE lp_flag DO

fetch wtht_cursor into v_heTongBian,v_wordType,v_albsz,v_bcChoinceTwo,v_serviceMiddleMoney,v_zhuanRangPriceT,v_bcChoinceNote11,v_jdr;

SELECT COUNT(*) INTO temp_count FROM temp_wtht_fee WHERE agentName=v_jdr;

IF temp_count=0 THEN

INSERT INTO temp_wtht_fee VALUES (v_jdr,0,0);

end if;

IF i%3<>0 THEN

SET i = i + 1;

if v_wordType = 4 then

if v_bcChoinceTwo like '%⑨%' then

set tmp_sum = tmp_sum + 3000;

end if;

if v_albsz = 3 then

if v_bcChoinceTwo like '%⑧%' then

set tmp_sum = tmp_sum + v_zhuanRangPriceT*0.005;

fetch wtht_cursor into v_heTongBian,v_wordType,v_albsz,v_bcChoinceTwo,v_serviceMiddleMoney,v_zhuanRangPriceT,v_bcChoinceNote11,v_jdr;

set i = i + 1;

set tmp_sum = tmp_sum + v_serviceMiddleMoney;

elseif v_bcChoinceTwo like '%⑪%' then

set tmp_sum = tmp_sum + v_bcChoinceNote11;

fetch wtht_cursor into v_heTongBian,v_wordType,v_albsz,v_bcChoinceTwo,v_serviceMiddleMoney,v_zhuanRangPriceT,v_bcChoinceNote11,v_jdr;

set i = i + 1;

set tmp_sum = tmp_sum + v_serviceMiddleMoney;

else

fetch wtht_cursor into v_heTongBian,v_wordType,v_albsz,v_bcChoinceTwo,v_serviceMiddleMoney,v_zhuanRangPriceT,v_bcChoinceNote11,v_jdr;

set i = i + 1;

set tmp_sum = tmp_sum + v_serviceMiddleMoney;

end if;

else

fetch wtht_cursor into v_heTongBian,v_wordType,v_albsz,v_bcChoinceTwo,v_serviceMiddleMoney,v_zhuanRangPriceT,v_bcChoinceNote11,v_jdr;

set i = i + 1;

set tmp_sum = tmp_sum + v_serviceMiddleMoney;

end if;

elseif v_wordType = 1 then

set tmp_sum = tmp_sum + v_serviceMiddleMoney;

end if;

ELSE

SET i = 1;

set tmp_sum = tmp_sum + v_serviceMiddleMoney;

UPDATE temp_wtht_fee set secondSignNum=secondSignNum+1,secondContractTotal=secondContractTotal+tmp_sum WHERE agentName=v_jdr;

set tmp_sum = 0;

END IF;

END WHILE;

CLOSE wtht_cursor;

END