名为'<Cursor Name>'的游标已存在

现象

解决方法

关于游标作用域

判断游标是否已存在

参考资料

现象：

.NET 应用程序，调用数据库存储过程，存储过程中使用游标遍历表格，格式如下：

DECLARE cursorTmp CURSOR FOR
    SELECT * FROM Student;
OPEN cursorTmp ;
FETCH NEXT FROM cursorTmp INTO @Name,@Age,@Score;
WHILE @@FETCH_STATUS = 0 
BEGIN
....
END
CLOSE C_LOC;
DEALLOCATE C_LOC;

程序调用(循环调用)一段时间之后报错，显示名为cursorTmp 的游标已存在。

解决方法：

声明游标的时候修改为：

DECLARE C_LOC CURSOR LOCAL FAST_FORWARD FOR

将游标范围定义为'LOCAL'解决这个问题；FAST_FORWARD非必须，若只遍历不修改，可以使用这个来提高遍历效率。

关于游标的作用域：

游标的作用域分有两种：

• Local：作用域为局部，只在定义它的批处理，存储过程或触发器中有效。
• Global：作用域为全局，由连接执行的任何存储过程或批处理中，都可以引用该游标

存储过程中声明的游标若不指定作用域则视具体数据库的设置而定，查看当前默认是Global还是Local，可以使用下面的语句查看：

select is_local_cursor_default from sys.databases where name = '[your database name]'

返回值为0代表默认为Global，1代表为Local。

判断游标是否已存在

可以使用下面的语句

IF (SELECT CURSOR_STATUS('global','myCursor')) >= -1
BEGIN
 DEALLOCATE myCursor
END

CURSOR_STATUS的返回值如下;

Return value	Cursor name	Cursor variable
1	The cursor result set has at least one row. For insensitive and keyset cursors, the result set has at least one row. For dynamic cursors, the result set can have zero, one, or more rows.	The cursor allocated to this variable is open. For insensitive and keyset cursors, the result set has at least one row. For dynamic cursors, the result set can have zero, one, or more rows.
0	The cursor result set is empty.*	The cursor allocated to this variable is open, but the result set is definitely empty.*
-1	The cursor is closed.	The cursor allocated to this variable is closed.
-2	Not applicable.	Has one of these possibilities: The previously called procedure did not assign a cursor to this OUTPUT variable. The previously assigned procedure assigned a cursor to this OUTPUT variable, but the cursor was in a closed state when the procedure completed. Therefore, the cursor is deallocated, and not returned to the calling procedure. No cursor is assigned to the declared cursor variable.
-3	A cursor with the specified name does not exist.	A cursor variable with the specified name does not exist, or if one exists, no cursor is yet allocated to it.

参考资料：

CURSOR_STATUS具体使用：https://docs.microsoft.com/en-us/sql/t-sql/functions/cursor-status-transact-sql?view=sql-server-ver15

查看游标已存在： https://stackoverflow.com/questions/7430560/how-to-check-if-cursor-exists-open-status