SQL的游标使用规则详解和范例

 

MS-SQL的游标是一种临时的数据库对象，既对可用来旋转储存在系统永久表中的数据行的副本，也可以指向储存在系统永久表中的数据行的指针。

游标为您提供了在逐行的基础上而不是一次处理整个结果集为基础的操作表中数据的方法。

1． 如何使用游标

1）     定义游标语句 Declare <游标名> Cursor For

2）     创建游标语句 Open <游标名>

3）     提取游标列值、移动记录指针 Fetch <列名列表> From <游标名> [Into <变量列表>]

4）     使用@@Fetch_Status利用While循环处理游标中的行

5）     删除游标并释放语句 Close <游标名>/Deallocate <游标名>

6）     游标应用实例

--定义游标

Declare cur_Depart Cursor

For Select cDeptID,cDeptName From Department into @DeptID,@DeptName

--创建游标

Open cur_Depart

--移动或提取列值

Fetch From cur_Depart into @DeptID,@DeptName

--利用循环处理游标中的列值

While @@Fetch_Status=0

Begin

    Print @DeptID,@DeptName

    Fetch From cur_Depart into @DeptID,@DeptName

End

--关闭/释放游标

Close cur_Depart

Deallocate cur_Depart

简单的过程：

定义游标

DECLARE CustomerCursor CURSOR FOR

SELECT acct_no,name,balance

FROM customer

WHERE province="北京";

打开游标

OPEN CustomerCursor;

提取数据--设置循环

lb_continue=True

ll_total=0

DO WHILE lb_continue

FETCH CustomerCursor

INTO:ls_acct_no, :ls_name, :ll_balance;

If sqlca.sqlcode=0 Then

ll_total+=ll_balance

Else

lb_continue=False

End If

LOOP

关闭游标

CLOSE CustomerCursor;

2． 语句的详细及注意

1）  定义游标语句

Declare < 游标名> [Insensitive] [Scroll] Cursor                     

    For <Select 语句> [FOR {Read Only | Update [ OF < 列名列表>]}]  

u      Insensitive DBMS创建查询结果集数据的临时副本（而不是使用直接引用数据库表中的真实数据行中的列）。游标是Read Only，也就是说不能修改其内容或底层表的内容；

u      Scroll 指定游标支持通过使用任意Fetch 选项（First Last Prior Next Relative Absolute）选取它的任意行作为当前行。如果此项省略，则游标将只支持向下移动单行（即只支持游标的Fetch Next）；

u      Select 语句 定义游标结果集的标准 SELECT 语句。在游标声明的 <Select语句>内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO；

u      Read Only 防止使用游标的用户通过更新数据或删除行改变游标的内容；

u      Update 创建可更新游标且列出值能被更新的游标列。如果子句中列入了任意列，则只有被列入的列才能被更新。如果Declare Cursor语句中只指定的UPDATE（没有列名列表），则游标将允许更新它的任何或所有列。

Declare cur_Depart Cursor

   For Select * From Department For Update OF cDeptID,cDeptName

2）  提取游标列值、移动记录指针语句

Fetch [Next | Prior | First | Last | {Absolute < 行号>} | {Relative < 行号>}] 

   From < 游标名> [Into < 变量列表……>]                          

每次执行Fetch语句时，DBMS移到游标中的下一行并把游标中的列值获取到Into中列出的变量中。因此Fetch语句的Into子句中列出的变量必须与游标定义中Select 语句中的列表的类型与个数相对应； 仅当定义游标时使用Scroll参数时，才能使用Fetch语句的行定位参数（First、Last、Prior、Next、Relative、Absolute）；如果Fetch语句中不包括参数Next | Prior | First | Last，DBMS将执行默认的Fetch Next；

u      Next 向下、向后移动一行（记录）；

u      Prior 向上、向前移动一行（记录）；

u      First 移动至结果集的第一行（记录）；

u      Last 移动至结果集的最后一行（记录）；

u      Absolute n 移动到结果集中的第n行。如果n是正值，DBMS从结果集的首部向后或向下移动至第n行；如果n是负数，则DBMS从结果集的底部向前或向上移动n行；         

Fetch Absolute 2 From cur_Depart Into @DeptID,@DeptName

u      Relative n    从指针的当前位置移动n行。如果n是正值，DBMS将行指针向后或向下移动至第n行；如果n是负数，则DBMS将行指针向前或向上移动n行；

          Fetch Relative 2 From cur_Depart Into @DeptID,@DeptName

3）  基于游标的定位DELETE/UPDATE 语句

如果游标是可更新的（也就是说，在定义游标语句中不包括Read Only参数）,就可以用游标从游标数据的源表中DELETE/UPDATE行，即DELETE/UPDATE基于游标指针的当前位置的操作；

举例：

--删除当前行的记录

Declare cur_Depart Cursor

    For Select cDeptID,cDeptName From Department into @DeptID,@DeptName

Open cur_Depart

Fetch From cur_Depart into @DeptID,@DeptName

Delete From Department Where CURRENT OF cur_Depart

--更新当前行的内容

Declare cur_Depart Cursor

   For Select cDeptID,cDeptName From Department into @DeptID,@DeptName

Open cur_Depart

Fetch From cur_Depart into @DeptID,@DeptName

   Update Department Set cDeptID=’2007’ + @DeptID Where CURRENT OF cur_Depart

3． 游标使用技巧及注意

