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

1第第13章章 游标及事务游标及事务13.1 游标的定义及优点游标的定义及优点13.2 游标的使用游标的使用13.3 使用游标修改数据使用游标修改数据13.4 事务的使用事务的使用返回目录返回目录变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分213.1 游标的定义及其优点 前面介绍的数据检索方法可以得到数据库中有关表的数据，但这些数据是作为一个结果集得到的，用户可以把这个结果集保存到一个文件里，或生成一个新表以便于以后使用。这种查询是非常重要的。但这种查询形式有一个很大的缺点，它不能对结果集中每一行的数据进行处理。使用游标可以实现对查询结果集中的数据逐行处理。13.1.1 游标的概念游标的概念 游标(Cursor)是一种处理数据的方法，为了查看或者处理结果集中的数据，游标提供了在结果集中向前或者向后浏览数据的能力。 可以把游标看成一种指针，它既可以指向当前位置，也可以指向结果集中的任意位置，它允许用户对指定位置的数据进行处理，可以把结果集中的数据放在数组、应用程序中或其它地方。变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分313.1.2 使用游标的优点使用游标的优点 从游标定义可以得知游标具有如下的优点: 允许程序对由查询语句SELECT返回的行集合中的每一行数据执行相同或不同的操作，而不是对整个行集合执行同一个操作 提供对基于游标位置的表中的行进行删除和更新的能力 游标实际上作为面向集合的数据库管理系统（RDBMS）和面向行的程序设计之间的桥梁，使这两种处理方式通过游标沟通起来变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分413.2 游标的使用13.2.1 使用游标的步骤使用游标的步骤 使用游标的工作流程如下图13-1所示。具体地说，有如下几个步骤： 创建游标。使用T-SQL语句生成一个结果集，并且定义游标的特征，如游标中的记录是否可以修改。 打开游标。 从游标的结果集中读取数据。从游标中检索一行或多行数据称为取数据。 对游标中的数据逐行操作。 关闭和释放游标。变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分513.2.2 游标的定义及使用过程游标的定义及使用过程 1. 声明游标声明游标 声明游标是指用DECLARE语句声明或创建一个游标。 声明游标的语法如下：DECLARE cursor_name SCROLL CURSORFOR select_statementFOR READ ONLY|UPDATEOF column_name_list 其中： cursor_name:是游标的名字，为一个合法的SQL Server标识符，游标的名字必须遵循SQL Server命名规范。 SCROLL：表示取游标时可以使用关键字NEXT、PRIOR、FIRST、LAST、ABSOLUTE、RELATIVE。每个关键字的含义将在介绍FETCH子句时讲解。 select_statement：是定义游标结果集的标准 SELECT语句，它可以是一个完整语法和语义的Transact-SQL的SELECT语句。变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分6 但是这个SELECT语句必须有FROM子句，不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。 FOR READ ONLY：指出该游标结果集只能读，不能修改。 FOR UPDATE：指出该游标结果集可以被修改。 OF column_name_list：列出可以被修改的列的名单。 应该注意应该注意: 游标有且只有两种方式：FOR READ ONLY或FOR UPDATE。 当游标方式指定为FOR READ ONLY时，游标涉及的表不能被修改。 当游标方式指定为FOR UPDATE时，可以删除或更新游标涉及的表中的行。通常，这也是缺省方式。 声明游标的DECLARE CURSOR 语句必须是在该游标的任何OPEN语句之前。变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分7 2. 打开游标打开游标 打开游标是指打开已被声明但尚未被打开的游标，打开游标使用OPEN语句。 打开游标的语法如下： OPEN cursor_name 其中： cursor_name是一个已声明的尚未打开的游标名。 注意：注意： 当游标打开成功时，游标位置指向结果集的第一行之前。 只能打开已经声明但尚未打开的游标。变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分8 3. 从打开的游标中提取行从打开的游标中提取行 游标被打开后，游标位置位于结果集的第一行前，此时可以从结果集中提取（FETCH）行。SQL Server将沿着游标结果集一行或多行向下移动游标位置，不断提取结果集中的数据，并修改和保存游标当前的位置，直到结果集中的行全部被提取。 从打开的游标中提取行的语法如下：FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE FROM cursor_name INTO fetch_target_list 其中： cursor_name：为一已声明并已打开的游标名字。 NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE：游标移动方向，缺省情况下是NEXT，即向下移动。 NEXT：取下一行数据。 PRIOR：取前一行数据。 FIRST：取第一行数据。变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分9 LAST：取最后一行数据。 ABSOLUTE：按绝对位置取数据。 RELATIVE：按相对位置取数据。 游标位置确定了结果集中哪一行可以被提取，如果游标方式为FOR UPDATE的话，也就确定该位置一行数据可以被更新或删除。 INTO fetch_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语句打开它，SQL Server会用该游标的定义重新创建这个游标的一个结果集。 关闭游标的语法如下：CLOSE cursor_name 其中： cursor_name：是已被打开并将要被关闭的游标名字。 在如下情况下，SQL Server会自动地关闭已打开的游标： 当你退出这个SQL Server会话时 从声明游标的存储过程中返回时变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分12 5. 释放游标释放游标 释放( Deallocate)游标是指释放所有分配给此游标的资源，包括该游标的名字。 释放游标的语法是：DEALLOCATE CURSOR cursor_name 其中： cursor_name：将要被DEALLOCATE释放的游标名字。如果释放一个已打开但未被关闭的游标，SQL Server会自动先关闭这个游标，然后再释放它。 注意一下关闭游标与释放游标的区别注意一下关闭游标与释放游标的区别：关闭游标并不改变游标的定义，一个游标关闭后，不需要再次声明，就可以重新打开并使用它。但一个游标释放后，就释放了与该游标有关的一切资源，也包括游标的声明，游标释放后就不能再使用该游标了，如需再次使用游标，就必须重新定义。变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分13【例【例13-1】定义一个游标，将教师表teacher中所有教师的教师姓名、教师职称显示出来。USE jwglGODECLARE teach_name VARCHAR(8),teach_profession VARCHAR(16)DECLARE teacher_coursor SCROLL CURSOR FOR SELECT teacher_name,profession FROM teacher FOR READ ONLYOPEN teacher_coursorFETCH FROM teacher_coursor INTO teach_name,teach_profession变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分14WHILE FETCH_STATUS=0 BEGIN PRINT 教师姓名:+teach_name+ +职 称: + teach_profession FETCH FROM teacher_coursor INTO teach_name , teach_profession ENDCLOSE teacher_coursorDEALLOCATE teacher_coursor变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分1513.3 使用游标修改数据 用户可以在UPDATE或DELETE语句中使用游标来更新、删除表或视图中的行，但不能用来插入新行。13.3.1 更新数据更新数据 通过在UPDATE语句中使用游标可以更新表或视图中的行。被更新的行依赖于游标位置的当前值。 更新数据语法形式如下： UPDATE table_name|view_name SET table_name.|view_name. column_name = new_value .n WHERE CURRENT OF cursor_name 其中： 紧跟UPDATE之后的table_name| view_name：要更新的表名或视图名，可以加或不加限定。但它必须是声明该游标的SELECT语句中的表名或视图名。变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分16 column_name：是要更新的列的列名，可以加或不加限定。但它们必须是声明游标的SELECT语句中UPDATE OF column_name_list的子集 new_value：为被更新列的新值，它可以是一个表达式、空值或子查询。 WHERE CURRENT OF：使SQL Server只更新由指定游标的游标位置当前值确定的行。cursor_name：是已声明为FOR UPDATE方式并已打开的游标名。变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分17 注意： 使用UPDATE.CURRENT OF语句一次只能更新当前游标位置确定的那一行，OPEN语句将游标位置定位在结果集第一行前，可以使用FETCH语句把游标位置定位在要被更新的数据行处。 用UPDATE.WHERE CURRENT OF语句更新表中的行时，不会移动游标位置，被更新的行可以再次被修改，直到下一个FETCH语句的执行。 UPDATE.WHERE CURRENT OF语句可以更新多表视图或被连接的多表，但只能更新其中一个表的行，即所有被更新的列都来自同一个表。变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分18【例【例13-2】通过游标将教师表teacher中记录号为3的章红老师的职称由“讲师”改为“教授”。USE jwglGODECLARE teacher_coursor SCROLL CURSOR FOR SELECT teacher_name,profession FROM teacher FOR UPDATE OF professionOPEN teacher_coursorFETCH ABSOLUTE 3 FROM teacher_coursorUPDATE teacherSET profession = 教授 WHERE CURRENT OF teacher_coursorFETCH ABSOLUTE 3 FROM teacher_coursorCLOSE teacher_coursorDEALLOCATE teacher_coursor变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分1913.3.2 删除数据删除数据 通过在DELETE语句中使用游标来删除表或视图中的行。被删除的行依赖于游标位置的当前值。 删除数据语法形式如下：DELETE FROMdatabase.owner.table_name|view_nameWHERE CURRENT OF cursor_name 其中： table_name|view_name：为要从其中删除行的表名或视图名，可以加或不加限定。但它必须是定义该游标的SELECT语句中的表名或视图名。 cursor_name：为已声明并已打开的游标名。 WHERE CURRENT OF：它使SQL Server只删除由指定游标的游标位置当前值确定的行。变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分20 注意：注意： 使用游标的DELETE语句，一次只能删除当前游标位置确定的那一行。OPEN语句将游标位置定位在结果集第一行之前，可以用FETCH语句把游标位置定位在要被删除的行处。 在DELETE语句中使用的游标必须声明为FOR UPDATE方式。而且声明游标的SELECT语句中不能含有连接操作或涉及多表视图，否则即使声明中指明了FOR UPDATE方式，也不能删除其中的行。 对使用游标删除行的表，要求有一个唯一索引。 使用游标的DELETE语句，删除一行后将游标位置向前移动一行。变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分2113.4 事务的使用13.4.1 什么是事务什么是事务 多用户并发存取同一数据可能会导致产生数据的不一致性问题。正确地使用事务可以有效控制这类问题发生的频度甚至能避免这类问题的发生。 所谓事务(Transaction ),是指一个操作序列，这些操作序列要么都被执行，要么都不被执行，它是一个不可分割的工作单元。 事务中任何一个语句执行时出错，系统都会返回到事务开始前的状态。 事务是并发控制的基本单元，是数据库维护数据一致性的单位。在每个事务结束时，都能保持数据一致性。 例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都被执行，要么都不被执行。所以，应该把他们看成一个事务。变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分2213.4.2 事务的特点及事务的管理事务的特点及事务的管理 1.事务的特性 事务具有4个属性：原子性(Atomicity)、一致性(Consistency)、隔离性(1solation)和永久性(Durability)。这4个属性简称为ACID特性。 原子性 原子性是指事务内的操作要么都被执行，要么都不被执行。 一致性 一致性是指在事务开始前和结束后，所有的数据都必须处于一致性的状态。 隔离性 隔离性是指一个事务所作的修改必须能够与其他事务所作的修改隔离起来。 永久性 永久性指如果事务正常结束，则它对数据库所做的修改应是永久地保存。 变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分232. 事务的管理事务控制语句：控制事务执行的语句。包括将一系列操作定义为一个工作单元来处理。锁机制：封锁正被一个事务修改的数据，防止其他用户访问到“不一致”的数据。事务日志：使事务具有可恢复性。为了尽可能避免死锁的出现，应注意：l在所有的事务中都按同一顺序来访问各个表。尽可能利用存储过程来完成一个事务，以保证对各表的访问次序都是一致的。l 事务应该尽量小且应尽快提交。l 避免人工输入操作出现在事务中。 避免并发地执行许多像INSERT、UPDATE、DELETE这类数据修改语句。变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分2413.4.3 事务控制语句事务控制语句 在SQL Server中，对事务的管理是通过事务控制语句和几个全局变量结合起来实现的。 1. 事务控制语句事务控制语句 SAVE TRAN：后跟保存点名，表示设置保存点 BEGIN TRAN tran_name：标识一个用户定义的事务的开始。tran_name为事务的名字，标识一个事务开始。 COMMIT TRAN tran_name：表示提交事务中的一切操作，结束一个用户定义的事务。使得对数据库的改变生效。 ROLLBACK TRAN tran_name|save_name：回退一个事务到事务的开头或一个保存点。表示要撤消该事务已做的操作，回滚到事务开始前或保存点前的状态。 SAVE TRAN save_name：在事务中设置一个保存点。它可以使一个事务内的部分操作回退。变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分25 2. 两个可用于事务管理的全局变量两个可用于事务管理的全局变量 两个可用于事务管理的全局变量是error及rowcount。 error：给出最近一次执行的出错语句引发的错误号，error为0表示未出错。 rowcount：给出受事务中已执行语句所影响的数据行数。 3. 事务控制语句的使用事务控制语句的使用 事务控制语句的使用方法如下： BEGIN TRAN / *A组语句序列* / SAVE TRAN save_point / *B组语句序列* / if error0 ROLLBACK TRAN save_point / *仅回退B组语句序列* /变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分26 COMMIT TRAN / *提交A组语句，且若未回退B组语句则提交B组语句* / 4. 在事务中不能包含的语句在事务中不能包含的语句 注意，事务中不能包含如下语句： CREATE DATABASE ALTER DATABASE BACKUP LOG DROP DATABASE RECONFIGURE RESTORE DATABASE RESTORE LOG UPDATE STATISTICS变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分27 5. 事务使用示例事务使用示例【例【例13-3】使用事务向表book中插入数据。USE jwglGOBEGIN TRAN tran_exampINSERT INTO book( book_id , book_name , publish_company)VALUES( dep04_s006_01 , VFP程序设计 , 南京大学出版社 )SAVE TRAN int_pointINSERT INTO book( book_id , book_name , publish_company )VALUES( dep04_s006_02 , VFP实验指导书 , 东南大学出版社 )GO变电站电气主接线是指变电站的变压器、输电线路怎样与电力系统相连接，从而完成输配电任务。变电站的主接线是电力系统接线组成中一个重要组成部分28INSERT INTO book(book_id , book_name)VALUES(dep04_s006_03 , VFP课程设计指导书)GOIF error0 ROLLBACK TRAN int_pointGOCOMMIT TRAN tran_exampGO