在SQL Server中使用游标变量的优点（声明@cn游标）
在 T-SQL 中，可以通过两种方式声明游标（据我所知）：
declare CursorNamecursorfor ...declare @CursorNamecursor
我正在运行一些测试，并且我注意到创建游标变量不会向 sp_cursor_list 的结果添加条目。
从性能、资源利用率等角度来看，使用第二种方法有什么优点/缺点吗？
PS：我知道潜在的游标性能问题。我并不是要求对游标与基于集的进行比较。或者光标与带有临时/表变量的 while 。
In T-SQL a cursor can be declared in two ways (that I know of):
declare CursorName cursor for ...declare @CursorName cursor
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.
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
使用我刚刚发现的 DECLARE @local_variable CURSOR 语法还有另一个优点。
当一个存储过程调用另一个存储过程并且两个过程同时打开游标时，就会出现这种优点。如果使用DECLAREcursor_nameCURSOR来定义游标，并且两个过程使用相同的cursor_name，那么您将得到
另一方面，如果使用
DECLARE @local_variable CURSOR来定义父存储过程和子存储过程中的游标，则@local_variable对于每个过程来说都是本地的，并且没有冲突。对于那些以前没有使用过此方法的人，这里有一个示例，使用@C作为局部变量：...
There is another advantage to using the
DECLARE @local_variable CURSORsyntax that I just discovered.The advantage occurs when one stored procedure calls another, and both procedures have cursors open at the same time. If
DECLARE cursor_name CURSORis used to define the cursors, and both procedures use the same cursor_name, then you getOn the other hand, If
DECLARE @local_variable CURSORis used to define the cursors in the parent and child stored procedures, then@local_variableis local to each procedure and there is no conflict. For those who haven't used this method before, here is an example, using@Cas the local variable:...
据我了解，游标变量的目的是能够将其用作存储过程中的输出变量，从而使您能够将游标中的数据发送到另一个控制过程。我没有尝试过，所以我不知道它到底是如何工作的，但这就是我从阅读在线书籍中得到的。如果存在任何可测量的性能差异，并且肯定不是一开始不使用光标所能获得的改进，我会感到惊讶。如果您不打算将其用作输出变量，我建议保留更常见的游标定义可能会使代码更易于维护。
也就是说，实际上需要游标的情况非常非常少。
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.
我会尽量避免诅咒（至少如果你考虑性能的话）。尝试为您的问题创建基于集合的解决方案。它们的处理速度通常比基于游标的解决方案快得多。
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.