如何使用 sql 游标更新 sql 服务器数据库中的数据
本文关键字:sql 数据库 数据 更新 何使用 游标 服务器 | 更新日期: 2023-09-27 18:36:36
我正在尝试开发一个库存管理系统。但问题是当我要在库存表中库存时,这些产品数量需要一次在项目表中更新。 例如,当我按下保存按钮时,我将增加库存表中的库存 100 种产品,其中所有产品的状态='发布' 将根据 itemcode 更新为 tbl_item.quantity+tbl_stock.qty。请帮帮我...
这是我增加库存的表格。
CREATE TABLE [dbo].[tbl_Addstock](
[id] [int] IDENTITY(1,1) NOT NULL,
[ItemCode] [varchar](50) NOT NULL,
[ItemName] [varchar](250) NOT NULL,
[qty] [decimal](18, 2) NOT NULL,
[sdate] [date] NOT NULL,
[sadmin] [varchar](50) NOT NULL,
[Status] [varchar](50) NULL,
CONSTRAINT [PK_tbl_Addstock] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
这是我的另一个项目表,我想在其中更新我的总库存信息。
CREATE TABLE [dbo].[tbl_Item](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NOT NULL,
[batchno] [nvarchar](50) NOT NULL,
[brand] [nvarchar](50) NOT NULL,
[itemcode] [varchar](50) NOT NULL,
[catg] [nvarchar](50) NOT NULL,
[supplier] [nvarchar](50) NULL,
[unitType] [nvarchar](50) NOT NULL,
[Quantity] [decimal](18, 2) NULL,
[buyingPrice] [money] NULL,
[sellingPrice] [money] NULL,
[qtyLimit] [int] NULL,
[vat] [decimal](18, 2) NULL,
CONSTRAINT [PK_tbl_Item] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
然后我尝试了游标查询。.
USE [NMCPMC]
GO
/****** Object: StoredProcedure [dbo].[sp_UpdateItem] Script Date: 01/07/2015 12:41:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec sp_UpdateItem
ALTER PROCEDURE [dbo].[sp_UpdateItem]
AS
BEGIN
SET NOCOUNT ON
DECLARE
@TEMP AS VARCHAR(50), @ItemCode varchar(20),
@eqty decimal(18,2)
DECLARE ITEM_CURSOR CURSOR FOR
SELECT ItemCode,qty FROM tbl_Addstock
OPEN ITEM_CURSOR
FETCH NEXT FROM ITEM_CURSOR
INTO @ItemCode,@TEMP
WHILE (@@FETCH_STATUS = 0)
BEGIN
--set @eqty = (select Quantity from tbl_Item)
-- SELECT @TEMP = SUM(ti.Quantity+ta.qty) from tbl_Addstock ta inner join tbl_Item ti on ta.ItemCode=ti.itemcode
-- UPDATE tbl_Item SET Quantity = @TEMP WHERE CURRENT OF ITEM_CURSOR
if exists(select * from tbl_Addstock where ItemCode=@ItemCode)
begin
UPDATE tbl_Addstock SET qty = qty+@TEMP WHERE ItemCode=@ItemCode
update tbl_Addstock set qty=qty-@TEMP WHERE ItemCode=@ItemCode
end
else
begin
-- insert into tbl_Addstock
update tbl_Addstock set qty=qty-@TEMP WHERE ItemCode=@ItemCode
end
print @ItemCode
print @TEMP
FETCH NEXT FROM ITEM_CURSOR
INTO @ItemCode,@TEMP
END
CLOSE ITEM_CURSOR
DEALLOCATE ITEM_CURSOR
SET NOCOUNT OFF
END
--EXEC sp_UpdateItem
因此,您希望将tbl_AddStock中表示的数量添加到tbl_Item中。您的 SP 似乎无法正常工作,因为您从未接触过表tbl_Item的量字段。
我会写
ALTER PROCEDURE [dbo].[sp_UpdateItem]
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ItemCode varchar(20),
@eqty decimal(18,2)
DECLARE ITEM_CURSOR CURSOR FOR
SELECT ItemCode,qty FROM tbl_Addstock WHERE Status = 'Posting'
OPEN ITEM_CURSOR
FETCH NEXT FROM ITEM_CURSOR
INTO @ItemCode,@eqty
WHILE (@@FETCH_STATUS = 0)
BEGIN
if exists(select * from tbl_Item where ItemCode=@ItemCode)
begin
UPDATE tbl_Item SET quantity = quantity + @eqty WHERE ItemCode=@ItemCode
UPDATE tbl_Addstock SET qty = qty - @eqty, Status = 'Posted' WHERE ItemCode=@ItemCode
-- COULD AVOID THE MATH. SIMPLY SET QTY=0
-- ? UPDATE tbl_Addstock SET qty = 0 WHERE ItemCode=@ItemCode
end
else
begin
-- THIS ELSE PART IS WHERE YOU DISCOVER THAT tbl_Item
-- HAD NO RECORD FOR THE @ItemCode.
-- YOU SHOULD ADD THE NEW ITEM TO THE tblItem TABLE
-- BUT MANY FIELDS OF THAT TABLE ARE NOT NULL (batchno, catg, brand, unittype)
-- SO YOU NEED TO KNOW THE VALUES FOR THESE
-- FIELDS OTHERWISE YOU WONT BE ABLE TO ADD ANYTHING
-- ??? -> INSERT INTO tbl_Item(ItemCode, Qty) VALUES (@itemcode, @eqty)
end
FETCH NEXT FROM ITEM_CURSOR
INTO @ItemCode,@qty
END
CLOSE ITEM_CURSOR
DEALLOCATE ITEM_CURSOR
SET NOCOUNT OFF
END