SQLServer关于存储过程实例应用分析

装载作业进度存储过程

/*===================================== StordProc Proc_DP_RsTmsDispatchList ===================================*/
PRINT 'Proc_DP_RsTmsDispatchList' 
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[Proc_DP_RsTmsDispatchList]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
     DROP PROCEDURE Proc_DP_RsTmsDispatchList
GO

CREATE PROC [dbo].[Proc_DP_RsTmsDispatchList]
@startTime DATETIME,
@endTime DATETIME,
@cOrgCode NVARCHAR(50)
AS
BEGIN
    DECLARE @cOrgName NVARCHAR(50)
    SELECT @cOrgName = cName FROM dbo.Organization WHERE cOrgCode = @cOrgCode
    ;WITH rd32 AS
    (   
        SELECT r.iDLsID,dnmaketime=MAX(e.dnmaketime),cBusCode=MAX(e.cBusCode)
        FROM dbo.rdrecords32 r WITH(NOLOCK) 
        LEFT JOIN dbo.rdrecord32 e WITH(NOLOCK) ON r.ID=e.ID GROUP BY r.iDLsID
    )
    SELECT tms.waybillNum,
    tms.code,tms.
    waybillStatus,
    tms.carrier,
    book_begin_time=DATEADD(HOUR,8,max(vr.book_begin_time)),
    book_end_time=DATEADD(HOUR,8,max(vr.book_end_time)),
    into_factory_time=DATEADD(HOUR,8,max(vr.into_factory_time)),
    pb.cMaker,
    cMakerName=s.UserName,
    startPickTime=MIN(d.dCreateTime),
    r.dnmaketime as endPickTime,
    iPickQty=SUM(ISNULL(wp.iQuantity,0)),
    iQuantity=SUM(ISNULL(b.iQuantity,0))INTO #tb
    FROM DP_Tmswaybill tms WITH(NOLOCK)
    LEFT JOIN DP_VehicleReservation vr WITH(NOLOCK) ON ISNULL(vr.bDelete,0)=0 AND vr.ref_order_id=tms.waybillNum and vr.license_plate=tms.code AND vr.salesOrganization LIKE '%' + @cOrgName + '%' --删除了时间条件--增加车牌关联
    LEFT JOIN dbo.DispatchList a WITH(NOLOCK) ON a.cHDefine1 = tms.waybillNum AND a.cOrgCode = @cOrgCode 
    LEFT JOIN dbo.DispatchLists b WITH(NOLOCK) ON a.DLID=b.DLID
    LEFT JOIN dbo.W1_PickLists c WITH(NOLOCK) ON c.iDLsID=b.iDLsID
    LEFT JOIN dbo.W1_PickList d WITH(NOLOCK) ON d.ID=c.ID and d.cHDefine1=a.cHDefine1--增加条件
    LEFT JOIN dbo.W1_PLBoxdetail pb WITH(NOLOCK) ON pb.RdsID = c.AutoID --删除了时间条件
    LEFT JOIN rd32 r WITH(NOLOCK) ON r.iDLsID = b.iDLsID and r.cBusCode=a.cDLCode --增加条件
    LEFT JOIN dbo.SysUser s WITH(NOLOCK) ON pb.cMaker = s.UserID
    left join (select cVouchCode,sum(iQuantity) as iQuantity from dbo.W1_PLScanBarcode with(nolock) group by cVouchCode) wp on wp.cVouchCode=d.cCode  --增加关联拣货表
    WHERE CONVERT(NVARCHAR(10),DATEADD(HOUR,8,vr.book_begin_time),23)=CONVERT(NVARCHAR(10),@startTime,23) 
    --AND ISNULL(tms.bDelete,0)=0 
    AND vr.salesOrganization LIKE '%' + @cOrgName + '%'
    AND tms.waybillStatus <>'10'--AND CONVERT(NVARCHAR(10),tms.dSearchDate,23)=CONVERT(NVARCHAR(10),@startTime,23)改了时间按照预约时间
    GROUP BY tms.waybillNum,tms.code,tms.waybillStatus,pb.cMaker,s.UserName,tms.carrier,r.dnmaketime
    --出数量
    ;WITH outlist AS 
    (
        SELECT a.cMaker,iQuantity=SUM(b.iQuantity)
        FROM dbo.rdrecord32 a WITH(NOLOCK)--销售出库单
        LEFT JOIN dbo.rdrecords32 b WITH(NOLOCK) ON a.ID = b.ID
        WHERE a.bRdFlag=0 AND a.cState=2 AND a.dnmaketime BETWEEN @startTime AND @endTime AND a.cOrgCode = @cOrgCode
        GROUP BY a.cMaker
        UNION ALL
        SELECT a.cMaker,iQuantity=SUM(b.iQuantity)
        FROM dbo.RdRecord09 a WITH(NOLOCK)--其他出库单
        LEFT JOIN dbo.rdrecords09 b WITH(NOLOCK) ON a.ID = b.ID
        WHERE a.cState=2 AND a.dnmaketime BETWEEN @startTime AND @endTime AND a.cOrgCode = @cOrgCode
        GROUP BY a.cMaker
        UNION ALL
        select a.cMaker,iQuantity=SUM(b.iQuantity)
        FROM dbo.AdjustPVouch a WITH(NOLOCK)--货位调整单
        LEFT JOIN dbo.AdjustPVouchs b WITH(NOLOCK) ON a.Id = b.ID
        WHERE a.cState=2 AND a.dnmaketime BETWEEN @startTime AND @endTime AND a.cOrgCode = @cOrgCode
        GROUP BY a.cMaker
        UNION ALL--后加拣货量
        select a.cUserId,iQuantity=SUM(a.iQuantity)
        FROM dbo.W1_PLScanBarcode a WITH(NOLOCK)
        left join W1_PickList b on b.cCode=a.cVouchCode--拣货单
        WHERE a.dCreateTime BETWEEN @startTime AND @endTime AND b.cOrgCode = @cOrgCode
        GROUP BY a.cUserId
    )
    SELECT cMaker,iQuantity=SUM(iQuantity) INTO #outlist FROM outlist GROUP BY cMaker

    --入数量
    ;WITH inlist AS 
    (   
        SELECT a.cMaker,iQuantity=SUM(b.iQuantity)
        FROM dbo.RdRecord08 a WITH(NOLOCK)--其他入库单
        LEFT JOIN dbo.rdrecords08 b WITH(NOLOCK) ON a.ID=b.ID
        WHERE a.cState=2 AND a.dnmaketime BETWEEN @startTime AND @endTime AND a.cOrgCode = @cOrgCode
        AND a.cMemo NOT LIKE '%PDA转库单传入%'--排除掉所有一步式转库单生成的【其他入库单】，因为计算个人工作量，只计算出库数量即可
        GROUP BY a.cMaker
        UNION ALL 
        SELECT a.cMaker,iQuantity=SUM(b.iQuantity)
        FROM dbo.RdRecord10 a WITH(NOLOCK)--产成品入库单
        LEFT JOIN dbo.rdrecords10 b WITH(NOLOCK) ON a.ID=b.ID
        WHERE a.cState=2 AND a.dnmaketime BETWEEN @startTime AND @endTime AND a.cOrgCode = @cOrgCode
        GROUP BY a.cMaker
        UNION ALL 
        SELECT a.cMaker,iQuantity=SUM(b.iQuantity)
        FROM dbo.RdRecord32 a WITH(NOLOCK) --销售出库单
        LEFT JOIN dbo.rdrecords32 b WITH(NOLOCK) ON a.ID=b.ID
        WHERE bRdFlag=1 AND a.cState=2 AND a.dnmaketime BETWEEN @startTime AND @endTime AND a.cOrgCode = @cOrgCode
        GROUP BY a.cMaker
        --去掉货位调整单计算到入库数量中
    )
    SELECT cMaker,iQuantity=SUM(iQuantity) INTO #inlist FROM inlist GROUP BY cMaker
    select a.waybillNum,a.code,a.waybillStatus,a.carrier,a.book_begin_time,into_factory_time=CASE WHEN CONVERT(NVARCHAR(10),a.into_factory_time,23)=CONVERT(NVARCHAR(10),@startTime,23) THEN a.into_factory_time ELSE NULL END,
    a.cMaker,a.cMakerName,a.startPickTime,a.endPickTime,a.book_end_time,
    PickTime=DATEDIFF(MINUTE, a.startPickTime, a.endPickTime),
    iqty=ROUND(CASE
     WHEN ISNULL(a.iQuantity,0)=0 THEN 0 ELSE CASE WHEN (ISNULL(a.iPickQty,0)/a.iQuantity)*100>100 THEN 100 ELSE (ISNULL(a.iPickQty,0)/a.iQuantity)*100 END END,0) INTO #temp
    FROM #tb a
    SELECT 'OK' AS MSG, '成功！' AS EM
    SELECT DISTINCT waybillNum,code,waybillStatus=CAST(waybillStatus AS INT),
    book_begin_time=CONVERT(CHAR(5),book_begin_time,108)+'-'+CONVERT(CHAR(5),book_end_time,108),
    into_factory_time=CONVERT(CHAR(5),into_factory_time,108),
    startPickTime=CONVERT(CHAR(5),startPickTime,108),
    endPickTime=CONVERT(CHAR(5),endPickTime,108),
    PickTime,iqty,carrier,cMakerName=STUFF((SELECT ','+cMakerName  FROM #temp b WHERE a.waybillNum=b.waybillNum AND ISNULL(a.code,'')=ISNULL(b.code,'') AND ISNULL(a.waybillStatus,'')=ISNULL(b.waybillStatus,'') AND ISNULL(a.carrier,'')=ISNULL(b.carrier,'') AND ISNULL(a.book_begin_time,'')=ISNULL(b.book_begin_time,'') AND ISNULL(a.into_factory_time,'')=ISNULL(b.into_factory_time,'') AND 
    ISNULL(a.startPickTime,'')=ISNULL(b.startPickTime,'') AND ISNULL(a.endPickTime,'')=ISNULL(b.endPickTime,'') AND ISNULL(a.PickTime,'')=ISNULL(b.PickTime,'') AND a.iqty=b.iqty FOR XML PATH('')),1,1,'') INTO #querytb FROM #temp a
    select * FROM #querytb ORDER BY waybillStatus
    select DISTINCT cMaker=a.UserName,inqty=b.iQuantity,outqty=c.iQuantity,sumqty=ISNULL(b.iQuantity,0)+ISNULL(c.iQuantity,0)
    FROM dbo.SysUser a WITH(NOLOCK)
    LEFT JOIN #inlist b ON a.UserID=b.cMaker
    LEFT JOIN #outlist c ON a.UserID=c.cMaker
    WHERE (ISNULL(b.iQuantity,0)<>0 OR ISNULL(c.iQuantity,0)<>0) AND a.UserID NOT LIKE '%admin%'
    ORDER BY (ISNULL(b.iQuantity,0)+ISNULL(c.iQuantity,0))
