SQLServer数据库应用技术实例教程 任务11 应用T-SQL程序管理数据.ppt

1、任务十一 应用T-SQL程序管理数据 应用游标及事务,任务：了解游标的作用，了解事务的概念及特点。应用游标管理“高职院校教学管理系统”数据库中的数据行，应用事务实现数据完整性。,11.1 了解T-SQL的批处理与流程控制 11.2 在“高职院校教学管理系统”数据库中应用游标 11.3 “高职院校教学管理系统”的事务实现 11.4 知识进阶 项目实训,了解游标的特点及生命周期 应用游标显示、删除、更新数据行 了解事务的概念 应用事务实施数据完整性,学习目标,应用游标及事务,11.1 了解T-SQL的批处理与流程控制 任务：了解SQL的批处理及流程控制语句，使用流程控制语句编写代码。,11.1.1

2、 批处理 11.1.2 流程控制,应用游标及事务,11.1.1 批处理,批是一组T-SQL语句，SQL Server 2005对T-SQL程序的编译和执行都是按“批”为单位进行的，称为批处理，以GO为结束符。 批的执行是这样的：当编译器读取到GO语句时，把它前面的所有语句当作一个单位来处理，并将这些语句打包发送到SQL Server服务器。GO语句本身不被执行，只作为表示批“到此结束”的前端指令。 批处理的特点是： SQL Server 服务器将批处理语句编译成一个执行单元，称为执行计划（如查询计划）。批中的某一条语句发生编译错误，将导致执行计划无法继续编译，使批处理中的任何语句都不被执行，即

3、批中语句要么编译成功都执行，要么编译不成功都不执行。 某些T-SQL语句必须放在一个单独的批中进行，如CREATE语句。,应用游标及事务,11.1.2 流程控制,流程控制语句用来控制语句、语句块、存储过程等的执行过程。不但可以控制语句的执行顺序，还可以使语句相互连接、关联或依存。 1程序块语句（BEGIN END）将多个语句封装起来构成一个语句块，SQL Server把它当作一条语句处理。 BEGIN sql_statement END 2判断语句（IF ELSE） 该判断语句用于条件判断，ELSE是可选的。当不包含ELSE时，构成二分支（条件）选择结构。该语句最多可以嵌套32层。 IF (c

4、ondition_express) sql_statement 1 ELSE IF(condition_express) n ELSE sql_statement 2。,应用游标及事务,11.1.2 流程控制,3分支判断语句（CASEWHEN） 用于多条件判断，作用类似于嵌套的IFELSE语句。 CASE column_name | variable_name WHEN logical_expression1 THEN result_expression1 WHEN logical_expression2 THEN result_expression2 ELSE result_expressi

5、on END 4循环语句（WHILE） 用于执行循环，根据循环条件重复执行某语句块。 WHILE BEGIN BREAK | CONTINUE sql_statement 2 END,应用游标及事务,11.1.2 流程控制,5无条件退出语句（RETURN） 使程序从一个查询、存储过程或批处理中无条件返回，其后面的语句不再被执行。 6跳转语句（GOTO） 使程序无条件从当前位置跳转到指定的程序执行点。该语句增加了程序的灵活性，但破坏了程序的结构化。 7延期执行语句（WAITFOR） 挂起当前语句的执行，直到指定的时间点或时间间隔到来。不能在同一个WAITFOR语句中既指定时间点，又指定时间间隔。

6、 WAITFOR TIME time | DELAY interval 8检测语句（IFEXISTS） 用于检测数据是否存在，和COUNT函数不同的是，它不考虑与之匹配的满足记录的执行语句，只是检测存在不存在，若发现数据存在就终止。 TIME time | DELAY interval,应用游标及事务,11.2 在“高职院校教学管理系统”数据库中应用游标 任务：了解游标的特点及生命周期，应用游标 显示、删除、更新“高职院校教学管理系统” JXGL中的数据行。,11.2.1 游标（CURSOR）及其特点 11.2.2 游标的定义及使用方法 11.2.3 应用游标管理数据,应用游标及事务,11.2

