第九章_T-SQL编程与应用2

1、数据库实用技术第九章第九章 T-SQLT-SQL编程与应用编程与应用T-SQL概述概述1T-SQL编程基础编程基础2流程控制语句流程控制语句3SQL Server 2008批处理批处理4事务事务5游标游标6数据库实用技术9.4 9.4 批处理批处理v 批处理l 批处理是由一个或多个T-SQL语句组成的 。l 批处理结束的符号或标志是GO 。l 注意：uCreate Default、Create Function、Create Procedure、Create Rule、Create Schema、Create Trigger和 Create View语句不能在批处理中与其他语句组合使用。u不能

2、在同一个批处理中更改表，然后引用新列。第九章第九章 T-SQL编程与应用编程与应用SQL Server 2008数据库实用技术vSQL Server服务器对批处理的处理分为四个阶段：服务器对批处理的处理分为四个阶段： 分析阶段，检查语法，验证合法性分析阶段，检查语法，验证合法性; 优化阶段，确定最有效的方法；优化阶段，确定最有效的方法； 编译阶段，生成执行计划；编译阶段，生成执行计划； 运行阶段，执行语句。运行阶段，执行语句。v批处理最重要的特征就是它作为一个不可分的实体批处理最重要的特征就是它作为一个不可分的实体在服务器上解释和执行。在服务器上解释和执行。数据库实用技术v SQL Serve

3、r有以下几种指定批处理的方法。有以下几种指定批处理的方法。l 应用程序。应用程序。l 存储过程或触发器。存储过程或触发器。l 由由EXECUTE语句执行的字符串是一个批处理，并编译为语句执行的字符串是一个批处理，并编译为一个执行计划。例如，一个执行计划。例如，EXEC (SELECT * FROM teachers)l 由由sp_executesql系统存储过程系统存储过程执行的字符串是一个批处理，执行的字符串是一个批处理，并编译为一个执行计划。并编译为一个执行计划。（执行可以多次重复使用或动态生成的 Transact-SQL 语句或批处理。Transact-SQL 语句或批处理可以包含嵌入参

4、数。运行时编译语句可能会使应用程序受到恶意攻击，例如，SQL 注入。）l 例如：例如：execute sp_executesql NSELECT * from jxgl.dbo.teachers （1） 批处理的指定数据库实用技术v GO是批处理的结束标志。把GO前面的所有语句当成一个批处理来执行。v GO命令和T-SQL语句不可处在同一行上。v 在批处理的第一条语句后执行任何存储过程必须包含EXECUTE关键字。v 局部(用户定义)变量的作用域限制在一个批处理中，不可在GO命令后引用。v RETURN可在任何时候从批处理中退出，而不执行位于RETURN之后的语句。(2) 批处理的结束与退出数

5、据库实用技术批处理批处理l （3）批处理执行出错的处理机制：u编译错误（如语法错误）可使执行计划无法编译。u大多数运行时错误将停止执行批处理中当前语句和它之后的语句。u某些违反约束时错误仅停止执行当前语句，而继续执行批处理中其他所有语句。u在遇到运行时错误的语句之前执行的语句不受影响。第九章第九章 T-SQL编程与应用编程与应用SQL Server 2008数据库实用技术USE BillingSystem1GO - 批处理结束标志CREATE VIEW customer_infoAS SELECT * FROM CustomerGO- CREATE VIEW语句与其他语句隔离SELECT *

6、FROM customer_infoGO补例 创建一个视图，使用GO命令将CREATE VIEW语句与批处理中的其他语句(如USE、SELECT语句等)隔离。去掉go？数据库实用技术为什么需要事务v例如，银行转账问题： 假定资金从账户A转到账户B，至少需要两步： 账户A的资金减少 然后账户B的资金相应增加 账户账户A账户账户B数据库实用技术v假定张三的账户直接转账1000元到李四的账户为什么需要事务CREATE TABLE bank( customerName CHAR(10), -顾客姓名顾客姓名 currentMoney MONEY -当前余额当前余额)GOALTER TABLE bank

7、 ADD CONSTRAINT CK_currentMoney CHECK(currentMoney=1)GOINSERT INTO bank(customerName,currentMoney) VALUES(张三张三,1000)INSERT INTO bank(customerName,currentMoney) VALUES(李四李四,1)创建账户表，存放用户的账户信息创建账户表，存放用户的账户信息添加约束：根据银行规定，账户添加约束：根据银行规定，账户余额不能少于余额不能少于1元，否则视为销元，否则视为销户户 张三开户，开户金额为张三开户，开户金额为1000元元 ；李四开户，开户金额；

