SQL 游标的替代方案是什么
我继承了一个数据库，其中有很多游标，根据我的阅读，游标非常慢且资源匮乏。如果可能的话我想删除这些并用其他东西替换。我熟悉 CTE，但想知道 WHILE 1 = 1 循环内的 CTE 是否是最佳实践？
有什么想法吗？
I have inherited a database that has a lot of cursors in it, and from what I have read, cursors and very slow and resource hungry. I'd like to remove these if possbile and replace with something else. I'm familiar with CTEs, but was wondering if a CTE inside of a WHILE 1 = 1 loop is a best practice or not?
Any ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您应该真正寻求删除任何类型的循环。如果游标可以重写为基于集合的语句（无循环），那么这就是您应该做的。
将游标转换为另一种循环形式可以减少开销，但仍然无法解决性能问题。
当然，有时需要进行循环 - 但您通常应该尝试将这些最小化
You should be looking to remove any type of loop really. If a cursor can be rewritten as a set-based statement (no looping) then that is what you should be looking to do.
Converting cursors to another form of looping can reduce overheads, but still will not solve performance problems.
Of course, sometimes there are needs to do loops - but you should generally try to mimimise those to the bare minimum
如果您发现大量游标，则通常表明 SQL 开发人员在编写 SQL 代码时过于程序化。
尝试找到可以将过程类型代码转换为 SQL 自己的基于集合的范例的领域。
Where you find a lot of cursors, it often points towards a poor SQL developer being too procedural when writing his SQL code.
Try to find areas where procedural-type code can be converted to SQL's own set-based paradigm.
为什么你认为你需要循环，它只是很少有必要。光标在做什么？如果没有更具体的例子，很难提供帮助。
我的一些建议是，不要在游标中使用值子句进行插入，而是在基于集合的插入中使用游标中的选择。 （更新和删除也可以通过连接到您在游标选择中挑选的数据来完成，而不是一次一条记录）
如果您正在对特定数据元素进行特殊处理，则 CASE 语句通常是更好的设置-基于方法来处理同一件事。
如果您正在决定是更新还是插入，那么请查看 MERGE。
由于您的游标正在调用存储过程，因此您的开发人员在不合适的情况下做出了重用代码的错误选择。然而，在 SQL Server 2008 中，您现在有一个新选项可以将表作为变量发送到存储过程，因此您可以将过程重写为基于集合，而不是处理单个记录。那么你就不再需要光标了。您将需要检查它在其他什么地方被使用，以确保如果您这样做，来自同一过程的单个记录插入仍然可以正确运行。没有什么说明您不能发送单行表，但是调用代码必须发送正确的表参数而不是十个单独的字段。或者您可以通过放弃您调用的存储过程并编写基于集合的插入来处理此问题。
几乎可以肯定，业务逻辑可以使用 CASE 语句来处理。
Why do you think that you need to loop,it is necessary only very rarely. What kinds of things are the cursors doing? It is hard to help without more specific examples.
A few suggestions I have are instead of using the values clause for an insert in a cursor, use the select from the cursor in a set-based insert instead. (Updates and deletes can also be done with joins to the data you picked out in the cursor select rather than one record at a time)
If you are doing special processing of a specific data element, the CASE statment is usually a better, set-based way to handle the same thing.
If you are deciding whether to update or insert, then look at MERGE.
Since your cursors are calling stored procs, your devs made the poor choice to reuse code when it was not appropriate. However, in SQL Server 2008, you now have a new option to send a table as a variable to a stored proc, so you could rewrite the proc to be set-based and not process individual records. Then you no longer need a cursor. You will need to examine where else it is being used to ensure that the single record inserts from the same proc still operate correctly if you do this. Nothing says you can't send a one-row table, but the calling code willhave to send the correct table parameter instead of ten separate fields. Or you could handle this by ditching the stored proc you call and writing a set-based insert.
The business logic can almost certainly be handled with the CASE statement.