SQL Server Basics: Cursors

The query statement may return multiple records. If the amount of data is very large, a cursor needs to be used to read the records in the query result set one by one. Applications can scroll or browse the data as needed. This article introduces the concept, classification, and basic operations of cursors.

1: Understanding Cursors
A cursor is a data access mechanism in SQL Server that allows users to access individual data rows. Users can process each row individually, thereby reducing system overhead and potential blocking situations. Users can also use the SQL code generated by these data and execute or output it immediately.

1. The concept of cursor

A cursor is a method of processing data, mainly used in stored procedures, triggers and T_SQL scripts. They make the contents of the result set available to other T_SQL statements. The ability to move forward or backward through data while viewing or processing a result set. Similar to a pointer in C language, it can point to any position in the result set. When the result set is to be processed individually one by one, a cursor variable pointing to the result set must be declared.

The data operation results in SQL Server are all set-oriented, and there is no expression form to describe a single record in the table, unless the WHERE clause is used to limit the query results. The use of cursors can provide this function, and the use of cursors is The operation process is more flexible and efficient.

2. Advantages of cursors

The SELECT statement returns a result set, but sometimes the application cannot always process the entire result set effectively. Cursors provide such a mechanism, which can include When fetching one record at a time from a multi-record result set, the cursor is always associated with a one-hop SQL select statement and consists of the result set and the cursor position pointing to a specific record. Using cursors has the following advantages:

(1). Allows the program to perform the same or different operations each time in the row set returned by the SELECT query statement, instead of performing the same operation on the entire set.

(2). Provides the ability to delete and update rows based on the cursor position.

(3). Cursors serve as a bridge between database management systems and application design, connecting the two processing methods.

3. Classification of cursors

SQL Server supports 3 cursor implementations:

(1). Transact_SQL cursor

is based on DECLARE CURSOR syntax and is mainly used for T_SQL scripts, stored procedures and triggers. T_SQL cursors are implemented on the server and managed by T_SQL statements sent from the client to the server. They may also be included in batches, stored procedures or triggers.

(2). Application Programming Interface (API) server cursor

supports API cursor functions in OLE DB and ODBC, and API server cursors are implemented on the server. Each time a client application calls an API cursor function, the SQL Server Native Client OLE DB provider or ODBC driver transmits the request to the server to operate on the API server cursor.

(3). Client-side cursors

are implemented internally by the SQL Server Native Client ODBC driver and the DLL that implements the ADO API. Client-side cursors are implemented by caching all result set rows on the client side. Each time a client application calls an API cursor function, the SQL Server Native Client ODBC driver or ADO DLL performs a cursor operation on the rows in the cached result set on the client.

Since both T_SQL cursors and server cursors are implemented on the server, they are collectively called server cursors.

ODBC and ADO define 4 cursor types supported by Microsoft SQL Server, so that 4 cursor types can be specified for T_SQL cursors.

The four types of API server cursors supported by SQL Server are:

(i). Forward-only cursor

Forward-only cursor does not support scrolling, it only supports sequential extraction of the cursor from beginning to end. Rows can only be retrieved after being extracted from the database. For all INSERT, UPDATE, and DELETE statements issued by the current user or submitted by other users that affect rows in the result set, the effects are visible when those rows are extracted from the cursor.

Since the cursor cannot scroll backwards, most changes made to a row in the database after the row has been fetched are not visible through the cursor. When a value is used to determine the position of a row in a modified result set (such as updating a column covered by a clustered index), the modified value is visible through the cursor.

(ii). Static cursors

SQL Server static cursors are always read-only. The complete result set is built in tempdb when the cursor is opened. A static cursor always displays the result set as it is when the cursor is opened.

Cursors do not reflect any changes made in the database that affect the membership of the result set, nor changes made to the column values ​​of the rows combined into the result set. Static cursors will not show up in the database when the cursor is opened. Inserted rows even if they match the search criteria of the cursor SELECT statement. If the rows that make up the result set are updated by other users, the new data values ​​will not appear in the static cursor. A static cursor displays rows that were deleted from the data after the cursor was opened. UPDATE, INSERT, or DELETE operations are not reflected in a static cursor (unless the cursor is closed and reopened), or even changes made using the same connection that opened the cursor.