8、李四开户，开户金额1元元 数据库实用技术为什么需要事务v目前两个账户的余额总和为：1000+1=1001元 数据库实用技术为什么需要事务v模拟实现转账 ： 从张三的账户转账1000元到李四的账户/*-转账测试：张三转账转账测试：张三转账1000元给李四元给李四-*/-我们可能会这样这样编写语句我们可能会这样这样编写语句-张三的账户少张三的账户少1000元，李四的账户多元，李四的账户多1000元元UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=张三张三UPDATE bank SET currentMoney=cu

9、rrentMoney+1000 WHERE customerName=李四李四GO-再次查看转账后的结果。再次查看转账后的结果。 SELECT * FROM bankGO请问：请问：执行转账语句后，张三、李四的账户执行转账语句后，张三、李四的账户余额为多少？余额为多少？张三的账户没有减少张三的账户没有减少 但李四的账户却多了但李四的账户却多了1000元元 100010012001元元 总额多出了总额多出了1000元！元！数据库实用技术为什么需要事务-张三的账户减少张三的账户减少1000元，李四的账户增加元，李四的账户增加1000元元UPDATE bank SET currentMoney=cu

10、rrentMoney-1000 WHERE customerName=张三张三UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四李四GOv错误原因分析：UPDATE语句违反约束：语句违反约束：余额余额=1元元执行失败，所以张三还是执行失败，所以张三还是1000元元继续往下执行：执行成功，所以李四变为继续往下执行：执行成功，所以李四变为1001元元如何解决呢？使用如何解决呢？使用事务事务数据库实用技术v 例如，企业取消了仓储部，需要将“仓储部”从department表中删除，而employee表中的部门编号与仓

11、储部相对应的员工也应删除。 假设仓储部编号为D004，第一条DELETE语句修改department表为：DELETE FROM department WHERE department_id = D004 第二条DELETE语句修改employee表为：DELETE FROM employee WHERE department_id = D004v 因此，必须保证这两条DELETE语句同时执行，或都不执行。这时可以使用数据库中的事务(Transaction)技术来实现。数据库实用技术 为了解决这种类似的情况，数据库管理系统提出了事务的概念：v 将一组相关操作绑定在一个事务中，为了使事务成功，必

12、须成功完成该事务中的所有操作。v 事务对上面转账问题的解决方法是：把转出和转入作为一个整体，形成一个操作集合，这个集合中的操作要么都不执行，要么都执行。SQL Server 2008数据库实用技术9.5 9.5 事务事务v 9.5.1 事务（Transaction）概念l 事务是完成一个应用处理的最小单元，由一个或多个对数据库操作的语句序列组成。l 事务是一个不可分割的工作单位。要么所有的操作都顺序完成，要么一个也不要做，绝不能只完成了部分操作，还有一些操作没有完成。v 任何对数据的修改都是在事务环境中进行的。v 在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。v 例如

13、，使用DELETE 命令或UPDATE 命令对数据库进行更新时一次只能操作一个表，这会带来数据库的数据不一致的问题。 第九章第九章 T-SQL编程与应用编程与应用SQL Server 2008数据库实用技术事务的四特征(ACID)或四原则l 原子性(Atomicity)：事务是数据库的逻辑工作单位，事务中的操作要么都做，要么都不做。 l 一致性(Consistency)：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。l 隔离性(Isolation)：一事务的执行不能被其它事务干扰。 l 持续性（永久性） (Durability) ：指事务一旦提交，则其对数据库中数据的改变就

14、应该是永久的 。 事务四原则保证了一个事务或者成功提交，或者失败滚回，二者必居其一，因此它对数据的修改具有可恢复性。即当事务失败时，它对数据的修改都会恢复到该事务执行前的状态。数据库实用技术事务事务v 9.5.2 事务分类l 按事务的启动和执行方式分：u自动提交事务：SQL Server 将一切操作作为事务处理，它不会在事务以外更改数据。如果没有用户定义事务，SQL Server会自己定义事务，称为自动提交事务。每条单独的语句都是一个事务。是SQL Server的默认事务管理模式。每个T-SQL语句如果成功地完成，则提交该语句；如果遇到错误，则回滚该语句。只要自动提交模式没有被显式或隐性事务替

15、代，SQL Server连接就以该默认模式进行操作。u显式事务：用户显示定义了事务的启动和结束。事务以BEGIN TRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显式结束。 u隐性事务：在当前事务完成提交或回滚后，新事务自动启动。隐性事务不需要使用BEGIN TRANSACTION语句标识事务的开始，但需要以COMMIT或ROLLBACK语句来提交或回滚事务。使用SET IMPLICIT_TRANSACTIONS ON / OFF语句可以启动/关闭隐式事务模式。SET IMPLICIT_TRANSACTIONS OFF是关闭隐性设置，返回自动提交模式。 第九章第九章 T

