1、第五章 游标,概述,游标的概念：游标是映射在结果集并在结果集内的单个行上建立一个位置的实体。使用游标，用户可以访问结果集中的任意一行数据，在将游标放到某行之后，可以在该行或从该位置开始的行块上执行操作，常见的操作是检索当前行或块。游标的主要功能如下： （1）允许定位到结果集的特定行 （2）从结果集的当前行检索一行或多行 （3）支持对结果集中当前行位置的行进行数据修改 （4）如果其他用户需要对显示在结果集中的数据进行修改，游标可 以提供不同级别的可见性支持 （5）提供脚本、存储过程和触发器中使用的访问结果集中的数据的sql语句。,概述,1.游标的组成游标包含两个部分：游标结果集、游标位置。游标结
2、果集：定义该游标的SELECT语句返回的行的集合。 游标位置：指向这个结果集某一行的当前指针。2.游标的分类 API服务器游标、 Transaction-SQL游标、 API客户端游标前两种游标都是运行在服务器上的，所以又叫做服务器游标。 API服务器游标包含以下四种：静态游标、动态游标、只进游标、键集驱动游标(Primary key) 静态游标：它的完整结果集将打开游标时建立的结果集存储在临时表中，(静态游标始终是只读的)。静态游标具有以下特点：总是按照打开游标时的原样显示结果集;不反映数据库中作的任何修改，也不反映对结果集行的列值所作的更改;不显示打开游标后在数据库中新插入的行;组成结果集
3、的行被其他用户更新，新的数据值不会显示在静态游标中;但是静态游标会显示打开游标以后从数据库中删除的行。,概 述,2.游标的分类API服务器游标包含的游标种类：动态游标：与静态游标相反，当滚动游标时动态游标反映结果集中的所有更改。结果集中的行数据值、顺序和成员每次提取时都会改变。只进游标：不支持滚动，它只支持游标从头到尾顺序提取数据行。 注意：只进游标也反映对结果集所做的所有更改。键集驱动游标：同时具有静态游标和动态游标的特点。打开时显示由其他用户更新其成员行的效果，但不显示插入或删除的效果。 键集驱动游标由一套被称为键集的惟一标识符控制。键是以惟一方式在结果集中标识行的列构成。键集是游标打开时
4、来自所有适合select语句的行中的一系列键集。,概述,3 Transaction-SQL游标 该游标是基于Declare Cursor 语法，主要用于Transaction-SQL脚本、存储过程以及触发器中。Transaction-SQL游标在服务器处理由客户端发送到服务器的Transaction-SQL语句。 在存储过程或触发器中使用Transaction-SQL游标的过程为： 声明Transaction-SQL变量包含游标返回的数据。为每个结果集列声明一个变量。声明足够大的变量来保存列返回的值，并声明变量的类型为可从数据类型隐式转换得到的数据类型。,概 述,在存储过程或触发器中使用Tra
5、nsaction-SQL游标的过程为：（1）使用Declare Cursor语句将Transaction-SQL游标与Select语句相关联。还可以利用Declare Cursor定义游标的只读、只进等特性。 （2）使用Open语句执行Select语句填充游标 （3）使用Fetch Into语句提取单个行，并将每列中得数据移至指定的变量中。注意：其他Transaction-SQL语句可以引用那些变量来访问提取的数据值。Transaction-SQL游标不支持提取行块。（4）使用Close语句结束游标的使用。注意：关闭游标以后，该游标还是存在，可以使用Open命令打开继续使用，只有调用Deall
6、ocate语句才会完全释放。 客户端游标:该游标将使用默认结果集把整个结果集高速缓存在客户端上，所有的游标操作都在客户端的高速缓存中进行。注意：客户端游标只支持只进和静态游标。不支持其他游标。,概 述,4.游标的生命周期 游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放游标。声明游标:是为游标指定获取数据时所使用的Select语句，声明游标并不会检索任何数据，它只是为游标指明了相应的Select 语句。 Declare 游标名称 Cursor 参数5.游标的参数Local与Global: Local表示游标的作用仅仅限于其所在的存储过程、触发器以及批处理中、执行完
7、毕以后游标自动释放。Global表示的是该游标作用域是整个会话层。由连接执行的任何存储过程、批处理等都可以引用该游标名称，仅在断开连接时隐性释放。Forward_only与Scroll:前者表示为只进游标，后者表示为可以随意定位。默认为前者。,概 述,5.游标的参数Static: 表示定义一个游标，其数据存放到一个临时表内，对游标的所有请求都从临时表中应答，因此，对该游标进行提取操作时返回的数据不反映对基表所作的修改，并且该游标不允许修改。Keyset:当游标打开时，键集驱动游标中行的身份与顺序是固定的，并把其放到临时表中。Dynamic:表示的是滚动游标时，动态游标反映对结果集内所有数据的更
8、改。Read_only :表示的是只读游标.Scroll_Locks:表示的是在使用的游标结果集数据上放置锁，当行读取到游标中然后对它们进行修改时，数据库将锁定这些行，以保证数据的一致性。Optimistic:其含义是游标将数据读取以后，如果这些数据被更新了，则通过游标定位进行的更新与删除操作将不会成功。,游标的声明,1 标准游标Declare MyCursor Cursor For Select * From goods2 只读游标Declare MyCusror Cursor For Select * From goods For Read Only3 可更新游标Declare MyCus
9、ror Cursor For Select * From goods For UpDate,游标的声明,修改当前游标的数据方法如下:例：将当前游标行的数据中的商品名改为y8848 Declare MyCusror Cursor For Select * From goods For UpDate open MyCusror fetch next from MyCusror UpDate goods Set GoodsName = y8848 Where Current Of MyCursor; 删除当前游标的数据方法如下：例：删除当前游标行的数据Delete From goods Where
10、Current Of MyCursor,游标的打开,打开游标使用Open语句用于打开Transaction-SQL服务器游标，执行Open语句的过程中就是按照Select语句进行填充数据，打开游标以后游标位置在第一行。打开游标：（1）全局游标：Open Global MyCursor（2）局部游标: Open MyCursor,读取游标数据,读取游标数据：在打开游标以后，使用Fetch语句从Transaction-SQL服务器游标中检索特定的一行。使用Fetch操作，可以使游标移动到下一个记录，并将游标返回的每个列的数据分别赋值给声明的本地变量。Fetch Next | Prior | Fir
11、st | Last | Absoluten| Relativen From MyCursor Into GoodsID,GoodsNameNext：表示返回结果集中当前行的下一行记录，如果第一次读取则返回第一行。默认的读取选项为NextPrior：表示返回结果集中当前行的前一行记录，如果第一次读取则没有行返回，并且把游标置于第一行之前。First：表示返回结果集中的第一行，并且将其作为当前行。Last：表示返回结果集中的最后一行，并且将其作为当前行。Absoluten：如果n为正数，则返回从游标头开始的第n行，并且返回行变成新的当前行。如果n为负，则返回从游标末尾开始的第n行，并且返回行为新的
12、当前行，如果n为0，则返回当前行。Relativen：如果n为正数，则返回从当前行开始的第n行，如果n为负,则返回从当前行之前的第n行，如果为0，则返回当前行。,关闭和释放游标,1 关闭游标调用的是Close语句，方式如下：（1）Close Global MyCursor （2）Close MyCursor2 释放游标调用的是Deallocate语句，方法如下： （1）Deallocate Glboal MyCursor （2）Deallocate MyCurso,游标实例,例1 ：声明一个游标，结果集为xuesheng表中的所有记录，然后打开此游标，并读取最后一行数据create table
13、 xuesheng (sno char(15),sname char(10),sage int )create table sc (sno char(15),cno char(10),cname char(10)insert into xuesheng values(2009142101,张三,20)insert into sc values(2009142102,01,数据库)declare mycursor scroll cursor for select * from xuesheng open mycursor fetch last from mycursor 例2 ：声明一个游标，结
14、果集为sc表中的所有记录，然后打开此游标，并读取第一行数据 declare mycursor1 scroll cursor for select * from sc open mycursor1 fetch next from mycursor1,游标实例,例3：定义一个游标，输出goods1表中的商品名称和编号，user1表如下： goodsid name quantity 01 牛奶 4000 02 面包 5000 03 苹果 250 04 荔枝 350declare goodsid char(10), goodsname char(10)declare mycursor scroll c
15、ursor for select goodsid ,goodsname from goods1 order by goodsid open mycursor fetch next from mycursor into goodsid ,goodsname while(fetch_status=0) -循环取出结果集中的数据 begin begin select goodsid=convert(char(10),goodsid) select goodsname=convert(char(10),goodsname) print goodsid+goodsname end fetch next
16、from mycursor into goodsid,goodsname end,游标实例,游标函数：1 . cursor_rows:返回连接上最后打开的游标中当前存在的行数量， cursor_rows函数的返回值如下：-m :游标被异步填充，返回值是键集中当前的行数。-1：游标为动态，因为动态游标可反映所有更改，所以符合游标的函 数不断变化，永远不能确定地说所有符合条件的行均已检索到。0：没有被打开的游标，没有符合最后打开的游标的行，或最后打开 的游标已经被关闭或被释放。n: 游标已完全填充。返回值是在游标中的总行数。,游标实例,1 cursor_rows例：请定义一个游标，如果游标没打开，
17、则打开游标；如果已经打开，则输出游标中的总行数（验证cursor_rows函数的使用）declare mycursor scroll cursor for select * from goods if cursor_rows=0 print 没有打开游标 open mycursor if cursor_rows0 print cursor_rows,游标实例,游标函数：2. cursor_statusCURSOR_STATUS( local , cursor_name | global , cursor_name | variable , cursor_variable ) local：指定一
18、个常量，该常量表明游标的源是一个本地游标名。cursor_name：游标名。游标名必须符合标识符的规则。global：指定一个常量，该常量表明游标的源是一个全局游标名。variable：指定一个常量，该常量表明游标的源是一个本地变量。cursor_variable：游标变量的名称。必须使用 cursor 数据类型定义游标变量。,游标实例,游标函数：2. cursor_status 返回指定游标的状态，cursor_status函数返回值如下：1 ：游标的结果集中至少有一行。0 ：游标的结果集为空。-1：游标被关闭。-2 ：游标不可用。-3 ：游标名称不存在。,游标实例,2 CURSOR_STA
19、TUS函数为一个标量函数，该函数用于确定是否返回游标和结果集。语法：(local, cursor_name |global, cursor_name |variable, cursor_variable ) local：一个常量，此常量表明游标的源是一个本地游标名称。 cursor_name：游标名称，游标名称必须是合法的标识符。 global：一个常量，此常量表明游标的源是一个全局游标名称。 variable：一个常量，此常量表明游标的源是一个本地变量。 cursor_variable：游标变量名称，此名称必须使用cursor数据类型定义游标变量。说明：动态游标不会返回如上结果。,游标实例,
20、2. cursor_status例：declare mycursor1 scroll cursor for select * from user1 print cursor_status(global,mycursor1) open mycursor1 print cursor_status(global,mycursor1),游标实例,游标函数：3 fetch_status：返回被fetch语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。该函数的返回值如下： 0: fetch语句成功； -1：fetch语句失败或此行不在结果集中； -2：被提取的行不存在。,游标实例,例：通过
21、使用fetch_status 变量输出游标中的所有记录。 declare mycursor scroll cursor for select * from user1 open mycursor fetch next from mycursor while fetch_status =0 fetch next from mycursor,游标实例,例:查询电子商务系学生信息,性别为“女”输出为female,否则输出为male.create table student(sno char(15), sname char(10),ssex char(10) ,sdept char(10)insert
22、 into student values(2009142101 , 张三 , 男 , ec)declare c1 cursor for select sno, sname , ssex from student where sdept=ecdeclare sno char(10),sname char(10),ssex char(5)Open c1Fetch c1 into sno, sname, ssexWhile fetch_status=0Beginif ssex=女begin set ssex=female endelse begin set ssex=male endSelect sno, sname ,ssexFetch c1 into sno,sname,ssexend,游标实例,例：更新游标 declare my_cur cursor scroll dynamic for select goodsname , goodsid from user1 For update open my_cur fetch next from my_cur while (fetch_status=0) begin update user1 set goodsname= j2 where current of my_cur fetch next from my_cur End,
