数据库行为特征设计-SQL程序设计.ppt

《数据库行为特征设计-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流程控制语句一）分支语句IF,IF BEGIN.

4、n END 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-计算id=8结点的

5、层数SET Lev=0WHILE 1=1BEGIN SET Id=(SELECT PId FROM GoodsClass1 WHERE Id=Id)IF Id IS NULL BREAK ELSE SET Lev=Lev+1ENDPRINT Lev,7.2 函数和表达式,函数是存储在数据库中可供其他程序调用的SQL程序，其基本特征是具有返回值根据返回类型的不同，SQL Server提供了返回单值的标量函数和返回一个表的表值函数两种类型的函数。函数创建后就永久存在于数据库中，直到使用删除语句删除它。SQL Server提供了大量的内置函数即标准函数供用户调用。表达式是符号与运算符的组合，SQL

6、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，如下列条件判断均为F

7、ALSE：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 Student,2）格式二CASEWHEN 逻辑表达式iTHEN 结果表达式i.nELSE 表达式n+1END例：SELECT a.

8、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.EleId,7.2.1.2标准函数,SQL Server提供了大量的各类标准函数SELECT语句中通过使用函数和函数的嵌套，可使我们用一个SELECT语句实现比较复杂

9、的查询需求,一）常用函数介绍,常用日期类型函数：GETDATE()YEAR(date)DATEDIFF(datepart,startdate,enddate)DATEADD(datepart,number,date)DATEPART(datepart,date),常用字符类型的函数LTRIM/RTRIM(character_expresion)REPLICATE(character_expression,integer_expression)LEN(string_expression)SUBSTRING(expression,start,length)CHARINDEX(expression1

10、,expression2,start_location)REPLACE(string_expression1,string_expression2,string_expression3)LEFT(character_expression,integer_expression),类型转换和其他函数 CONVERT(data_type(length),expression,style)STR(float_expr,length,decimal)ISNULL(check_expression,replacement_value)POWER(numeric_expression,y),二）函数的应用实

