公司做了一个需求，初期设想通过存储过程来初始化表数据，所以写了一个存储过程，如下：
--CREATE PROCEDURE [dbo].[PROC_HANDLE_SHOPCAR_TXD_SR_TYPE]
ALTER PROCEDURE [dbo].[PROC_HANDLE_SHOPCAR_TXD_SR_TYPE]
AS
BEGIN
-------------------- 第一部分----------------------------
DECLARE @SELF_XD BIGINT;
BEGIN
SELECT
@SELF_XD = 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;
END;
PRINT '--------统计更新门店和伙伴自主下单购物车记录数 ：' + CAST(@SELF_XD AS VARCHAR(20));
IF @SELF_XD > 0
BEGIN
PRINT '--------更新门店和伙伴自主下单购物车记录开始...';
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;
BEGIN
SET @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_TITLE
INTO #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_ID
WHERE shopcar.STATUS = 0
AND shopcar.GRP = 'winretailsr'
AND shopcar.PRE_ORDER_NO IS NULL
AND shopcar.TXD_SR_TYPE IS NULL;
SELECT
@SR_TXD_CUSTOMER_COUNT = COUNT(1) FROM (
SELECT DISTINCT CUSTOMER_ID,CUST_TITLE FROM #CRM_SHOP_CAR_TXD_TMP
) COUNT_TMP;
END;
IF @SR_TXD_COUNT > 0
BEGIN
PRINT '--------更新替下单购物车记录开始...';
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) = 0
BEGIN
SET @TOTAL_PAGE = (@SR_TXD_CUSTOMER_COUNT / @PAGE_SIZE);
END;
ELSE
BEGIN
SET @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_PAGE
BEGIN
PRINT '第' + CAST(@PAGE_NUM as varchar(20)) + '页开始...';
DECLARE SAILER_SHOPCAR_LIST_CURSOR CURSOR FORWARD_ONLY READ_ONLY STATIC
FOR
SELECT TOP 2000
CUSTOMER_ID,
CUST_TITLE
FROM
(
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_ALIAS
WHERE
PAGE_ROW_NUMBER > (@PAGE_NUM - 1) * @PAGE_SIZE
ORDER BY
PAGE_ROW_NUMBER;
-- 打开游标
OPEN SAILER_SHOPCAR_LIST_CURSOR;
-- 遍历游标
FETCH NEXT FROM SAILER_SHOPCAR_LIST_CURSOR INTO @SAILER_CUSTOMER_ID,@CUST_TITLE;
WHILE @@fetch_status = 0
BEGIN
PRINT '@SAILER_CUSTOMER_ID ：' + CAST(@SAILER_CUSTOMER_ID as varchar(20));
IF @CUST_TITLE = '业代'
BEGIN
SET @TXD_SR_TYPE = 0;
END;
ELSE IF @CUST_TITLE = '店主'
BEGIN
SET @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 = '经销商'
BEGIN
SET @TXD_SR_TYPE = 3;
SELECT
TOP 1
@TXD_SR_CUSTOMER = CBI.CUSTOMER_ID
FROM
CRM_BUSINESS_INFO CBI WITH ( NOLOCK )
LEFT JOIN CRM_SR_GROUP_CUSTOMER CSGCL1 WITH ( NOLOCK ) ON CBI.CUSTOMER_ID = CSGCL1.CUSTOMER_ID
LEFT JOIN CRM_SR_GROUP_CUSTOMER_LOG CSGCL2 WITH ( NOLOCK ) ON CSGCL2.SR_GROUP_ID = CSGCL1.SR_GROUP_ID
LEFT JOIN CRM_WS_CUSTOMER CWC WITH ( NOLOCK ) ON CSGCL2.CUSTOMER_ID = CWC.CUSTOMER_ID
LEFT JOIN CRM_POI_INFO CPI WITH ( NOLOCK ) ON CPI.POI_CODE = CWC.ORG_ID
WHERE
CBI.BUS_TYPE = 13
AND CBI.BUS_STATUS = 1
AND CSGCL1.STATUS = 1
AND CSGCL2.STATUS = 1
AND 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 NULL
BEGIN
SET @TXD_SR_TYPE = 2;
SELECT TOP 1
@TXD_SR_CUSTOMER = srRelation.SR_CUSTOMER_ID
FROM CRM_DEALER_SR_RELATIONSHIP srRelation WITH(NOLOCK)
INNER JOIN CRM_WS_CUSTOMER customer WITH(NOLOCK) ON srRelation.DEALER_ID = customer.ORG_ID
AND customer.CUST_TITLE = '经销商'
INNER JOIN CRM_POI_INFO poiInfo WITH(NOLOCK) ON poiInfo.POI_CODE = customer.ORG_ID
AND poiInfo.DEALER_SOURCE_TYPE = 'X村通伙伴'
WHERE srRelation.STATUS = 1
AND 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_CURSOR CURSOR FORWARD_ONLY READ_ONLY STATIC
FOR
SELECT
ID
FROM #CRM_SHOP_CAR_TXD_TMP
WHERE CUSTOMER_ID = @SAILER_CUSTOMER_ID AND CUST_TITLE = @CUST_TITLE ORDER BY ID
OPEN SAILER_SHOPCAR_CUSTOMER_CURSOR;
FETCH NEXT FROM SAILER_SHOPCAR_CUSTOMER_CURSOR INTO @SHOP_CAR_ID;
WHILE @@fetch_status = 0
BEGIN
UPDATE CRM_SHOP_CAR SET
TXD_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 3
END;
PRINT '--------更新替下单购物车记录结束...';
-- IF 2
END;
-- 1
END;上线的时候在预上线环境执行，考虑执行时间需要7-8分钟，会影响线上的业务，所以进行了把存储过程拆分成多个sql来进行数据初始化，经过同事和我的共同探讨，总结成了如下的sql：
-- 自主下单
UPDATE CRM_SHOP_CAR
SET TXD_SR_TYPE = 0
WHERE TXD_SR_TYPE IS NULL
AND ID IN ( SELECT ID
FROM CRM_SHOP_CAR WITH(NOLOCK)
WHERE GRP != 'winretailsr'
AND STATUS = 0
AND PRE_ORDER_NO IS NULL
);
-- 业代内购
UPDATE C
SET C.TXD_SR_TYPE = 0
FROM CRM_SHOP_CAR C WITH(NOLOCK)
LEFT JOIN dbo.CRM_WS_CUSTOMER W WITH(NOLOCK) ON C.CUSTOMER_ID = W.CUSTOMER_ID
WHERE W.CUST_TITLE = '业代'
AND C.GRP = 'winretailsr'
AND C.STATUS = 0
AND C.PRE_ORDER_NO IS NULL
AND C.TXD_SR_TYPE IS NULL;
-- 掌柜给门店下单
UPDATE C
SET C.TXD_SR_TYPE = 1,
C.TXD_SR_CUSTOMER = I.BUS_CUSTOMER_ID
FROM CRM_SHOP_CAR C
LEFT JOIN CRM_WS_CUSTOMER W WITH(NOLOCK) ON C.CUSTOMER_ID = W.CUSTOMER_ID
LEFT JOIN dbo.VIEW_SR_RETAIL_INFO I WITH(NOLOCK) ON W.CUSTOMER_ID = I.RETAIL_CUSTOMER_ID
WHERE W.CUST_TITLE = '门店'
AND C.GRP = 'winretailsr'
AND C.STATUS = 0
AND C.PRE_ORDER_NO IS NULL
AND C.TXD_SR_TYPE IS NULL;
-- 合伙人给伙伴下单
UPDATE C
SET C.TXD_SR_TYPE = 3,
C.TXD_SR_CUSTOMER = d.CUSTOMER_ID
FROM CRM_SHOP_CAR C
LEFT JOIN CRM_WS_CUSTOMER W WITH(NOLOCK) ON C.CUSTOMER_ID = W.CUSTOMER_ID
LEFT JOIN dbo.VIEW_SR_RETAIL_INFO I ON W.CUSTOMER_ID = I.RETAIL_CUSTOMER_ID
LEFT JOIN ( SELECT CBI.CUSTOMER_ID ,
CWC.CUSTOMER_ID C_CUSTOMER_ID
FROM CRM_BUSINESS_INFO CBI WITH ( NOLOCK )
LEFT JOIN CRM_SR_GROUP_CUSTOMER CSGCL1 WITH ( NOLOCK ) ON CBI.CUSTOMER_ID = CSGCL1.CUSTOMER_ID
LEFT JOIN CRM_SR_GROUP_CUSTOMER_LOG CSGCL2 WITH ( NOLOCK ) ON CSGCL2.SR_GROUP_ID = CSGCL1.SR_GROUP_ID
LEFT JOIN CRM_WS_CUSTOMER CWC WITH ( NOLOCK ) ON CSGCL2.CUSTOMER_ID = CWC.CUSTOMER_ID
LEFT JOIN CRM_POI_INFO CPI WITH ( NOLOCK ) ON CPI.POI_CODE = CWC.ORG_ID
WHERE CBI.BUS_TYPE = 13
AND CBI.BUS_STATUS = 1
AND CSGCL1.STATUS = 1
AND CSGCL2.STATUS = 1
AND CWC.CUST_TITLE = '经销商'
AND CPI.DEALER_SOURCE_TYPE = 'X村通伙伴'
AND CPI.STATUS = '1'
) d ON d.C_CUSTOMER_ID = C.CUSTOMER_ID
WHERE W.CUST_TITLE = '经销商'
AND C.GRP = 'winretailsr'
AND C.STATUS = 0
AND C.PRE_ORDER_NO IS NULL
AND d.CUSTOMER_ID IS NOT NULL
AND C.TXD_SR_TYPE IS NULL;
-- 掌柜给伙伴下单
UPDATE C
SET C.TXD_SR_TYPE = 2,
C.TXD_SR_CUSTOMER = d.SR_CUSTOMER_ID
FROM CRM_SHOP_CAR C
LEFT JOIN CRM_WS_CUSTOMER W WITH(NOLOCK) ON C.CUSTOMER_ID = W.CUSTOMER_ID
LEFT JOIN dbo.VIEW_SR_RETAIL_INFO I WITH(NOLOCK) ON W.CUSTOMER_ID = I.RETAIL_CUSTOMER_ID
LEFT JOIN ( SELECT srRelation.SR_CUSTOMER_ID ,
customer.CUSTOMER_ID
FROM CRM_DEALER_SR_RELATIONSHIP srRelation WITH( NOLOCK )
INNER JOIN CRM_WS_CUSTOMER customer WITH ( NOLOCK ) ON srRelation.DEALER_ID = customer.ORG_ID
AND customer.CUST_TITLE = '经销商'
INNER JOIN CRM_POI_INFO poiInfo WITH ( NOLOCK ) ON poiInfo.POI_CODE = customer.ORG_ID
AND poiInfo.DEALER_SOURCE_TYPE = 'X村通伙伴'
WHERE srRelation.STATUS = 1
) d ON d.CUSTOMER_ID = C.CUSTOMER_ID
WHERE W.CUST_TITLE = '经销商'
AND C.GRP = 'winretailsr'
AND C.STATUS = 0
AND C.PRE_ORDER_NO IS NULL
AND d.CUSTOMER_ID IS NOT NULL
AND C.TXD_SR_TYPE IS NULL;
UPDATE C
SET C.TXD_SR_TYPE = 1,
C.TXD_SR_CUSTOMER = I.BUS_CUSTOMER_ID
FROM CRM_SHOP_CAR C
LEFT JOIN CRM_WS_CUSTOMER W WITH(NOLOCK) ON C.CUSTOMER_ID = W.CUSTOMER_ID
LEFT JOIN dbo.VIEW_SR_RETAIL_INFO I WITH(NOLOCK) ON W.CUSTOMER_ID = I.RETAIL_CUSTOMER_ID
WHERE W.CUST_TITLE = '店主'
AND C.GRP = 'winretailsr'
AND C.STATUS = 0
AND C.PRE_ORDER_NO IS NULL
AND C.TXD_SR_TYPE IS NULL;针对实际的业务及系统考虑，具体问题具体分析，只要是在合理的范围内，不影响线上业务的基础上，来做程序处理，之前遇到了更为复杂的业务场景，可能通过数据库SQL脚本解决不了，需要写程序来解决。 通过博客可以把自己遇到的内容分享出来，知识共享，共同学习进步。
工作中可能会遇到各种问题，各种各样的情况导致最后上线前可能会匆忙的来处理事情，程序员都会遇到，只要不断经历和总结，以后会避免越来越少的中间过程，要勇敢面对一切。 不断提升自己的能力，才是硬道理。
2974
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