1） 利用Order By改变游标中行的顺序。此处应该注意的是，只有在查询的中Select 子句中出现的列才能作为Order by子句列，这一点与普通的Select语句不同；

2） 当语句中使用了Order By子句后，将不能用游标来执行定位DELETE/UPDATE语句；如何解决这个问题，首先在原表上创建索引，在创建游标时指定使用此索引来实现；例如：

Declare cur_Depart Cursor

For Select cDeptID,cDeptName From Department With INDEX(idx_ID)

For Update Of cDeptID,cDeptName

通过在From子句中增加With Index来实现利用索引对表的排序；

3） 在游标中可以包含计算好的值作为列；

4） 利用@@Cursor_Rows确定游标中的行数

4． 使用系统过程管理游标

在建立一个游标之后，便可利用系统过程对游标进行管理管理，游标的系统过程主要有以下几个： sp_cursor_list 、 sp_describe_cursor 、 sp_describe_cursor_tables 、 sp_describe_cursor_columns 。

1）  sp_cursor_list    显示在当前作用域内的游标及其属性。其命令格式为：

sp_cursor_list [ @cursor_return = ] cursor_variable_name OUTPUT，

[ @cursor_scope = ] cursor_scope

参数：

·         [ @cursor_return =] cursor_variable_name OUTPUT：声明的游标变量的名称。 cursor_variable_name 的数据类型为 cursor，没有默认值。游标是可滚动的、动态的只读游标。

·         [ @cursor_scope =] cursor_scope：指定要报告的游标级别。 cursor_scope 的数据类型为 int，没有默认值，可以是下列值中的一个。

值	描述
1	报告所有本地游标。
2	报告所有全局游标。
3	报告本地游标和全局游标。

提示： 由于 sp_cursor_list 是一个含有游标类型变量 @cursor_return ，且有 OUTPUT 保留字的系统过程，游标变量 @cursor_return 中的结果集与 pub_cur 游标中的结果集是不同的。

2）  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 ' ]
    }

参数：

·         [ @cursor_return =] output_cursor_variable OUTPUT：声明游标变量的名称，该变量接收游标输出。 output_cursor_variable 的数据类型为 cursor，没有默认值。调用 sp_describe_cursor 时，不能与任何游标相关联。返回的游标是可滚动的动态只读游标。

·         [ @cursor_source =] { N'local' | N'global' | N'variable' }：指定是使用本地游标的名称、全局游标的名称、还是游标变量的名称来指定当前正在对其进行报告的游标。参数是 nvarchar(30)。

·         [ @cursor_identity =] N' local_cursor_name ']：由具有 LOCAL 关键字或默认设置为 LOCAL 的 DECLARE CURSOR 语句创建的游标的名称。 local_cursor_name 的数据类型为 nvarchar(128)。

·         [ @cursor_identity =] N' global_cursor_name ']：由具有 GLOBAL 关键字或默认设置为 GLOBAL 的 DECLARE CURSOR 语句创建的游标的名称。也可以是由 ODBC 应用程序打开然后通过调用 SQLSetCursorName 对游标命名的 API 服务器游标的名称。 global_cursor_name 的数据类型为 nvarchar(128)。

·         [ @cursor_identity =] N' input_cursor_variable ']：与开放游标相关联的游标变量的名称。 input_cursor_variable 的数据类型为 nvarchar(128)。

提示： sp_descride_cursor_tables 和 sp_describe_cursor_columms 的命令格式与 sp_describe_cursor 的命令格式一样。

5． 游标种类

MS SQL SERVER 支持三种类型的游标：Transact_SQL 游标，API 服务器游标和客户游标。

1）  Transact_SQL 游标Transact_SQL 游标是由DECLARE CURSOR 语法定义、主要用在Transact_SQL 脚本、存储过程和触发器中。Transact_SQL 游标主要用在服务器上，由从客户端发送给服务器的Transact_SQL 语句或是批处理、存储过程、触发器中的Transact_SQL 进行管理。 Transact_SQL 游标不支持提取数据块或多行数据。

2）  API 游标 API 游标支持在OLE DB， ODBC 以及DB_library 中使用游标函数，主要用在服务器上。每一次客户端应用程序调用API 游标函数，MS SQL SEVER 的OLE DB 提供者、ODBC 驱动器或DB_library 的动态链接库（DLL） 都会将这些客户请求传送给服务器以对API游标进行处理。

3）  客户游标 客户游标主要是当在客户机上缓存结果集时才使用。在客户游标中，有一个缺省的结果集被用来在客户机上缓存整个结果集。客户游标仅支持静态游标而非动态游标。由于服务器游标并不支持所有的Transact-SQL 语句或批处理，所以客户游标常常仅被用作服务器游标的辅助。因为在一般情况下，服务器游标能支持绝大多数的游标操作。

由于API 游标和Transact-SQL 游标使用在服务器端，所以被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。在本章中我们主要讲述服务器（后台）游标。

select count(id) from info

select * from info

--清除所有记录

truncate table info

declare @i int

set @i=1

while @i<1000000

begin

insert into info values('Justin'+str(@i),'深圳'+str(@i))

set @i=@i+1

end

6． 游标和游标的优点

在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条T_SQL 选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。如果曾经用 C 语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，只要文件打开 成功，该文件句柄就可代表该文件。对于游标而言，其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。

我们知道关系数据库管理系统实质是面向集合的，在 MS SQL中并没有一种描述表中单一记录的表达形式，除非使用where 子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。 SERVER

由此可见，游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；而且，正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。