(iii).Key-driven cursor

Membership and order of rows in this cursor are fixed. A keyset-driven cursor is controlled by a set of unique identifiers (keys) called a keyset. A key is generated from a set of columns that uniquely identifies each row of the result set. A key set is a set of key values ​​from all rows that meet the requirements of the SELECT statement when the cursor is opened. The keyset corresponding to a keyset-driven cursor is generated in tempdb when the cursor is opened.

(IV).Dynamic cursor

Dynamic cursor is opposite to static cursor. Dynamic cursors reflect all changes made to the result set as the cursor is scrolled. The row data values, order, and members in the result set change with each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor. They will be immediately visible if updated via a cursor using API functions such as SQLSePos or the T_SQL WHERE CURRENT OF clause. Updates made outside the cursor are not visible until committed unless the cursor's transaction isolation level is set to read uncommitted.
Two: Basic operations of the cursor

1. Declare the cursor

The cursor mainly includes two parts: the cursor result set and the cursor position. The cursor result set is the set of rows returned by the SELECT statement that defines the cursor, and the cursor position points to this result set. A pointer to a row.

Before using a cursor, you must declare the cursor. The DECLARE CURSOR statement is used in SQL Server. Declaring a cursor includes defining the scrolling behavior of the cursor and the user-generated query for the result set operated by the cursor. The syntax format is as follows:

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [,...n] ] ]

cursor_name: Yes The name of the T_SQL server cursor defined.

LOCAL: The scope of this cursor is local to the batch, stored procedure or trigger created within it.

GLOBAL: Specifies that the scope of the cursor is global

FORWARD_ONLY: Specifies that the cursor can only scroll from the first row to the last row. FETCH NEXT is the only supported fetch option. If the STATIC, KEYSET, and DYNAMIC keywords are not specified when FORWARD_ONLY is specified, the cursor operates as a DYNAMIC cursor. If both FORWARD_ONLY and SCROLL are specified, the cursor operates as a DYNAMIC cursor unless the STATIC, KEYSET, and DYNAMIC keywords are specified. , otherwise it defaults to FORWARD_ONLY. STATIC, KEYSET and DYNAMIC cursors default to SCROLL. Unlike database APIs such as ODBC and ADO, STATIC, KEYSET, and DYNAMIC T_SQL cursors support FORWARD_ONLY.

STATIC: Define a cursor to create a temporary copy of the data that will be used by the cursor. All requests to the cursor are not answered from the temporary table in tempdb; therefore, when performing a fetch operation on the cursor Modifications to the base table are not reflected in the returned data, and the cursor does not allow modifications.

KEYSET: Specifies that when the cursor is opened, the membership and order of the rows under the cursor are fixed. The keys that uniquely identify rows are built into a table called keyset within tempdb.

DYNAMIC: Define a cursor to reflect all data changes made to rows within the result set as the cursor is scrolled. The data values, order, and membership of rows change on each fetch, and the ABSOLUTE fetch option is not supported with dynamic cursors.

FAST_FORWARD: Specifies the FORWARD_ONLY and READ_ONLY cursors with performance optimization enabled. If SCROLL or FOR_UPDATE is specified, FAST_FORWARD cannot be specified.

SCROLL_LOCKS: Specifies that positioning updates or deletions through cursors must succeed. SQL Server locks rows when they are read into a cursor to ensure that they can be modified later. SCROLL_LOCKS cannot be specified if FAST_FORWARD or STATIC is also specified.

OPTIMISTIC: Specifies that if the row has been updated since the cursor was read in, positioned updates or positioned deletes through the cursor will not succeed. When a row is read into a cursor, SQL Server does not lock the row. It instead uses the timestamp column value comparison to determine whether the row has been modified since it was read into the cursor. If the table does not contain a timestamp column, it instead uses the checksum value to determine , if the row is modified, an attempted positioned update or delete will fail, and OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.

