游标sql server_SQL Server游标教程

游标sql server

介绍 (Introduction)

大多数使用Microsoft SQL Server的人至少会听说过游标，而且即使人们基本了解SQL Server游标的作用，他们也不总是确定何时使用游标以及如何在其后面编写代码。 。 因此，本文将向后退一步，并提供有关可以用于什么SQL Server游标的解释，以及可以自己运行进行测试的基本示例。

事务性与程序性编程 (Transactional versus Procedural Programming)

SQL Server is a relational database management system (RDBMS), and T-SQL is a transactional programming language. This means that it is designed to execute its work in all-or-nothing runs. The database engine is optimized to work in this manner and, in general, it is more than sufficient to execute simple all-in-one type transactions.

SQL Server是一种关系数据库管理系统（RDBMS），而T-SQL是一种事务性编程语言。 这意味着它旨在以全有或全无的方式执行其工作。 数据库引擎经过优化以这种方式工作，并且通常，执行简单的多合一类型事务绰绰有余。

Many other programming languages including C# and Visual Basic are iterative or procedural programming languages whereby the general flow of things is to treat each instance of an object separately and when dealing with many objects one would tend to loop over the same code until the stack is diminished and processed.

包括C＃和Visual Basic在内的许多其他编程语言是迭代或过程编程语言，通过这种语言，事物的一般流程是分别处理对象的每个实例，并且当处理许多对象时，人们往往会遍历同一代码，直到堆栈减少为止。和处理。

Cursors however, like WHILE loops, break away from the transactional nature of T-SQL and allow for programmers to treat each result of a SELECT statement in a certain way by looping through them.

但是，游标像WHILE循环一样，摆脱了T-SQL的事务性，并允许程序员通过遍历它们以某种方式来处理SELECT语句的每个结果。

In the IT Engineering world it is common place for people to learn languages like C#, VB, java, C++ or any other the other iterative-type languages before having to deal with SQL in any real/advanced way. It is for this reason, and sadly so, that SQL Server cursors are often very prolific in some applications. It is a common trap that developers fall into and for good reason. The logic behind cursors can be perfect and the idea of writing one can seem good but one runs into real problems when it comes to performance because SQL Server is no longer about to treat whole chunks of data at once and instead has to repeat reads and writes for each result (which can be catastrophic for I/O performance)

在IT工程界，人们必须先学习C＃，VB，java，C ++等语言，然后再以任何实际/高级方式处理SQL，然后再学习其他语言。 正是由于这个原因，令人遗憾的是，SQL Server游标在某些应用程序中通常非常丰富。 这是开发人员陷入并有充分理由的一个常见陷阱。 游标背后的逻辑可能是完美的，写一个游标的想法看似不错，但是在性能方面却遇到了一个实际问题，因为SQL Server不再打算一次处理全部数据，而不得不重复读和写对于每个结果（可能会对I / O性能造成灾难性影响）

Therefore, as a general rule of thumb, and for good performance do not use cursors.

因此，一般而言，为了获得良好的性能，请勿使用游标。

However, there are some situations in which cursors can be lifesavers. I can think of a couple right off the bat:

但是，在某些情况下游标可以成为救生员。 我可以马上想到一对夫妇：

1. transaction isolation levels and they are outside of the scope of this article. However, if one has a READ COMMITTED transaction isolation level which is the case by default, SQL Server cursors or while loops can be helpful to break full table updates into multiple smaller batches. 事务隔离级别 ，它们不在本文讨论范围之内。 但是，如果默认情况下具有READ COMMITTED事务隔离级别，则SQL Server游标或while循环有助于将全表更新分为多个较小的批处理。
2. The second case where cursors may be useful is to create a “for each” type logic in T-SQL scripts. For example, if one would like to handle the deployment of individual tables to another database one could use a cursor and sp_executeSql to run a chunk of T-SQL for each table in a give list.
   游标可能有用的第二种情况是在T-SQL脚本中创建“针对每个”类型的逻辑。 例如，如果要处理将单个表部署到另一个数据库的工作，则可以使用游标和sp_executeSql为给定列表中的每个表运行T-SQL块。

In the example below we will loop through the contents of a table and select the description of each ID we find.

