MS-SQL游标的使用及理解

    与windows或DOS的“光标”不同，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

2．语句的详细及注意

   1）定义游标语句

   Declare <游标名>  [Insensitive] [Scroll]  Cursor      For  <Select 语句>  [FOR {Read Only | Update [ OF <列名列表>]}]

    Insensitive  DBMS创建查询结果集数据的临时副本（而不是使用直接引用数据库表中的真实数据行中的列）。游标是Read Only，也就是说不能修改其内容或底层表的内容；     Scroll  指定游标支持通过使用任意Fetch 选项（First  Last  Prior  Next Relative  Absolute）选取它的任意行作为当前行。如果此项省略，则游标将只支持向下移动单行（即只支持游标的Fetch  Next）；     Select语句  定义游标结果集的标准 SELECT 语句。在游标声明的 <Select语句>内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO；     Read Only  防止使用游标的用户通过更新数据或删除行改变游标的内容；     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；     Next  向下、向后移动一行（记录）；     Prior  向上、向前移动一行（记录）；     First  移动至结果集的第一行（记录）；     Last  移动至结果集的最后一行（记录）；     Absolute  n  移动到结果集中的第n行。如果n是正值，DBMS从结果集的首部向后或向下移动至第n行；如果n是负数，则DBMS从结果集的底部向前或向上移动n行；          Fetch  Absolute  2  From  cur_Depart  Into  @DeptID,@DeptName     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确定游标中的行数；