如何在SQL Server 2008存储过程中编写游标

Question

我的数据库中有两个表

Coupon表

• id(int)
• Name(nvarchar(max))
• NoofUses(int)

CouponUse表

• id(int)
• Couponid(int)
• CreateDate(datetime)

每当用户点击一张优惠券时，就会在CouponUse表中写入一个包含该优惠券id的条目。

现在，在Coupon表中有一个名为 NoofUses 的列。我想编写一个存储过程内部的游标，遍历 CouponUse 表并查看一个优惠券有多少行记录，并将该数字填写到 Coupon的 NoofUses 字段中。

我有这个查询

  select COUNT(*) as totalcount , Name as name from Coupon as coupon 
  join CouponUse as couponuse on coupon.id = couponuse.couponid
  group by couponuse.couponid , coupon.Name

它从couponuse中获取优惠券名称和数量。

但我不知道如何在使用游标的存储过程中实现它？

对于问题的任何询问，都将不胜感激，谢谢。

- Smartboy

Answer 1

尝试以下代码片段。您可以从应用程序中调用下面的存储过程，以便更新优惠券表中的NoOfUses。

CREATE PROCEDURE [dbo].[sp_UpdateCouponCount]
AS

Declare     @couponCount int,
            @CouponName nvarchar(50),
            @couponIdFromQuery int


Declare curP cursor For

  select COUNT(*) as totalcount , Name as name,couponuse.couponid  as couponid from Coupon as coupon 
  join CouponUse as couponuse on coupon.id = couponuse.couponid
  where couponuse.id=@cuponId
  group by couponuse.couponid , coupon.Name

OPEN curP 
Fetch Next From curP Into @couponCount, @CouponName,@couponIdFromQuery

While @@Fetch_Status = 0 Begin

    print @couponCount
    print @CouponName

    update Coupon SET NoofUses=@couponCount
    where couponuse.id=@couponIdFromQuery


Fetch Next From curP Into @couponCount, @CouponName,@couponIdFromQuery

End -- End of Fetch

Close curP
Deallocate curP

希望这有所帮助！

- Gowdhaman008

---

@Gowdhaman，有一个问题，这将一次只更新一个优惠券，我希望当我执行存储过程时，它可以更新所有优惠券而不是单个。 - Smartboy

我现在已经编辑了查询。从参数中删除了CouponId。将couponId添加到游标的选择查询中[因为它已经]在您的group by语句中可用。将该ID用于更新语句。这可能会对您有所帮助。 - Gowdhaman008

现在我遇到了这个错误 Cursorfetch: INTO 列表中声明的变量数必须与所选列的数量相匹配。 - Smartboy

我认为我们没有在查询中设置@couponIdFromQuery。 - Smartboy

@Smartboy，我在Fetch Next Statement中错过了couponIdFromQuery。我已经再次编辑了代码。我看到你的问题已解决，而没有使用marc_s的Cursor。很高兴知道这一点。我以为你真的需要这个光标。 - Gowdhaman008

有人能告诉我如何使用Resultset()方法在Java中检索记录吗？ - Rajeev

Answer 2

仅使用一个简单的 UPDATE 语句有何问题？

UPDATE dbo.Coupon
SET NoofUses = (SELECT COUNT(*) FROM dbo.CouponUse WHERE Couponid = dbo.Coupon.ID)

这就是所需要的！没有凌乱和复杂的游标，没有循环，也没有RBAR（逐行痛苦处理）……只有一个漂亮、简单、干净的基于集合的SQL语句。

- marc_s

---

太棒了@marc_s，我希望有一天我能像你一样成为SQL专家...向你致敬，这不是你第一次解决我的很多问题，再次感谢你。 - Smartboy

@Smartboy： <呵呵>谢谢！这主要是时间和经验的问题-一旦你像我一样老了，你也会变得很聪明 :-) - marc_s

无论如何，请给我任何你认为学习SQL的最佳建议。我的意思是，我应该如何学习SQL以应对那种情况。 - Smartboy

Answer 3

您可以创建一个触发器，每当在CouponUse表中使用couponid时，更新Coupon表中的NoofUses列。

查询：

CREATE TRIGGER [dbo].[couponcount] ON [dbo].[couponuse]
FOR INSERT
AS
if EXISTS (SELECT 1 FROM Inserted)
  BEGIN
UPDATE dbo.Coupon
SET NoofUses = (SELECT COUNT(*) FROM dbo.CouponUse WHERE Couponid = dbo.Coupon.ID)
end 

- bharat