END

GO

存储过程逐行分析

逐行分析存储过程Proc_DP_RsTmsDispatchList，理解每一部分的作用和目的：

USE [Mom_Data_ZHBM001_ZTBM001]

设置当前数据库上下文为Mom_Data_ZHBM001_ZTBM001。

GO

GO是SQL Server的批处理分隔符，用于标记SQL脚本的一个执行单元的结束。

/****** Object:  StoredProcedure [dbo].[Proc_DP_RsTmsDispatchList]    Script Date: 2024/7/3 18:06:54 ******/

注释行，描述对象类型（存储过程）、所有者（dbo）和脚本创建日期。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

启用ANSI空值处理和标识符引用规则，确保SQL语句在不同环境下的一致性。

ALTER PROC [dbo].[Proc_DP_RsTmsDispatchList]
@startTime DATETIME,
@endTime DATETIME,
@cOrgCode NVARCHAR(50)
AS
BEGIN

定义一个存储过程Proc_DP_RsTmsDispatchList，接受三个参数：开始时间@startTime、结束时间@endTime和组织代码@cOrgCode。

DECLARE @cOrgName NVARCHAR(50)
SELECT @cOrgName = cName FROM dbo.Organization WHERE cOrgCode = @cOrgCode

声明并初始化变量@cOrgName，用于存储与@cOrgCode相对应的组织名称。

