SQL Server 存储过程之嵌套游标

SQL库存警告设置

最新推荐文章于 2022-09-28 17:07:58 发布

桓獾

最新推荐文章于 2022-09-28 17:07:58 发布

阅读量656

点赞数

本文介绍了一个SQL过程，用于为特定公司、客户和商品配置库存上限和下限警告。通过内外层游标遍历所有有效客户及其对应商品，检查并更新或插入库存限制信息。

[sql]view plaincopyprint? 
  

USE [NFGC_Workflow]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Ben.Jiang
-- Create date:2011/9/13
-- Description:添加库存警告
-- =============================================
ALTER PROCEDURE [dbo].[Insert_AlltblStockLimit]
@Company NVARCHAR(50),
@MaxEnable bit,
@MinEnable bit,
@Max NVARCHAR(50),
@Min NVARCHAR(50),
@MaxAlertAheadDays int,
@MinAlertAheadDays int
AS
BEGIN
DECLARE @CustomerCur CURSOR;--声明外层游标
DECLARE @Id NVARCHAR(50),@ItemCode NVARCHAR(50),@customer NVARCHAR(50);
--获取所有客户记录
SET @CustomerCur=CURSOR FOR select distinct [cust-num] from(SELECT A.[cust-num],
ISNULL(B.name,A.[name]) as name from dbo.Erp_custaddr a
LEFT JOIN dbo.Erp_custaddr_CHS B ON A.[cust-num]=B.[cust-num] AND A.[cust-seq]=B.[cust-seq]
where A.[credit-hold]='False' and A.[cust-seq]=0) as cust
OPEN @CustomerCur --打开外层游标
FETCH NEXT FROM @CustomerCur INTO @customer--提取外层游标行
WHILE(@@FETCH_STATUS=0)
BEGIN
DECLARE @varCur CURSOR;--声明内层游标
--获取每一个客户的商品记录
SET @varCur = CURSOR FOR SELECT DISTINCT item FROM dbo.Erp_itemcust AS A INNER JOIN [Erp_ux-customer]
AS B ON A.[cust-num]=B.[cust-num] AND B.[cust-num] IS NOT NULL AND LEN(A.[cust-num])>0 AND B.[cust-seq]=0
INNER JOIN Erp_imsAs AS C ON C.sItem=A.item AND C.sCust=A.[cust-num] AND C.cActFlg = '0'
WHERE A.item IS NOT NULL AND LEN(A.item)>0 AND A.[cust-num]=@customer;
OPEN @varCur --打开内层游标
FETCH NEXT FROM @varCur INTO @ItemCode
WHILE(@@FETCH_STATUS=0)
BEGIN
IF (exists(SELECT Id from tblStockLimit where Company=@Company and Custnum=@customer and ItemCode=@ItemCode))
--存在该记录更新记录
BEGIN
SET @Id=(SELECT Id from tblStockLimit where Company=@Company and Custnum=@customer and ItemCode=@ItemCode)
update tblStockLimit set MaxEnable=@MaxEnable,Max=case when @Max<>'' then @Max else null end,
MaxAlertAheadDays=case when @MaxAlertAheadDays<>'' then @MaxAlertAheadDays else null end,
MinEnable=@MinEnable,Min=case when @Min<>'' then @Min else null end,
MinAlertAheadDays=case when @MinAlertAheadDays<>'' then @MinAlertAheadDays else null end where Id=@Id;
END
ELSE
--不存在该记录新增记录
BEGIN
insert into tblStockLimit(Company,Custnum,ItemCode,MaxEnable,Max,MaxAlertAheadDays,MinEnable,Min,MinAlertAheadDays)
values('NFGS',@customer,@ItemCode,@MaxEnable,case when @Max<>'' then @Max else null end,
case when @MaxAlertAheadDays<>'' then @MaxAlertAheadDays else null end,@MinEnable,
case when @Min<>'' then @Min else null end,case when @MinAlertAheadDays<>'' then @MinAlertAheadDays else null end);
END
FETCH NEXT FROM @varCur INTO @ItemCode--内层游标向下移动一行
END
CLOSE @varCur
DEALLOCATE @varCur
FETCH NEXT FROM @CustomerCur INTO @customer--内层游标结束后,外层游标继续向下移动一行
END
CLOSE @CustomerCur
DEALLOCATE @CustomerCur
END