SQL第章游标及事务ppt课件.ppt

第13章游标及事务 13 1游标的定义及优点13 2游标的使用13 3使用游标修改数据13 4事务的使用 返回目录 2 13 1游标的定义及其优点 前面介绍的数据检索方法可以得到数据库中有关表的数据 但这些数据是作为一个结果集得到的 用户可以把这个结果集保存到一个文件里 或生成一个新表以便于以后使用 这种查询是非常重要的 但这种查询形式有一个很大的缺点 它不能对结果集中每一行的数据进行处理 使用游标可以实现对查询结果集中的数据逐行处理 13 1 1游标的概念游标 Cursor 是一种处理数据的方法 为了查看或者处理结果集中的数据 游标提供了在结果集中向前或者向后浏览数据的能力 可以把游标看成一种指针 它既可以指向当前位置 也可以指向结果集中的任意位置 它允许用户对指定位置的数据进行处理 可以把结果集中的数据放在数组 应用程序中或其它地方 3 13 1 2使用游标的优点从游标定义可以得知游标具有如下的优点 允许程序对由查询语句SELECT返回的行集合中的每一行数据执行相同或不同的操作 而不是对整个行集合执行同一个操作提供对基于游标位置的表中的行进行删除和更新的能力游标实际上作为面向集合的数据库管理系统 RDBMS 和面向行的程序设计之间的桥梁 使这两种处理方式通过游标沟通起来 本章首页 4 13 2游标的使用 13 2 1使用游标的步骤使用游标的工作流程如下图13 1所示 具体地说 有如下几个步骤 创建游标 使用T SQL语句生成一个结果集 并且定义游标的特征 如游标中的记录是否可以修改 打开游标 从游标的结果集中读取数据 从游标中检索一行或多行数据称为取数据 对游标中的数据逐行操作 关闭和释放游标 5 13 2 2游标的定义及使用过程1 声明游标声明游标是指用DECLARE语句声明或创建一个游标 声明游标的语法如下 DECLAREcursor name SCROLL CURSORFORselect statement FOR READONLY UPDATE OFcolumn name list 其中 cursor name 是游标的名字 为一个合法的SQLServer标识符 游标的名字必须遵循SQLServer命名规范 SCROLL 表示取游标时可以使用关键字NEXT PRIOR FIRST LAST ABSOLUTE RELATIVE 每个关键字的含义将在介绍FETCH子句时讲解 select statement 是定义游标结果集的标准SELECT语句 它可以是一个完整语法和语义的Transact SQL的SELECT语句 6 但是这个SELECT语句必须有FROM子句 不允许使用关键字COMPUTE COMPUTEBY FORBROWSE和INTO FORREADONLY 指出该游标结果集只能读 不能修改 FORUPDATE 指出该游标结果集可以被修改 OFcolumn name list 列出可以被修改的列的名单 应该注意 游标有且只有两种方式 FORREADONLY或FORUPDATE 当游标方式指定为FORREADONLY时 游标涉及的表不能被修改 当游标方式指定为FORUPDATE时 可以删除或更新游标涉及的表中的行 通常 这也是缺省方式 声明游标的DECLARECURSOR语句必须是在该游标的任何OPEN语句之前 7 2 打开游标打开游标是指打开已被声明但尚未被打开的游标 打开游标使用OPEN语句 打开游标的语法如下 OPENcursor name其中 cursor name是一个已声明的尚未打开的游标名 注意 当游标打开成功时 游标位置指向结果集的第一行之前 只能打开已经声明但尚未打开的游标 8 3 从打开的游标中提取行游标被打开后 游标位置位于结果集的第一行前 此时可以从结果集中提取 FETCH 行 SQLServer将沿着游标结果集一行或多行向下移动游标位置 不断提取结果集中的数据 并修改和保存游标当前的位置 直到结果集中的行全部被提取 从打开的游标中提取行的语法如下 FETCH NEXT PRIOR FIRST LAST ABSOLUTE RELATIVE FROM cursor name INTOfetch target list 其中 cursor name 为一已声明并已打开的游标名字 NEXT PRIOR FIRST LAST ABSOLUTE RELATIVE 游标移动方向 缺省情况下是NEXT 即向下移动 NEXT 取下一行数据 PRIOR 取前一行数据 FIRST 取第一行数据 9 LAST 取最后一行数据 ABSOLUTE 按绝对位置取数据 RELATIVE 按相对位置取数据 游标位置确定了结果集中哪一行可以被提取 如果游标方式为FORUPDATE的话 也就确定该位置一行数据可以被更新或删除 INTOfetch target list 指定存放被提取的列数据的目的变量清单 这个清单中变量的个数 数据类型 顺序必须与定义该游标的select statement的SELECT list中列出的列清单相匹配 为了更灵活地操纵数据 可以把从已声明并已打开的游标结果集中提取的列数据 分别存放在目的变量中 10 有两个全局变量提供关于游标活动的信息 FETCH STATUS保存着最后FETCH语句执行后的状态信息 其值和含义如下 0 表示成功完成FETCH语句 1 表示FETCH语句执行有错误 或者当前游标位置已在结果集中的最后一行 结果集中不再有数据 2 提取的行不存在 rowcount保存着自游标打开后的第一个FETCH语句 直到最近一次的FETCH语句为止 已从游标结果集中提取的行数 每个打开的游标都与一特定的 rowcount有关 关闭游标时 该 rowcount变量也被删除 在FETCH语句执行后查看这个变量 可得知从游标结果集中已提取的行数 11 4 关闭游标关闭 Close 游标是停止处理定义游标的那个查询 关闭游标并不改变它的定义 可以再次用open语句打开它 SQLServer会用该游标的定义重新创建这个游标的一个结果集 关闭游标的语法如下 CLOSEcursor name其中 cursor name 是已被打开并将要被关闭的游标名字 在如下情况下 SQLServer会自动地关闭已打开的游标 当你退出这个SQLServer会话时从声明游标的存储过程中返回时 12 5 释放游标释放 Deallocate 游标是指释放所有分配给此游标的资源 包括该游标的名字 释放游标的语法是 DEALLOCATECURSORcursor name其中 cursor name 将要被DEALLOCATE释放的游标名字 如果释放一个已打开但未被关闭的游标 SQLServer会自动先关闭这个游标 然后再释放它 注意一下关闭游标与释放游标的区别 关闭游标并不改变游标的定义 一个游标关闭后 不需要再次声明 就可以重新打开并使用它 但一个游标释放后 就释放了与该游标有关的一切资源 也包括游标的声明 游标释放后就不能再使用该游标了 如需再次使用游标 就必须重新定义 13 例13 1 定义一个游标 将教师表teacher中所有教师的教师姓名 教师职称显示出来 USEjwglGODECLARE teach nameVARCHAR 8 teach professionVARCHAR 16 DECLAREteacher coursorSCROLLCURSORFORSELECTteacher name professionFROMteacherFORREADONLYOPENteacher coursorFETCHFROMteacher coursorINTO teach name teach profession 14 WHILE FETCH STATUS 0BEGINPRINT 教师姓名 teach name 职称 teach professionFETCHFROMteacher coursorINTO teach name teach professionENDCLOSEteacher coursorDEALLOCATEteacher coursor 本章首页 15 13 3使用游标修改数据 用户可以在UPDATE或DELETE语句中使用游标来更新 删除表或视图中的行 但不能用来插入新行 13 3 1更新数据通过在UPDATE语句中使用游标可以更新表或视图中的行 被更新的行依赖于游标位置的当前值 更新数据语法形式如下 UPDATE table name view name SET table name view name column name new value n WHERECURRENTOFcursor name其中 紧跟UPDATE之后的table name view name 要更新的表名或视图名 可以加或不加限定 但它必须是声明该游标的SELECT语句中的表名或视图名 16 column name 是要更新的列的列名 可以加或不加限定 但它们必须是声明游标的SELECT语句中UPDATEOFcolumn name list的子集new value 为被更新列的新值 它可以是一个表达式 空值或子查询 WHERECURRENTOF 使SQLServer只更新由指定游标的游标位置当前值确定的行 cursor name 是已声明为FORUPDATE方式并已打开的游标名 17 注意 使用UPDATE CURRENTOF语句一次只能更新当前游标位置确定的那一行 OPEN语句将游标位置定位在结果集第一行前 可以使用FETCH语句把游标位置定位在要被更新的数据行处 用UPDATE WHERECURRENTOF语句更新表中的行时 不会移动游标位置 被更新的行可以再次被修改 直到下一个FETCH语句的执行 UPDATE WHERECURRENTOF语句可以更新多表视图或被连接的多表 但只能更新其中一个表的行 即所有被更新的列都来自同一个表 18 例13 2 通过游标将教师表teacher中记录号为3的章红老师的职称由 讲师 改为 教授 USEjwglGODECLAREteacher coursorSCROLLCURSORFORSELECTteacher name professionFROMteacherFORUPDATEOFprofessionOPENteacher coursorFETCHABSOLUTE3FROMteacher coursorUPDATEteacherSETprofession 教授 WHERECURRENTOFteacher coursorFETCHABSOLUTE3FROMteacher coursorCLOSEteacher coursorDEALLOCATEteacher coursor 19 13 3 2删除数据通过在DELETE语句中使用游标来删除表或视图中的行 被删除的行依赖于游标位置的当前值 删除数据语法形式如下 DELETE FROM database owner table name view name WHERECURRENTOFcursor name其中 table name view name 为要从其中删除行的表名或视图名 可以加或不加限定 但它必须是定义该游标的SELECT语句中的表名或视图名 cursor name 为已声明并已打开的游标名 WHERECURRENTOF 它使SQLServer只删除由指定游标的游标位置当前值确定的行 20 注意 使用游标的DELETE语句 一次只能删除当前游标位置确定的那一行 OPEN语句将游标位置定位在结果集第一行之前 可以用FETCH语句把游标位置定位在要被删除的行处 在DELETE语句中使用的游标必须声明为FORUPDATE方式 而且声明游标的SELECT语句中不能含有连接操作或涉及多表视图 否则即使声明中指明了FORUPDATE方式 也不能删除其中的行 对使用游标删除行的表 要求有一个唯一索引 使用游标的DELETE语句 删除一行后将游标位置向前移动一行 本章首页 21 13 4事务的使用 13 4 1什么是事务多用户并发存取同一数据可能会导致产生数据的不一致性问题 正确地使用事务可以有效控制这类问题发生的频度甚至能避免这类问题的发生 所谓事务 Transaction 是指一个操作序列 这些操作序列要么都被执行 要么都不被执行 它是一个不可分割的工作单元 事务中任何一个语句执行时出错 系统都会返回到事务开始前的状态 事务是并发控制的基本单元 是数据库维护数据一致性的单位 在每个事务结束时 都能保持数据一致性 例如 银行转帐工作 从一个帐号扣款并使另一个帐号增款 这两个操作要么都被执行 要么都不被执行 所以 应该把他们看成一个事务 22 13 4 2事务的特点及事务的管理1 事务的特性事务具有4个属性 原子性 Atomicity 一致性 Consistency 隔离性 1solation 和永久性 Durability 这4个属性简称为ACID特性 原子性原子性是指事务内的操作要么都被执行 要么都不被执行 一致性一致性是指在事务开始前和结束后 所有的数据都必须处于一致性的状态 隔离性隔离性是指一个事务所作的修改必须能够与其他事务所作的修改隔离起来 永久性永久性指如果事务正常结束 则它对数据库所做的修改应是永久地保存 23 2 事务的管理事务控制语句 控制事务执行的语句 包括将一系列操作定义为一个工作单元来处理 锁机制 封锁正被一个事务修改的数据 防止其他用户访问到 不一致 的数据 事务日志 使事务具有可恢复性 为了尽可能避免死锁的出现 应注意 在所有的事务中都按同一顺序来访问各个表 尽可能利用存储过程来完成一个事务 以保证对各表的访问次序都是一致的 事务应该尽量小且应尽快提交 避免人工输入操作出现在事务中 避免并发地执行许多像INSERT UPDATE DELETE这类数据修改语句 24 13 4 3事务控制语句在SQLServer中 对事务的管理是通过事务控制语句和几个全局变量结合起来实现的 1 事务控制语句SAVETRAN 后跟保存点名 表示设置保存点 BEGINTRAN tran name 标识一个用户定义的事务的开始 tran name为事务的名字 标识一个事务开始 COMMITTRAN tran name 表示提交事务中的一切操作 结束一个用户定义的事务 使得对数据库的改变生效 ROLLBACKTRAN tran name save name 回退一个事务到事务的开头或一个保存点 表示要撤消该事务已做的操作 回滚到事务开始前或保存点前的状态 SAVETRANsave name 在事务中设置一个保存点 它可以使一个事务内的部分操作回退 25 2 两个可用于事务管理的全局变量两个可用于事务管理的全局变量是 error及 rowcount error 给出最近一次执行的出错语句引发的错误号 error为0表示未出错 rowcount 给出受事务中已执行语句所影响的数据行数 3 事务控制