;WITH rd32 AS
(   
    SELECT r.iDLsID,dnmaketime=MAX(e.dnmaketime),cBusCode=MAX(e.cBusCode)
    FROM dbo.rdrecords32 r WITH(NOLOCK) 
    LEFT JOIN dbo.rdrecord32 e WITH(NOLOCK) ON r.ID=e.ID GROUP BY r.iDLsID
)

创建CTE（公用表表达式）rd32，用于获取每个iDLsID下的最大dnmaketime和cBusCode，WITH(NOLOCK)提示读取时不锁定表，提高并发性能。

SELECT tms.waybillNum,
tms.code,tms.
waybillStatus,
tms.carrier,
...
FROM DP_Tmswaybill tms WITH(NOLOCK)
LEFT JOIN ...

从DP_Tmswaybill表开始构建主查询，连接多个表，包括车辆预约、发货清单、拣货记录等，收集运输调度相关数据。

WHERE CONVERT(NVARCHAR(10),DATEADD(HOUR,8,vr.book_begin_time),23)=CONVERT(NVARCHAR(10),@startTime,23) 
...
AND tms.waybillStatus <>'10'...

应用多个筛选条件，包括时间范围、组织名称匹配、运输状态等，注意DATEADD用于调整时间，适应时区差异。

GROUP BY tms.waybillNum,tms.code,tms.waybillStatus,...

