asp.net中数据库存储过程实例

作者:袖梨 2022-06-25

其二是,可以把部分用Transaction-SQL 编写的程序作为存储过程存储在SQL Server 中,并创建应用程序来调用存储过程,对数据结果进行处理存储过程能够通过接收参数向调用者返回结果集,结果集的格式由调用者确定;返回状态值给调用者,指明调用是成功或是失败;包括针对数据库的操作语句,并且可以在一个存储过程中调用另一存储过程。


 代码如下 复制代码

--表结构
USE [OnlyTest]
GO
 
/****** Object:  Table [dbo].[Stock]    Script Date: 11/30/2012 18:10:05 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Stock]') AND type in (N'U'))
DROP TABLE [dbo].[Stock]
GO
 
USE [OnlyTest]
GO
 
/****** Object:  Table [dbo].[Stock]    Script Date: 11/30/2012 18:10:05 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[Stock](
 [StockID] [bigint] IDENTITY(1,1) NOT NULL,
 [ProductID] [int] NOT NULL,
 [StockDate] [date] NOT NULL,
 [StockQty] [int] NOT NULL,
 CONSTRAINT [PK_Stock] PRIMARY KEY CLUSTERED
(
 [ProductID] ASC,
 [StockDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
 
 
 
USE [OnlyTest]
GO
 
/****** Object:  StoredProcedure [dbo].[usp_OptStock]    Script Date: 11/30/2012 18:09:35 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_OptStock]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_OptStock]
GO
 
/****** Object:  StoredProcedure [dbo].[usp_OptStock_SmallCode]    Script Date: 11/30/2012 18:09:35 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_OptStock_SmallCode]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_OptStock_SmallCode]
GO
 
USE [OnlyTest]
GO
 
/****** Object:  StoredProcedure [dbo].[usp_OptStock]    Script Date: 11/30/2012 18:09:35 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
--并发库存,标准做法
CREATE PROCEDURE [dbo].[usp_OptStock]
    @ProductID INT ,
    @StartDate DATE ,
    @EndDate DATE ,
    @OptNumber INT
AS
    DECLARE @OptDays INT ;
    DECLARE @HaveDays INT ;
    SET @OptDays = DATEDIFF(day, @StartDate, @EndDate) + 1 ;
 
    BEGIN TRANSACTION ;
   
    SELECT  @HaveDays = COUNT(*)
    FROM    dbo.Stock
    WHERE   StockQty >= @OptNumber
            AND StockDate >= @StartDate
            AND StockDate <= @EndDate
            AND ProductID = @ProductID
           
    IF @HaveDays = @OptDays
        BEGIN
            UPDATE  dbo.Stock
            SET     StockQty = StockQty - @OptNumber
            WHERE   StockQty >= @OptNumber
                    AND StockDate >= @StartDate
                    AND StockDate <= @EndDate
                    AND ProductID = @ProductID
            COMMIT ;
            RETURN 0 ;
        END
    ELSE
        BEGIN
            ROLLBACK ;
            RETURN -110 ;
        END
 
GO
 
/****** Object:  StoredProcedure [dbo].[usp_OptStock_SmallCode]    Script Date: 11/30/2012 18:09:35 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
--并发库存,鲁莽做法。
CREATE PROCEDURE [dbo].[usp_OptStock_SmallCode]
    @ProductID INT ,
    @StartDate DATE ,
    @EndDate DATE ,
    @OptNumber INT
AS
    DECLARE @OptDays INT ;
    SET @OptDays = DATEDIFF(day, @StartDate, @EndDate) + 1 ;
 
    BEGIN TRANSACTION
 
    UPDATE  dbo.Stock
    SET     StockQty = StockQty - @OptNumber
    WHERE   StockQty >= @OptNumber
            AND StockDate >= @StartDate
            AND StockDate <= @EndDate
            AND ProductID = @ProductID
  
    IF @@ROWCOUNT = @OptDays
        BEGIN
            COMMIT
            RETURN 0
        END
    ELSE
        BEGIN
            ROLLBACK
            RETURN -110
        END
 
GO
 
 
 
 
 
 
 
--测试代码
USE OnlyTest
 
GO
 
TRUNCATE TABLE dbo.Stock
 
INSERT INTO dbo.Stock VALUES 
( 1,'2012-1-1',1),
( 1,'2012-1-2',2),
( 1,'2012-1-3',3),
( 1,'2012-1-4',4),
( 1,'2012-1-5',5)
 
 
DECLARE @return_value int
 
EXEC @return_value = [dbo].[usp_OptStock]
  @ProductID = 1,
  @StartDate = '2012-1-2',
  @EndDate = '2012-1-4',
  @OptNumber = 2
 
SELECT '库存充足返回' = @return_value
SELECT * FROM dbo.Stock
 
EXEC @return_value = [dbo].[usp_OptStock]
  @ProductID = 1,
  @StartDate = '2012-1-2',
  @EndDate = '2012-1-4',
  @OptNumber = 2
 
SELECT '库存不足返回' = @return_value
SELECT * FROM dbo.Stock

相关文章

精彩推荐