《游标(2012年12月)》由会员分享，可在线阅读，更多相关《游标(2012年12月)（47页珍藏版）》请在金锄头文库上搜索。
1、 目标 了解游标的概念 掌握游标的使用方法游标的概念 游标(Cursor)它使用户可逐行访问由SQL Server返回的结果集。使用游标(cursor)的一 个主要的原因就是把集合操作转换成单个记 录处理方式。用SQL语言从数据库中检索数 据后，结果放在内存的一块区域中，且结果 往往是一个含有多个记录的集合。游标机制 允许用户在SQL server内逐行地访问这些记 录，按照用户自己的意愿来显示和处理这些 记录。游标的种类 按处理特性： 静态游标 动态游标（缺省 ） 键集驱动游标 按游标结果集是否允许 改动 只读游标 可写游标（缺省 ） 按游标在结果集移动方式 滚动游标 前向游标(单进)（缺省
2、）静态游标打开静态游标时，SQL SERVER将游 标结果集的所有数据行一次性复制到系统数 据库Tempdb的临时表中，当基表中的数据 发生变化时，游标的结果集并不相应的发生 变化。 静态游标移动时消耗资源较少，但占用 了较多的临时表空间。动态游标当滚动游标时，动态游标反应结果集中 所作的所有修改，所有用户所作的全部 UPDATE、INSERT、DELETE语句均通过游 标可见。 当基表中的数据发生变化时，游标的结 果集相应的发生变化。 动态游标移动时消耗资源较多，但占用 了最少的临时表空间。键集驱动游标键集驱动游标介于静态游标和动态游标之 间，兼有两者的特点。打开键集驱动游标后，游标中的成员
3、和行 顺序是固定的。键集驱动游标由一套惟一标 识符控制，这些惟一标识符就是键集。用户对基表中的非关键值列插入数据或进 行修改造成数据值的变化，在整个游标中都 是可见。键集驱动游标的键集在游标打开时建立在 数据库tempdb中。 假设有如下三张表ProductsProductSortUnits游标的使用步骤 声明游标 打开游标 从打开的游标中提取行 关闭游标 释放游标声明游标 声明游标是指用DECLARE语句声明或创建一个游标。 声明游标的语法如下： DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR RE
4、AD ONLY|UPDATEOF column_name_list 其中： cursor_name ：是游标的名字，为一个合法的SQL Server标识 符，游标的名字必须遵循SQL Server命名规范。 SCROLL：表示取游标时 可以使用关键字NEXT、PRIOR 、FIRST、LAST、ABSOLUTE、RELATIVE。每个关键字的含 义将在介绍FETCH子句时讲 解。省略则只NEXT可用 select_statement：是定义游标结 果集的标准 SELECT 语句，它可以是一个完整语法和语义 的Transact-SQL的SELECT 语句。但是这个SELECT语句必须有FROM子
5、句，不允许使用关 键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。FOR READ ONLY：指出该游标结 果集只能读，不能 修改。 FOR UPDATE：指出该游标结 果集可以被修改。 OF column_name_list：列出可以被修改的列的名单 。 注意： 1.游标有且只有两种方式：FOR READ ONLY 或FOR UPDATE，默认为FOR UPDATE 方式。 2.当游标方式指定为FOR READ ONLY时，游 标涉及的表不能被修改。 3.当游标方式指定为FOR UPDATE时，可以删 除或更新游标涉及的表中的行。 4.游标必须先申明再打开即声
6、明游标的 DECLARE CURSOR 语句必须是在该游标的任何 OPEN语句之前。简单定义游标 都缺省的：动态，向前遍历，可修改的游标 INSENSITIVE（不敏感游标，即静态游标 ） 定义一个游标，以创建将由该游标使用的 数据的临时复本。对游标的所有请求都从 tempdb 中的该临时表中得到应答；因此， 在对该游标进行提取操作时返回的数据中不 反映对基表所做的修改，并且该游标不允许 修改。使用 SQL-92 语法时，如果省略 INSENSITIVE，（任何用户）对基表提交的 删除和更新都反映在后面的提取中。Transact-SQL扩展游标定义格式 语法格式如下：DECLARE curso
7、r_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 打开游标 打开游标是指打开已被声明但尚未被打开的游标，打开游 标使用OPEN语句。 打开游标的语法如下：OPEN cursor_name 其中：cursor_name是一个已声明的尚未打开的游标名。 注意： 当游
8、标打开成功时，游标位置指向结果集的 第一行之前。 只能打开已经声明但尚未打开的游标。从打开的游标中提取行 游标被打开后，游标位置位于结果集的第一行前，此时 可以从结果集中提取（FETCH）行。SQL Server将沿着 游标结果集一行或多行向下移动游标位置，不断提取结 果集中的数据，并修改和保存游标当前的位置，直到结果 集中的行全部被提取。 从打开的游标中提取行的语法如下： FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE FROM cursor_name INTO fetch_target_list 其中： cursor_name：为一已声明并已打开
9、的游标名字。 NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE： 游标移动方向，缺省情况下是NEXT，即向下移动。 NEXT：取下一行数据。 PRIOR：取前一行数据。 FIRST：取第一行数据。LAST：取最后一行数据。 ABSOLUTE：按绝对 位置取数据，从第一行开始取记 录。 FETCH ABSOLUTE n FROM myCursor INTO SortID, SorttName n为正，则从记录 集首部向下移动至第n条记录 ，负则从 记录 集底部向上移动n行。 RELATIVE：按相对位置取数据。游标位置确定了结 果集中哪一行可以被提取，如果游标方式为
10、FOR UPDATE的 话，也就确定该位置一行数据可以被更新或删除。 FETCH RELATIVE n FROM myCursor INTO SortID, SorttName n为正，则从当前指针位置向下移动至第n条记录 ，负则从 当前指针位置向上移动n行。 INTO fetch_target_list：指定存放被提取的列数据的 目的变量清单。这个清单中变量的个数、数据类型、顺序必 须与定义该 游标的select_statement的SELECT_list中列出的 列清单相匹配。为了更灵活地操纵数据，可以把从已声明并已 打开的游标结 果集中提取的列数据，分别存放在目的变量中。 有两个全局变量
11、提供关于游标活动的信息： FETCH_STATUS 保存着最后FETCH语句执行后的状态信息，其 值和含义如下： 0 ：表示成功完成FETCH 语句。 -1：表示FETCH语句执行有错误 ，或者当前游标 位置已在结果集中的最后一行，结果集中不再有数据。 -2：提取的行不存在。 rowcount 保存着自游标打开后的第一个FETCH语句，直 到最近一次的FETCH语句为止，已从游标结 果集中提取的行 数即已FETCH的记录 数，而非所有记录 数。 每个打开的游标都与一特定的rowcount有关，关闭游 标时 ，该rowcount变量也被删除。在FETCH语句执行后查看这个 变量，可得知从游标结
12、果集中已提取的行数。 思考：rowcount只能得到已FETCH的记录 数，如果要取集合内所有的记录 数，必须循环把集合内的记录 都 FETCH一遍，那么有没办法直接在打 开游标后就获取到游标结 果集的行数呢？ 答：可以声明游标为 静态游标(INSENSITIVE)，在打开游标后， CURSOR_ROWS 的值即是游标结 果集的行数。具体代码见 PPT第19页。 思考：从上面的例子可以得出 rowcount与CURSOR_ROWS 有 什么不同？关闭游标 关闭(Close)游标是停止处理定义游标的那个查询。关闭 游标并不改变它的定义，可以再次用open语句打开它， SQL Server会用该游
13、标的定义重新创建这个游标的一个结 果集。 关闭游标的语法如下：CLOSE cursor_name 其中： cursor_name：是已被打开并将要被关闭的游标名字 。 在如下情况下，SQL Server会自动地关闭已打开的游 标： 当你退出这个SQL Server会话时 从声明游标的存储过 程中返回时，即遇到 RETURN 时。释放游标 释放( Deallocate)游标是指释放所有分配给此游标的资源 ，包括该游标的名字。 释放游标的语法是： DEALLOCATE CURSOR cursor_name 其中： cursor_name：将要被DEALLOCATE释放的游标名字 。如果释放一个已打
14、开但未被关闭的游标，SQL Server会自 动先关闭这 个游标，然后再释放它。 关闭游标与释放游标的区别：关闭游标并不改变游标的 定义，一个游标关闭后，不需要再次声明，就可以直接重新 打开并使用它。但一个游标释放后，就释放了与该游标有 关的一切资源，也包括游标的声明，游标释放后就不能再使 用该游标了，如需再次使用游标，就必须重新定义。在以 后学编程语言的过程中，也要主意关闭和释放的区别，和 这里类似。详细代码-设置不显示计数，即不显示每条SQL语句执行完成后下面显示的(x行受影响) SET NOCOUNT ON-声明变量，用以存放未来游标中取出的值DECLARE SortID INTDECL
15、ARE SorttName NVARCHAR(50)-声明游标，主意myCursor前面没有 符号DECLARE myCursor CURSOR FOR SELECT ID, SorttName FROM dbo.ProductSort-打开游标OPEN myCursor-读取数据FETCH NEXT FROM myCursor INTO SortID, SorttName（接下）（接上）-判断游标的状态 - 0为fetch成功- -1：fetch语句失败或此行不在结果集中- -2：被提取的行不存在WHILE(FETCH_STATUS = 0)BEGINPRINT 产品类别： + SorttNameSELECT Products.ProductName AS 产品名称, Products.Price AS 单价, Units.UnitName AS 计价单位 FROM dbo.Products LEFT OUTER JOIN dbo.Units ON dbo.Products.UnitID = dbo.Units.ID WHERE dbo.Products.ProductSortID = SortID（接下）（接上）-用游标去取下一条记录FETCH NEXT FROM myCursor INTO SortID, Sort