使用GROUP BY对结果进行分组，以便计算每组的最大值、最小值和总和。

INTO #tb

将上述查询结果存储到临时表#tb中，用于后续处理。

接下来的部分构建outlist和inlist CTE，分别计算出库和入库数量，涉及多个表和不同的时间范围。

SELECT cMaker,iQuantity=SUM(iQuantity) INTO #outlist FROM outlist GROUP BY cMaker

将outlist中的结果汇总，按cMaker分组，计算总的出库数量，并存入#outlist临时表。

类似地，对inlist进行汇总处理。

select a.waybillNum,a.code,a.waybillStatus,...
FROM #tb a

从#tb中选择数据，进一步处理和计算，如调整into_factory_time字段的值。

SELECT 'OK' AS MSG, '成功！' AS EM

这部分似乎是为了返回一个状态消息，指示查询成功。

SELECT DISTINCT waybillNum,code,waybillStatus=CAST(waybillStatus AS INT),...
FROM #temp a

从处理后的#temp表中选择不重复的数据行，进行最终的格式化和排序。

最后，从SysUser、#inlist、#outlist的连接中选择数据，生成包含操作员出入库活动的汇总信息。

ORDER BY (ISNULL(b.iQuantity,0)+ISNULL(c.iQuantity,0))
END

对结果按出入库总量排序，并以END关键字结束存储过程的定义。

整个存储过程的设计目的是为了生成运输调度报告，涵盖了从订单接收、车辆预约、发货准备到拣货完成的全过程，并提供了操作员的出入库活动概览。通过CTE、聚合函数、动态时间调整和复杂的连接逻辑，能够满足复杂的业务报告需求。