《SQL Server数据库应用技术实例教程》课件任务十一.ppt

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

2、批处理及流程控制语句，使用流程控制语句编写代码。11.1.1 批处理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 END2判断语句（IF ELSE）该判断语句用于条件判断，ELSE是可选的。当不包含ELSE时，构成二分支（条件）选择

4、结构。该语句最多可以嵌套32层。IF(condition_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 resu

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

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

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

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

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

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

11、取游标的一般操作如下：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：提取游标结果集最后一行，当前行指定为游标最后一行。ABSOLUTE(n|nvar)：当n或nvar

12、为正数时，提取从游标头开始的第n行，并将该行指定为当前行；当n或nvar为负数时，提取从游标尾之前的第n行，并将该行指定为当前行；当n或nvar为0时，没有被提取的行。n必须为整型常量。RELATIVE(n|nvar)：当n或nvar为正数时，提取游标当前行之后的第n行，并将该行指定为当前行；当n或nvar为负数时，提取游标当前行之前的第n行，并将该行指定为当前行；当n或nvar为0时，提取当前行。如果对游标的第一次提取时，n或nvar的值为负数或0，则没有返回行。INTO variable_name,n：将提取出的数据（某一个或多个列）赋值给局部变量。应用游标及事务（4）处理数据（）处理数据

13、（DELETE/UPDATE）。使用DELETE、UPDATE语句对提取出的数据进行更新或删除操作，而且操作结果反馈给结果集对应的所有数据表。但是一次只能处理游标提取出的一行数据。（5）关闭游标（）关闭游标（CLOSE）。）。使用CLOSE cursor_name|cursor_variable_name语句关闭暂时不用的游标。（6）释放语句（）释放语句（DEALLOCATE）。）。使用 DEALLOCATE cursor_name|cursor_variable_ name语句释放不再使用的游标。11.2.2 游标的定义及使用方法 提示：释放游标与关闭游标的不同在于：关闭游标并不改变游标的定

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

15、colunm_name=new_values WHERE CURRENT OF cursor_name11.2.2 游标的定义及使用方法 其中：其中：table_name|view_name：游标的SELECT 语句中的表或视图名。colunm_name：游标定义中FOR UPDATE OF column list的子集。WHERE CURRENT OF cursor_name：只能更新游标当前位置的行，游标只能是指定了更新功能并已被打开的游标。更新数据时不会自动移动游标的位置，被更新的行可再次被更新，直到执行下一个FETCH语句。提示：提示：若要更新一行中的多个列，则set colunm1_

16、name=new_values1,colunm2_name=new_ values2 应用游标及事务（2）删除指定行。被删除的行有一个唯一索引。使用DELETE WHERE CURRENT OF语句删除表或视图中的数据行，一次只能删除游标当前位置指定的一行。基本语法如下：DELETE FROM table_name|view_name WHERE CURRENT OF cursor_name11.2.2 游标的定义及使用方法 应用游标及事务3两个用于游标管理的全局变量两个用于游标管理的全局变量（1）FETCH_STATUS：保存最后一个FETCH语句执行后的状态信息，用以下几个不同的值表示不同

17、的含义：0：成功完成一个提取操作。-1：提取语句执行错误，或游标的当前位置已经是结果集中的最后一行（游标不会向下移动到下一行）。-2：指定提取的行不存在。如结果集中只有10个数据行，而指定的位置为ABSOLUTE 20。（2）rowcount：保存从游标打开后第一个提取语句被执行，一直到最后一个提取语句为止，所有被提取出的数据行数。一旦结果集中所有数据行被提取，则rowcount的值就是结果集的总行数。11.2.2 游标的定义及使用方法 应用游标及事务11.2.3 应用游标管理数据 1提取数据行提取数据行【例11-9】定义一个游标，查询教师任课信息，逐行显示教师姓名、职称、课程名及任课班级。代

18、码如下：代码1：USE JXGLGO /*定义游标*/declare teac_cla_course_cursor SCROLL CURSOR FOR/*建立结果集*/SELECT t.teac_name,t.profession,c.course_name,tcc.class_idFROM teacher_info t,course_info c,teacher_class_course tccWHERE t.teac_id=tcc.teac_id and c.course_id=tcc.course_id/*指定游标属性*/FOR READ only应用游标及事务11.2.3 应用游标管理

19、数据 代码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_name,t_prof,course_name,cla_id/*判断游标是否成功提取数据*/WHILE fetch_status=0应用游标及事务11.2.3 应用游标

20、管理数据 代码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_idEND/*关闭游标*/CLOSE teac_cla_course_cursor/*释放游标*/DEALLOCATE teac_cla_course_cursorGO 应用游标及事务11.2.3 应用游标管理数据

21、 2修改数据行【例11-10】定义一个游标，将表teacher_class_course中第三行记录的“教室编号”值改为sy104。代码如下：代码1：USE JXGLGO DECLARE croomid_update_cursor SCROLL CURSOR FORSELECT teac_id,course_id,termFROM teacher_class_course/*指定要修改的列*/FOR UPDATE OF termOPEN croomid_update_cursor/*提取结果集中的第三行数据*/FETCH ABSOLUTE 3 FROM croomid_update_curso

22、r 应用游标及事务11.2.3 应用游标管理数据 代码2：/*更新排课表第三行term列的值，该行是游标的当前位置，由上一个FETCH语句指定*/UPDATE teacher_class_course SET term=5WHERE CURRENT OF croomid_update_cursor/*重新提取结果集中的第三行，此时该行中的term列值已被更新。更新之后，游标位置仍不改变*/FETCH ABSOLUTE 3 FROM croomid_update_cursor CLOSE croomid_update_cursorDEALLOCATE croomid_update_cursorG

23、O 提示：提示：使用UPDATEWHERE CURRENT OF语句不能更新来自多个表的同一个列的值。如将上例中的term列改为teac_id列。应用游标及事务11.2.3 应用游标管理数据 3删除数据行【例11-11】定义一个游标，将表teacher_class_course中计算机系的第二门课程删除。代码如下：代码1：USE JXGLGO DECLARE tcc_delete_cursor SCROLL CURSOR FOR/*查询出计算机系的课程信息*/SELECT*FROM teacher_class_courseWHERE course_id LIKE J%OPEN tcc_dele

24、te_cursorFETCH ABSOLUTE 2 FROM tcc_delete_cursor应用游标及事务11.2.3 应用游标管理数据 代码2：/*删除游标结果集中的第一行数据*/DELETE FROM teacher_class_course WHERE CURRENT OF tcc_delete_cursorFETCH ABSOLUTE 2 FROM tcc_delete_cursorCLOSE tcc_delete_cursorDEALLOCATE tcc_delete_cursorGO 应用游标及事务11.3 11.3 “高职院校教学管理系统高职院校教学管理系统”的事务实现的事务

25、实现 任务：任务：了解事务的特点和事务控制原理。应用事务对“高职院校教学管理系统”数据库进行数据一致性检查，在向表中输入数据时，通过设置保存点以避免多个输入操作全部被回滚。11.3.1 什么是事务11.3.2 事务控制语句11.3.3 应用事务管理应用游标及事务11.3.1 什么是事务 1什么是事务（Transaction）事务（事务（Transaction）是需要一次完成的操作集合，是SQL Server数据库操作的基本逻辑单元。它可以是一条SQL语句，或一组SQL语句，或整个SQL程序，它必须彻底被执行完成或根本不被执行。一个事务必须具有以下4个重要特点，称为事务的ACID（四个特点英文拼

26、写的首字母组合）属性。（1）原子性（Atomicity）（2）一致性（Consistency）（3）隔离性（Isolation）（4）持久性（Durability）应用游标及事务 2事务的状态对数据进行操作的事务共包含5种状态：（1）活动状态（2）部分提交状态（3）失败状态（4）提交状态（5）终止状态提交状态和终止状态的事务称为已决事务，活动状态、部分提交状态和失败状态的事务称为未决事务。SQL Server 2005提供了完善的未决事务处理机制回滚（ROLLBACK），将数据库状态恢复到事务开始前的初始状态。部分提交状态失败状态提交状态终止状态活动状态已决事务未决事务事务的五种状态 11.3

27、.1 什么是事务 应用游标及事务11.3.2 事务控制语句 1事务控制语句事务控制语句 SQL Server 2005提供了专门的事务控制语句，供编写应用程序时控制事务的提交和回滚。（1）BEGIN TRANSACTION transaction_name|transaction_variable_name：建立一个事务/事务变量名，标识事务的开始。（2）COMMIT TRAN transaction_name|transaction_variable_name：提交一个事务执行，也用来表示一个事务的结束。（3）ROLLBACK TRAN transaction_name|savepoint_

28、name：回滚一个事务。（4）SAVE TRANSACTION：设置事务执行保存点。应用游标及事务2两个用于事务管理的全局变量两个用于事务管理的全局变量（1）ERROR：给出最近一次执行的出错语句的错误号，值为“0”时表示未出错。（2）rowcount：受事务中已执行语句所影响到的数据行数。一般显示格式为“*行受影响”。3事务控制语句的应用：事务控制语句的应用格式如下：事务控制语句的应用：事务控制语句的应用格式如下：BEGIN TRANSACTION transaction_name A语句组 SAVE TRANSACTION B语句组 IF ERROR 0 /*只回滚B语句组*/ROLLBA

29、CK TRAN savepoint_name /*提交 A语句组，若未回滚B语句组，则提交B语句组*/COMMIT TRAN11.3.2 事务控制语句 应用游标及事务11.3.3 应用事务管理 1事务管理事务管理 SQL Server 2005的事务管理分为以下三个方面：（1）事务控制语句：控制事务执行的语句。（2）锁机制：封锁正被一个事务修改的数据。（3）事务日志：使事务具有可恢复性。2提交事务提交事务 将两个删除操作放在一个事务中，保证它们同时执行以实现数据一致性。应用游标及事务11.3.3 应用事务管理 【例11-12】从表teacher_info中删除编号为TJ001的教师信息。在表t

30、eacher_class_course和表teacher_info中同时存储了教师的信息，从数据一致性考虑，当teacher_info中的教师信息被删除时，对应信息也应从表teacher_class_course中被删除。代码如下：USE JXGLGOBEGIN TRANDELETE FROM teacher_class_courseWHERE teac_id=TJ001DELETE FROM teacher_infoWHERE teac_id=TJ001COMMIT TRANGO应用游标及事务11.3.3 应用事务管理 3回滚事务【例11-13】设置事务保存点，向表teacher_class

31、_course插入TY0104教师的任课信息。代码1如下：USE JXGLGOBEGIN 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

32、GO应用游标及事务11.3.3 应用事务管理 代码2：INSERT INTO teacher_class_course VALUES (TY0104,JDB003,J060205,sy304,5)-录入值的数目少于列的数目SAVE TRAN point_threeGOIF error0 ROLLBACK TRAN point_two -事务回滚至保存点point_twoGOCOMMIT TRAN insert_tcc_infoGO应用游标及事务11.4 11.4 知识进阶知识进阶 任务：任务：了解数据库并发的概念和特点，了 解SQL Server 2005的锁机制。11.4.1 关于并发问题1

33、1.4.2 了解锁机制应用游标及事务11.4.1 关于并发问题 当多个用户在同一时刻操作同一数据库时，称为数据库并发操作。这种并发对数据库来讲会产生如下问题：（1）更新丢失（写错误）。（2）读取脏数据。（3）非重复读。（4）读取幻像。在多用户并发的网络数据库系统中，多个用户的并发访问要求必须以某种机制实现数据的一致性和完整性。SQL Server 2005提供两种机制控制并发。（1）立足于事先干预的控制机制。（2）立足于事后协调冲突的控制机制。应用游标及事务11.4.2 了解锁机制 锁是一种并发控制机制，它可以保证事务的隔离性。有三类对象拥有锁：事务、游标和会话。1SQL Server 200

34、5锁的类型（1）共享锁。（2）排他锁。（3）修改锁。（4）结构锁。（5）意向锁。2死锁 一般来说，对数据库的修改由一个事务组成，此事务读取数据，获取资源的共享锁，然后修改行，将锁转换为排他锁。当两个事务获得了某个资源的共享锁，试图同时修改数据，并都要将锁转换为排他锁时，两个事务都会等对方释放共享锁，便形成了死锁。应用游标及事务3SQL Server解决死锁的主要方法 SQL Server能够自动定期搜索和处理死锁问题，解决死锁常用的方法有以下三种：（1）要求每个事务一次就将要使用的资源全部加锁，否则就不能继续执行。（2）预先规定一个锁顺序，所有事务都按这个顺序实行加锁，这样就不会发生死锁。（3）允许死锁发生，系统采用某些方式诊断当前系统中是否有锁发生，一旦发现，选择一个会话作为牺牲品，然后终止当前事务来打断死锁。11.4.2 了解锁机制 应用游标及事务项目实训项目实训 1实训题目实训题目使用游标访问“销售合同管理系统”数据库中的数据行。2实训目的实训目的（1）掌握T-SQL的批处理和流程控制语句的使用。（2）掌握游标的定义和使用方法。（3）了解事务的概念，并能在实际项目中应用事务。3实训内容实训内容（1）使用游标查询数据行。（2）使用游标删除数据行。（3）使用游标更新数据。（4）事务的应用。应用游标及事务