TYPE_WARNING: Specifies that a warning message is sent to the client when the cursor is implicitly converted from the requested type to another type.

select_statement: is the standard SELECT statement that defines the cursor result set.

[Example] Declare a cursor named cursor_fruit

USE sample_db;
GO
DECLARE cursor_fruit CURSOR FOR
SELECT f_name,f_price FROM fruits;

2. Open the cursor

You must open the cursor before using it. The syntax for opening a cursor is as follows:

OPEN [ GLOBAL ] cursor_name | cursor_variable_name;

GLOBAL: Specify cursor_name as a global cursor.

cursor_name: The name of the declared cursor. If both the global and local cursors use cursor_name as their name, then cursor_name refers to the global cursor if GLOBAL is specified, otherwise cursor_name refers to the local cursor.

cursor_variable_name: The name of the cursor variable.

[Example] Open the cursor named cursor_fruit declared in the above example

USE sample_db;
GO
OPEN cursor_fruit;

3. Read the data in the cursor

After opening the cursor, you can read the data in the cursor. The FETCH command can read a certain value in the cursor. A row of data. The syntax of FETCH is as follows:

ETCH 
[ [ NEXT | PRIOR | FIRST | LAST
         | ABSOLUTE { n | @nvar }
         | RELATIVE { n | @nvar }
    ]
FROM
]
{ { [GLOBAL ] cursor_name } | @cursor_variable_name}
[ INTO @variable_name [ ,...n ] ]

NEXT:紧跟当前行返回结果行，并且当前行递增为返回行，如果FETCH NEXT为对游标的第一次提取操作，则返回结果集中的第一行。NEXT为默认的游标提取选项。

PRIOR：返回紧邻当前行前面的结果行，并且当前行递减为返回行，如果FETCH PRIOR为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。

FIRST：返回游标中的第一行并将其作为当前行。

LAST:返回游标中的最后一行并将其作为当前行。

ABSOLUTE { n | @nvar }：如果n或@nvar为正，则返回从游标头开始向后n行的第n行，并将返回行变成新的当前行。如果n或@nvar为负，则返回从游标末尾开始向前的n行的第n行，并将返回行变成新的当前行。如果n或@nvar为0，则不返回行。n必须是整数常量，并且@nvar的数据类型必须为int、tinyint或smallint.

RELATIVE { n | @nvar }：如果n或@nvar为正，则返回从当前行开始向后的第n行。如果n或@nvar为负，则返回从当前行开始向前的第n行。如果n或@nvar为0，则返回当前行,对游标第一次提取时，如果在将n或@nvar设置为负数或0的情况下指定FETCH RELATIVE,则不返回行，n必须是整数常量，@nvar的数据类型必须是int、tinyint或smallint.

GLOBAL：指定cursor_name是全局游标。

cursor_name：已声明的游标的名称。如果全局游标和局部游标都使用cursor_name作为其名称，那么如果指定了GLOBAL，则cursor_name指的是全局游标，否则cursor_name指的是局部游标。

@cursor_variable_name：游标变量名，引用要从中进行提取操作的打开的游标。

INTO @variable_name [ ,…n ]：允许将提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果集列的数据类型相匹配，或是结果集列数据类型所支持的隐士转换。变量的数目必须与游标选择列表中的列数一致。

【例】使用名称为cursor_fruit的光标，检索fruits表中的记录，输入如下：

USE sample_db;
GO
FETCH NEXT FROM cursor_fruit
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM cursor_fruit
END;

4.关闭游标

SQL Server 在打开游标之后，服务器会专门为游标开辟一定的内存空间存放游标操作的数据结果集，同时游标的使用也会根据具体情况对某些数据进行封锁。所以在不使用游标的时候，可以将其关闭，以释放游标所占用的服务器资源，关闭游标使用CLOSE语句。语法格式如下：

CLOSE [ GLOBAL ] cursor_name | cursor_variable_name

【例】关闭名称为cursor_fruit的游标

CLOSE cursor_fruit;

5.释放游标