7、.1 游标（CURSOR）及其特点,使用SELECT语句查询数据，返回内存中的结果，称为结果集。结果集由多行数据组成，在内存中不可分，无法对某个数据行进行操作。则交互式联机应用程序需要借助游标（CURSOR）实现对结果集中数据行的处理。游标是实现对数据逐行处理的机制。,应用游标及事务,11.2.1 游标（CURSOR）及其特点,应用游标及事务,游标最大特点是有严格的生命周期。顺序如下： 定义：定义游标的名称、类型和属性，其属性主要指游标所在的结果集，是一条SELECT语句。 打开：或称为调用，只有打开游标，才能为游标结果集填充数据，即关系到游标所在的结果集。 读取：从结果集中读取单行的数据，在

8、此期间游标可以在结果集中逐行移动。 执行：按需要对提取出的数据行进行显示、删除或更新操作，并将修改结果反馈给结果集的来源表（可以称为游标的基表）。 关闭：停止使用游标的查询，但并不删除游标的定义，可再次打开游标进行数据操作。 释放：删除游标的定义，并释放游标占用的所有资源。,11.2.2 游标的定义及使用方法,1游标的使用步骤 （1）定义游标（DECLARE CURSOR）。 使用DECLARE语句定义游标。基本语法如下： DECLARE cursor_name CURSOR FORWORD_ONLY | SCROLL STATIC | KEYSET | DYNAMIC | FAST_FORW

9、ARD READ_ONLY | SCROLL_LOCKS | OPTIMISTIC TYPE_WARNING FOR select_statement FOR UPDATE OF column list , n,其中： FORWORD_ONLY：指定游标只能从第一行移动到最后一行。 SCROLL：指定所有的提取语句都可以使用。 STATIC：创建由该游标使用的数据的临时复本。 KEYSET：游标打开时，游标中行的成员资格和顺序已经固定。 DYNAMIC：滚动游标时对行所做的数据更新将反馈到游标基表。 FAST_FORWARD：启用性能优化。 READ_ONLY：禁止通过游标更新数据表。 SCR

10、OLL_LOCKS：确保通过游标完成的更新或删除可以成功。 OPTIMISTIC：如果行读入游标后被更新，则通过游标进行的定位更新或定位删除不成功。 TYPE_WARNING：游标从所请求的类型隐性地转化为另一种类型。 select_statement：定义游标结果集。,应用游标及事务,（2）打开游标（OPEN CURSOR）。 使用OPEN语句打开定义的游标。可以使用该语句多次打开还未释放掉的游标，但只能打开处于关闭状态的游标。基本语法如下： OPEN cursor_name | cursor_variable_name 其中的cursor_variable_name为游标变量，取得游标提取

11、出的列值。 （3）提取数据（FETCH）。 读取游标的一般操作如下： FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTE (n | nvar) | RELATIVE (n | nvar) FROM cursor_name | cursor_variable_name INTO variable_name , n ,11.2.2 游标的定义及使用方法,其中： NEXT：提取游标所在行的下一行，当前行自动递增为下一行。 PRIOR：提取游标所在行的前一行，且当前行自动递减为前一行。FIRST：提取游标结果集的第一行，当前行指定为游标第一行。 LAST：提取游标

12、结果集最后一行，当前行指定为游标最后一行。 ABSOLUTE (n | nvar)：当n或nvar为正数时，提取从游标头开始的第n行，并将该行指定为当前行；当n或nvar为负数时，提取从游标尾之前的第n行，并将该行指定为当前行；当n或nvar为0时，没有被提取的行。n必须为整型常量。 RELATIVE (n | nvar)：当n或nvar为正数时，提取游标当前行之后的第n行，并将该行指定为当前行；当n或nvar为负数时，提取游标当前行之前的第n行，并将该行指定为当前行；当n或nvar为0时，提取当前行。如果对游标的第一次提取时，n或nvar的值为负数或0，则没有返回行。 INTO variab

