In T-SQL a cursor can be declared in two ways (that I know of):
在T-SQL中，游标可以用两种方式声明（我知道）：
declare CursorName cursor for ...- 声明CursorName游标...
declare @CursorName cursor- 声明@CursorName游标
I was running some tests and I notice that the creation of a cursor variable will not add an entry to the result of sp_cursor_list.
我正在运行一些测试，我注意到创建游标变量不会在sp_cursor_list的结果中添加一个条目。
Is there any advantage/disadvantage on using the second approach from the point of view of performance, resource utilization, etc?
从性能，资源利用等角度来看，使用第二种方法是否有任何优势/劣势？
PS: I am aware of potential cursor performance issues. I am not asking for a comparison on cursors vs set based. Or cursor vs while with temp/table variable.
PS：我知道潜在的游标性能问题。我不是要求对游标与基于集合进行比较。或者使用temp / table变量的游标与vs。
3 个解决方案
#1
4
From what I read the purpose of the cursor variable is to be able to use it as an output variable in stored proc, thus enabling you to send the data in the cursor to another controlling proc. I have not tried this so I don't know exactly how it would work, but that is what I get from reading Books Online. I would be surprised if there is any measurable performance difference and certainly not the the improvement you could get by not using a cursor in the first place. If you aren't planning to use it as an output variable, I'd suggest that staying with the more common cursor definiton might make the code easier to maintain.
从我读到的，游标变量的目的是能够将它用作存储过程中的输出变量，从而使您能够将游标中的数据发送到另一个控制过程。我没有试过这个，所以我不确切知道它是如何工作的，但这就是我从阅读在线书籍中获得的。如果有任何可衡量的性能差异，我会感到惊讶，当然不是因为首先不使用光标而获得的改进。如果您不打算将它用作输出变量，我建议保留更常见的游标定义可能会使代码更容易维护。
That said, there are very, very few cases where a cursor is actually needed.
也就是说，实际上需要光标的情况非常非常少。
#2
5
There is another advantage to using the DECLARE @local_variable CURSOR syntax that I just discovered.
使用我刚刚发现的DECLARE @local_variable CURSOR语法还有另一个优点。
The advantage occurs when one stored procedure calls another, and both procedures have cursors open at the same time. If DECLARE cursor_name CURSOR is used to define the cursors, and both procedures use the same cursor_name, then you get
当一个存储过程调用另一个存储过程时，优势就会发生，并且两个过程同时打开游标。如果DECLARE cursor_name CURSOR用于定义游标，并且两个过程都使用相同的cursor_name，那么你得到
Msg 16915: A cursor with the name 'cursor_name' already exists.
消息16915：名称为“cursor_name”的游标已存在。
On the other hand, If DECLARE @local_variable CURSOR is used to define the cursors in the parent and child stored procedures, then @local_variable is local to each procedure and there is no conflict. For those who haven't used this method before, here is an example, using @C as the local variable:
另一方面，如果使用DECLARE @local_variable CURSOR来定义父存储过程和子存储过程中的游标，则@local_variable对于每个过程都是本地的，并且没有冲突。对于之前没有使用过这种方法的人，这里有一个例子，使用@C作为局部变量：
DECLARE @C AS CURSOR;
SET @C = CURSOR FOR SELECT ...;
OPEN @C;
FETCH NEXT FROM @C INTO ...;
...
...
#3
0
I would try to avoid cursers as much as possible (at least if you think about performance). Try to create a set based solution for your problem. They will usually be processed much faster then a cursor based solution.
我会尽量避免使用光标（至少如果你考虑性能）。尝试为您的问题创建基于集合的解决方案。它们通常比基于光标的解决方案处理得快得多。
#1
4
From what I read the purpose of the cursor variable is to be able to use it as an output variable in stored proc, thus enabling you to send the data in the cursor to another controlling proc. I have not tried this so I don't know exactly how it would work, but that is what I get from reading Books Online. I would be surprised if there is any measurable performance difference and certainly not the the improvement you could get by not using a cursor in the first place. If you aren't planning to use it as an output variable, I'd suggest that staying with the more common cursor definiton might make the code easier to maintain.
从我读到的，游标变量的目的是能够将它用作存储过程中的输出变量，从而使您能够将游标中的数据发送到另一个控制过程。我没有试过这个，所以我不确切知道它是如何工作的，但这就是我从阅读在线书籍中获得的。如果有任何可衡量的性能差异，我会感到惊讶，当然不是因为首先不使用光标而获得的改进。如果您不打算将它用作输出变量，我建议保留更常见的游标定义可能会使代码更容易维护。
That said, there are very, very few cases where a cursor is actually needed.
也就是说，实际上需要光标的情况非常非常少。
#2
5
There is another advantage to using the DECLARE @local_variable CURSOR syntax that I just discovered.
使用我刚刚发现的DECLARE @local_variable CURSOR语法还有另一个优点。
The advantage occurs when one stored procedure calls another, and both procedures have cursors open at the same time. If DECLARE cursor_name CURSOR is used to define the cursors, and both procedures use the same cursor_name, then you get
当一个存储过程调用另一个存储过程时，优势就会发生，并且两个过程同时打开游标。如果DECLARE cursor_name CURSOR用于定义游标，并且两个过程都使用相同的cursor_name，那么你得到
Msg 16915: A cursor with the name 'cursor_name' already exists.
消息16915：名称为“cursor_name”的游标已存在。
On the other hand, If DECLARE @local_variable CURSOR is used to define the cursors in the parent and child stored procedures, then @local_variable is local to each procedure and there is no conflict. For those who haven't used this method before, here is an example, using @C as the local variable:
另一方面，如果使用DECLARE @local_variable CURSOR来定义父存储过程和子存储过程中的游标，则@local_variable对于每个过程都是本地的，并且没有冲突。对于之前没有使用过这种方法的人，这里有一个例子，使用@C作为局部变量：
DECLARE @C AS CURSOR;
SET @C = CURSOR FOR SELECT ...;
OPEN @C;
FETCH NEXT FROM @C INTO ...;
...
...
#3
0
I would try to avoid cursers as much as possible (at least if you think about performance). Try to create a set based solution for your problem. They will usually be processed much faster then a cursor based solution.
我会尽量避免使用光标（至少如果你考虑性能）。尝试为您的问题创建基于集合的解决方案。它们通常比基于光标的解决方案处理得快得多。