如何在存储过程中编写游标 [英] How to write a cursor inside a stored procedure

问题描述

我的数据库中有两张桌子



优惠券表



id（int）

名称（nvarchar（max））

NoofUses（int）





CouponUse表



id（int）

Couponid（int）

CreateDate（datetime）





每当用户点击优惠券时，一个条目会进入包含该优惠券ID的CouponUse表格



现在优惠券表中有一个名为NoofUses的列。我想在一个存储过程中写一个光标，它循环在couponuse表上，看到一个优惠券有多少行，并在优惠券的NoofUses字段中填写该数字。



我有这个查询



选择COUNT（*）作为totalcount，名称作为优惠券的优惠券名称

加入CouponUse作为优惠券上的优惠券.id = couponuse.couponid

group by couponuse.couponid，coupon.Name



给我优惠券名称及其优惠券数量



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



请帮帮我。



我该怎么办？



问候，

Narasiman P.

I have two tables in my database

Coupon table

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


CouponUse Table

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


Whenever a user clicks on a coupon an entry goes into the CouponUse table containing that Coupon''s id

Now there is a column in the coupon table called NoofUses. I want to write a cursor inside a stored procedure which loops over couponuse table and sees how many rows are there for one coupon and fill that number in NoofUses field in coupon.

I have this query

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

which gives me the coupon name and its count from couponuse

But I don''t know how to implement that in a stored procedure using a cursor?

Please help me.

how can i do?

Regards,
Narasiman P.

推荐答案

你好，

我不认为你需要使用光标。游标太慢了。你永远不应该使用游标。



这是一个UPDATE命令可以解决你的问题：

Hello,
I don''t think that you need to use a cursor. The cursors are too slow. You should never use cursors.

Here is an UPDATE command which resolves your problem:

UPDATE [Coupon table] SET NoofUses = Total.Nr
FROM [Coupon table] INNER JOIN 
         (SELECT COUNT(Couponid) AS Nr, Couponid FROM [CouponUse Table] GROUP BY Couponid)Total
ON Total.Couponid = [Coupon table].id

当Sandeep消化时。网上有很多这方面的例子。但是，使用游标，它们可能会很慢。它们有很多用途，但大多数实例可以使用普通外观，基于集合的查询或使用RANK / DENSE_RANK函数进行排序，以便您可以根据查询插入/更新。



试着看看你是否可以在没有游标的情况下完成这项任务。如果需要游标，则使用此格式。还要注意光标的额外开关（FAST_FORWARD，READONLY等）



SQL游标：



< a href =http://msdn.microsoft.com/en-GB/library/ms180169.aspx> http://msdn.microsoft.com/en-GB/library/ms180169.aspx [< a href =http://msdn.microsoft.com/en-GB/library/ms180169.aspxtarget =_ blanktitle =新窗口> ^ ]

As Sandeep sugested. There are many examples on the web for this. however, with cursors they can be slow. There are many uses for them but the majority of instances you can use normal looks, set based queries or use RANK / DENSE_RANK functions for ordering so that you can insert / update based on query.

Try to see if you can accomplish this without a cursor. If a cursor is needed then use this format. Also note the extra switches for the cursor (FAST_FORWARD, READONLY etc)

SQL Cursors:

http://msdn.microsoft.com/en-GB/library/ms180169.aspx[^]

DECLARE @Col1 INT,
        @Col2 VARCHAR(100)

DECLARE cur CURSOR FAST_FORWARD FOR 
SELECT col1, col2
FROM TableName

OPEN cur

FETCH NEXT FROM cur 
INTO @Col1, @Col2

WHILE @@FETCH_STATUS = 0
BEGIN
    --Write your insert / update statement here based on @col variables.


    FETCH NEXT FROM cur
    INTO @Col1, @Col2
END 
CLOSE cur;
DEALLOCATE cur;

如果有，请告诉我其他什么？

Let me know if there is anything else?

class sss
{



}

这篇关于如何在存储过程中编写游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！