《游标操作和应用》ppt课件

《《游标操作和应用》ppt课件》由会员分享，可在线阅读，更多相关《《游标操作和应用》ppt课件（35页珍藏版）》请在金锄头文库上搜索。

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

2、14日,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说明游标为只读的或可修改的。默认是可修改的

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

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

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

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

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

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

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

10、数据行数；n：结果集合的行数；0：结果集中没有匹配的行；-1：指出游标是动态的。,12,2019年1月14日,1.1.2 游标变量,游标变量声明的格式： DECLARE cursor_variable_name CURSOR 游标变量声明后，必须和某个游标相关联才能实现游标操作。 有两种方法建立游标和游标变量之间的关联：,13,2019年1月14日,1.1.2 游标变量,第一种： 先声明游标和游标变量，之后用SET语句将游标赋给游标变量。 例如： DECLARE cur_var CURSOR DECLARE C1 CURSOR FOR SELECT * FROM COURSE SET cur_

11、var=C1,2019年1月14日,1.1.2 游标变量,第二种： 不声明游标，直接在SET语句中将各种游标定义赋给游标变量例如： DECLARE cur_var CURSOR SET cur_var= CURSOR FOR SELECT * FROM COURSE 当游标变量与游标相关联之后，在Transact-SQL游标语句中就可以使用游标变量代替游标名，实现各种操作。 例如： OPEN cur_var,2019年1月14日,10.2 游标数据操作,10.2.1 打开游标 打开游标在声明以后，如果要从游标中读取数据必须打开 游标。打开一个Transact-SQL服务器游标使用OPEN命令。

12、 语法格式： OPEN GLOBAL | 参数说明： （1）GLOBAL 定义游标为一全局游标。 （2）游标名 如果一个全局游标和一个局部游标都使用同一个游标名，则如果使用GLOBAL便表明其为全局游标，否则表明其为局部游标。 （3）游标变量 为定义的游标变量。当打开一个游标后时，MS SQL SERVER首先检查声明游标的语法是否正确，如果游标声明中有变量，则将变量值带入。,16,2019年1月14日,10.2.1 打开游标,在游标被成功打开之后，CURSOR_ROWS 全局变量将用来记录游标内数据行数。为了提高性能，MS SQL SERVER允许以异步方式从基础表向KEYSET或静态游标读

13、入数据，即如果MS SQL SERVER的查询优化器估计从基础表中返回给游标的数据行已经超过sp_configure cursor threshold参数值，则MS SQL SERVER将启动另外一个独立的线程来继续从基础表中读入符合游标定义的数据行，此时可以从游标中读取数据进行处理而不必等到所有的符合游标定义的数据行都从基础表中读入游标。CURSOR_ROWS 变量存储的正是在调用CURSOR_ROWS时，游标已从基础表读入的数据行。CURSOR_ROWS的返回值有以下四个，如表10-1所示。,2019年1月14日,表10-1 CURSOR_ROWS变量,18,2019年1月14日,10.2

14、.2 读取游标数据,当游标被成功打开以后，就可以从游标中逐行地读取数据，以进行相关处理。从游标中读取数据主要使用FETCH 命令。 格式： FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTEn|nvar | RELATIVE n |nvar FROM GLOBAL |cursor_variable_name INTO , ,2019年1月14日,参数说明： （1）NEXT说明如果是在OPEN后第一次执行FETCH命令，则返回结果集的第一行，否则使游标（指针）指向结果集的下一行；NEXT是默认的选项，也是最常用的一种方法 （2）PRIOR、FIRST、LAST、ABSOLU

15、TE n|nvar、RELATIVE n|nvar等各项，只有在定义游标时使用了SCROLL选项才可以使用； PRIOR是返回结果集当前行的前一行；如果FETCH PRIOR是第一次读取游标中数据，则无数据记录返回，并把游标位置设为第一行。 FIRST是返回结果集的第一行； LAST是返回结果集的最后一行；,20,2019年1月14日,ABSOLUTE n | nvar 如果n或nvar为正数，则返回游标结果集中的第n或nvar行数据。如果n或nvar 为负数，则返回结果集内倒数第n或nvar 行数据。若n或nvar超过游标的数据子集范畴，则FETCH_STARS返回-1，在该情况下，如果n或

16、nvar为负数，则执行FETCH NEXT命令会得到第一行数据，如果n或nvar为正值，执行FETCH PRIOR命令则会得到最后一行数据。n或nvar 可以是一固定值也可以是一smallint, tinyint 或int类型的变量。,21,2019年1月14日,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 或nv