第10章游标操作和应用.ppt

1、第10章 游标操作和应用,游标是数据库中一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制 。,2018年7月6日,1,第10章 游标操作和应用,10.1 游标声明 10.2 游标数据操作 10.3 游标应用实例分析,2018年7月6日,2,10.1 游标声明,10.1.1 游标声明（1）1DECLARE CURSOR命令(SQL-92标准) 语法格式： DECLARE INSENSITIVESCROLLCURSOR FOR FORREAD ONLY|UPDATEOF ,2018年7月6日,

2、3,10.1 游标声明,10.1.1 游标声明（1） 参数说明： （1）INSENSITIVE指出所声明的游标为不敏感游标，即静态游标。当省略INSENSITIVE选项时，已提交的游标基表修改和删除操作能够反映到其后的游标提取结果中。 （2）SCROLL指出该游标可以用FETCH命令里定义的所有方法来存取数据，允许删除和更新（假定没有使用INSENSITIVE选项）； （3）语句决定游标结果集合，但在其中不能使用COMPUTE、COMPUTE BY、FOR BROWSE和INTO等关键字。 （4）FOR READ ONLY或FOR UPDATE说明游标为只读的或可修改的。默认是可修改的。 （5

3、）UPDATE OF , 定义可以修改的列。如果省略 OF ,，则允许修改所有列。,2018年7月6日,4,10.1.1 游标声明,10.1.1 游标声明（1）【例10-1】下面语句声明游标CSstudent_cur1： 【例10-1】下面的语句声明游标“学生表_cur1”。DECLARE 学生表_cur1 SCROLL CURSORFOR SELECT 学号,姓名,性别,籍贯,所在院系,累计学分 FROM 学生表 WHERE 专业=计算机,2018年7月6日,10.1 游标声明,10.1.1 游标声明（2）2DECLARE CURSOR命令(Transact-SQL) 语法格式： DECLA

4、RE CURSOR LOCAL|GLOBAL FORWARD_ONLY|SCROLL STATIC|KEYSET|DYNAMIC|FAST_FORWAR READ_ONLY|SCROLL_LOCKS|OPTIMISTIC TYPE_WARNING FOR FOR UPDATEOF ,2018年7月6日,6,10.1 游标声明,10.1.1 游标声明（2）参数说明： （1）LOCAL和GLOBAL选项分别说明DECLARE CURSOR语句所声明的游标为局部游标和全局游标。 （2）FORWARD_ONLY选项声明只进游标，即FETCH语句中只能使用NEXT选项。 （3）STATIC与SQL-92

5、声明中的INSENTIVE关键字的功能相同，它将游标声明为静态游标，禁止应用程序通过它修改基表数据。 （4）DYNAMIC将游标声明为动态游标，也就是说，其结果结合是动态变化的，能够随时反映用户已提交的更改结果。提取动态游标数据时，不能使用ABSOLUTE提取选项定位游标指针。 （5）KEYSET关键字声明键集驱动游标，键集游标中的数据行及其顺序是固定的。,2018年7月6日,10.1 游标声明,10.1.1 游标声明（2）参数说明： （1）LOCAL和GLOBAL选项分别说明DECLARE CURSOR语句所声明的游标为局部游标和全局游标。 （2）FORWARD_ONLY选项声明只进游标，即

6、FETCH语句中只能使用NEXT选项。 （3）STATIC与SQL-92声明中的INSENTIVE关键字的功能相同，它将游标声明为静态游标，禁止应用程序通过它修改基表数据。 （4）DYNAMIC将游标声明为动态游标，也就是说，其结果结合是动态变化的，能够随时反映用户已提交的更改结果。提取动态游标数据时，不能使用ABSOLUTE提取选项定位游标指针。 （5）KEYSET关键字声明键集驱动游标，键集游标中的数据行及其顺序是固定的。,2018年7月6日,10.1 游标申明,10.1.1 游标申明（2）（6）FAST_FORWARD指出起用优化的FORWARD_ONLY和READ_ONLY游标。 （7