游标操作的结果集空间虽然被释放了，但是游标本身也会占用一定的计算集资源，所以使用完游标之后，为了收回被游标占用的资源，应该将游标释放。释放游标使用DEALLOCATE语句，语法格式如下：

DEALLOCATE [GLOBAL] cursor_name | @ccursor_variable_name

@ccursor_variable_name：游标变量的名称，@ccursor_variable_name必须为cursor类型。

DEALLOCATE @ccursor_variable_name 语句只删除对游标变量名称的引用，直到批处理、存储过程或触发器结束时变量离开作用域，才释放变量。

【例】使用DEALLOCATE语句释放名称为cursor_fruit的变量，输入如下：

DEALLOCATE cursor_fruit;

三:游标的运用

1.使用游标变量

声明变量用DECLARE，为变量赋值可以用set或SELECT语句，对于游标变量的声明和赋值，其操作基本相同。在具体使用时，首先要创建一个游标，将其打开后，将游标的值赋给游标变量，并通过FETCH语句从游标变量中读取值，最后关闭释放游标。

【例】声明名称为@varCursor的游标变量，输入如下：

DECLARE @varCursor Cursor --声明游标变量
DECLARE cursor_fruit CURSOR FOR --创建游标
SELECT f_name,f_price FROM fruits;
OPEN cursor_fruit --打开游标
SET @varCursor=cursor_fruit --为游标变量赋值
FETCH NEXT FROM @varCursor --从游标变量中读取值
WHILE @@FETCH_STATUS=0 --判断FETCH语句是否执行成功
BEGIN
FETCH NEXT FROM @varCursor --读取游标变量中的数据
END
CLOSE @varCursor --关闭游标
DEALLOCATE @varCursor; --释放游标

2.用游标为变量赋值

在游标的操作过程中，可以使用FETCH语句将数据值存入变量，这些保持表中列值的变量可以在后面的程序中使用。

【例】创建游标cursor_variable,将fruits表中的记录f_name,f_price值赋给变量@fruitName和@fruitPrice，并打印输出。

3.用ORDER BY 子句改变游标中的执行顺序

游标是一个查询结果集，那么能不能对结果进行排序呢？答案是否定的。与基本的SELECT语句中的排序方法相同，ORDER BY子句添加到查询中可以对游标查询的结果排序。

注意：只有出现在游标中的SELECT语句中的列才能作为ORDER BY 子句的排序列，而对与非游标的SELECT语句中，表中任何列都可以作为ORDER BY 的排序列，即使该列没有出现在SELECT语句的查询结果列中。

【例】声明名称为cursor_order的游标，对fruits表中的记录按照价格字段降序排列，输入语句如下：

4.用游标修改数据

【例】声明整型变量@sid=101,然后声明一个对fruits表进行操作的游标，打开该游标，使用FETCH NEXT方法来获取游标中的每一行的数据，如果获取到的记录的s_id的字段值与@sid值相同，将s_id=@sid的记录中的f_price修改为12.2，最后关闭释放游标，输入如下：

5.用游标删除数据

使用游标删除数据时，既可以删除游标结果集中的数据，也可以删除基本表中的数据

【例】使用游标删除fruits表中s_id=102的记录，如下

以上例子的sql脚本：

USE sample_db;
create TABLE fruits(
f_id int IDENTITY(1,1) PRIMARY KEY,--水果id
s_id int not null, --供应商id
f_name varchar(255) not null,--水果名称
f_price decimal(8,2) not null --水果价格
);
insert into fruits (s_id,f_name,f_price) 
 values
 (101,'apple',5.8),
 (102,'blackberry',6.8),
 (105,'orange',4.5),
 (102,'banana',3.5),
 (103,'lemon',8.0),
 (104,'grape',7.6),
 (101,'melon',10.5);
--1.声明名称为cursor_fruit的游标
USE sample_db;
GO
DECLARE cursor_fruit CURSOR FOR
SELECT f_name,f_price FROM fruits;
--2.打开游标
OPEN cursor_fruit;
--3.读取游标中的数据
--【例】使用名称为cursor_fruit的光标，检索fruits表中的记录，输入如下：
USE sample_db;
GO
FETCH NEXT FROM cursor_fruit 
WHILE @@FETCH_STATUS=0
BEGIN
 FETCH NEXT FROM cursor_fruit
