SQL-Server-2012数据库技术与应用11.pptx

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2017/8/29 Tuesday,#,第,11,章 游标,SQL Server,2012,数,据库技术与应用,目录,11.1,游标的概念,11.2,用,DECLARE,语句定义,游标,11.3,用,OPEN,语句打开,游标,11.4,用,FETCH,语句从游标中提取,数据,11.5,用,CLOSE,语句关闭,游标,11.6,用,DEALLOCATE,语句释放,游标,11.1,游标的概念,游标的主要用途就是在,T-SQL,脚本程序、存储过程、触发器中对,SELECT,语句返回的结果集进行逐行逐字段处理，把一个完整的数据表按行分开，一行一行地逐一提取记录，并从这一记录行中逐一提取各项数据。,游标与变量类似，必须先定义后使用。,游标的使用过程是：定义声明游标 打开游标 从游标中提取记录并分离数据 关闭游标释放游标。,11.2,用,DECLARE,语句定义游标,基于,SQL-92,标准的,DECLARE,语句，,语法格式,如下,：,DECLARE,游标名,INSENSITIVE SCROLL CURSOR,FOR SELECT,语句,FOR READ ONLY|UPDATA OF,字段名,n ,11.2,用,DECLARE,语句定义游标,INSENSITIVE,表示定义游标时自动在系统的,tempdb,数据库中创建一个临时表来存储游标使用的数据，在游标使用过程中基表数据改变时不会影响游标使用的数据，但该游标的数据不允许修改。省略该项表示游标直接从基表中取得数据，即游标使用的数据将随基表数据的变化而动态变化。,SCROLL,表示该游标可以在,FETCH,语句中任意指定数据的提取方式，省略该项表示该游标仅支持,NEXT,顺序提取方式。,11.2,用,DECLARE,语句定义游标,SELECT,语句指定该游标使用的结果集，但不允许使用,COMPUTE,或,INTO,子句。,READ,ONLY,表示只读，该游标中的数据不允许修改，即不允许在,UPDATE,或,DELETE,语句中引用该游标。,UPDATA,OF,字段名,，,n,表示在该游标内可以更新基本表的指定字段，省略字段名列表表示可以更新所有字段。,11.2,用,DECLARE,语句定义游标,T-SQL,的,DECLARE,语法格式,如下：,DECLARE,游标名,CURSOR,FORWARD_ONLY|SCROLL STATIC|KEYSET|DYNAMIC|FAST_FORWARD,READ_ONLY|OPTIMISTIC TYPE_WARNING,FOR SELECT,语句,FOR UPDATE OF,字段名,n ,11.2,用,DECLARE,语句定义游标,说明,如下,:,FORWARD_ONLY,指定该游标为顺序结果集，只能用,NEXT,向后方式顺序提取记录。,SCROLL,指定该游标为滚动结果集，可以使用向前、向后、定位方式提取记录。,STATIC,与,INSENSITIVE,含义相同，在系统,tempdb,数据库中创建临时表存储游标使用的数据，即游标不会随基本表内容而变化，同时也无法通过游标来更新基本表。,11.2,用,DECLARE,语句定义游标,KEYSET,指定游标中列的顺序是固定的，并且在,tempdb,内建立一个,KEYSET,表，基本表数据修改时能反映到游标中。如果基本表添加符合游标的新记录，则该游标无法读取（但其他语句使用,WHERE CURRENT OF,子句可对游标中新添加的记录数据进行修改）。如果游标中的一行被删除掉，则用游标提取时,FETCH_STATUS,的返回值为,-2,。,DYNAMIC,指定游标中的数据将随基本表而变化，但需要大量的游标资源。,FAST_FORWARD,指定启用了性能优化的,FORWARD_ONLY,、,READ_ONLY,游标。如果指定,FAST_FORWARD,，则不能也指定,SCROLL,或,FOR_UPDATE,。,FAST_FORWARD,和,FORWARD_ONLY,是互斥的；如果指定一个，则不能指定另一个。,11.2,用,DECLARE,语句定义游标,Read_Only,指不能通过游标对数据进行删改。,OPTIMISTIC,指明若游标中的数据已发生变化，则对游标数据进行更新或删除时可能会导致失败。,TYPE_WARNING,指定若游标中的数据类型被修改成其他类型时，给客户端发送警告。,若,省略,FORWARD_ONLY|SCROLL,，则不使用,STATIC,、,KEYSET,和,DYNAMIC,时默认为,FORWARD_ONLY,游标，使用,STATIC,、,KEYSET,或,DYNAMIC,之一则默认为,SCROLL,游标,。,若省略,READ_ONLY|OPTIMISTIC,参数，则默认选项如下。,如果,未使用,UPDATE,参数不支持更新，则游标为,READ_ONLY,。,11.2,用,DECLARE,语句定义游标,STATIC,和,FAST_FORWARD,类型游标默认为,READ_ONLY,。,DYNAMIC,和,KEYSET,类型游标默认为,OPTIMISTIC,。,注意：,不能,将,SQL-92,游标语法与,MS SQL Server,游标的扩展语法混合使用。,若,在,CURSOR,前使用了,SCROLL,或,INSENSITIVE,，则为,SQL-92,游标语法，不能再在,CURSOR,和,FOR SELECT,语句之间使用任何保留字，反之同理,。,11.2,用,DECLARE,语句定义游标,【,任务,11.1】,创建一个名为,Cur_Buyer,的标准游标，如图,11-1,所示。,11.2,用,DECLARE,语句定义游标,【,任务,11.2】,创建一个名为,Cur_Buyer_01,的只读游标，如图,11.2,所示。,11.2,用,DECLARE,语句定义游标,【,任务,11.3】,创建一个名为,Cur_Buyer_02,的更新游标，如图,11.3,所示。,11.3,用,OPEN,语句打开游标,语法格式,如下,:,OPEN GLOBAL,游标名,11.3,用,OPEN,语句打开游标,【,任务,11.4】,首先声明一个名为,Buyer_01,的游标，然后使用,OPEN,命令打开该游标，如图,11.4,所示。,11.3,用,OPEN,语句打开游标,【,任务,11.5】,使用游标查看数据库,Manage,中的客户资料表,Buyers,中记录的个数。在查询分析器中编写以下代码。,11.4,用,FETCH,语句从游标中提取数据,语法格式,如下,:,FETCH next|prior|first|last|absolute n|nvar|relative n|nvar,FROM GLOBAL,游标名,INTO ,变量名,n ,11.4,用,FETCH,语句从游标中提取数据,在,游标内有一个游标指针,CURSOR,指向游标结果集的某个记录行,称为当前行，游标刚打开时,CURSOR,指向游标结果集第一行之前。,FETCH,之后的参数为提取记录的方式，可以是以下方式之一。,NEXT,顺序向下提取当前记录行的下一行，并将其作为当前行。第一次对游标操作时取第一行为当前行，处理完最后一行，再用,FETCH,NEXT,，则,CURSOR,指向结果集最后一行之后，,FETCH_STATUS,的值为,-1,。,11.4,用,FETCH,语句从游标中提取数据,PRIOR,顺序向前提取当前记录的前一行，并将其作为当前行。第一次用,FETCH PRIOR,对游标操作时，没有记录返回，游标指针,CURSOR,仍指向第一行之前。,FIRST,提取游标结果集的第一条记录，并将其作为当前行。,LAST,提取游标结果集的最后一条记录，并将其作为当前行。,ABSOLUTE,n|nvar,按绝对位置提取游标结果集的第,n,或第,nvar,条记录，并将其作为当前行。若,n,或,nvar,为负值，则提取结尾之前的倒数第,n,或第,nvar,条记录。,n,为整数，,nvar,为整数类型变量。,11.4,用,FETCH,语句从游标中提取数据,RELATIVE n|nvar,按相对位置提取当前记录之后（正值）或之前（负值）的第,n,或第,nvar,条记录，并将其作为当前行。,FROM,指定提取记录的游标，,global,用于指定全局游标，省略为局部游标。,INTO,指定将提取记录中的字段数据存入对应的局部变量中。变量名列表的个数、类型必须与结果集中记录的字段的个数、类型相匹配。,11.4,用,FETCH,语句从游标中提取数据,【,任务,11.6】,用,FETCH_STATUS,控制一个,WHILE,循环中的游标活动，如图,11.6,所示。,11.5,用,CLOSE,语句关闭游标,语法格式,如下,:,CLOSE GLOBAL,游标名,11.5,用,CLOSE,语句关闭游标,语句功能：释放游标中的结果集，解除游标记录行上的游标指针。,当游标提取记录完毕后，应及时关闭该游标释放结果集的内存空间。游标关闭后，其定义结构仍然存储在系统中，但不能提取记录和定位更新，需要时可用,OPEN,语句再次打开。,注意：关闭只有定义而没有打开的游标会产生语法错误。,11.5,用,CLOSE,语句关闭游标,【,任务,11.7】,声明一个名为,CloseCursor,的游标，并使用,Close,语句关闭游标，如图,11.7,所示。,11.6,用,DEALLOCATE,语句释放游标,语法格式,如下,:,DEALLOCATE GLOBAL,游标名,11.6,用,DEALLOCATE,语句释放游标,语句功能：删除指定的游标，释放该游标所占用的所有系统,资源。,最后，总结一下使用游标访问数据的步骤。,（,1,）用,DECLARE CURSOR,语句声明游标。,（,2,）用,OPEN,语句打开游标。,（,3,）用,FETCH,语句从游标中提取记录。,（,4,）用,CLOSE,语句关闭游标。,（,5,）用,DEALLOCATE,语句释放游标。,11.6,用,DEALLOCATE,语句释放游标,【,任务,11.8】,使用,DEALLOCATE,命令释放名为,FreeCursor,的游标，如图,11.8,所示。,11.6,用,DEALLOCATE,语句释放游标,【,任务,11.9】,使用游标逐条查看数据库,Manage,中订单信息表,Sales,中的记录。,11.6,用,DEALLOCATE,语句释放游标,【,任务,11.10】,使用游标对库存量小于,20,的商品按库存量多少显示不同的进货提示信息。,谢 谢！,