7、）SCROLL_LOCKS选项要求SQL Server在将数据读入游标的同时锁定基表中的数据行，以确保以后能够通过游标成功地对基表进行定位删除和修改操作。 （8）OPTIMISTIC说明在填充游标时不锁定基表中的数据行。 （9）TYPE_WARNING指出在声明游标过程中，如果无法建立用户指定类型的游标而隐式转换为另一类型时，给客户端发出警告消息。 （10）FOR UPDATEOF , 定义游标内可更新的列。如果提供了 OF ,，则只允许修改列出的列。如果在 UPDATE 中未指定列的列表，除非指定了 READ_ONLY 并发选项，否则所有列均可更新。,2018年7月6日,10.1 游标声明,

8、10.1.1 游标声明（2）【例10-2】下面的语句声明游标学生表_cur2。DECLARE 学生表_cur2 CURSORLOCAL SCROLL DYNAMICTYPE_WARNINGFOR SELECT 学号,姓名,性别,籍贯,所在院系,累计学分 FROM 学生表 WHERE 专业=计算机,2018年7月6日,10.1 游标声明,10.1.1 游标声明（2）3查看游标信息和状态在Transact_SQL程序中，执行以下系统存储过程和函数能够检索游标属性信息和状态信息：sp_cursor_list：检索当前连接所有可见游标sp_describe_cursor：检索游标属性信息，如作用域、名

9、称、类型、状态和行数。sp_describe_cursor_columns：检索游标结果集合中的列属性。sp_describe_cursor_tables：检索游标锁引用的基表信息。,2018年7月6日,CURSOR_STATUS：读取游标状态或检查游标变量是否与游标相关联。FETCH_STATUS：读取最后一次游标数据提取操作结果状态。0：取操作成功；-1：取操作失败，所指定的位置超出了范围；-2：要取的行不在记录集内，已从集合中删除。 CURSOR_ROWS：显示游标集合中的行数。-n：正在向游标中载入数据，反映的是结果集当前的数据行数；n：结果集合的行数；0：结果集中没有匹配的行；-1：

10、指出游标是动态的。,12,2018年7月6日,1.1.2 游标变量,游标变量声明的格式：DECLARE cursor_variable_name CURSOR游标变量声明后，必须和某个游标相关联才能实现游标操作。有两种方法建立游标和游标变量之间的关联：,13,2018年7月6日,1.1.2 游标变量,第一种： 先声明游标和游标变量，之后用SET语句将游标赋给游标变量。例如： DECLARE cur_var CURSOR DECLARE C1 CURSOR FOR SELECT * FROM COURSE SET cur_var=C1,2018年7月6日,1.1.2 游标变量,第二种：不声明游标

11、，直接在SET语句中将各种游标定义赋给游标变量例如： DECLARE cur_var CURSOR SET cur_var= CURSOR FOR SELECT * FROM COURSE当游标变量与游标相关联之后，在Transact-SQL游标语句中就可以使用游标变量代替游标名，实现各种操作。 例如： OPEN cur_var,2018年7月6日,10.2 游标数据操作,10.2.1 打开游标打开游标在声明以后，如果要从游标中读取数据必须打开游标。打开一个Transact-SQL服务器游标使用OPEN命令。语法格式：OPEN GLOBAL | 参数说明：（1）GLOBAL 定义游标为一全局游

12、标。（2）游标名 如果一个全局游标和一个局部游标都使用同一个游标名，则如果使用GLOBAL便表明其为全局游标，否则表明其为局部游标。（3）游标变量 为定义的游标变量。当打开一个游标后时，MS SQL SERVER首先检查声明游标的语法是否正确，如果游标声明中有变量，则将变量值带入。,16,2018年7月6日,10.2.1 打开游标,在游标被成功打开之后，CURSOR_ROWS 全局变量将用来记录游标内数据行数。为了提高性能，MS SQL SERVER允许以异步方式从基础表向KEYSET或静态游标读入数据，即如果MS SQL SERVER的查询优化器估计从基础表中返回给游标的数据行已经超过sp_

