SQL 使用数据库游标有什么好处？

What are the benefits of using database cursor?

提问by Vikas

It is based on the interview question that I faced.

它基于我面临的面试问题。

Very short definition can be

很短的定义可以是

It can be used to manipulate the rows returned by a query.

它可用于操作查询返回的行。

Besides the use of the cursor (Points are listed hereon MSDN), I have a question in my mind that if we can perform all the operations using query or stored procedure (if I'm not wrong, Like we can use Transact-SQL for ms-sql), is there any concrete point that we should use cursor?

除了使用光标（分列在这里的MSDN），我在我的脑海的一个问题，如果我们能够执行所有使用查询或存储过程（操作如果我没有错，就像我们可以使用Transact-SQL对于 ms-sql)，我们应该使用游标有什么具体的点吗？

回答by AndreasT

Using cursors compared to big resultsets is like using video streaming instead of downloading an video in one swoop, and watching it when it has downloaded. If you download, you have to have a few gigs of space and the patience to wait until the download finished. Now, no matter how fast your machine or network may be, everyone watches a movie at the same speed.

与大结果集相比，使用游标就像使用视频流而不是一次性下载视频，并在下载后观看。如果你下载，你必须有一些空间和耐心等待下载完成。现在，无论您的机器或网络有多快，每个人都以相同的速度观看电影。

Normally any query gets sent to the server, executed, and the resultset sent over the network to you, in one burst of activity. The cursor will give you access to the data row by row and stream every row only when you request it (can actually view it).

通常，任何查询都会在一次活动中发送到服务器并执行，然后通过网络将结果集发送给您。游标将使您能够逐行访问数据，并且仅在您请求时才流式传输每一行（实际上可以查看它）。

• A cursor can save you time - because you don't need to wait for the processing and download of your complete recordset
• It will save you memory, both on the server and on the client because they don't have to dedicate a big chunk of memory to resultsets
• Load-balance both your network and your server - Working in "burst" mode is usually more efficient, but it can completely block your server and your network. Such delays are seldom desirable for multiuser environments. Streaming leaves room for other operations.
• Allows operations on queried tables (under certain conditions) that do not affect your cursor directly. So while you are holding a cursor on a row, other processes are able to read, update and even delete other rows. This helps especially with very busy tables, many concurrent reads and writes.

• 游标可以节省您的时间 - 因为您不需要等待完整记录集的处理和下载
• 它会节省您在服务器和客户端上的内存，因为它们不必将大量内存专用于结果集
• 平衡您的网络和您的服务器 - 在“突发”模式下工作通常更有效，但它可以完全阻止您的服务器和您的网络。对于多用户环境来说，这种延迟很少是可取的。流式传输为其他操作留下了空间。
• 允许对不直接影响游标的查询表（在某些条件下）进行操作。因此，当您将游标放在一行上时，其他进程能够读取、更新甚至删除其他行。这对于非常繁忙的表、许多并发读取和写入尤其有用。

Which brings us to some caveats, however:

然而，这给我们带来了一些警告：

• Consistency: Using a cursor, you do (usually) not operate on a consistent snapshot of the data, but on a row. So your concurrency/consistency/isolation guarantees drop from the whole database (ACID) to only one row. You can usually inform your DBMS what level of concurrency you want, but if you are too nitpicky (locking the complete table you are in), you will throw away many of the resource savings on the server side.

