1、Transact SQL编程,Transact SQL简介,ANSI SQL的不足 缺乏逻辑控制结构 不支持对SQL Server属性的控制 Transact SQL（简称T-SQL） 是对ANSI SQL的扩充，增加了一些Microsoft专用扩展，是一种SQL方言 提供了编写结构程序的能力,T-SQL批处理,由一个或多个T-SQL命令构成 SQL Server将这些命令作为一个整体执行，如果发生了错误，将会取消批处理中所有T-SQL命令的操作,T-SQL批处理的构成,声明变量可选 DECLARE 变量名 变量数据类型 程序体 SQL命令 T-SQL扩展命令，如流程控制命令 程序结束 GO,
2、T-SQL变量,用DECLARE命令声明 数据类型，包括Create Table语句中可以使用的所有数据类型，以及table和SQL Variant数据类型。 Numbers，Dates，Characters，Binary，Unique Identifiers (GUID)，SQL Variants，Image和Text，Table，Cursor，User-defined,变量的作用域和默认值,作用域：当前批处理 默认值：空值 变量的例子： DECLARE Test INT, Test2 VARCHAR(20),变量的赋值与显示,使用SET命令 SET 变量名 使用SELECT命令 SELEC
3、T 变量名目标表达式，FROM 表名 显示变量数据 SELECT 变量名或常量， PRINT 变量名或常量,批处理的例子,DECLARE Test INT, 声明变量TestTwo NVARCHAR(25)SELECT Test, TestTwoSET Test = 1 给变量赋值 SET TestTwo = a valueSELECT Test, TestTwo 显示变量的数据 Go 批处理结束SELECT Test as BatchTwo, TestTwo,批处理的例子（续）,DECLARE REGIONID INT, REGIONDESCRIPTION NCHAR(20),CONDITI
4、ON INTUSE NorthwindSET CONDITION = 1 SELECT REGIONID = REGIONID,REGIONDESCRIPTION = REGIONDESCRIPTION FROM REGION WHERE REGIONID = CONDITIONSELECT REGIONID, REGIONDESCRIPTIONGO,执行批处理,可以在查询分析器中打开一个.sql脚本文件，并通过按下F5键或者选择“查询”“执行”菜单项来执行一个SQL脚本程序中的全部批处理。 可以修改Windows的文件设置，以便通过双击一个.sql文件来打开查询分析器。 在查询分析器中可以通
5、过突出显示来选择一些命令，并通过按下F5键或者选择“查询”“执行”菜单项来执行这些选定的T-SQL命令。 在应用程序中，可以通过使用ADO或者ODBC来提交并执行T-SQL批处理。,选择数据库,在查询分析器的工具条显示了当前的数据库，也可以使用它来改变当前的数据库。 在代码中，可以使用use命令来选定当前的数据库。在批处理中使用use命令可以为它以后的语句指定数据库。,DDL命令,DDL命令不能与其他T-SQL命令放到一个批处理中。或者说，DDL命令必须单独放到自己的批处理中 包括： Create Alter delete,执行存储过程,在SQL批处理中可以使用EXEC命令来调用存储过程 语法
6、： EXEC 例子： EXEC sp_help,格式化T-SQL代码,续行 无需特殊的续行符号，T-SQL可自动识别语句的开始和结束 注释 ANSI标准注释 由两个短横线打头，到行尾结束的，适合于单行注释 C语言风格的注释 由/*开始，由*/结束的。适合于多行注释,注释的例子,/* 本程序由ABC编写用于查询地区表的数据 */ SELECT REGIONDESCRIPTION -结果列 FROM REGION - 要查询的表 WHERE REGIONID = 1 -条件 GO 批处理结束,流程控制（一）,IF IF Condition（条件表达式）Statement 只能对其后的一条语句的执行
7、与否进行控制即一个if只能控制一条命令。同时，它也没有then，以及用来结束if块的end if命令。 例子 IF 1 = 0PRINT Line One PRINT Line Two,流程控制（二）,BEGINEND 它可将多条命令作为一个整体构成if命令的下一条命令 语法： IF Condition BeginMultiple lines End,流程控制（三）,IFELSE 与IF类似，ELSE只能对其后的一条语句或者BEGINEND块的执行与否进行控制 语法： IF Condition Single line or begin/end block of code ELSE Single
8、 line or begin/end block of code,流程控制（四）,WHILE 在条件为真的情况下， WHILE命令可以循环地执行代码。与IF命令类似， WHILE命令只能循环执行其后的一条T-SQL命令。如果想循环执行一组命令，则需要使用BEGINEND块。 可以使用CONTINUE命令，可以从循环体中跳回到WHILE命令，并按照正常的方式测试循环条件。 可以使用break命令立即终止循环，并继续执行循环体以后的命令,流程控制（五）,Declare Temp IntSet Temp = 0While Temp 3 BeginPrint tested condition + St
9、r(Temp)Set Temp = Temp + 1 End,获取SQL Server的信息,sp_help命令及其84个变体可以返回有关服务器、数据库、对象、连接以及其它更多方面的信息。 基本的sp_help可以列出当前数据库中的可用对象，而其它使用方法则会返回有关不同数据库或者设置的详细信息。 使用对象名作为参数来执行sp_help，可以返回有关该对象更进一步的详细信息。,全局变量,在T-SQL中，全局变量是可以被调用的、只读的系统变量，用于向当前的连接和/或批处理提供系统的状态信息。 不能创建全局变量。系统中全局变量的数量是固定不变的，一共有33个，所有这些全局变量都是以两个号打头的（如
10、表12-1所示）。其中最常用的全局变量有：Error、Identity和ServerName等。,临时表和表变量,临时表和表变量与标准的基本表不同。其生命周期很短暂。当创建它的批处理或者过程结束时，临时表就被删除了。 由于它们是临时的，所以很适合用来在不同的对象之间传递数据或者为临时性的任务短时间保存数据。,局部临时表,创建临时表的方法与创建基本表的方式基本相同，只是在创建临时表时须使用由#号打头的表名。 临时表是创建并保存在tempdb数据库中的。 临时表的作用域是有限的。只有创建局部临时表的连接才能够看得到它。即使有上千的用户都使用了同样的表名创建了临时表，他们每个人也只能看到他们自己创建
11、的临时表。 例子：CREATE TABLE #ProductTemp ( ProductID INT PRIMARY KEY),全局临时表,全局临时表与局部临时表类似，但其作用域更大，所有的用户都可以引用全局临时表。 全局临时表的生命周期比局部临时表更长，只有当最后一个引用它的会话结束之后才会将它删除。 要创建全局临时表，需要使用由两个#号打头的表名：#TableName 例子： CREATE TABLE #TempWork(PK INT, Col1 INT),表变量,表变量类似于临时表，其优点在于它只存在于内存之中。 表变量与变量具有同样的作用域和生命周期，只有创建它们的批处理、过程或者函数
12、才能够看到它们。当这些批处理、过程或者函数结束的时候，相应的表变量也就不再存在了。 表变量是作为变量来声明的，不需要使用SQL DDL语句来创建它们。当在SQL查询中引用表变量时，可以像使用普通表一样使用它，只不过这个表的名字是一个变量名而已。,表变量例子,DECLARE WorkTable TABLE (PK INT PRIMARY KEY,Col1 INT NOT NULL)INSERT INTO WorkTable (PK, Col1)VALUES ( 1, 101)SELECT PK, Col1 FROM WorkTable,游标,SQL是用来处理行的集合的。但在一些情况下，我们却需要
13、使用代码来逐行的处理数据。针对这些情况，SQL提供了可以逐行遍历记录行集合的游标。,游标的基础知识,游标首先根据select语句创建结果集，然后一次从中获取一行数据。 游标的生命周期包含五个阶段： 声明游标。声明游标并不会检索任何数据；它只是为游标指定了获取数据时所使用的select语句。在declare后面指定游标的名字的时候，不需要使用号： DECLARE CursorName CURSOR CursorOptions FOR Select Statement,游标的基础知识（一）,打开游标，检索数据并填充游标： OPEN CursorName FETCH操作会使游标移动到下一条记录，并将
14、游标返回的每个列的数据分别赋值给本地变量（这些本地变量必须预先予以声明） FETCH CursorName INTO Variable1,Variable2,游标的基础知识（二）,关闭游标，释放数据，但保留select语句。游标关闭以后，还可以使用open命令再次打开它。（Close命令是与open命令相对的）。 Close CursorName 释放游标，释放相关的内存，并删除游标的定义（Deallocate命令是与declare命令相对的）。 DEALLOCATE CursorName,游标的作用域,可以为游标指定两种作用域。一种是局部的，此时游标只能在创建它的批处理中使用；另一种是全局的
15、，可以在同一个连接所调用的所有过程中使用游标。 游标的作用域是在声明游标时指定的。 语法： DECLARE CursorName CURSOR Local or Global FOR Select Statement,使用游标,对于使用游标来说，有两个全局变量非常重要。其中，cursor_rows将会返回游标中的行数。 Fetch_Status全局变量可以返回在最近一次执行fetch命令之后游标的状态。对于操作游标时的流程控制来说，这个全局变量很有用，它可以告诉我们何时到达了游标结果集的尾部。Fetch_Status可以返回如下的值，它们分别具有不同的含义：0最近一次fetch命令成功地获取到
16、一行数据。1最近一次fetch命令到达了结果集的尾部。2最近一次获取的行不可用；该行已经被删除。 将Fetch_Status变量与while命令配合使用来创建循环，可以方便地对游标中的行进行遍历。 WHILE Fetch_Status = 0,利用T-SQL遍历树,数据库：Family 表：Person（PersonID, LastName, FirstName, MaidenName, Gender, FatherID, MotherID, DateOfBirth, DateOfDeath）,利用T-SQL遍历树（一）,在Family数据库中，由于家族的世代数是动态的，而编写SQL sele
17、ct查询时却需要预先知道都要引用哪些表，所以要列出家族的全部世代是很困难的。利用select查询，我们只能查找固定数目的世代。当家族的世代数未知时，预先编码的select语句就无法胜任这项工作了。,利用T-SQL遍历树（二）,USE Family SELECT Person.FirstName + + IsNull(Person.SrJr, )as Grandfather,Gen1.FirstName + + IsNull(Gen1.SrJr, ) as Gen1,Gen2.FirstName + + IsNull(Gen2.SrJr, ) as Gen2FROM PersonLEFT JOI
18、N Person Gen1ON Person.PersonID = Gen1.FatherIDLEFT JOIN Person Gen2ON Gen1.PersonID = Gen2.FatherIDWHERE Person.PersonID = 2 GO,利用T-SQL遍历树（三）,目标：生成一个家族的谱系树，将孩子以缩进的形式显示在父代之下。 方法： 使用游标来查找当前家族成员的所有孩子（即MotherID或者FatherID等于当前家族成员PersonID的那些人）。一旦声明并打开了这个游标，在每次调用fetch命令之后，都将会打印当前获取到的孩子的信息，并递归地调用这个程序的另一个实例
19、来查看刚找到的这个孩子是否还有自己的孩子。如果是这样，将会对这个孩子的孩子再重复执行上面描述的过程，依此类推，不断反复。,利用T-SQL遍历树（四）,CREATE PROCEDURE ExamineChild(ParentID INT) AS SET Nocount On DECLARE ChildID INT,Childname VARCHAR(25)DECLARE cChild CURSOR LOCAL FAST_FORWARDFOR SELECT PersonID,Firstname + + LastName + + IsNull(SrJr, )as PersonNameFROM Per
20、sonWHERE Person.FatherID = ParentIDOR Person.MotherID = ParentIDORDER BY Person.DateOfBirth,利用T-SQL遍历树（五）,OPEN cChildFETCH cChild INTO ChildID, ChildName - prime the cursorWHILE Fetch_Status = 0BEGINPRINTSPACE(NestLevel * 2) + + + Cast(ChildID as VARCHAR(4)+ + ChildName- Recursively find the grandch
21、ildrenEXEC ExamineChild ChildIDFETCH cChild INTO ChildID, ChildNameENDCLOSE cChild DEALLOCATE cChild,利用T-SQL遍历树（六）,执行ExamineChild EXEC ExamineChild 2,利用T-SQL遍历树（七）,当数据量较少的时候，使用游标来解决遍历树的问题就足够了；但是，当数据量较大时，这种方法就会出现问题。这是由于两个原因造成的： 首先，SQL Server限制存储过程最多只能嵌套32层，所以当树的层次超过32层（还需要减去调用递归游标代码的程序已经嵌套的层次数）的时候，上述
22、代码就会出现问题； 其次，是性能问题（这是所有使用游标的方案一般都会具有的问题）。设想一下，将具有500万行的树数据集合组织为12个层次，上面的代码虽然可以正常工作，但是它却需要对游标进行500万次的重复调用，这相当于要执行500万个返回单行的select语句。,利用T-SQL遍历树（八）,使用基于集合的方法几乎可以取代所有的使用游标的方法。对于上面的问题，基于集合的方法可以使用单个的insert/select语句处理家族树的每个级别或者说世代，并显著地提高性能。 这个批处理程序首先将第一个家族成员存入#FamilyTree临时表。接着，它会遍历每一个世代，并使用多条件连接将双亲属于上一个世代
23、的每一个家族成员都加入到这个临时表中去。 对于#FamilyTree临时表中的每一个家族成员，其FamilyLine列中都包含的是其双亲的FamilyLine数据与其自身的PersonID的串联结果。利用FamilyLine所提供的数据可以对于家族树进行排序。,利用T-SQL遍历树（九）,CREATE TABLE #FamilyTree (PersonID INT,Generation INT,FamilyLine VarChar(25) Default )DECLAREGeneration INT,FirstPerson INT SET Generation = 1 SET FirstPer
24、son = 2 - prime the temp table with the top person(s) in the queue INSERT #FamilyTree (PersonID, Generation, FamilyLine)SELECT FirstPerson, Generation, FirstPerson,利用T-SQL遍历树（十）,WHILE RowCount 0BEGINSET Generation = Generation + 1INSERT #FamilyTree (PersonID, Generation, FamilyLine)SELECT Person.Per
25、sonID,Generation,#FamilyTree.FamilyLine+ + Str(Person.PersonID,5)FROM PersonJOIN #FamilyTreeON #FamilyTree.Generation = Generation - 1AND(Person.MotherID = #FamilyTree.PersonIDORPerson.FatherID = #FamilyTree.PersonID)END,显示家族谱系树,SELECT SPACE(Generation * 2) + + + Cast(#FamilyTree.PersonID as VARCHAR
26、(4) + + FirstName + + LastName+ IsNull(SrJr, ) AS FamilyTreeFROM #FamilyTreeJOIN PersonON #FamilyTree.PersonID = Person.PersonIDORDER BY FamilyLine,性能比较,对于具有500万行数据和12个层次的树，基于集合的方法只需要循环执行12次，并执行12个可以优化的查询。,错误处理,Error全局变量包含了代码中执行的最近一条T-SQL命令的错误状态。其中，0表示命令执行成功 每一个命令都会对Error进行更新，即使测试它的值的命令都会更新它的值。为了能够得
27、到上一次发生错误时的状态，可以将发生错误时的状态保持在一个局部变量之中，以便对其进行适当的检查和处理。,错误处理（一）,USE Family DECLARE err INT UPDATE PersonSET PersonID = 1Where PersonID = 2 SET err = Error IF err 0 Begin- error handling codePrint err End,错误处理（二）,RowCount记录了SQL语句所操作的行数。通过RowCount可以判断SQL语句是否功地操作了数据。 与Error的情况类似，每一个T-SQL命令都会改变RowCount的状态。,
28、错误处理（三）,USE FAMILY UPDATE PersonSET LastName = JohnsonWHERE PersonID = 100 IF RowCount = 0 Begin- error handling codePrint no rows affected End,错误处理（四）,如果批处理使用了逻辑事务（begin tran/commit tran），错误处理程序的第一个操作就应当是回滚这个事务，这样可以释放该事务所持有的所有锁。 如果错误是由存储过程自身的逻辑所发现的，应当产生一个错误消息，以便通知用户或者前端的应用程序。如果错误是由SQL Server发现的，那么SQL Server就会自动的产生一个错误消息。 如果有必要，可以将错误记录到一个错误表中。 结束批处理。如果它是一个存储过程、用户定义函数或者触发器，应该使用return命令来结束它。,