13、configure cursor threshold参数值，则MS SQL SERVER将启动另外一个独立的线程来继续从基础表中读入符合游标定义的数据行，此时可以从游标中读取数据进行处理而不必等到所有的符合游标定义的数据行都从基础表中读入游标。CURSOR_ROWS 变量存储的正是在调用CURSOR_ROWS时，游标已从基础表读入的数据行。CURSOR_ROWS的返回值有以下四个，如表10-1所示。,2018年7月6日,表10-1 CURSOR_ROWS变量,18,2018年7月6日,10.2.2 读取游标数据,当游标被成功打开以后，就可以从游标中逐行地读取数据，以进行相关处理。从游标中读取数

14、据主要使用FETCH 命令。格式： FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTEn|nvar | RELATIVE n |nvar FROM GLOBAL |cursor_variable_name INTO , ,2018年7月6日,参数说明：（1）NEXT说明如果是在OPEN后第一次执行FETCH命令，则返回结果集的第一行，否则使游标（指针）指向结果集的下一行；NEXT是默认的选项，也是最常用的一种方法（2）PRIOR、FIRST、LAST、ABSOLUTE n|nvar、RELATIVE n|nvar等各项，只有在定义游标时使用了SCROLL选项才可以使用；

15、PRIOR是返回结果集当前行的前一行；如果FETCH PRIOR是第一次读取游标中数据，则无数据记录返回，并把游标位置设为第一行。 FIRST是返回结果集的第一行； LAST是返回结果集的最后一行；,20,2018年7月6日,ABSOLUTE n | nvar 如果n或nvar为正数，则返回游标结果集中的第n或nvar行数据。如果n或nvar 为负数，则返回结果集内倒数第n或nvar 行数据。若n或nvar超过游标的数据子集范畴，则FETCH_STARS返回-1，在该情况下，如果n或nvar为负数，则执行FETCH NEXT命令会得到第一行数据，如果n或nvar为正值，执行FETCH PRIO

16、R命令则会得到最后一行数据。n或nvar 可以是一固定值也可以是一smallint, tinyint 或int类型的变量。,21,2018年7月6日,RELATIVE n | nvar 若n或nvar为正数，则读取游标当前位置起向后的第n或nvar行数据；如果n或nvar为负数，则读取游标当前位置起向前的第n或nvar行数据。若n或nvar超过游标的数据子集范畴，则FETCH_STARS 返回-1，在该情况下，如果n或nvar为负数，则执行FETCH NEXT命令则会得到第一行数据；如果n或nvar为正值，执行FETCH PRIOR命令则会得到最后一行数据。n 或nvar可以是一固定值也可以是

17、一smallint, tinyint或int类型的变量。,22,2018年7月6日,23,表10-2 FETCH_STATUS变量,2018年7月6日,10.2.3 关闭游标,在处理完游标中数据之后必须关闭游标来释放数据结果集和定位于数据记录上的锁，有两种方法关闭游标。1使用CLOSE命令关闭游标格式：CLOSE GLOBAL 游标名 | 游标变量 说明：CLOSE语句关闭游标，但不释放游标占用的数据结构，应用程序可以再次执行OPEN语句打开和填充游标。2自动关闭游标 游标可应用在存储过程、触发器和Transact_SQL脚本中。如果在声明游标与释放游标之间使用了事务结构，则在结束事务时游标会

18、自动关闭。,2018年7月6日,例如以下执行过程：（1）声明一个游标（2）打开游标（3）读取游标（4）BEGIN TRANSATION（5）数据处理（6）COMMIT TRANSATION（6）回到步骤(3),25,2018年7月6日,10.2.4 释放游标当CLOSE命令关闭游标时，并没有释放游标占用的数据结构。因此要使用DEALLOCATE命令，删除掉游标与游标名或游标变量之间的联系，并且释放游标占用的所有系统资源。语法格式：DEALLOCATE GLOBAL 游标名 | 游标变量,26,2018年7月6日,27,参数说明：（1）对游标进行操作的语句使用游标名称或游标变量引用游标。DEAL