在下面的示例中，我们将遍历表的内容，并选择找到的每个ID的描述。

Take this simple table as an example:

以这个简单的表格为例：

CREATE TABLE #ITEMS (ITEM_ID uniqueidentifier NOT NULL, ITEM_DESCRIPTION VARCHAR(250) NOT NULL)
INSERT INTO #ITEMS
VALUES
(NEWID(), 'This is a wonderful car'),
(NEWID(), 'This is a fast bike'),
(NEWID(), 'This is a expensive aeroplane'),
(NEWID(), 'This is a cheap bicycle'),
(NEWID(), 'This is a dream holiday')

Here are the results of a SELECT * FROM #ITEMS query:

以下是SELECT * FROM #ITEMS查询的结果：

Now say we want to cut the selection of each description into 5 separate transactions. Here is the basic T-SQL cursor syntax to do that.

现在说我们想将每个描述的选择分成5个单独的事务。 这是执行此操作的基本T-SQL游标语法。

DECLARE @ITEM_ID uniqueidentifier  -- Here we create a variable that will contain the ID of each row.
 
DECLARE ITEM_CURSOR CURSOR  -- Here we prepare the cursor and give the select statement to iterate through
FOR
SELECT ITEM_ID
FROM #ITEMS
 
OPEN ITEM_CURSOR -- This charges the results to memory
 
FETCH NEXT FROM ITEM_CURSOR INTO @ITEM_ID -- We fetch the first result
 
WHILE @@FETCH_STATUS = 0 --If the fetch went well then we go for it
BEGIN
 
SELECT ITEM_DESCRIPTION -- Our select statement (here you can do whatever work you wish)
FROM #ITEMS
WHERE ITEM_ID = @ITEM_ID -- In regards to our latest fetched ID
 
FETCH NEXT FROM ITEM_CURSOR INTO @ITEM_ID -- Once the work is done we fetch the next result
 
END
-- We arrive here when @@FETCH_STATUS shows there are no more results to treat
CLOSE ITEM_CURSOR  
DEALLOCATE ITEM_CURSOR -- CLOSE and DEALLOCATE remove the data from memory and clean up the process

Running the basic SQL Server cursor above will loop through each ID in the #ITEMS table and SELECT its corresponding ITEM_DESCRIPTION in 5 separate transactions. You should get the following results after executing the cursor:

运行上面的基本SQL Server游标将循环遍历#ITEMS表中的每个ID，并在5个单独的事务中选择其对应的ITEM_DESCRIPTION。 执行游标后，您应该获得以下结果：

This example may see pointless but consider the fact that you can write any T-SQL you like in between

该示例可能没有意义，但考虑到您可以在两者之间编写任何T-SQL的事实

WHILE @@FETCH_STATUS = 0 
BEGIN
…and…
 
FETCH NEXT FROM [CursorName]

You can certainly imagine many possible usages.

您当然可以想象出许多可能的用法。

结论 (Conclusion)

This article is not meant to be used to proliferate the use of SQL Server cursors even more throughout your applications. As a general rule of thumb one should always think twice, even three times whether the use of a cursor is acceptable for their current problem. 99% of the time the same problem can be dealt with in a purely transactional manner, as is the norm in T-SQL. However, as mentioned above, there are certain exceptions to that rule when it becomes perfectly acceptable to sacrifice performance to avoid blocking the application or just because there is no other option (this is very rarely the case in IT)

本文并非旨在用于在整个应用程序中甚至更多地使用SQL Server游标。 作为一般经验法则，应该始终三思而后行，甚至三遍考虑使用游标是否可以解决当前的问题。 就像T-SQL中的规范那样，有99％的时间可以通过纯粹事务性的方式处理相同的问题。 但是，如上所述，当为降低性能以避免阻塞应用程序而完全可以接受时，或者仅由于没有其他选择（该情况在IT中很少见）时，该规则会有某些例外

So, if you have determined that you absolutely need to use a SQL Server cursor, go ahead and build on the example above.

因此，如果您确定绝对需要使用SQL Server游标，请继续使用上面的示例。

翻译自: https://www.sqlshack.com/sql-server-cursor-tutorial/

游标sql server