13、le_name , n：将提取出的数据（某一个或多个列）赋值给局部变量。,应用游标及事务,（4）处理数据（DELETE /UPDATE）。 使用DELETE、UPDATE语句对提取出的数据进行更新或删除操作，而且操作结果反馈给结果集对应的所有数据表。但是一次只能处理游标提取出的一行数据。 （5）关闭游标（CLOSE）。 使用CLOSE cursor_name | cursor_variable_name语句关闭暂时不用的游标。 （6）释放语句（DEALLOCATE）。 使用 DEALLOCATE cursor_name | cursor_variable_ name语句释放不再使用的游标。,1

14、1.2.2 游标的定义及使用方法,提示：释放游标与关闭游标的不同在于：关闭游标并不改变游标的定义，当需要时可再次打开游标并进行数据操作；而游标一旦被释放，其定义也就被删除，该游标无法再打开，如果想再次使用该游标，必须重新定义。当从定义游标的存储过程中返回时，游标会自动关闭。,应用游标及事务,2应用游标管理数据 游标被打开后，自动指向结果集中第一行数据之前。每提取一行，SQL Server将修改和保存游标当前的位置，并自动向下移动一行，直到结果集中的数据行全部被提取完。 （1）更新一行中的列。 使用UPDATEWHERE CURRENT OF语句更新表或视图中的数据行，被更新的行依赖于游标位置的

15、当前值。基本语法如下： UPDATE table_name | view_name SET colunm_name=new_values WHERE CURRENT OF cursor_name,11.2.2 游标的定义及使用方法,其中： table_name | view_name：游标的SELECT 语句中的表或视图名。 colunm_name：游标定义中FOR UPDATE OF column list的子集。 WHERE CURRENT OF cursor_name：只能更新游标当前位置的行，游标只能是指定了更新功能并已被打开的游标。更新数据时不会自动移动游标的位置，被更新的行可再次被

16、更新，直到执行下一个FETCH语句。,提示：若要更新一行中的多个列，则set colunm1_name=new_values1, colunm2_name=new_ values2 ,应用游标及事务,（2）删除指定行。 被删除的行有一个唯一索引。使用DELETE WHERE CURRENT OF语句删除表或视图中的数据行，一次只能删除游标当前位置指定的一行。基本语法如下： DELETE FROM table_name | view_name WHERE CURRENT OF cursor_name,11.2.2 游标的定义及使用方法,应用游标及事务,3两个用于游标管理的全局变量 （1）FETC

17、H_STATUS：保存最后一个FETCH语句执行后的状态信息，用以下几个不同的值表示不同的含义： 0：成功完成一个提取操作。 -1：提取语句执行错误，或游标的当前位置已经是结果集中的最后一行（游标不会向下移动到下一行）。 -2：指定提取的行不存在。如结果集中只有10个数据行，而指定的位置为ABSOLUTE 20。 （2）rowcount：保存从游标打开后第一个提取语句被执行，一直到最后一个提取语句为止，所有被提取出的数据行数。一旦结果集中所有数据行被提取，则rowcount的值就是结果集的总行数。,11.2.2 游标的定义及使用方法,应用游标及事务,11.2.3 应用游标管理数据,1提取数据行

18、 【例11-9】定义一个游标，查询教师任课信息，逐行显示教师姓名、职称、课程名及任课班级。代码如下： 代码1： USE JXGL GO /* 定义游标 */ declare teac_cla_course_cursor SCROLL CURSOR FOR /* 建立结果集 */ SELECT t.teac_name, fession ,c.course_name, tcc.class_id FROM teacher_info t,course_info c, teacher_class_course tcc WHERE t.teac_id=tcc.teac_id and c.cour

