公司做了一个需求，初期设想通过存储过程来初始化表数据，所以写了一个存储过程，如下：
--CREATE PROCEDURE [dbo].[PROC_HANDLE_SHOPCAR_TXD_SR_TYPE]ALTER PROCEDURE [dbo].[PROC_HANDLE_SHOPCAR_TXD_SR_TYPE]AS BEGIN-------------------- 第一部分----------------------------DECLARE@SELF_XD BIGINT;BEGINSELECT@SELF_XD = COUNT(1)FROMCRM_SHOP_CAR WITH(NOLOCK) WHERESTATUS = 0 AND GRP != 'winretailsr' AND PRE_ORDER_NO IS NULL AND TXD_SR_TYPE IS NULL;END;PRINT '--------统计更新门店和伙伴自主下单购物车记录数 ：' + CAST(@SELF_XD AS VARCHAR(20));IF @SELF_XD > 0BEGINPRINT '--------更新门店和伙伴自主下单购物车记录开始...';UPDATE CRM_SHOP_CAR SET TXD_SR_TYPE = 0 WHERE STATUS = 0 AND GRP != 'winretailsr' AND PRE_ORDER_NO IS NULL AND TXD_SR_TYPE IS NULL;PRINT '--------更新门店和伙伴自主下单购物车记录完成...';END;-------------------- 第二部分----------------------------DECLARE@SR_TXD_COUNT BIGINT;DECLARE @SR_TXD_CUSTOMER_COUNT BIGINT; DECLARE @PAGE_SIZE INT;DECLARE @TOTAL_PAGE INT;DECLARE @PAGE_NUM INT;BEGINSET @PAGE_NUM = 1;SET @PAGE_SIZE = 2000;SELECT @SR_TXD_COUNT = COUNT(1)FROM CRM_SHOP_CAR WITH(NOLOCK)WHERE STATUS = 0 AND GRP = 'winretailsr' AND PRE_ORDER_NO IS NULL AND TXD_SR_TYPE IS NULL;PRINT '--------统计更新替下单购物车记录数 ：' + CAST(@SR_TXD_COUNT AS VARCHAR(20));SELECT shopcar.ID,shopcar.CUSTOMER_ID,cwc.CUST_TITLEINTO #CRM_SHOP_CAR_TXD_TMP FROM CRM_SHOP_CAR shopcar WITH(NOLOCK)JOIN CRM_WS_CUSTOMER cwc WITH(NOLOCK) on shopcar.CUSTOMER_ID = cwc.CUSTOMER_IDWHERE shopcar.STATUS = 0 AND shopcar.GRP = 'winretailsr'AND shopcar.PRE_ORDER_NO IS NULLAND shopcar.TXD_SR_TYPE IS NULL;SELECT @SR_TXD_CUSTOMER_COUNT = COUNT(1) FROM (SELECT DISTINCT CUSTOMER_ID,CUST_TITLEFROM #CRM_SHOP_CAR_TXD_TMP) COUNT_TMP;END;IF @SR_TXD_COUNT > 0BEGINPRINT '--------更新替下单购物车记录开始...';DECLARE @SAILER_CUSTOMER_ID BIGINT;DECLARE @CUST_TITLE VARCHAR(20);DECLARE @TXD_SR_TYPE INT; -- 1.掌柜替门店下单(内购) 2.掌柜替伙伴下单 3.合伙人替伙伴下单DECLARE @TXD_SR_CUSTOMER BIGINT;DECLARE @SHOP_CAR_ID BIGINT;IF (@SR_TXD_CUSTOMER_COUNT % @PAGE_SIZE) = 0BEGINSET @TOTAL_PAGE = (@SR_TXD_CUSTOMER_COUNT / @PAGE_SIZE);END;ELSEBEGINSET @TOTAL_PAGE = (@SR_TXD_CUSTOMER_COUNT / @PAGE_SIZE) + 1;END;PRINT '客户分组总数：' + CAST(@SR_TXD_CUSTOMER_COUNT as varchar(20));PRINT '分页总数：' + CAST(@TOTAL_PAGE as varchar(20));-- 分页处理WHILE @PAGE_NUM <= @TOTAL_PAGEBEGINPRINT '第' + CAST(@PAGE_NUM as varchar(20)) + '页开始...';DECLARE SAILER_SHOPCAR_LIST_CURSORCURSOR FORWARD_ONLY READ_ONLY STATICFORSELECT TOP 2000 CUSTOMER_ID,CUST_TITLEFROM( SELECT ROW_NUMBER() OVER ( ORDER BY CUSTOMER_ID ASC) PAGE_ROW_NUMBER,CUSTOMER_ID,CUST_TITLE FROM (SELECT DISTINCT CUSTOMER_ID,CUST_TITLE FROM #CRM_SHOP_CAR_TXD_TMP) AS PAGE_TABLE_ALIAS ) AS PAGE_TABLE_ALIASWHEREPAGE_ROW_NUMBER > (@PAGE_NUM - 1) * @PAGE_SIZEORDER BYPAGE_ROW_NUMBER;-- 打开游标OPEN SAILER_SHOPCAR_LIST_CURSOR;-- 遍历游标FETCH NEXT FROM SAILER_SHOPCAR_LIST_CURSOR INTO @SAILER_CUSTOMER_ID,@CUST_TITLE;WHILE @@fetch_status = 0BEGINPRINT '@SAILER_CUSTOMER_ID ：' + CAST(@SAILER_CUSTOMER_ID as varchar(20));IF @CUST_TITLE = '业代'BEGINSET @TXD_SR_TYPE = 0;END;ELSE IF @CUST_TITLE = '店主' BEGINSET @TXD_SR_TYPE = 1;SELECT @TXD_SR_CUSTOMER = BUS_CUSTOMER_ID FROM VIEW_SR_RETAIL_INFO WITH(NOLOCK) WHERE ISNULL(IS_VALID, 0) = 1 AND RETAIL_CUSTOMER_ID = @SAILER_CUSTOMER_ID;END;-- ELSE IF @CUST_TITLE = '经销商'BEGINSET @TXD_SR_TYPE = 3;SELECTTOP 1@TXD_SR_CUSTOMER = CBI.CUSTOMER_ID FROMCRM_BUSINESS_INFO CBI WITH ( NOLOCK )LEFT JOIN CRM_SR_GROUP_CUSTOMER CSGCL1 WITH ( NOLOCK ) ON CBI.CUSTOMER_ID = CSGCL1.CUSTOMER_IDLEFT JOIN CRM_SR_GROUP_CUSTOMER_LOG CSGCL2 WITH ( NOLOCK ) ON CSGCL2.SR_GROUP_ID = CSGCL1.SR_GROUP_IDLEFT JOIN CRM_WS_CUSTOMER CWC WITH ( NOLOCK ) ON CSGCL2.CUSTOMER_ID = CWC.CUSTOMER_IDLEFT JOIN CRM_POI_INFO CPI WITH ( NOLOCK ) ON CPI.POI_CODE = _IDWHERECBI.BUS_TYPE = 13AND CBI.BUS_STATUS = 1AND CSGCL1.STATUS = 1AND CSGCL2.STATUS = 1AND CWC.CUST_TITLE = '经销商'AND CPI.DEALER_SOURCE_TYPE = 'X村通伙伴'AND CPI.STATUS = '1'AND CWC.CUSTOMER_ID = @SAILER_CUSTOMER_ID;IF @TXD_SR_CUSTOMER IS NULLBEGINSET @TXD_SR_TYPE = 2;SELECT TOP 1 @TXD_SR_CUSTOMER = srRelation.SR_CUSTOMER_IDFROM CRM_DEALER_SR_RELATIONSHIP srRelation WITH(NOLOCK)INNER JOIN CRM_WS_CUSTOMER customer WITH(NOLOCK) ON srRelation.DEALER_ID = _ID AND customer.CUST_TITLE = '经销商'INNER JOIN CRM_POI_INFO poiInfo WITH(NOLOCK) ON poiInfo.POI_CODE = _ID AND poiInfo.DEALER_SOURCE_TYPE = 'X村通伙伴'WHERE srRelation.STATUS = 1AND customer.CUSTOMER_ID = @SAILER_CUSTOMER_ID;END;END;--PRINT 'SAILER_CUSTOMER_ID= ' + CAST(@SAILER_CUSTOMER_ID as varchar(20))+ ',TXD_SR_CUSTOMER='+ CAST(@TXD_SR_CUSTOMER as varchar(20)) +',TXD_SR_TYPE = ' + CAST(@TXD_SR_TYPE as varchar(20));DECLARE SAILER_SHOPCAR_CUSTOMER_CURSORCURSOR FORWARD_ONLY READ_ONLY STATICFORSELECT ID FROM #CRM_SHOP_CAR_TXD_TMPWHERE CUSTOMER_ID = @SAILER_CUSTOMER_ID AND CUST_TITLE = @CUST_TITLE ORDER BY IDOPEN SAILER_SHOPCAR_CUSTOMER_CURSOR;FETCH NEXT FROM SAILER_SHOPCAR_CUSTOMER_CURSOR INTO @SHOP_CAR_ID;WHILE @@fetch_status = 0BEGINUPDATE CRM_SHOP_CAR SETTXD_SR_TYPE = @TXD_SR_TYPE,TXD_SR_CUSTOMER = @TXD_SR_CUSTOMER WHERE ID = @SHOP_CAR_ID;FETCH NEXT FROM SAILER_SHOPCAR_CUSTOMER_CURSOR INTO @SHOP_CAR_ID;END;CLOSE SAILER_SHOPCAR_CUSTOMER_CURSOR;DEALLOCATE SAILER_SHOPCAR_CUSTOMER_CURSOR;FETCH NEXT FROM SAILER_SHOPCAR_LIST_CURSOR INTO @SAILER_CUSTOMER_ID,@CUST_TITLE;END;CLOSE SAILER_SHOPCAR_LIST_CURSOR;DEALLOCATE SAILER_SHOPCAR_LIST_CURSOR;SET @PAGE_NUM = (@PAGE_NUM + 1);--PRINT '@PAGE_NUM + 1 = ' + CAST(@PAGE_NUM as varchar(20));-- WHILE 3END;PRINT '--------更新替下单购物车记录结束...';-- IF 2END;-- 1END;
上线的时候在预上线环境执行，考虑执行时间需要7-8分钟，会影响线上的业务，所以进行了把存储过程拆分成多个sql来进行数据初始化，经过同事和我的共同探讨，总结成了如下的sql：
-- 自主下单 UPDATE CRM_SHOP_CARSETTXD_SR_TYPE = 0WHERE TXD_SR_TYPE IS NULLAND ID IN ( SELECT IDFROM CRM_SHOP_CAR WITH(NOLOCK)WHERE GRP != 'winretailsr'AND STATUS = 0AND PRE_ORDER_NO IS NULL );-- 业代内购UPDATE CSETC.TXD_SR_TYPE = 0FROM CRM_SHOP_CAR C WITH(NOLOCK)LEFT JOIN dbo.CRM_WS_CUSTOMER W WITH(NOLOCK) ON C.CUSTOMER_ID = W.CUSTOMER_IDWHERE W.CUST_TITLE = '业代'AND C.GRP = 'winretailsr'AND C.STATUS = 0AND C.PRE_ORDER_NO IS NULLAND C.TXD_SR_TYPE IS NULL;-- 掌柜给门店下单UPDATE CSETC.TXD_SR_TYPE = 1,C.TXD_SR_CUSTOMER = I.BUS_CUSTOMER_IDFROM CRM_SHOP_CAR C LEFT JOIN CRM_WS_CUSTOMER W WITH(NOLOCK) ON C.CUSTOMER_ID = W.CUSTOMER_IDLEFT JOIN dbo.VIEW_SR_RETAIL_INFO I WITH(NOLOCK) ON W.CUSTOMER_ID = I.RETAIL_CUSTOMER_IDWHERE W.CUST_TITLE = '门店'AND C.GRP = 'winretailsr'AND C.STATUS = 0AND C.PRE_ORDER_NO IS NULLAND C.TXD_SR_TYPE IS NULL;-- 合伙人给伙伴下单UPDATE CSETC.TXD_SR_TYPE = 3,C.TXD_SR_CUSTOMER = d.CUSTOMER_IDFROM CRM_SHOP_CAR CLEFT JOIN CRM_WS_CUSTOMER W WITH(NOLOCK) ON C.CUSTOMER_ID = W.CUSTOMER_IDLEFT JOIN dbo.VIEW_SR_RETAIL_INFO I ON W.CUSTOMER_ID = I.RETAIL_CUSTOMER_IDLEFT JOIN ( SELECT CBI.CUSTOMER_ID ,CWC.CUSTOMER_ID C_CUSTOMER_IDFROM CRM_BUSINESS_INFO CBI WITH ( NOLOCK )LEFT JOIN CRM_SR_GROUP_CUSTOMER CSGCL1 WITH ( NOLOCK ) ON CBI.CUSTOMER_ID = CSGCL1.CUSTOMER_IDLEFT JOIN CRM_SR_GROUP_CUSTOMER_LOG CSGCL2 WITH ( NOLOCK ) ON CSGCL2.SR_GROUP_ID = CSGCL1.SR_GROUP_IDLEFT JOIN CRM_WS_CUSTOMER CWC WITH ( NOLOCK ) ON CSGCL2.CUSTOMER_ID = CWC.CUSTOMER_IDLEFT JOIN CRM_POI_INFO CPI WITH ( NOLOCK ) ON CPI.POI_CODE = _IDWHERE CBI.BUS_TYPE = 13AND CBI.BUS_STATUS = 1AND CSGCL1.STATUS = 1AND CSGCL2.STATUS = 1AND CWC.CUST_TITLE = '经销商'AND CPI.DEALER_SOURCE_TYPE = 'X村通伙伴'AND CPI.STATUS = '1') d ON d.C_CUSTOMER_ID = C.CUSTOMER_IDWHERE W.CUST_TITLE = '经销商'AND C.GRP = 'winretailsr'AND C.STATUS = 0AND C.PRE_ORDER_NO IS NULLAND d.CUSTOMER_ID IS NOT NULLAND C.TXD_SR_TYPE IS NULL;-- 掌柜给伙伴下单UPDATE CSETC.TXD_SR_TYPE = 2,C.TXD_SR_CUSTOMER = d.SR_CUSTOMER_IDFROM CRM_SHOP_CAR CLEFT JOIN CRM_WS_CUSTOMER W WITH(NOLOCK) ON C.CUSTOMER_ID = W.CUSTOMER_IDLEFT JOIN dbo.VIEW_SR_RETAIL_INFO I WITH(NOLOCK) ON W.CUSTOMER_ID = I.RETAIL_CUSTOMER_IDLEFT JOIN ( SELECT srRelation.SR_CUSTOMER_ID ,customer.CUSTOMER_IDFROM CRM_DEALER_SR_RELATIONSHIP srRelation WITH( NOLOCK )INNER JOIN CRM_WS_CUSTOMER customer WITH ( NOLOCK ) ON srRelation.DEALER_ID = _IDAND customer.CUST_TITLE = '经销商'INNER JOIN CRM_POI_INFO poiInfo WITH ( NOLOCK ) ON poiInfo.POI_CODE = _IDAND poiInfo.DEALER_SOURCE_TYPE = 'X村通伙伴'WHERE srRelation.STATUS = 1) d ON d.CUSTOMER_ID = C.CUSTOMER_IDWHERE W.CUST_TITLE = '经销商'AND C.GRP = 'winretailsr'AND C.STATUS = 0AND C.PRE_ORDER_NO IS NULLAND d.CUSTOMER_ID IS NOT NULLAND C.TXD_SR_TYPE IS NULL;UPDATE CSETC.TXD_SR_TYPE = 1,C.TXD_SR_CUSTOMER = I.BUS_CUSTOMER_IDFROM CRM_SHOP_CAR C LEFT JOIN CRM_WS_CUSTOMER W WITH(NOLOCK) ON C.CUSTOMER_ID = W.CUSTOMER_IDLEFT JOIN dbo.VIEW_SR_RETAIL_INFO I WITH(NOLOCK) ON W.CUSTOMER_ID = I.RETAIL_CUSTOMER_IDWHERE W.CUST_TITLE = '店主'AND C.GRP = 'winretailsr'AND C.STATUS = 0AND C.PRE_ORDER_NO IS NULLAND C.TXD_SR_TYPE IS NULL;
针对实际的业务及系统考虑，具体问题具体分析，只要是在合理的范围内，不影响线上业务的基础上，来做程序处理，之前遇到了更为复杂的业务场景，可能通过数据库SQL脚本解决不了，需要写程序来解决。 通过博客可以把自己遇到的内容分享出来，知识共享，共同学习进步。
工作中可能会遇到各种问题，各种各样的情况导致最后上线前可能会匆忙的来处理事情，程序员都会遇到，只要不断经历和总结，以后会避免越来越少的中间过程，要勇敢面对一切。不断提升自己的能力，才是硬道理。