• Transmitting every row by itself can be very inefficient, since every packet has negotiation overhead that you might avoid by sending big, maybe compressed, chunks of data per packet. ( No DB server or client library is stupid enough to transmit every row individually, there's caching and chunking on both ends, still, it is relevant.)

• Cursors are harder to do right. Consider a query with a big resultset, motivating you to use a cursor, that uses a GROUP BY clause with aggregate functions. (Such queries are common in data warehouses). The GROUP BY can completely trash your server, because it has to generate and store the whole resultset at once, maybe even holding locks on other tables.

• 一致性：使用游标，您（通常）不是对数据的一致快照进行操作，而是对一行进行操作。所以你的并发/一致性/隔离保证从整个数据库 (ACID) 下降到只有一行。您通常可以通知您的 DBMS 您想要什么级别的并发，但是如果您太挑剔（锁定您所在的完整表），您将浪费服务器端的许多资源节省。

• 单独传输每一行可能非常低效，因为每个数据包都有协商开销，您可以通过为每个数据包发送大的、可能是压缩的数据块来避免这些开销。（没有 DB 服务器或客户端库会愚蠢到单独传输每一行，两端都有缓存和分块，但它仍然是相关的。）

• 游标更难做对。考虑一个具有大结果集的查询，它促使您使用游标，该游标使用带有聚合函数的 GROUP BY 子句。（此类查询在数据仓库中很常见）。GROUP BY 可以完全破坏您的服务器，因为它必须立即生成和存储整个结果集，甚至可能锁定其他表。

Rule of thumb:

经验法则：

• If you work on small, quickly created resultsets, don't use cursors.
• Cursors excell on ad hoc, complex (referentially), queries of sequential nature with big resultsets and low consistency requirements.

• 如果您处理小型、快速创建的结果集，请不要使用游标。
• 游标擅长临时、复杂（参考）、具有大结果集和低一致性要求的顺序性质的查询。

"Sequential nature" means there are no aggregate functions in heavy GROUP BY clauses in your query. The server can lazily decide to compute 10 rows for your cursor to consume from a cache and do other stuff meanwhile.

“顺序性”意味着查询中的重 GROUP BY 子句中没有聚合函数。服务器可以懒惰地决定为您的游标计算 10 行以从缓存中使用并同时执行其他操作。

HTH

HTH

回答by Quassnoi

A cursor is a tool that allows you to iterate the records in a set. It has concepts of orderand current record.

游标是一种工具，允许您迭代集合中的记录。它有order和current record 的概念。

Generally, SQLoperates with multisets: these are sets of possibly repeating records in no given order, taken as a whole.

通常，SQL使用多重集进行操作：这些是没有给定顺序的可能重复记录的集合，作为一个整体。

Say, this query:

说，这个查询：

SELECT  *
FROM    a
JOIN    b
ON      b.a = a.id

, operates on multisets aand b.

, 对多重集a和 进行操作b。

Nothing in this query makes any assumptions about the order of the records, how they are stored, in which order they should be accessed, etc.

此查询中的任何内容都没有对记录的顺序、它们的存储方式、访问它们的顺序等做出任何假设。

This allows to abstract away implementation details and let the system try to choose the best possible algorithm to run this query.

这允许抽象出实现细节，并让系统尝试选择最佳算法来运行此查询。

However, after you have transformed all your data, ultimately you will need to access the records in an ordered way and one by one.

但是，在您转换了所有数据之后，最终您将需要以一种有序的方式并一项一项地访问记录。

You don't care about how exactly the entries of a phonebook are stored on a hard drive, but a printer does require them to be feed in alphabetical order; and the formatting tags should be applied to each record individually.

您并不关心电话簿的条目究竟是如何存储在硬盘驱动器上的，但打印机确实要求按字母顺序输入它们；并且格式标签应该单独应用于每条记录。

That's exactly where the cursors come into play. Each time you are processing a resultset on the client side, you are using a cursor. You don't get megabytes of unsorted data from the server: you just get a tiny variable: a resultset descriptor, and just write something like this:

这正是游标发挥作用的地方。每次在客户端处理结果集时，您都在使用游标。您不会从服务器获得数兆字节的未排序数据：您只会获得一个小变量：结果集描述符，只需编写如下内容：

while (!rs.EOF) {
   process(rs);
   rs.moveNext();
}

That's cursor that implements all this for you.

这是为您实现所有这些的游标。

This of course concerns database-client interaction.

这当然涉及数据库-客户端交互。

As for the database itself: insidethe database, you rarely need the cursors, since, as I have told above, almost all data transformations can be implemented using set operations more efficiently.

至于数据库本身：在数据库内部，您很少需要游标，因为正如我上面所说，几乎所有数据转换都可以使用集合操作更有效地实现。

However, there are exceptions:

但是，也有例外：

• Analytic operationsin SQL Serverare implemented very poorly. A cumulative sum, for instance, could be calculated much more efficiently with a cursor than using the set-based operations
• Processing data in chunks. There are cases when a set based operation should be sequentially applied to a portionof a set and the results of each chunk should be committed independently. While it's still possible to do it using set-based operations, a cursor is often a more preferred way to do this.
• Recursionin the systems that do not support it natively.

• 分析操作中SQL Server都很差实施。例如，使用游标可以比使用基于集合的操作更有效地计算累积和
• 分块处理数据。在某些情况下，基于集合的操作应该顺序应用于集合的一部分，并且每个块的结果应该独立提交。虽然仍然可以使用基于集合的操作来完成它，但游标通常是一种更受欢迎的方法。
• 在本机不支持它的系统中递归。

You also may find this article worth reading:

您可能还会发现这篇文章值得一读：

• The Island of Misfit Cursors

• 错误的游标岛

回答by Quassnoi

Using a cursor it is possible to read sequentially through a set of data, programmatically, so it behaves in a similar manner to conventional file access, rather than the set-based behaviour characteristic of SQL.

使用游标可以以编程方式顺序读取一组数据，因此它的行为方式类似于传统的文件访问，而不是 SQL 的基于集合的行为特征。

There are a couple of situations where this may be of use:

这在以下几种情况下可能有用：

1. Where it is necessary to simulate file-based record access behaviour - for example, where a relational database is being used as the data storage mechanism for a piece of code that was previously written to use indexed files for data storage.

2. Where it is necessary to process data sequentially - a simple example might be to calculate a running total balance for a specific customer. (A number of relational databases, such as Oracle and SQLServer, now have analytical extensions to SQL that should greatly reduce the need for this.)

1. 需要模拟基于文件的记录访问行为的地方 - 例如，将关系数据库用作之前编写的一段代码的数据存储机制，以使用索引文件进行数据存储。

2. 需要按顺序处理数据的地方 - 一个简单的例子可能是计算特定客户的运行总余额。（许多关系数据库，例如 Oracle 和 SQLServer，现在对 SQL 进行了分析扩展，这应该会大大减少对此的需求。）

Inevitably, wikipedia has more: http://en.wikipedia.org/wiki/Database_cursor

不可避免地，维基百科有更多：http: //en.wikipedia.org/wiki/Database_cursor

回答by Hurda

With cursor you access one row at a time. So it is good to use it when you want manipulate with a lot of rows but with only one at a given time.

使用游标一次访问一行。因此，当您想要操作很多行但在给定时间只有一个行时，最好使用它。

I was told at my classes, the reason to use cursor is you want to access more rows than you can fit your memory - so you can't just get all rows into a collection and then loop through it.

我在课堂上被告知，使用游标的原因是您想要访问的行数超出了您的内存容量 - 所以您不能只是将所有行放入一个集合中，然后循环遍历它。

回答by Clodoaldo Neto

Sometimes a set based logic can get quite complex and opaque. In these cases and if the performance is not an issue a server side cursor can be used to replace the relational logic with a more manageable and familiar (to a non relational thinker) procedural logic resulting in easier maintenance.

有时，基于集合的逻辑会变得非常复杂和不透明。在这些情况下，如果性能不是问题，则可以使用服务器端游标将关系逻辑替换为更易于管理和熟悉（对非关系思考者而言）的程序逻辑，从而更容易维护。