19、se_id=tcc.course_id /* 指定游标属性 */ FOR READ only,应用游标及事务,11.2.3 应用游标管理数据,代码2： /* 打开游标 */ OPEN teac_cla_course_cursor /* 定义游标变量 */ DECLARE teac_name nvarchar(20), t_prof nvarchar(20), course_name nvarchar(40), cla_id char(8) /* 提取结果集中的第一行数据，并按提取顺序赋值给游标变量 */ FETCH FROM teac_cla_course_cursor INTO teac_n

20、ame , t_prof,course_name,cla_id /* 判断游标是否成功提取数据 */ WHILE fetch_status=0,应用游标及事务,11.2.3 应用游标管理数据,代码3： /* 按格式显示提取的数据行，并继续提取下一行（此时游标已自动指定下一行） */ BEGIN PRINT N教师: +teac_name+, +N职称: +t_prof + , +N课程：+course_name+, +N班级：+cla_id FETCH FROM teac_cla_course_cursor INTO teac_name,t_prof,course_name,cla_id EN

21、D /* 关闭游标 */ CLOSE teac_cla_course_cursor /* 释放游标 */ DEALLOCATE teac_cla_course_cursor GO,应用游标及事务,11.2.3 应用游标管理数据,2修改数据行 【例11-10】定义一个游标，将表teacher_class_course中第三行记录的“教室编号”值改为sy104。代码如下： 代码1： USE JXGL GO DECLARE croomid_update_cursor SCROLL CURSOR FOR SELECT teac_id,course_id,term FROM teacher_class_

22、course /* 指定要修改的列 */ FOR UPDATE OF term OPEN croomid_update_cursor /* 提取结果集中的第三行数据 */ FETCH ABSOLUTE 3 FROM croomid_update_cursor,应用游标及事务,11.2.3 应用游标管理数据,代码2： /* 更新排课表第三行term列的值，该行是游标的当前位置，由上一个FETCH语句指定 */ UPDATE teacher_class_course SET term=5 WHERE CURRENT OF croomid_update_cursor /* 重新提取结果集中的第三行，

23、此时该行中的term列值已被更新。更新之后，游标位置仍不改变 */ FETCH ABSOLUTE 3 FROM croomid_update_cursor CLOSE croomid_update_cursor DEALLOCATE croomid_update_cursor GO,提示：使用UPDATEWHERE CURRENT OF语句不能更新来自多个表的同一个列的值。如将上例中的term列改为teac_id列。,应用游标及事务,11.2.3 应用游标管理数据,3删除数据行 【例11-11】定义一个游标，将表teacher_class_course中计算机系的第二门课程删除。代码如下： 代

24、码1： USE JXGL GO DECLARE tcc_delete_cursor SCROLL CURSOR FOR /* 查询出计算机系的课程信息 */ SELECT * FROM teacher_class_course WHERE course_id LIKE J% OPEN tcc_delete_cursor FETCH ABSOLUTE 2 FROM tcc_delete_cursor,应用游标及事务,11.2.3 应用游标管理数据,代码2： /* 删除游标结果集中的第一行数据 */ DELETE FROM teacher_class_course WHERE CURRENT OF

25、 tcc_delete_cursor FETCH ABSOLUTE 2 FROM tcc_delete_cursor CLOSE tcc_delete_cursor DEALLOCATE tcc_delete_cursor GO,应用游标及事务,11.3 “高职院校教学管理系统”的事务实现 任务：了解事务的特点和事务控制原理。应用事务对“高职院校教学管理系统”数据库进行数据一致性检查，在向表中输入数据时，通过设置保存点以避免多个输入操作全部被回滚。,11.3.1 什么是事务 11.3.2 事务控制语句 11.3.3 应用事务管理,应用游标及事务,11.3.1 什么是事务,1什么是事务（Tran