16、-SQL编程与应用编程与应用数据库实用技术事务事务v 9.5.3 事务语句l 一个显示事务可以由三个语句来描述，分别为：u开始事务u提交事务u回滚事务第九章第九章 T-SQL编程与应用编程与应用SQL Server 2008数据库实用技术事务事务l 1.开始事务：u显式地指明一个事务的开始。u其语法格式为：BEGIN TRANSACTION 事务名|事务变量名l 2.提交事务：u标志一个事务的结束。u其语法格式为：COMMIT TRANSACTION 事务名|事务变量名第九章第九章 T-SQL编程与应用编程与应用SQL Server 2008数据库实用技术事务事务l 3.事务回滚：u使得事务回

17、滚到起点或指定的保存点处，标志一个事务的结束。u其语法格式为：ROLLBACK TRANSACTION 事务名|事务变量名|保存点名|保存点变量名l 4.设置事务内保存点：u设置事务回滚的保存点。u其语法格式为： SAVE TRANSACTION 保存点名|保存点变量名第九章第九章 T-SQL编程与应用编程与应用SQL Server 2008数据库实用技术事务事务l 5.错误处理语句：uTryCatch语句捕获系统错误。u具体语法格式如下：BEGIN TRY END TRYBEGIN CATCH END CATCH第九章第九章 T-SQL编程与应用编程与应用SQL Server 2008数据库

18、实用技术事务事务uTryCatch语句说明： Try块后必须紧跟相关联的 Catch块。在 End Try和 Begin Catch语句之间不能有任何其他语句。 如果Try块所包含的代码中没有错误，则当Try块中最后一个语句完成运行后，将执行 End Catch语句之后的语句。 如果 Try块所包含的代码中有错误，则跳转到BEGIN CATCH后，执行Catch块语句。 如果End Catch语句是存储过程或触发器的最后一个语句，程序控制将回到调用该存储过程或运行该触发器的语句。 由Catch块捕获的错误不会返回到调用应用程序。 TryCatch构造可以嵌套。第九章第九章 T-SQL编程与应用

19、编程与应用SQL Server 2008数据库实用技术事务事务【例9-11】在计费系统中，将客户“新大白宾馆”的客户ID（CID）改为“20100146”。【分析】在计费系统中，有四个表中含有CID字段：客户信息表、产品表、帐单信息表和支付信息表。为了保证数据修改的一致性，修改CID应该在一事务中进行。第九章第九章 T-SQL编程与应用编程与应用SQL Server 2008数据库实用技术事务事务【例9-11】代码如下：USE BillingSystem1GODECLARE Cust_ID intSELECT Cust_ID=CID FROM Customer WHERE CName=新大白宾

20、馆SELECT * FROM Customer WHERE CName=新大白宾馆BEGIN TRANSACTION- 启动事务BEGIN TRY UPDATE Customer SET CID=20100147 WHERE CID=Cust_ID UPDATE EProduct SET CID=20100147 WHERE CID=Cust_ID UPDATE Bills SET CID=20100147 WHERE CID=Cust_ID UPDATE Payment SET CID=20100147 WHERE CID=Cust_IDEND TRYBEGIN CATCH SELECT E

21、RROR_LINE() AS ERRORLINE,- 返回发生错误的行号 ERROR_MESSAGE() AS ERRORMESSAGE- 返回错误信息 IF TRANCOUNT0 ROLLBACK TRANSACTION- 回滚事务END CATCHIF TRANCOUNT0 COMMIT TRANSACTION- 提交事务SELECT * FROM Customer WHERE CName=新大白宾馆GOSQL Server 2008ERROR_LINE() ERROR_LINE() 数据库实用技术【补例9-1】定义一个事务，向customer表添加一条记录，并设置保存点。然后再删除该记录

22、，并滚回到事务的保存点，提交该事务。USE BillingSystem1GOBEGIN TRANSACTIONINSERT INTO Customer(CID, RID,CName,Cpassword, CRegistrationDate,CType,CStatus)VALUES(20134178,3104178,赖大金,615792,2013/5/22,流动,1)select * from CustomerWHERE CID =20134178SAVE TRANSACTION SAVEPOINT_1DELETE FROM CustomerWHERE CID =20134178ROLLBACK