11、例,把Supplier中供应商名称SuppName中包含“南市区”的全部改成“黄埔区”：UPDATE Supplier SET SuppName=REPLACE(SuppName,南市区,黄埔区)如果供应商名称前必须冠以所在省市，并以“-”与后面的供应商名分割，要求分列查询供应商所在省市和供应商名 SELECT SUBSTRING(SuppName,1,CHARINDEX(-,SuppName)-1),SUBSTRING(SuppName,CHARINDEX(-,SuppName)+1,LEN(SuppName)-CHARINDEX(-,SuppName)FROM Supplier,输出学生的

12、学号、姓名以及年龄，年龄的计算方法为：如当前日期的“月日”小于出生日期的“月日”，年龄当前年份出生年份1如当前日期的“月日”大于等于出生日期的“月日”，年龄当前年份出生年份SELECT StdId,StdName,YEAR(GETDATE()-YEAR(Birthday)-CASEWHEN LEFT(CONVERT(CHAR(10),Birthday,101),5)LEFT(CONVERT(CHAR(10),GETDATE(),101),5)THEN 1ELSE 0ENDFROM Student,查询“09”开头的下一个可用的学号，假设学号总长为5位字符，下一个可用编号就是“09”开头的学号中

13、后三位的最大编号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语句）END函数一旦创建，就永久存在，直到使用DROP FUNCTION删除它,函数选项：ENCRYPTION 和SCHEMABINDING,ENCRYPTION：

14、加密该函数体中的内容，使用任何工具都无法看到函数体程序。SCHEMABINDING：将函数绑定（Binding）到它所引用的数据库对象，即不能更改或除去该函数所引用的数据库对象，避免由于删除或修改了函数所引用的数据对象，而使函数无法正常运行。,例：供应商名称格式化,要求：Supplier表中供应商名为英文，要求对其格式化，每个单词的第一字母大写，若两个单词之间出现多于一个空格，则去除多余的空格。创建一个函数，该函数参数为字符串，返回的是根据要求格式化后的字符串假设函数名为FormatStr，则对供应商名的格式化语句为：UPDATE Supplier Set SuppName=dbo.Forma

15、tStr(SuppName)以sa登录数据库，创建所有对象包括函数都属于一个特定的数据库用户dbo（DataBase Owner），dbo是SQL Server为每个数据库预置的数据库用户，在引用函数的时候必须在函数名前加“dbo.”,7.2.3表值函数,表值函数可以返回一个表，即其返回类型是table类型表值函数分为单语句表值函数和多语句表值函数 如果一个查询比较复杂，通常我们可以把它定义成一个视图，如果查询包含了参数，一般情况下我们仍能使用视图实现，但可能会使视图对应的查询语句变得更为复杂，这个时候就可以考虑使用单语句表值函数。如果一个查询根本无法用一个SELECT语句完成时，就可以考虑使

16、用多语句表值函数，即用一段程序来获得查询数据,7.2.3.1 单语句表值函数,创建单语句表值函数的语句为：CREATE FUNCTION 函数名(参数名 参数类型=默认值,.n)RETURNS TABLE WITH ASRETURN 查询语句该函数将返回执行“查询语句”后得到的结果集，可以使用下列语句获得结果：SELECT*FROM 函数名(参数表),例:查询某个学生选修各门课的名称和成绩,建立单语句表值函数CREATE FUNCTION AllGrade(StdId char(6)RETURNS table ASRETURN SELECT c.EleName,b.GradeFROM Stud

17、ent 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”课程的情况，结果中包括学号和课程名，对没有选修该课程的学生，课程名为NULL,定义表值函数：CREATE FUNCTION Student_Some_Elective(EleId CHAR(6)RETURNS TABLEASRE

18、TURN 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.EleId,7.2.3.2多语句表值函数,创建多语句表值函数的语句为：CREATE FUNCTION 函数名(参数名 参数类型=默认值,.n)RETURNS 表变量名 TABLE WITH ASBEGIN 函数体E

19、ND函数体内可包含一个由多个语句组成的程序，该程序完成获取最终需要的数据，并把它插入或更新到“表变量名”表示的表中，在执行RETURN时，该表数据将被作为函数的结果返回。,例：查询某个班学生选修的各门课的及格和不及格人数,查询结果包括：课程号、课程名、及格人数和不及格人数CREATE FUNCTION ElectiveGradeSummary(ClassId CHAR(6)RETURNS PassNumSummaryTABLE(ElectiveId CHAR(6),EleName VARCHAR(20),PassNum INT,NotPassNum INT)ASBEGIN-插入班级学生选修的课

20、程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=ClassId)-计算及格人数UPDATE PassNumSummary SET PassNum=(SELECT count(*)FROM Student_Elective WHERE EleId=Electiv

21、eId AND Grade=60 AND StdId IN(SELECT StdId FROM Student WHERE ClassId=ClassId)-计算不及格人数UPDATE PassNumSummary SET NotPassNum=(SELECT count(*)FROM Student_Elective WHERE EleId=ElectiveId AND Grade60 AND StdId IN(SELECT StdId FROM Student WHERE ClassId=ClassId)RETURN END查询班号为“09001”班级中各门选修课的及格和不及格人数的查询语

22、句为：SELECT*FROM ElectiveGradeSummary(09001),7.3 存储过程,若一个算法最终返回一个标量，可以使用标量函数若一个算法要返回一个表，可直接使用查询语句，使用视图或使用表值函数，若一个算法要返回多于一个标量或不需要返回任何值，则可考虑使用存储过程。,存储过程,存储过程是被存储在数据库中的可以接受和返回用户提供参数的SQL程序存储过程在创建时被编译和优化，创建后可被其他存储过程、函数调用。客户端的应用程序也可通过向数据库服务器提交EXECUTE命令调用存储过程。存储过程被第一次调用后，将驻留在内存中，所以执行效率高，存储过程运行于数据库服务器端，其数据来源及

23、输出结果通常存储在一台计算机上，与在客户机上实现相同算法相比，可以大大减少了客户机和服务器之间的通信量。,7.3.1存储过程的创建和调用,一）创建存储过程命令格式为：CREATE PROCEDURE 存储过程名参数名 参数类型=默认值 OUTPUT,.nWITH RECOMPLE|ENCRIPTION|RECOMPILE,ENCRIPTION AS SQL语句,.nOUTPUT：该参数在存储过程退出后，其值将返回至调用程序，在调用存储过程时也要使用关键字OUTPUT。RECOMPLE：创建的存储过程在每次运行时都将被重新编译。,二）调用存储过程,命令格式 EXEC UTE 返回状态=存储过程名

24、 参数=值|变量 OUTPUT|DEFAULT,.n WITH RECOMPILE SQL Server存储过程调用时，参数必须为常数或变量，但不能是表达式或函数，假设PROC是一个存储过程，以下调用都是错误的：EXECUTE PROC a+1EXECUTE PROC ROUND(a)WITH RECOMPILE的作用是在运行存储过程前对存储过程重新编译，若创建存储过程时已包含该选项，则此处不必再打开此选项。,三）在查询分析器中调试存储过程,SQL Server的查询分析提供了对存储过程的运行跟踪机制。在左侧的对象浏览器窗口中，右击要调试的储存过程，在弹出的快捷菜单中选择“调试”命令，该存储过

25、程的源程序将出现在窗口中，并出现一个用于跟踪调试的工具栏。包含了设置断点、单步跟踪、运行到光标处等几乎所有的常用的跟踪调试功能，在程序下方列出相关的全局和局部变量的当前取值。,四）删除和修改存储过程,删除存储过程的语句格式：DROP PROCEDURE 过程名,.n修改存储过程的语句格式：ALTER PROCEDURE.(余下部分格式同CREATE PROCEDURE)使用ALTER PROCEDURE将覆盖原存在于数据库中同名的存储过程。,例：编写一个存储过程，参数为班号和课程号，通过存储过程的输出（OUTPUT）参数获得指定班级和课程的及格和不及格人数,存储过程：CREATE PROCED

26、URE GetPassNumEleId CHAR(6),ClassId CHAR(6),PassNum INTEGER OUTPUT,NotPassNum INTEGER OUTPUTASSELECT PassNum=count(*)FROM Student_Elective a JOIN Student b ON a.StdId=b.StdIdWHERE a.EleId=EleId AND b.ClassId=ClassId AND Grade=60SELECT NotPassNum=count(*)FROM Student_Elective a JOIN Student b ON a.St

27、dId=b.StdIdWHERE a.EleId=EleId AND b.ClassId=ClassId AND Grade60RETURN调用该存储过程的批处理程序为：DECLARE PassN INT,NotPassN INTEXECUTE GetPassNum ele001,0901,PassN OUTPUT,NotPassN OUTPUTSELECT PassN,NotPassN,7.4 触发器,商场的总经理希望能动态地看到各类商品当日实时的销售总额，实现这个功能可有两种方法：按一定的时间间隔用查询语句汇总各类商品当日的销售额在商品表（Goods）中增加一个存放“当日销售额（SaleA

28、mt）”的列，每一笔销售数据存入数据库时，更新商品表相应商品的“当日销售额（SaleAmt）”第二种方法把汇总工作分散到每笔销售中，提高了查询的效率第二种方法可用触发器来实现，触发器是一种特殊的存储过程，当对指定表执行指定的数据修改语句时自动执行。建立一个触发器，在对销售表实施插入操作时触发，触发的程序就是实现把插入到销售表的某个商品的销售额累加到商品表中的SaleAmt中。,7.4.1创建触发器,建立触发器完整的语句格式为：CREATE TRIGGER 触发器名 ON 表|视图 WITH ENCRYPTION FOR|AFTER|INSTEAD OF DELETE,INSERT,UPDATE

29、 AS IF UPDATE(列)AND|OR UPDATE(列).n|IF(COLUMNS_UPDATED()位运算符 被更新的位掩码)比较运算符 列的位掩码.n SQL 语句.n,7.4.2触发器应用实例,利用触发器可以：实现合计数同步实现有条件的关联插入实现引用表外码的置空操作实现对多数据源视图的删除操作实现对多数据源视图的更新操作,一）合计数同步,CREATE TRIGGER Tri_SaleDetail ON SaleDetail FOR INSERTASBEGIN UPDATE Goods SET SaleAmt=SaleAmt+(SELECT SaleQty*SalePrice F

30、ROM INSERTED)WHERE GoodsNo=(SELECT GoodsNo FROM INSERTED)END,二）有条件的关联插入,CREATE TRIGGER Tri_StudentON StudentFOR INSERT ASIF(SELECT ClassId FROM INSERTED)=0901INSERT INTO Student_ElectiveSELECT a.StdId,b.EleId,NULL FROM INSERTED a,Elective b,三）实现引用表外码的置空操作,在删除或修改了某个班的班号后，属于该班的学生的班号设置为空。可尝试建立下列触发器：CRE

31、ATE TRIGGER Tri_ClassON Class FOR DELETE,UPDATEASIF UPDATE(ClassId)UPDATE Student SET ClassId=NULL WHERE ClassId=(SELECT ClassId FROM DELETED)可删除或修改class表中的classid值，观察student中相应值classid的变化，改进后的触发器见书。,7.5 临时表和表变量,在处理一个复杂的业务逻辑的过程中，其数据源可能是一些数据表，经过若干个步骤最终得到一个结果表，在每一个中间步骤中，可能需要产生一些中间结果表，作为后一步骤的输入，最终的结果表和

32、中间的结果表可能都具有以下两个特征：数据的临时性，在得到最终结果表后，就不再需要这些中间结果表数据，而在最终结果被使用后（如查询后），最终结果表的数据也将不再需要保留。数据的独立性，对两个不同的数据库连接，执行同一个程序，其中间结果和最终结果互不相关，即两个用户同时调用了一个程序，由于输入条件的不同，算法过程中的中间结果和最终结果也互不相同。,7.5.1.1临时表,使用CREATE语句创建数据表时，数据表名以一个“#”开头，则SQL Server就把该表处理为本地临时表，本地临时表具有以下特点：生存期：所有在和数据库连接期间创建的本地临时表在连接断开时自动被删除，对存储过程建立的本地临时表，当

33、存储过程结束时，将自动删除这些本地临时表。本地临时表之间的独立性：同一个程序多个用户同时执行，程序中所创建的本地临时表将是不同的。存储过程中的作用域：由创建本地临时表的存储过程以及该存储过程所调用的存储过程都可以引用该临时表，但调用创建此表的存储过程不能引用该表。本地临时表的第1和第2个特点，和中间结果表和最终结果表的临时性和独立性的需求特征是完全一致的,二）全局临时表,使用CREATE语句创建数据表时，数据表名以两个“#”开头，则SQL Server就把该表处理为全局临时表，全局临时表具有以下特点：生存期：全局临时表在创建此表的连接断开并且其他连接停止对其引用时被自动删除。作用域：全局临时表

34、在生存期内，所有与数据库的连接都能访问到它。全局性：在不同的与数据库的连接下，不允许重复创建同名的全局临时表，所有连接对同名的全局临时表的读写操作针对的将是同一个数据表。,7.5.1.2表变量,表变量可用于函数、存储过程和批处理中，用于存储结果集以供后续处理，同普通变量一样，表变量的作用域为声明该变量的函数、存储过程或批处理内。在作用域内，表变量可像常规表一样用INSERT、UPDATE和DELETE和SELECT语句插入、更新、删除和和查询。表变量的声明同局部变量的声明一样，如：DECLARE AllGrade TABLE(Id CHAR(6)PRIMARY KEY,Name VARCHAR

35、(20),subName VARCHAR(20),Grade INT)可将函数返回类型定义为TABLE类型，返回TABLE类型的函数称为表值函数,表变量与临时表差异：,表变量的作用域比本地临时表更小，由此：在存储过程中，使用表变量相对于临时表，减少了重新编译量。表变量的操作对其他资源的锁定时间就比临时表要少。T-SQL没有提供类似其他高级语言中的数组和结构类型，在程序设计中当需要使用类似数组或结构类型的变量时，可考虑使用表变量,7.5.1.3临时表和表变量应用实例,要求对月累计销售数量进行汇总查询一）使用批处理及本地临时表创建与月销售汇总表同结构的本地临时表汇总产生各月的商品累计销售数，并把这

36、些数据插入到临时表中汇总产生个各月的年累计销售数，用这些数据更新临时表中的年累计销售数列用临时表实现月销售汇总表的输出在技术难度上要低于用视图和SELECT语句 所有商品的月销售汇总数据，适合数据量不大的情况，能使用户在切换商品的时候，系统能快速作出反应,二）使用存储过程和全局临时表,用存储过程实现上列程序，由于存储过程执行完毕，该临时表就被消除，客户端程序就无法获得这些数据。所以若使用存储过程就必须使用全局临时表，上述程序要做如下修改：临时表的表名前再加一个“#”号表示为全局临时表。建立全局临时表前检查该表是否存在，如存在则不再创建该表。判断要获取的数据是否已经在全局临时表中存在，若已经存在

37、，就不用再产生这些数据。全局临时表使得两个查询同样数据的用户，只需要第一个用户运行生成数据的程序，第二个用户将能坐享其成地获得已生成的数据。,7.6 游标,由查询语句可以得到的是一个结果集，我们有时需要对此结果集进行逐行处理，游标则提供了这种机制。游标是系统为用户开设的一个数据缓冲区，存放SELECT语句的执行结果，每个游标区都有一个名字，用户可以用FETCH语句逐一从游标中获取行，并把行的列值赋给变量。使用游标的一般步骤是：声明游标、打开游标、移动游标指针并取得当前行数据、关闭和释放游标。,1.说明游标,使用DECLARE语句语句格式DECLARE CURSOR FOR 功能是一条说明性语句

38、，这时DBMS并不执行SELECT指定的查询操作。,2.打开游标,使用OPEN语句语句格式OPEN 功能打开游标实际上是执行相应的SELECT语句，把所有满足查询条件的记录从指定表取到缓冲区中这时游标处于活动状态，指针指向查询结果集中第一条记录之前,3.移动游标指针并取的当前记录数据,使用FETCH语句语句格式FETCH NEXT|PRIOR|FIRST|LAST FROM INTO,.,功能指定方向移动游标指针，然后将缓冲区中的当前记录取出来赋给变量。NEXT|PRIOR|FIRST|LAST：指定推动游标指针的方式。NEXT：向前推进一条记录 PRIOR：向回退一条记录 FIRST：推向第

39、一条记录 LAST：推向最后一条记录 缺省值为NEXT,说明(1)变量必须与SELECT语句中的目标列表达式具有一一对应关系(2)FETCH语句通常用在一个循环结构中，通过循环执行FETCH语句逐条取出结果集中的行进行处理(3)通过检测全局变量FETCH_STATUS的值控制循环结束：若前一个FETCH成功取到行数据则变量值为0，当取到最后一行数据后再执行FETCH，则该变量值为-1。,4.关闭游标和释放游标,使用close语句关闭游标，释放当前结果集并且解除定位游标的行上的游标锁定，被关闭的游标可再次被打开。使用deallocate语句删除游标引用，组成该游标的数据结构由系统释放。不使用de

40、allocate，再次declare这个游标时，将出现“游标已存在”的错误。,例：给学生分配参观券，方法为独坐同学得一张票，两个邻坐同学只分配一张票，分配原则为：若邻坐同学为异性，则分配给女同学，否则分配给平均成绩高的一个，若平均成绩相同，则分配两张票。（在students中增加ticket列，类型为bit,0为初始状态，1表示得到票子）,使用存储过程及游标,create procedure ticketasbegindeclare id char(6)declare id_side char(6)declare sex bitdeclare sex_side bitdeclare avggr

41、ade integerdeclare avggrade_side integerdeclare cur_student cursor for select stdid,sideid,stdsex,avggrade from studentopen cur_studentfetch next from cur_student into id,id_side,sex,avggradeupdate student set ticket=0-恢复ticket为初始状态while FETCH_STATUS=0-若前一fetch取到行数据值为0，取到最后一行数据后再运行fetch，则该值为-1begins

42、elect sex_side=stdsex,avggrade_side=avggrade from student where stdid=id_side-获得邻座同学信息if id_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_studentde

43、allocate cur_studentend/运行execute ticket,判断语句解释：,1)if id_side is null or(sexsex_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：被提取的行不存在。,思考：,对邻座同学为同性

44、且平均成绩相同的情况为何在程序中未反映？程序是否有漏洞？学生A和为邻座，在决定是否可得票的同时，是否得票也已确定，是否可改进程序使循环次数减少到原来的一半？平均成绩事实上可根据表grade计算得到，若不使用students中avggrade列，如何修改程序？,7.7 事务7.7.1事务定义方法及基本特性,使用事务可以保证一组SQL语句的执行，要么全部成功，若有一句语句不成功，则全部语句均不执行。事务的开始使用BEGIN TRANSACTION事务的结束可以是：COMMIT：提交，即所有语句均执行ROLLBAK：回滚，即取消所有语句,实例：,在查询分析器中打开一个连接，选择DEMO数据库，然后输

45、入下面的批处理程序更改供应商的编号：START TRANSACTIONUPDATE Supplier SET SuppilerId=SH0002 WHERE SuppilerId=SH0001UPDATE BuySummary SET SupplierId=SH0002 WHERE SuppilerId=SH0001执行上列的批处理程序，执行完毕后，继续执行：ROLLBACKSELECT*FROM SupplierSELECT*FROM BuySummary查询的结果Supplier和BuySummary两个表的数据没有发生变化。同样重复上述过程，但把ROLLBACK改成COMMIT，则最后查

46、询结果Supplier和BuySummary两个表的数据被改变。,事务的特性：,原子性（Atomicity）：事务是不可分割的逻辑工作单位 一致性（Consistency）：事务在完成时，必须使所有的数据都保持一致状态隔离性（Isolation）：在并发执行情况下，一个事务的执行不能被其他事务所干扰持续性（Durability）：事务完成之后，它对于系统的影响是永久性的，即使当系统或介质发生故障时，已提交事务的更新也不能被丢失,7.7.2加锁,数据库管理系统通常使用加锁技术确保在并发情况下事务完整性和数据一致性为数据对象加锁可以防止用户读取正在由其他用户更改的数据，也可以防止多个用户同时更改相

47、同数据。数据库管理系统会自动根据对数据对象不同的操作确定以某种方式锁定被操作的数据对象,一）锁的类型,共享锁：若事务T对数据对象A加上共享锁，则其它事务只能再对A加共享锁，而不能加排它锁，直到T释放A上的共享锁，通常被用于数据查询。排它锁：若事务T对数据对象A加上排它锁，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。通常被用于数据修改,二）锁的封锁粒度,加锁的对象可以是数据的逻辑单元或物理单元，逻辑单元可以包括列、行、表、索引及整个数据库。物理单元包括页（数据页或索引页）和块等。封锁对象的大小称为封锁的粒度，在一个系统中同时支持多种封锁粒度供不同的事务选择称为多粒度封锁。封锁粒度

48、越大，系统被封锁的对象就越少，系统并发度也越小，系统开销也越小，反之则相反,三）等待锁释放的方式,当要操作的数据对象被其它事务加锁而使当前操作无法进行时，我们也可以称该操作被阻塞，可以让数据库服务器选择以下一种方式处理阻塞的操作：不断检测锁是否被释放，一旦释放则执行已被阻塞的操作在限定的时间内检测锁是否被释放，一旦释放则执行已被阻塞的操作，若到达限定时间仍未解锁，则返回锁请求超时错误信息。直接返回锁请求超时错误信息,7.7.3隔离级别,隔离是数据库管理系统针对并发事务间的冲突提供的安全保证，通过为数据对象加锁的方法在并发执行的事务间提供不同级别的分离SQL Server提供四种事务间的隔离级别

49、，它们分别是：提交读（READ COMMITTED）未提交读（READ UNCOMMITTED）可重复读（REPEATABLE READ）可串行读（SERIALIZABLE）隔离级别是针对某个连接，不同的连接可以有不同的隔离级别。,一）提交读（READ COMMITTED）,设置命令为：SET TRANSACTION ISOLATION LEVEL READ COMMITTED 为SQL Server默认的隔离级别。其基本特征是事务读取的数据对象始终是被提交的数据，而不会是被某个事务修改但还未提交的数据查询某个数据对象时，系统将在该数据对象上加共享锁；修改数据对象时，则加排它锁，事务结束后释放

50、锁存在不可重复读和幻想读问题,二）未提交读（READ UNCOMMITTED）,设置命令为：SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED四个隔离级别中限制最小的级别，基本特征是事务读取的不一定是数据库中的数据，而可能是被其他并发事务修改但还没有提交的数据当某个事务查询某个数据对象时，不对该数据加锁，在修改数据对象时，不对数据对象加共享锁，也不遵守排它锁，即事务结束前不允许其他并发事务修改该数据对象，但允许其他事务读取该数据对象，并且读取的为本事务未提交的数据。存在脏读（Dirty Read）问题,三）可重复读（REPEATABLE READ）