在建立一个游标后,便可利用系统存储过程对游标进行管理,管理游标的系统过程主要有以下几个:sp_cursor_list、sp_describe_cursor、sp_describe_cursor_tables、sp_describe_cursor_columns.
1.sp_cursor_list
sp_cursor_list显示在当前作用域内的游标及其属性,其命令格式为:
sp_cursor_list[@cursor_return=]cursor_variable_name OUTPUT,
[@cursor_scope=]cursor_scope
其中,cursor_variable_name为游标变量,cursor_scope指出游标的作用域如下表所示。
cursor_scope值 | 描述 |
1 | 表是返回所有的LOCAL游标 |
2 | 表是返回所有的GLOBAL游标 |
3 | 表示LOCAL、GLOBAL游标都返回 |
举例:声明一个键值驱动游标,并使用sp_cursor_list报告该游标的特性。
DECLARE employee_cur CURSOR KEYSET
FOR
SELECT employee_name FROM Employee
WHERE employee_name like'肖%'
OPEN employee_cur /*打开游标employee_cursor*/
/*声明游标变量@report以存储来自sp_cursor_list的游标信息*/
DECLARE @report CURSOR
/*执行sp_cursor_list将信息送游标变量@report*/
EXEC master.dbo.sp_cursor_list @cursor_return=@report OUTPUT
@cursor_scope=2
FETCH NEXT FROM @report
WHILE(@@fetch_status<>-1)
BEGIN
FETCH NEXT FROM @report
END
CLOSE @report
DEALLOCATE @report
GO
CLOSE employee_cur
DEALLOCATE employee_cur
GO
运行结果如图所示
reference_name | cursor_name | cursor_scope | status | model | concurrency | scroollable | open_status | cursor_rows | fetch_status | column_count | |
1 | employee_cur | employee_cur | 2 | 1 | 2 | 3 | 1 | 1 | 1 | 9 | 1 |
2.sp_describe_cursor
sp_describe_cursor用来显示游标的属性。其语法格式如下:
sp_describe-cursor[@cursor_return=]output_cursor_variable OUTPUT
{[,[@cursor_source=]N'local',[@cursor_identity=]N'local_cursor_name']
|[,[@cursor_source=]N'global',[@cursor_identity=]N'global_cursor_name']
|[,[@cursor_source=]N'variable',[@cursor_identity=]N'input_cursor_variable']
}
其中,output_cursor_variable为游标变量,接受游标的输出;@cursor_scource指定进行报告的游标是LOCAL、GLOBAL或游标变量;@cursor_identity指定具有LOCAL类型、GLOBAL类型或关联的游标变量的名称。
举例:定义并打开一个全局游标,使用sp_decribe_cursor报告游标的特性。
DECLARE employee_cur CURSORSTATIC FOR
SELECT employee_name FROM employee
OPEN employee_cur
DECLARE @report CURSOR
EXEC master.dbo.sp_describe_cursor @cursor_return =@Report OUTPUT,
@cursor_source =N'global',@cursor_identity=N'emloyee_cur'
FETCH NEXT from @report
WHILE(@@FETCH_STATUS<>-1)
BEGIN
FETCH NEXT from @report
END
CLOSE @report
DEALLOCATE @report
GO
CLOSE employee_cur
DEALLOCATE employee_cur
GO
说明:
(1)sp_describe_cursor_tables用来显示游标引用的基本表。
(2)sp_describe_cursor_columns用来显示游标结果集中数据列的属性。