mysql 存储过程

最新推荐文章于 2024-10-06 16:31:09 发布

iteye_16428

最新推荐文章于 2024-10-06 16:31:09 发布

阅读量99

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/iteye_16428/article/details/82574377

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

begin  
	#声明一个标记，当游标状态为最后一条记录时，修改该变量  
	declare done int;
	#本次处理成功同步（insert/update的商品数量）
	DECLARE INSERT_CUSTOMER_COUNT INT;
	DECLARE UPDATE_CUSTOMER_COUNT INT;
	
	declare shopId VARCHAR (50);
	declare buyerMemberId VARCHAR (100);	
	
	declare mainAccountId VARCHAR (50);
	declare num int(11);
	declare quantity int(11);
	DECLARE payment double;
	

	
	#用于保存循环采番所得到的ID值
	DECLARE orderId VARCHAR(50);
	
	DECLARE tradeSuccessCount INT(11) DEFAULT 0;
	DECLARE tradeSuccessAmount DOUBLE DEFAULT 0;
	DECLARE tradeCloseCount INT(11) DEFAULT 0;
	DECLARE tradeCloseAmount DOUBLE DEFAULT 0;
	DECLARE productBuyCount INT(11) DEFAULT 0;
	DECLARE productCloseCount INT(11) DEFAULT 0;
	DECLARE avgCustomerPrice DOUBLE DEFAULT 0;
	DECLARE oids VARCHAR(5000);
	
	
	#定义游标
	DECLARE WAIT_SYNC_DATA_CUR CURSOR FOR (SELECT GROUP_CONCAT(o.id) as oids,COUNT(o.id) AS countPay, sum(e.quantity) AS quantity, OWNER_ACCOUNT_ID,  o.BUYER_MEMBER_ID, o.FN_SHOP_ID, sum(o.sum_product_payment) AS sumPay FROM u_sync_albb_order o LEFT JOIN u_shop ON SHOP_ID = FN_SHOP_ID LEFT JOIN u_sync_albb_order_entry e ON o.id = e.parent_id WHERE o.IF_STATISTICS_FOR_CUSTOMER = 0 AND o.data_src = 1 AND  o.`status` = orderStatus AND o.FN_SHOP_ID='DPZH0000000200000002'  AND NOT EXISTS ( SELECT INFO_ID FROM cr_customer_info_count WHERE shop_Id = o.FN_SHOP_ID AND CUSTMOR_ID = o.buyer_member_id ) GROUP BY o.FN_SHOP_ID, o.buyer_member_id, o.`status` LIMIT 0,limitSize);
	DECLARE WAIT_UPDATE_DATA_CUR CURSOR FOR (SELECT GROUP_CONCAT(o.id) as oids,COUNT(o.id) AS countPay, sum(e.quantity) AS quantity, OWNER_ACCOUNT_ID,  o.BUYER_MEMBER_ID, o.FN_SHOP_ID, sum(o.sum_product_payment) AS sumPay FROM u_sync_albb_order o LEFT JOIN u_shop ON SHOP_ID = FN_SHOP_ID LEFT JOIN u_sync_albb_order_entry e ON o.id = e.parent_id WHERE o.IF_STATISTICS_FOR_CUSTOMER = 0 AND o.data_src = 1 AND  o.`status` = orderStatus AND o.FN_SHOP_ID='DPZH0000000200000002' AND  EXISTS ( SELECT INFO_ID FROM cr_customer_info_count WHERE shop_Id = o.FN_SHOP_ID AND CUSTMOR_ID = o.buyer_member_id ) GROUP BY o.FN_SHOP_ID, o.buyer_member_id, o.`status` LIMIT 0,limitSize);
	
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
	
	#打开游标
	OPEN WAIT_SYNC_DATA_CUR;
	OPEN WAIT_UPDATE_DATA_CUR;
	
	SET INSERT_CUSTOMER_COUNT=0; 	
	SET UPDATE_CUSTOMER_COUNT=0; 	
	#循环游标
	cursor_loop1:LOOP
	#把数据取出
	   FETCH WAIT_SYNC_DATA_CUR INTO oids,num,quantity,mainAccountId,buyerMemberId,shopId,payment; #读取数据到游标  
	   IF done=1 THEN
			LEAVE cursor_loop1;
	   END IF;
			
			#统计会员信息
			if orderStatus='success' THEN 
				SET tradeSuccessCount=num;
				SET tradeSuccessAmount=payment;
				SET productBuyCount=quantity;
				SET avgCustomerPrice=tradeSuccessAmount/tradeSuccessCount;
			ELSEIF orderStatus='cancel' THEN
				SET tradeCloseCount=num;
				SET tradeCloseAmount=payment;
				SET productCloseCount=quantity;
			END IF;
			INSERT INTO cr_customer_info_count ( INFO_ID, CUSTMOR_ID, SHOP_ID, TRADE_SUCCESS_COUNT, TRADE_SUCCESS_AMOUNT, TRADE_CLOSE_COUNT, TRADE_CLOSE_AMOUNT, PRODUCT_BUY_COUNT, PRODUCT_CLOSE_COUNT, AVG_CUSTOM_PRICE, MAIN_ACCOUNT_ID )VALUES (f_id_get('CUSTOM_SUMMARY_ID'),buyerMemberId,shopId,tradeSuccessCount,tradeSuccessAmount,tradeCloseCount,tradeCloseAmount,productBuyCount,productCloseCount,avgCustomerPrice,mainAccountId);
			#更新同步状态
			UPDATE u_sync_albb_order SET IF_STATISTICS_FOR_CUSTOMER=1 WHERE id in (oids) AND FN_SHOP_ID=shopId;
		SET INSERT_CUSTOMER_COUNT = INSERT_CUSTOMER_COUNT+1;	
	END LOOP cursor_loop1;
	CLOSE WAIT_SYNC_DATA_CUR;
	SET done=0;
	#循环游标
	cursor_loop2:LOOP
	#把数据取出
	   FETCH WAIT_UPDATE_DATA_CUR INTO oids,num,quantity,mainAccountId,buyerMemberId,shopId,payment; #读取数据到游标 
	   IF done=1 THEN
			LEAVE cursor_loop2;
	   END IF;
	
			#统计会员信息
				if orderStatus='success' THEN 
				SET tradeSuccessCount=num;
				SET tradeSuccessAmount=payment;
				SET productBuyCount=quantity;
				SET avgCustomerPrice=tradeSuccessAmount;
			ELSEIF orderStatus='cancel' THEN
				SET tradeCloseCount=num;
				SET tradeCloseAmount=payment;
				SET productCloseCount=quantity;
			END IF;
			UPDATE cr_customer_info_count SET TRADE_SUCCESS_COUNT=TRADE_SUCCESS_COUNT+tradeSuccessCount,TRADE_SUCCESS_AMOUNT=TRADE_SUCCESS_AMOUNT+tradeSuccessAmount,TRADE_CLOSE_COUNT=TRADE_CLOSE_COUNT+tradeCloseCount,TRADE_CLOSE_AMOUNT=TRADE_CLOSE_AMOUNT+tradeCloseAmount,PRODUCT_BUY_COUNT=PRODUCT_BUY_COUNT+productBuyCount,PRODUCT_CLOSE_COUNT=PRODUCT_CLOSE_COUNT+productCloseCount,AVG_CUSTOM_PRICE=(AVG_CUSTOM_PRICE*PRODUCT_BUY_COUNT+avgCustomerPrice)/(tradeSuccessCount+TRADE_SUCCESS_COUNT) WHERE CUSTMOR_ID=buyerMemberId AND SHOP_ID=shopId;
			#更新同步状态
			UPDATE u_sync_albb_order SET IF_STATISTICS_FOR_CUSTOMER=1 WHERE id IN (oids) AND FN_SHOP_ID=shopId;
				
		SET UPDATE_CUSTOMER_COUNT = UPDATE_CUSTOMER_COUNT+1;	
	END LOOP cursor_loop2;
	CLOSE WAIT_UPDATE_DATA_CUR;
	
	
	SELECT INSERT_CUSTOMER_COUNT,UPDATE_CUSTOMER_COUNT;
END

有时候还是需要拆分的思想