《数据库行为特征设计-SQL程序设计.ppt》由会员分享，可在线阅读，更多相关《数据库行为特征设计-SQL程序设计.ppt（81页珍藏版）》请在金锄头文库上搜索。
1、7.1SQL程序基础7.2函数和表达式流程控制语句7.3存储过程触发器7.4触发器游标7.5临时表和表变量7.6游标7.7事务第八章 数据库行为特征设计SQL程序设计7.1 SQL程序基础7.1.1批处理：批处理是一组Transact-SQL 语句，其中可以包括变量的流程控制语句由客户端应用程序一次性地发送到数据库服务器SQL Server 的数据库服务器将批处理语句编译成一个可执行单元，此单元称为执行计划，执行计划中的语句被逐条执行。批处理中语法错误：将使执行计划无法编译，所有语句都不执行。批处理中的逻辑（运行）错误（如违反完整性规则）：该语句将不能正常运行，该语句以前语句正常执行，该语句以
2、后语句大多数情况下不能正常运行，少数情况下仍能正常运行。7.1.2 变量的声明和使用变量分局部变量和全局变量局部变量是可以保存指定类型的单个数据值的对象，其名称前必须标以“”。全局变量是由系统提供且预先声明的变量，在引用它的时候在名称前必须标以“”。一）局部变量局部变量的生存期从声明它的地方开始，直到声明它的批处理、存储过程或函数结束局部变量的定义： DECLARE ，.n 例：DECLARE num INTEGER局部变量赋值：SET 变量名=或SELECT =表达式FROM ，.n WHERE 返回局部变量值：PRINT 或SELECT 四）全局变量 全局变量是由系统提供且预先声明的变量，
3、系统根据当前的运行环境和状况对其进行赋值。用户程序不能改变其值，但它可以被任何批处理程序、存储过程和函数读取。SQL Server提供了30多个全局变量，下面列出了一些常用的全局变量。全局变量ERROR：返回最后执行的 SQL 语句的错误代码。FETCH_STATUS：读取游标状态，返回最近的FETCH 语句的执行状态IDENTITY：返回最后插入的标识值。CREATE TABLE Teacher(TeacherId INT IDENTITY(1000,1) PRIMARY KEY,TeacherName VARCHAR(20)7.1.3流程控制语句一）分支语句IFIF BEGIN .n EN
4、D ELSE BEGIN .n END /如果如果IF和和ELSE后只有一个语句后只有一个语句,可以省略可以省略BEGIN和和END二）循环语句循环语句的一般格式：WHILE BEGIN语句.nBREAKCONTINUE 语句.nEND BREAK语句使循环结束 CONTINUE语句进入下一循环三） RETURN语句RETURN expression结束程序，RETURN以后的程序将不被执行。若包含表达式，通常在函数中使用，表示该函数返回该表达式值，若在存储过程中使用，表达式值必须为整型。例：实现对某个结点在树结构中所在层数的计算DECLARE Id INT,Lev INTSET Id=8-计
5、算id=8结点的层数SET Lev=0WHILE 1=1BEGIN SET Id=(SELECT PId FROM GoodsClass1 WHERE Id=Id) IF Id IS NULL BREAK ELSE SET Lev=Lev+1ENDPRINT Lev7.2 函数和表达式函数是存储在数据库中可供其他程序调用的SQL程序，其基本特征是具有返回值根据返回类型的不同，SQL Server提供了返回单值的标量函数和返回一个表的表值函数两种类型的函数。函数创建后就永久存在于数据库中，直到使用删除语句删除它。SQL Server提供了大量的内置函数即标准函数供用户调用。表达式是符号与运算符的
6、组合，SQL Server对其求值以获得单个数据值。7.2.1 表达式和标准函数一）各种类型的数据运算及数据类型的隐形转换1）数值和字符串混合运算SELECT 100+123：输出为2232）日期和数值混合运算SELECT GETDATE() + 365：输出为明年与今日同月同日的日期3）字符串转换为日期UPDATE Student SET Birthday=- 1990-4-174）日期转换成字符串SELECT LEFT(GETDATE(),2)，结果为“04”，即当前的月份字符串二）表达式和空值当表达式中存在空值，则表达式值为空值。当条件表达式中出现空值，则条件表达式值为FALSE，如下列
7、条件判断均为FALSE：IF(X=NULL)IF (3NULL)IF(NULL=NULL)在需要判断某表达式是否为空时，必须使用IS及IS NOT：IF(X IS NULL)IF(X IS NOT NULL)三）CASE表达式1）格式一CASE 表达式0WHEN 表达式i THEN 结果表达式i .n ELSE 表达式n+1END例：SELECT StdId,StdName,CASE StdSexWHEN 1 THEN 男WHEN 0 THEN 女ELSE 未知ENDFROM Student2）格式二CASEWHEN 逻辑表达式iTHEN 结果表达式i.nELSE 表达式n+1END例：SEL
8、ECT a.StdId,a.StdName,b.EleName,Grade=CASE WHEN c.Grade=90 THEN 优WHEN c.Grade=80 THEN 良WHEN c.Grade=70 THEN 中WHEN c.Grade=60 THEN 及格ELSE 不及格ENDFROM Student a,Elective b,Student_Elective cWHERE a.StdId=c.StdId AND b.EleId=c.EleId7.2.1.2标准函数SQL Server提供了大量的各类标准函数SELECT语句中通过使用函数和函数的嵌套，可使我们用一个SELECT语句实现
9、比较复杂的查询需求一）常用函数介绍常用日期类型函数：lGETDATE()lYEAR(date)lDATEDIFF ( datepart , startdate , enddate )lDATEADD ( datepart , number, date )lDATEPART ( datepart , date )常用字符类型的函数lLTRIM/RTRIM(character_expresion)lREPLICATE (character_expression , integer_expression)lLEN (string_expression)lSUBSTRING (expression ,
10、 start , length)lCHARINDEX (expression1 , expression2 , start_location )lREPLACE(string_expression1,string_expression2,string_expression3)lLEFT ( character_expression , integer_expression )类型转换和其他函数 lCONVERT (data_type(length), expression , style)lSTR(float_expr,length,decimal)lISNULL ( check_expres
11、sion , replacement_value )lPOWER ( numeric_expression , y )二）函数的应用实例把Supplier中供应商名称SuppName中包含“南市区”的全部改成“黄埔区”：UPDATE Supplier SET SuppName=REPLACE(SuppName, 南市区,黄埔区)如果供应商名称前必须冠以所在省市，并以“-”与后面的供应商名分割，要求分列查询供应商所在省市和供应商名 SELECT SUBSTRING(SuppName,1,CHARINDEX(-,SuppName)-1),SUBSTRING(SuppName, CHARINDEX(
12、-,SuppName)+1,LEN(SuppName)- CHARINDEX(-,SuppName) FROM Supplier输出学生的学号、姓名以及年龄，年龄的计算方法为：l如当前日期的“月日”小于出生日期的“月日”，年龄当前年份出生年份1l如当前日期的“月日”大于等于出生日期的“月日”，年龄当前年份出生年份SELECT StdId,StdName,YEAR(GETDATE()-YEAR(Birthday)-CASEWHEN LEFT(CONVERT(CHAR(10),Birthday,101),5)LEFT(CONVERT(CHAR(10),GETDATE(),101),5) THEN
13、1ELSE 0ENDFROM Student查询“09”开头的下一个可用的学号，假设学号总长为5位字符，下一个可用编号就是“09”开头的学号中后三位的最大编号1后的编号 SELECT 09+RIGHT(REPLICATE(0,2)+LTRIM(STR(MAX(RIGHT(StdId,3)+1,3),3)FROM Student WHERE StdId LIKE 09%7.2.2自定义函数标量函数创建标量函数的语句为 ：CREATE FUNCTION 函数名 ( 参数名 参数类型 = 默认值 ,.n ) RETURNS 返回类型WITH ASBEGIN 函数体（其中必须包含RETURN语句）EN
14、D函数一旦创建，就永久存在，直到使用DROP FUNCTION删除它函数选项：ENCRYPTION 和SCHEMABINDINGENCRYPTION：加密该函数体中的内容，使用任何工具都无法看到函数体程序。SCHEMABINDING：将函数绑定（Binding）到它所引用的数据库对象，即不能更改或除去该函数所引用的数据库对象，避免由于删除或修改了函数所引用的数据对象，而使函数无法正常运行。例：供应商名称格式化要求：Supplier表中供应商名为英文，要求对其格式化，每个单词的第一字母大写，若两个单词之间出现多于一个空格，则去除多余的空格。创建一个函数，该函数参数为字符串，返回的是根据要求格式化
15、后的字符串假设函数名为FormatStr，则对供应商名的格式化语句为：UPDATE Supplier Set SuppName=dbo.FormatStr(SuppName)以sa登录数据库，创建所有对象包括函数都属于一个特定的数据库用户dbo（DataBase Owner），dbo是SQL Server为每个数据库预置的数据库用户，在引用函数的时候必须在函数名前加“dbo.” 7.2.3 表值函数表值函数可以返回一个表，即其返回类型是table类型表值函数分为单语句表值函数和多语句表值函数 如果一个查询比较复杂，通常我们可以把它定义成一个视图，如果查询包含了参数，一般情况下我们仍能使用视图实
16、现，但可能会使视图对应的查询语句变得更为复杂，这个时候就可以考虑使用单语句表值函数。如果一个查询根本无法用一个SELECT语句完成时，就可以考虑使用多语句表值函数，即用一段程序来获得查询数据 7.2.3.1 单语句表值函数创建单语句表值函数的语句为：CREATE FUNCTION 函数名 ( 参数名 参数类型 = 默认值 ,.n ) RETURNS TABLE WITH ASRETURN 查询语句该函数将返回执行“查询语句”后得到的结果集，可以使用下列语句获得结果：SELECT * FROM 函数名(参数表)例:查询某个学生选修各门课的名称和成绩建立单语句表值函数CREATE FUNCTION
17、 AllGrade ( StdId char(6) ) RETURNS table ASRETURN SELECT c.EleName,b.GradeFROM Student a LEFT JOIN Student_Elective b ON a.StdId=b.StdIdLEFT JOIN Elective c ON b.EleId=c.EleIdWHERE a.StdId=StdId使用下列语句获得结果：SELECT * FROM AllGrade(该学生学号) 用表值函数实现先选择后进行外连接的查询：查询所有学生选修“ele002”课程的情况，结果中包括学号和课程名，对没有选修该课程的学
18、生，课程名为NULL定义表值函数：CREATE FUNCTION Student_Some_Elective(EleId CHAR(6)RETURNS TABLEASRETURN SELECT * FROM Student_Elective WHERE EleId=EleId使用查询语句：SELECT a.StdName,c.EleName FROM Student a LEFT JOIN Student_Some_Elective(ele002) b on a.StdId=b.StdIdLEFT JOIN Elective c ON b.EleId=c.EleId7.2.3.2多语句表值函数
19、创建多语句表值函数的语句为：CREATE FUNCTION 函数名 ( 参数名 参数类型 = 默认值 ,.n ) RETURNS 表变量名 TABLE WITH ASBEGIN 函数体END函数体内可包含一个由多个语句组成的程序，该程序完成获取最终需要的数据，并把它插入或更新到“表变量名”表示的表中，在执行RETURN时，该表数据将被作为函数的结果返回。例：查询某个班学生选修的各门课的及格和不及格人数查询结果包括：课程号、课程名、及格人数和不及格人数CREATE FUNCTION ElectiveGradeSummary (ClassId CHAR(6)RETURNS PassNumSumma
20、ryTABLE (ElectiveId CHAR(6),EleName VARCHAR(20),PassNum INT,NotPassNum INT) ASBEGIN -插入班级学生选修的课程插入班级学生选修的课程INSERT into PassNumSummary(ElectiveId,EleName)SELECT DISTINCT a.EleId,b.EleName FROM Student_Elective a JOIN Elective b ON a.EleId=b.EleIdWHERE StdId in (SELECT StdId FROM Student WHERE ClassId
21、=ClassId)-计算及格人数计算及格人数UPDATE PassNumSummary SET PassNum=( SELECT count(*) FROM Student_Elective WHERE EleId=ElectiveId AND Grade=60 AND StdId IN (SELECT StdId FROM Student WHERE ClassId=ClassId)-计算不及格人数计算不及格人数UPDATE PassNumSummary SET NotPassNum=( SELECT count(*) FROM Student_Elective WHERE EleId=El
22、ectiveId AND Grade=60SELECT NotPassNum=count(*) FROM Student_Elective a JOIN Student b ON a.StdId=b.StdIdWHERE a.EleId=EleId AND b.ClassId=ClassId AND Grade60RETURN调用该存储过程的批处理程序为：DECLARE PassN INT,NotPassN INTEXECUTE GetPassNum ele001,0901,PassN OUTPUT,NotPassN OUTPUTSELECT PassN,NotPassN7.4 触发器商场的总
23、经理希望能动态地看到各类商品当日实时的销售总额，实现这个功能可有两种方法：l按一定的时间间隔用查询语句汇总各类商品当日的销售额l在商品表（Goods）中增加一个存放“当日销售额（SaleAmt）”的列，每一笔销售数据存入数据库时，更新商品表相应商品的“当日销售额（SaleAmt）”第二种方法把汇总工作分散到每笔销售中，提高了查询的效率第二种方法可用触发器来实现，触发器是一种特殊的存储过程，当对指定表执行指定的数据修改语句时自动执行。建立一个触发器，在对销售表实施插入操作时触发，触发的程序就是实现把插入到销售表的某个商品的销售额累加到商品表中的SaleAmt中。7.4.1 创建触发器建立触发器完
24、整的语句格式为：CREATE TRIGGER 触发器名触发器名 ON 表表 | 视图视图 WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE AS IF UPDATE ( 列列 ) AND | OR UPDATE ( 列列 ) . n | IF ( COLUMNS_UPDATED ( ) 位运算符位运算符 被更新的位掩码被更新的位掩码 ) 比较运算符比较运算符 列的位掩码列的位掩码 .n SQL 语句语句 .n 7.4.2 触发器应用实例利用触发器可以：实现合计数同步实现有条件的关联插入实现引用表外码的置空操作实现
25、对多数据源视图的删除操作实现对多数据源视图的更新操作一）合计数同步CREATE TRIGGER Tri_SaleDetail ON SaleDetail FOR INSERTASBEGIN UPDATE Goods SET SaleAmt=SaleAmt+ (SELECT SaleQty*SalePrice FROM INSERTED) WHERE GoodsNo=(SELECT GoodsNo FROM INSERTED)END二）有条件的关联插入CREATE TRIGGER Tri_StudentON StudentFOR INSERT ASIF (SELECT ClassId FROM
26、INSERTED)=0901INSERT INTO Student_ElectiveSELECT a.StdId,b.EleId,NULL FROM INSERTED a,Elective b三）实现引用表外码的置空操作在删除或修改了某个班的班号后，属于该班的学生的班号设置为空。可尝试建立下列触发器：CREATE TRIGGER Tri_ClassON Class FOR DELETE,UPDATEASIF UPDATE(ClassId) UPDATE Student SET ClassId=NULL WHERE ClassId= (SELECT ClassId FROM DELETED)可删
27、除或修改class表中的classid值，观察student中相应值classid的变化，改进后的触发器见书。7.5 临时表和表变量在处理一个复杂的业务逻辑的过程中，其数据源可能是一些数据表，经过若干个步骤最终得到一个结果表，在每一个中间步骤中，可能需要产生一些中间结果表，作为后一步骤的输入，最终的结果表和中间的结果表可能都具有以下两个特征：数据的临时性，在得到最终结果表后，就不再需要这些中间结果表数据，而在最终结果被使用后（如查询后），最终结果表的数据也将不再需要保留。数据的独立性，对两个不同的数据库连接，执行同一个程序，其中间结果和最终结果互不相关，即两个用户同时调用了一个程序，由于输入条
28、件的不同，算法过程中的中间结果和最终结果也互不相同。7.5.1.1临时表使用CREATE语句创建数据表时，数据表名以一个“#”开头，则SQL Server就把该表处理为本地临时表，本地临时表具有以下特点：l生存期：所有在和数据库连接期间创建的本地临时表在连接断开时自动被删除，对存储过程建立的本地临时表，当存储过程结束时，将自动删除这些本地临时表。l本地临时表之间的独立性：同一个程序多个用户同时执行，程序中所创建的本地临时表将是不同的。l存储过程中的作用域：由创建本地临时表的存储过程以及该存储过程所调用的存储过程都可以引用该临时表，但调用创建此表的存储过程不能引用该表。本地临时表的第1和第2个特
29、点，和中间结果表和最终结果表的临时性和独立性的需求特征是完全一致的二）全局临时表使用CREATE语句创建数据表时，数据表名以两个“#”开头，则SQL Server就把该表处理为全局临时表，全局临时表具有以下特点：l生存期：全局临时表在创建此表的连接断开并且其他连接停止对其引用时被自动删除。l作用域：全局临时表在生存期内，所有与数据库的连接都能访问到它。l全局性：在不同的与数据库的连接下，不允许重复创建同名的全局临时表，所有连接对同名的全局临时表的读写操作针对的将是同一个数据表。7.5.1.2表变量表变量可用于函数、存储过程和批处理中，用于存储结果集以供后续处理，同普通变量一样，表变量的作用域为
30、声明该变量的函数、存储过程或批处理内。在作用域内，表变量可像常规表一样用INSERT、UPDATE和DELETE和SELECT语句插入、更新、删除和和查询。表变量的声明同局部变量的声明一样，如：DECLARE AllGrade TABLE (Id CHAR(6) PRIMARY KEY,Name VARCHAR(20), subName VARCHAR(20),Grade INT)可将函数返回类型定义为TABLE类型，返回TABLE类型的函数称为表值函数表变量与临时表差异：表变量的作用域比本地临时表更小，由此：l在存储过程中，使用表变量相对于临时表，减少了重新编译量。l表变量的操作对其他资源的
31、锁定时间就比临时表要少。T-SQL没有提供类似其他高级语言中的数组和结构类型，在程序设计中当需要使用类似数组或结构类型的变量时，可考虑使用表变量7.5.1.3 临时表和表变量应用实例要求对月累计销售数量进行汇总查询一）使用批处理及本地临时表l创建与月销售汇总表同结构的本地临时表l汇总产生各月的商品累计销售数，并把这些数据插入到临时表中l汇总产生个各月的年累计销售数，用这些数据更新临时表中的年累计销售数列用临时表实现月销售汇总表的输出在技术难度上要低于用视图和SELECT语句 所有商品的月销售汇总数据，适合数据量不大的情况，能使用户在切换商品的时候，系统能快速作出反应二）使用存储过程和全局临时表
32、用存储过程实现上列程序，由于存储过程执行完毕，该临时表就被消除，客户端程序就无法获得这些数据。所以若使用存储过程就必须使用全局临时表，上述程序要做如下修改：l临时表的表名前再加一个“#”号表示为全局临时表。l建立全局临时表前检查该表是否存在，如存在则不再创建该表。l判断要获取的数据是否已经在全局临时表中存在，若已经存在，就不用再产生这些数据。全局临时表使得两个查询同样数据的用户，只需要第一个用户运行生成数据的程序，第二个用户将能坐享其成地获得已生成的数据。7.6 游标由查询语句可以得到的是一个结果集，我们有时需要对此结果集进行逐行处理，游标则提供了这种机制。游标是系统为用户开设的一个数据缓冲区
33、，存放SELECT语句的执行结果，每个游标区都有一个名字，用户可以用FETCH语句逐一从游标中获取行，并把行的列值赋给变量。使用游标的一般步骤是：声明游标、打开游标、移动游标指针并取得当前行数据、关闭和释放游标。1. 说明游标使用DECLARE语句语句格式DECLARE CURSOR FOR 功能l是一条说明性语句，这时DBMS并不执行SELECT指定的查询操作。2. 打开游标使用OPEN语句语句格式OPEN 功能l打开游标实际上是执行相应的SELECT语句，把所有满足查询条件的记录从指定表取到缓冲区中l这时游标处于活动状态，指针指向查询结果集中第一条记录之前3.移动游标指针并取的当前记录数据
34、使用FETCH语句语句格式FETCH NEXT|PRIOR|FIRST|LAST FROM INTO ,.功能l指定方向移动游标指针，然后将缓冲区中的当前记录取出来赋给变量。lNEXT|PRIOR|FIRST|LAST：指定推动游标指针的方式。l NEXT：向前推进一条记录l PRIOR：向回退一条记录l FIRST：推向第一条记录l LAST：推向最后一条记录l 缺省值为NEXT说明(1) 变量必须与SELECT语句中的目标列表达式具有一一对应关系(2) FETCH语句通常用在一个循环结构中，通过循环执行FETCH语句逐条取出结果集中的行进行处理(3)通过检测全局变量FETCH_STATUS
35、的值控制循环结束：若前一个FETCH成功取到行数据则变量值为0，当取到最后一行数据后再执行FETCH，则该变量值为-1。4. 关闭游标和释放游标使用close语句关闭游标，释放当前结果集并且解除定位游标的行上的游标锁定，被关闭的游标可再次被打开。使用deallocate语句删除游标引用，组成该游标的数据结构由系统释放。不使用deallocate，再次declare这个游标时，将出现“游标已存在”的错误。例：给学生分配参观券，方法为独坐同学得一张票，两个邻坐同学只分配一张票，分配原则为：若邻坐同学为异性，则分配给女同学，否则分配给平均成绩高的一个，若平均成绩相同，则分配两张票。（在student
36、s中增加ticket列，类型为bit,0为初始状态，1表示得到票子）使用存储过程及游标create procedure ticketasbegindeclare id char(6)declare id_side char(6)declare sex bitdeclare sex_side bitdeclare avggrade integerdeclare avggrade_side integerdeclare cur_student cursor for select stdid,sideid,stdsex,avggrade from studentopen cur_studentfet
37、ch next from cur_student into id,id_side,sex,avggradeupdate student set ticket=0 -恢复恢复ticket为初始状态为初始状态while FETCH_STATUS = 0 -若前一若前一fetch取到行数据值为取到行数据值为0，取到最后一行数据后再运行取到最后一行数据后再运行fetch，则该值为则该值为-1beginselect sex_side=stdsex,avggrade_side=avggrade from student where stdid=id_side -获得邻座获得邻座同学信息同学信息if id_
38、side is null or (sexsex_side and sex=1) or (sex=sex_side and avggrade=avggrade_side)update student set ticket=1 where stdid=idfetch next from cur_student into id,id_side,sex,avggrade -取下一行数据取下一行数据endclose cur_studentdeallocate cur_studentend/运行运行execute ticket判断语句解释：1) if id_side is null or (sexsex_
39、side and sex=1) or (sex=sex_side and avggrade=avggrade_side)(邻座为空邻座为空) or (和邻座不同性且为女同学和邻座不同性且为女同学) or (和邻座和邻座同性且成绩大于等于邻座同学同性且成绩大于等于邻座同学)，在此条件下，当前记在此条件下，当前记录对应的同学得到票子。录对应的同学得到票子。2) while FETCH_STATUS = 0全局变量全局变量Fetch_status：0：FETCH 语句成功；语句成功；-1：FETCH 语句失败或此行不在结果集中；语句失败或此行不在结果集中；-2：被提取：被提取的行不存在。的行不存在。
40、思考：对邻座同学为同性且平均成绩相同的情况为何在程序中未反映？程序是否有漏洞？学生A和为邻座，在决定是否可得票的同时，是否得票也已确定，是否可改进程序使循环次数减少到原来的一半？平均成绩事实上可根据表grade计算得到，若不使用students中avggrade列，如何修改程序？7.7 事务7.7.1事务定义方法及基本特性使用事务可以保证一组SQL语句的执行，要么全部成功，若有一句语句不成功，则全部语句均不执行。事务的开始使用BEGIN TRANSACTION事务的结束可以是：lCOMMIT：提交，即所有语句均执行lROLLBAK：回滚，即取消所有语句实例：在查询分析器中打开一个连接，选择DE
41、MO数据库，然后输入下面的批处理程序更改供应商的编号：START TRANSACTIONUPDATE Supplier SET SuppilerId=SH0002 WHERE SuppilerId=SH0001UPDATE BuySummary SET SupplierId=SH0002 WHERE SuppilerId=SH0001执行上列的批处理程序，执行完毕后，继续执行：ROLLBACKSELECT * FROM SupplierSELECT * FROM BuySummary查询的结果Supplier和BuySummary两个表的数据没有发生变化。同样重复上述过程，但把ROLLBACK
42、改成COMMIT，则最后查询结果Supplier和BuySummary两个表的数据被改变。事务的特性：原子性（Atomicity）：事务是不可分割的逻辑工作单位 一致性（Consistency）：事务在完成时，必须使所有的数据都保持一致状态隔离性（Isolation）：在并发执行情况下，一个事务的执行不能被其他事务所干扰持续性（Durability ）：事务完成之后，它对于系统的影响是永久性的，即使当系统或介质发生故障时，已提交事务的更新也不能被丢失7.7.2 加锁数据库管理系统通常使用加锁技术确保在并发情况下事务完整性和数据一致性为数据对象加锁可以防止用户读取正在由其他用户更改的数据，也可以
43、防止多个用户同时更改相同数据。 数据库管理系统会自动根据对数据对象不同的操作确定以某种方式锁定被操作的数据对象 一）锁的类型共享锁：若事务T对数据对象A加上共享锁，则其它事务只能再对A加共享锁，而不能加排它锁，直到T释放A上的共享锁，通常被用于数据查询。排它锁：若事务T对数据对象A加上排它锁，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。通常被用于数据修改二） 锁的封锁粒度加锁的对象可以是数据的逻辑单元或物理单元，逻辑单元可以包括列、行、表、索引及整个数据库。物理单元包括页（数据页或索引页）和块等。封锁对象的大小称为封锁的粒度，在一个系统中同时支持多种封锁粒度供不同的事务选择称为
44、多粒度封锁。封锁粒度越大，系统被封锁的对象就越少，系统并发度也越小，系统开销也越小，反之则相反三）等待锁释放的方式当要操作的数据对象被其它事务加锁而使当前操作无法进行时，我们也可以称该操作被阻塞，可以让数据库服务器选择以下一种方式处理阻塞的操作：l不断检测锁是否被释放，一旦释放则执行已被阻塞的操作l在限定的时间内检测锁是否被释放，一旦释放则执行已被阻塞的操作，若到达限定时间仍未解锁，则返回锁请求超时错误信息。l直接返回锁请求超时错误信息7.7.3 隔离级别隔离是数据库管理系统针对并发事务间的冲突提供的安全保证，通过为数据对象加锁的方法在并发执行的事务间提供不同级别的分离SQL Server提供
45、四种事务间的隔离级别，它们分别是：l提交读（READ COMMITTED）l未提交读（READ UNCOMMITTED）l可重复读（REPEATABLE READ）l可串行读（SERIALIZABLE） 隔离级别是针对某个连接，不同的连接可以有不同的隔离级别。 一）提交读（READ COMMITTED）设置命令为：SET TRANSACTION ISOLATION LEVEL READ COMMITTED 为SQL Server默认的隔离级别。其基本特征是事务读取的数据对象始终是被提交的数据，而不会是被某个事务修改但还未提交的数据查询某个数据对象时，系统将在该数据对象上加共享锁；修改数据对象时
46、，则加排它锁，事务结束后释放锁存在不可重复读和幻想读问题二）未提交读（READ UNCOMMITTED）设置命令为： SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED四个隔离级别中限制最小的级别，基本特征是事务读取的不一定是数据库中的数据，而可能是被其他并发事务修改但还没有提交的数据当某个事务查询某个数据对象时，不对该数据加锁，在修改数据对象时，不对数据对象加共享锁，也不遵守排它锁，即事务结束前不允许其他并发事务修改该数据对象，但允许其他事务读取该数据对象，并且读取的为本事务未提交的数据。存在脏读（Dirty Read）问题三）可重复读（REPE
47、ATABLE READ）设置命令为： SET TRANSACTION ISOLATION LEVEL REPEATABLE READ基本特征是一个事务中两次相同查询，第二次查询结果一定包含第一次的查询结果，但也可能包含其它并发事务新插入的数据。事务A中执行了查询，则查询所使用的所有数据将加共享锁直至事务结束，以防止在该事务结束前其他并发事务更新或删除这些数据，但允许其他事务新增数据避免了提交读隔离级别中不可重复读的问题。四）可串行读（SERIALIZABLE）设置命令为： SET TRANSACTION ISOLATION LEVEL SERIALIZABLE可串行读的基本特征事务中两次相同查
48、询，其结果完全相同。假设事务A中执行了查询，则查询所使用的所有数据将被锁定，以防止其他事务更新或删除这些数据，同时不允许其他并发事务新增（插入）可能改变事务A中查询结果的数据 7.7.4SQL Server的事务管理模式SQL Server提供了三种事务模式 ：自动提交模式：SQL Server的默认事务管理模式。每个 Transact-SQL 语句在完成时，若成功则被提交，若发生错误，则回滚该语句。 显式事务模式：即事务以BEGIN TRANSACTION开始，以COMMIT或ROLLBACK结束，结束事务后，返回到自动提交模式 。隐性事务模式：l启动和关闭：SET_IMPLICIT_TRA
49、NSACTIONS ON/OFF关闭后回到自动提交模式 l在提交或回滚当前事务后自动启动新事务l当 SQL Server 首次执行下列任何语句时，都会自动启动一个事务，这些语句是：ALTER TABLE、INSERT、CREATE、OPEN、DELETE、REVOKE、DROP、SELECT、FETCH、TRUNCATE TABLE、GRANT和UPDATE。在发出 COMMIT 或 ROLLBACK 语句之前，该事务将一直保持有效 7.7.5 事务应用实例在存储过程执行中，若其中某SQL语句执行失败，将不影响该语句前后的其他SQL语句的执行，这将威胁数据逻辑上的一致性，所以必须增加事务控制。
50、验证：可以在上节存储过程的合适的位置加入将导致错误的语句insert into students (id) values (000001)，假设students中已存在000001学号学生，然后运行后观察各行的students.ticket值全局变量error的使用一个SQL 语句的执行时，如果语句执行成功，则 ERROR 设置为 0。若出现一个错误，ERROR 返回此错误信息代码，直到另一条SQL 语句被执行。可以在master数据库中的 sysmessages 系统表中查看与 ERROR 错误代码相关的文本信息。由于 ERROR 在每一条语句执行后被清除并且重置，应在语句验证后立即检查它，
51、或将其保存到一个局部变量中以备事后查看。增加事务控制create procedure ticketasbegindeclare id char(6)declare id_side char(6)declare sex bitdeclare sex_side bitdeclare avggrade integerdeclare avggrade_side integerdeclare haveerror bit -记录执行记录执行SQL是否有错是否有错set haveerror=0declare cur_student cursor for select stdid,sideid,stdsex,
52、avggrade from studentopen cur_studentfetch next from cur_student into id,id_side,sex,avggradebegin transaction -事务开始事务开始update student set ticket=0 -恢复恢复ticket为初始状态为初始状态if error0 set haveerror=1while FETCH_STATUS = 0 and haveerror=0 begin select sex_side=stdsex,avggrade_side=avggrade from student wh
53、ere stdid=id_side if id_side is null or (sexsex_side and sex=1) or (sex=sex_side and avggrade=avggrade_side) begin update student set ticket=1 where stdid=id if error0 begin set haveerror=1 break end endfetch next from cur_student into id,id_side,sex,avggrade endif haveerror=1 rollbackelse commitclo
54、se cur_studentdeallocate cur_studentend使用select语句完成得票同学的编号和姓名的查询，比较和以上做法的优缺点：建立视图：（每个学生及邻座学生信息）create view v_stdpair (stdid,stdname,stdsex,avggrade, sideid,sexside,avggradeside) as select a.stdid,a.stdname,a.stdsex,a.avggrade,a.sideid, b.stdsex,b.avggrade from student a left join student b on a.sideid=b.stdidselect stdid,stdname from v_stdpair where (stdsex=1 and sexside=0) or (sex=sexside and avggrade=avggradeside) or idside is null