END;
--4.关闭关闭名称为cursor_fruit的游标
CLOSE cursor_fruit
--5.释放游标
DEALLOCATE cursor_fruit;
--游标的运用
--1.使用游标变量
--声明名称为@varCursor的游标变量
DECLARE @varCursor Cursor --声明游标变量
DECLARE cursor_fruit CURSOR FOR --创建游标
SELECT f_name,f_price FROM fruits;
OPEN cursor_fruit --打开游标
SET @varCursor=cursor_fruit --为游标变量赋值
FETCH NEXT FROM @varCursor --从游标变量中读取值
WHILE @@FETCH_STATUS=0 --判断FETCH语句是否执行成功
BEGIN
 FETCH NEXT FROM @varCursor --读取游标变量中的数据
END
CLOSE @varCursor --关闭游标
DEALLOCATE @varCursor; --释放游标 
--2.用游标为变量赋值
--创建游标cursor_variable,将fruits表中的记录f_name,f_price值赋给变量@fruitName和@fruitPrice，并打印输出。
DECLARE @fruitName varchar(50),@fruitPrice DECIMAL(8,2)
DECLARE cursor_variable CURSOR FOR
SELECT f_name,f_price FROM fruits
WHERE s_id=101;
OPEN cursor_variable
FETCH NEXT FROM cursor_variable
INTO @fruitName,@fruitPrice
PRINT '编号为101的供应商提供的水果种类和价格为：'
WHILE @@FETCH_STATUS=0
BEGIN 
 PRINT @fruitName+' '+STR(@fruitPrice,8,2)
FETCH NEXT FROM cursor_variable
INTO @fruitName,@fruitPrice
END
CLOSE cursor_variable
DEALLOCATE cursor_variable;
--3.用ORDER BY子句改变游标中的执行顺序
--声明名称为cursor_order的游标，对fruits表中的记录按照价格字段降序排列，输入语句如下：
DECLARE cursor_order CURSOR FOR
SELECT f_id,f_name,f_price FROM fruits
ORDER BY f_price DESC
OPEN cursor_order
FETCH NEXT FROM cursor_order
WHILE @@FETCH_STATUS=0
FETCH NEXT FROM cursor_order
CLOSE cursor_order
DEALLOCATE cursor_order;
--4.用游标修改数据
--【例】声明整型变量@sid=101,然后声明一个对fruits表进行操作的游标，打开该游标，
--使用FETCH NEXT方法来获取游标中的每一行的数据，
--如果获取到的记录的s_id的字段值与@sid值相同，将s_id=@sid的记录中的f_price修改为12.2，最后关闭释放游标，输入如下：
DECLARE @sid INT,@id INT =101
DECLARE cursor_fruit CURSOR FOR
SELECT s_id FROM fruits;
OPEN cursor_fruit
FETCH NEXT FROM cursor_fruit INTO @sid
WHILE @@FETCH_STATUS=0
BEGIN
 IF @sid=@id
BEGIN 
 UPDATE fruits SET f_price=11.1 WHERE s_id=@id
END
FETCH NEXT FROM cursor_fruit INTO @sid
END
CLOSE cursor_fruit
DEALLOCATE cursor_fruit;
SELECT * FROM fruits where s_id=101;
--5.使用游标删除数据
--【例】使用游标删除fruits表中s_id=102的记录，如下
DECLARE @sid1 INT,@id1 int=102
DECLARE cursor_delete CURSOR FOR
SELECT s_id FROM fruits;
OPEN cursor_delete
FETCH NEXT FROM cursor_delete INTO @sid1
WHILE @@FETCH_STATUS=0
BEGIN
 IF @sid1=@id1
BEGIN 
 DELETE FROM fruits where s_id=@id1
END
FETCH NEXT FROM cursor_delete INTO @sid1
END
CLOSE cursor_delete
DEALLOCATE cursor_delete;
SELECT * FROM fruits where s_id=102;