23、 TRANSACTION SAVEPOINT_1COMMIT TRANSACTIONGOselect * from CustomerWHERE CID =20134178数据库实用技术【例9-2】定义一个事务，向course_score表中添加记录。如果添加成功，则给该位同学加5分，否则不操作。BEGIN TRANSACTIONINSERT INTO course_score (stu_id,cour_id,score)VALUES(200712110111,1210022,58)IF ERROR=0BEGIN PRINT 添加成功UPDATE course_score SET score=s

24、core+5 WHERE stu_id=200712110111COMMIT TRANSACTIONENDELSEBEGIN PRINT 添加失败ROLLBACK TRANSACTIONEND数据库实用技术使用事务时的考虑v1. 事务应该尽可能短。v2. 避免事务嵌套 数据库实用技术9.6 9.6 游标游标v 9.6.1 游标概念l 游标(Cursor) ：u游标是一种处理数据的方法，游标使得SQL Server语言可以逐行处理结果集中的数据；u主要用在存储过程、触发器和 T-SQL脚本中。 u可以把游标看成一种指针，可以指向结果集中的任意位置，它允许用户对指定位置的数据进行处理，可以把结果集

25、中的数据放在数组、应用程序中或其它地方。u游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，才使两个数据处理方式能够进行沟通。第九章第九章 T-SQL编程与应用编程与应用SQL Server 2008数据库实用技术9.6 9.6 游标游标l 游标包括以下两个部分u游标结果集（Cursor Result Set）由定义该游标的SELECT语句返回的行的集合。u游标位置(Cursor Position）指向这个集合中某一行的指针。第九章第九章 T-SQL编程与应用编程与应用SQL Server 2008数据库实用技术游标游标l 游标具有以下优点u允许定位在结果集的特定行。u从结果集

26、的当前位置检索一行或多行。u支持对结果集中当前位置的行进行数据修改。u为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。u提供脚本、存储过程和触发器中使用的访问结果集中的数据的T-SQL语句。l SQL Server 2008支持以下两种请求游标的方法uT-SQL：使用T-SQL语句定义的游标。u数据库应用程序编程接口（Application Programming Interface，API）游标函数。第九章第九章 T-SQL编程与应用编程与应用SQL Server 2008数据库实用技术9.6.2 使用游标使用游标(CURSOR)的步骤如下。声明游标。在使用游标之

27、前，首先需要声明游标。打开游标。打开一个游标意味着在游标中输入了相关的记录信息。获取记录信息。首先将游标当前指向的记录保存到一个局部变量中，然后游标将自动移向下一条记录。将一条记录读入某个局部变量后，就可以根据需要对其进行处理了。关闭游标。释放游标锁定的记录集。释放游标。释放游标自身所占用的资源。数据库实用技术游标游标l 1.声明游标u游标在使用之前，必须先声明。u声明游标语句语法格式如下：DECLARE CURSOR INSENSITIVESCROLL CURSORFOR SELECT语句FOR READ ONLY|UPDATE OF 列名1,nu参数说明： INSENSITIVEINSEN

28、SITIVE：在TempDB中建立创建由该游标使用数据的临时表，并对其进行任何操作。 SCROLLSCROLL：FIRSTFIRST、LASTLAST、PRIORPRIOR、NEXTNEXT（默认）、RELATIVERELATIVE、ABSOLUTEABSOLUTE。 SELECTSELECT语句：定义游标结果集的标准SELECTSELECT语句。 READ ONLYREAD ONLY：定义只读游标，不能修改数据。 UPDATE OF UPDATE OF 列名1,n1,n：定义游标中可更新的列。第九章第九章 T-SQL编程与应用编程与应用SQL Server 2008数据库实用技术表1 Dec

29、lare1 Declare命令中SCROLLSCROLL的取值SCROLL选项选项含义含义FIRST提取游标中的第一行数据提取游标中的第一行数据LAST提取游标中的最后一行数据提取游标中的最后一行数据PRIOR提取游标当前位置的上一行数据提取游标当前位置的上一行数据NEXT提取游标当前位置的下一行数据提取游标当前位置的下一行数据RELATIVE n提取游标当前位置之前或之后的第提取游标当前位置之前或之后的第n行数据行数据(n正，往下，正，往下，n负，往上负，往上)ABSULUTE n提取游标中的第提取游标中的第n行数据行数据数据库实用技术游标游标l 2.打开游标u打开游标使用OPEN语句，其语

30、法格式如下：OPEN GLOBAL |u参数说明：GLOBAL选项是指定其后的游标为全局游标。u注意：u使用OPEN语句打开游标后，游标位置位于查询结果集的第一行。u只能打开已经声明但尚未打开的游标。第九章第九章 T-SQL编程与应用编程与应用SQL Server 2008数据库实用技术游标游标l 3.提取数据u在打开游标后，利用FETCH语句从查询结果集中提取数据。u使用FETCH语句一次可以提取一条记录。uFETCH语句的语法格式如下：FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|nvar|RELATIVE n|nvarFROMGLOBAL |INTO 变量

31、名,n第九章第九章 T-SQL编程与应用编程与应用SQL Server 2008数据库实用技术游标游标uFETCH语句参数说明： NEXT：提取紧跟当前行之后的结果行，并且将当前行向后移一行。 PRIOR：提取紧临当前行前面的一行，并且将当前行向前移一行。 FIRST：提取游标中的第一行并将其设置为当前行。 LAST：提取游标中的最后一行并将其设置为当前行。 ABSOLUTE n|nvar：如果n或nvar为正（负）数，提取从游标头开始（尾之前）的第n行，并将返回的行变成新的当前行；如果n或nvar为0，则没有行返回。n或nvar必须为整型常量。 RELATIVE n|nvar：如果n或nva

32、r为正（负）数，提取当前行之后（前）的第n行，并将返回的行变成新的当前行；如果n或nvar为0，提取当前行。 GLOBAL：指定其后的游标是全局游标。 INTO 变量名列表：允许将提取的列数据保存到局部变量中。第九章第九章 T-SQL编程与应用编程与应用SQL Server 2008数据库实用技术游标游标l 4.关闭游标u关闭游标使用CLOSE语句，其语法格式如下：CLOSE GLOBAL |作用：关闭已打开的游标，之后不能对游标进行读取等操作，但可以使用OPEN语句再次打开该游标。l 5.释放游标u其语法格式如下： DEALLOCATE GLOBAL | 作用： 删除游标与游标名或游标变量之

33、间的联系，并且释放游标占 用的所有系统资源。 删除后不可再用，必须重新声明方可。第九章第九章 T-SQL编程与应用编程与应用SQL Server 2008数据库实用技术表2 fetch_status变量返回值返回值描描 述述0FETCH命令已成功执行命令已成功执行-1FETCH命令失败或者行数据己超出了结果命令失败或者行数据己超出了结果集集-2所读取的数据已经不存在。所读取的数据已经不存在。注意：游标位置决定了结果集中哪一行的数据可以被提取，如果游标方式为FOR UPDATE，则可决定哪一行数据库可以更新或者删除。FETCH_STATUS变量报告上一个FETCH语句的状态，其取值和含义如下：数

34、据库实用技术游标游标【例9-12】利用游标生成商业客户的联系人及联系电话信息表。【分析】由于FETCH语句一次可以从结果集中取出一条记录，但是商业客户可能不止一个。所以，要设计一个循环，将FETCH语句放在循环体内，直到将结果集中的商业客户的联系人信息全部提取完，才跳出循环。第九章第九章 T-SQL编程与应用编程与应用SQL Server 2008数据库实用技术【例9-12】代码如下：DECLARE Cust_id int,Cust_RID int,Cust_name varchar(60),relationer_name varchar(20),relationer_tel char(11)

35、PRINT - 商业客户联系人信息-DECLARE customer_cursor Cursor - 声明游标customer_cursor FOR SELECT CID,RID,CName FROM Customer WHERE CType=商业OPEN customer_cursor- 打开游标customer_cursorFETCH NEXT FROM customer_cursor INTO Cust_id,Cust_RID,Cust_nameWHILE Fetch_Status=0BEGIN PRINT CONVERT(char(8),Cust_id)+ +Cust_name+联系人

36、： DECLARE relationer_cursor Cursor - 声明游标relationer_cursor FOR SELECT RName,RTelephone FROM Relationer WHERE RID=Cust_RIDOPEN relationer_cursor FETCH NEXT FROM relationer_cursor INTO relationer_name,relationer_tel数据库实用技术IF Fetch_Status0 PRINT 无联系人ELSEBEGINWHILE Fetch_Status=0 BEGIN PRINT +relationer

37、_name+，电话：+relationer_telFETCH NEXT FROM relationer_cursor INTO relationer_name,relationer_tel END CLOSE relationer_cursor DEALLOCATE relationer_cursor FETCH NEXT FROM customer_cursor INTO Cust_id, Cust_RID,Cust_name ENDENDCLOSE customer_cursorDEALLOCATE customer_cursor数据库实用技术DECLARE s int,n int,t int,c intSET S=0SET n=1WHILE n=10 BEGIN SET c=1 SET t=1 WHILE c=n BEGIN SET t=t*c SET c=c+1 END SET s=s+t SET n=n+1 ENDSELECT s