19、LOCATE删除游标与游标名称或游标变量之间的关联。如果一个名称或变量是最后引用游标的名称或变量，则将释放游标，游标使用的任何资源也随之释放。（2）DEALLOCATE游标变量语句只删除对游标命名变量的引用。直到批处理、存储过程或触发器结束时变量离开作用域，才释放变量。在DEALLOCATE cursor_variable_name 语句之后，可以使用SET语句使变量与另一个游标关联。,2018年7月6日,例如：USE 教学管理GODECLARE MyCursor CURSORSET MyCursor = CURSOR LOCAL SCROLL FORSELECT * FROM STUDENT

20、-下面语句释放游标及游标占用的资源DEALLOCATE MyCursor-用SET命令将游标变量MyCursor同另一个游标关联SET MyCursor = CURSOR LOCAL SCROLL FORSELECT * FROM COURSEGO,28,2018年7月6日,10.2.5 游标定位修改和删除操作,如果在声明游标时使用了FOR UPDATE语句，那么就可以在UPDATE或DELETE命令中以WHERE CURRENT OF关键字直接修改或删除当前游标中当前行的数据。当改变游标中数据时，这种变化会自动地影响到游标的基础表。但是如果在声明游标时选择了INSENSITIVE选项时，该游

21、标中的数据不能被修改，具体含义请参看声明游标一节中对INSENSITIVE选项的详细解释。,29,2018年7月6日,10.2.5 游标定位修改和删除操作,语法格式：游标定位修改UPDATE语句的格式如下UPDATE 表名 SET 子句 WHERE CURRENT OF GLOBAL 游标名 |游标变量游标定位删除DELETE语句的格式为如下DELETE FROM 表名 WHERE CURRENT OF GLOBAL 游标名 |游标变量,2018年7月6日,10.3 游标应用实例分析,【例10-6】使用游标逐个检查并修改每个开课号在选课表中的学生选修人数，并显示输出。SET NOCOUNT O

22、NDECLARE 开课号 CHAR(6), 限选人数 INT, 已选人数 INTDECLARE message CHAR(80)-创建包含开课号、限选人数和已选人数信息的游标CUR_选课人数。DECLARE CUR_选课人数 cursor FOR SELECT 开课号,限选人数FROM 开课表 FOR UPDATE OF 已选人数-打开游标，根据开课表依次提取每个开课计划的数据。OPEN CUR_选课人数FETCH NEXT FROM CUR_选课人数 INTO 开课号,限选人数WHILE fetch_status=0,31,2018年7月6日,10.3 游标应用实例分析,【例10-6】续BE

23、GIN-显示开课号、限选人数。SELECT message=开课号+开课号+ +限选人数+ CONVERT(CHAR(2), 限选人数) PRINT message-在选课表中计算该开课号的选课人数。SELECT 已选人数=COUNT(*) FROM 选课表 WHERE 开课号=开课号PRINT 已选人数+ +CONVERT(CHAR(2), 已选人数)-返回到游标CUR_选课人数，根据计算出的累计学分修改该生的原累计学分。,2018年7月6日,10.3 游标应用实例分析,例10-6】续IF 已选人数=限选人数UPDATE 开课表SET 已选人数=已选人数WHERE CURRENT OF CU

24、R_选课人数ELSEPRINT 这个开课计划选择的人数已经超过限选人数请调整！FETCH NEXT FROM CUR_选课人数 INTO 开课号,限选人数ENDCLOSE CUR_选课人数DEALLOCATE CUR_选课人数,2018年7月6日,小 结,游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录，并在应用程序当中进行单个处理。游标通过以下方式对SQL语句结果集进行处理： 允许定位在结果集的特定行。 从结果集的当前位置检索一行或多行。 支持对结果集中当前位置的行进行数据修改。 为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。 提供脚本、存储过程和触发器中使用的访问结果集中的数据的Transact-SQL语句。,34,2018年7月6日,35,Thank you very much!,谢谢您的光临！,下一章,2018年7月6日,