26、saction） 事务（Transaction）是需要一次完成的操作集合，是SQL Server数据库操作的基本逻辑单元。它可以是一条SQL语句，或一组SQL语句，或整个SQL程序，它必须彻底被执行完成或根本不被执行。 一个事务必须具有以下4个重要特点，称为事务的ACID（四个特点英文拼写的首字母组合）属性。 （1）原子性（Atomicity） （2）一致性（Consistency） （3）隔离性（Isolation） （4）持久性（Durability）,应用游标及事务,2事务的状态 对数据进行操作的事务共包含5种状态： （1）活动状态 （2）部分提交状态 （3）失败状态 （4）提交状态 （

27、5）终止状态 提交状态和终止状态的事务称为已决事务，活动状态、部分提交状态和失败状态的事务称为未决事务。SQL Server 2005提供了完善的未决事务处理机制回滚（ROLLBACK），将数据库状态恢复到事务开始前的初始状态。,事务的五种状态,11.3.1 什么是事务,应用游标及事务,11.3.2 事务控制语句,1事务控制语句 SQL Server 2005提供了专门的事务控制语句，供编写应用程序时控制事务的提交和回滚。 （1）BEGIN TRANSACTION transaction_name | transaction_variable_name：建立一个事务/事务变量名，标识事务的开始

28、。 （2）COMMIT TRAN transaction_name | transaction_variable_name：提交一个事务执行，也用来表示一个事务的结束。 （3）ROLLBACK TRAN transaction_name | savepoint_name：回滚一个事务。 （4）SAVE TRANSACTION：设置事务执行保存点。,应用游标及事务,2两个用于事务管理的全局变量 （1）ERROR：给出最近一次执行的出错语句的错误号，值为“0”时表示未出错。 （2）rowcount：受事务中已执行语句所影响到的数据行数。一般显示格式为“*行受影响”。 3事务控制语句的应用：事务控制

29、语句的应用格式如下： BEGIN TRANSACTION transaction_name A语句组 SAVE TRANSACTION B语句组 IF ERROR 0 /* 只回滚B语句组 */ ROLLBACK TRAN savepoint_name /* 提交 A语句组，若未回滚B语句组，则提交B语句组 */ COMMIT TRAN,11.3.2 事务控制语句,应用游标及事务,11.3.3 应用事务管理,1事务管理 SQL Server 2005的事务管理分为以下三个方面： （1）事务控制语句：控制事务执行的语句。 （2）锁机制：封锁正被一个事务修改的数据。 （3）事务日志：使事务具有可恢

30、复性。 2提交事务 将两个删除操作放在一个事务中，保证它们同时执行以实现数据一致性。,应用游标及事务,11.3.3 应用事务管理,【例11-12】从表teacher_info中删除编号为TJ001的教师信息。 在表teacher_class_course和表teacher_info中同时存储了教师的信息，从数据一致性考虑，当teacher_info中的教师信息被删除时，对应信息也应从表teacher_class_course中被删除。代码如下： USE JXGL GO BEGIN TRAN DELETE FROM teacher_class_course WHERE teac_id=TJ001

31、 DELETE FROM teacher_info WHERE teac_id=TJ001 COMMIT TRAN GO,应用游标及事务,11.3.3 应用事务管理,3回滚事务 【例11-13】设置事务保存点，向表teacher_class_course插入TY0104教师的任课信息。代码1如下： USE JXGL GO BEGIN TRANSACTION insert_tcc_info INSERT INTO teacher_class_course VALUES ( TY0104,JDB001,J080205,sy102,1,15) SAVE TRAN point_one -设置事务处理保存点point_one INSERT INTO teacher_class_course VALUES ( TY0104,JDB002,J070102,sy302,3,16) SAVE TRAN point_two -设置事务处理保存点point_two GO,应用游标及事务,11.3.3 应用事务管理,代码2： INSERT INTO teacher_class_course VALUES ( TY0104,JDB003,J060205,sy304,5) -录入值的数目少于列的数目 SAVE TRA