1 / 51

Transact SQL 编程

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 批处理的构成. 声明变量 [ 可选 ]

nyla
Download Presentation

Transact SQL 编程

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Transact SQL编程

  2. Transact SQL简介 • ANSI SQL的不足 • 缺乏逻辑控制结构 • 不支持对SQL Server属性的控制 • Transact SQL（简称T-SQL） • 是对ANSI SQL的扩充，增加了一些Microsoft专用扩展，是一种SQL方言 • 提供了编写结构程序的能力

  3. T-SQL批处理 • 由一个或多个T-SQL命令构成 • SQL Server将这些命令作为一个整体执行，如果发生了错误，将会取消批处理中所有T-SQL命令的操作

  4. T-SQL批处理的构成 • 声明变量[可选] • DECLARE @变量名 变量数据类型 • 程序体 • SQL命令 • T-SQL扩展命令，如流程控制命令 • 程序结束 • GO

  5. T-SQL变量 • 用DECLARE命令声明 • 数据类型，包括Create Table语句中可以使用的所有数据类型，以及table和SQL Variant数据类型。 • Numbers，Dates，Characters，Binary，Unique Identifiers (GUID)，SQL Variants，Image和Text，Table，Cursor，User-defined

  6. 变量的作用域和默认值 • 作用域：当前批处理 • 默认值：空值 • 变量的例子： • DECLARE @Test INT, @Test2 VARCHAR(20)

  7. 变量的赋值与显示 • 使用SET命令 • SET @变量名＝<值表达式> • 使用SELECT命令 • SELECT @变量名＝目标表达式，… FROM 表名 • 显示变量数据 • SELECT @变量名或常量，… • PRINT @变量名或常量

  8. 批处理的例子 DECLARE @Test INT, @TestTwo NVARCHAR(25) SELECT @Test, @TestTwo SET @Test = 1 SET @TestTwo = ‘a value’ SELECT @Test, @TestTwo Go SELECT @Test as BatchTwo, @TestTwo

  9. 批处理的例子（续） DECLARE @REGIONID INT, @REGIONDESCRIPTION NCHAR(20), @CONDITION INT USE Northwind SET @CONDITION = 1 SELECT @REGIONID = REGIONID, @REGIONDESCRIPTION = REGIONDESCRIPTION FROM REGION WHERE REGIONID = @CONDITION SELECT @REGIONID, @REGIONDESCRIPTION GO

  10. 执行批处理 • 可以在查询分析器中打开一个.sql脚本文件，并通过按下F5键或者选择“查询”“执行”菜单项来执行一个SQL脚本程序中的全部批处理。 • 可以修改Windows的文件设置，以便通过双击一个.sql文件来打开查询分析器。 • 在查询分析器中可以通过突出显示来选择一些命令，并通过按下F5键或者选择“查询”“执行”菜单项来执行这些选定的T-SQL命令。 • 在应用程序中，可以通过使用ADO或者ODBC来提交并执行T-SQL批处理。

  11. 选择数据库 • 在查询分析器的工具条显示了当前的数据库，也可以使用它来改变当前的数据库。 • 在代码中，可以使用use命令来选定当前的数据库。在批处理中使用use命令可以为它以后的语句指定数据库。

  12. DDL命令 • DDL命令不能与其他T-SQL命令放到一个批处理中。或者说，DDL命令必须单独放到自己的批处理中 • 包括： • Create • Alter • delete

  13. 执行存储过程 • 在SQL批处理中可以使用EXEC命令来调用存储过程 • 语法： • EXEC <存储过程名> • 例子： • EXEC sp_help

  14. 格式化T-SQL代码 • 续行 • 无需特殊的续行符号，T-SQL可自动识别语句的开始和结束 • 注释 • ANSI标准注释 • 由两个短横线打头，到行尾结束的，适合于单行注释 • C语言风格的注释 • 由/*开始，由*/结束的。适合于多行注释

  15. 注释的例子 /* 本程序由ABC编写 用于查询地区表的数据 */ SELECT REGIONDESCRIPTION --结果列 FROM REGION -- 要查询的表 WHERE REGIONID = 1 --条件 GO

  16. 流程控制（一） • IF • IF Condition Statement • 只能对其后的一条语句的执行与否进行控制－即一个if只能控制一条命令。同时，它也没有then，以及用来结束if块的end if命令。 ‘ • 例子 IF 1 = 0 PRINT ‘Line One’ PRINT ‘Line Two’

  17. 流程控制（二） • BEGIN…END • 它可将多条命令作为一个整体构成if命令的下一条命令 • 语法： IF Condition Begin Multiple lines End

  18. 流程控制（三） • IF…ELSE • 与IF类似，ELSE只能对其后的一条语句或者BEGIN…END块的执行与否进行控制 • 语法： IF Condition Single line or begin/end block of code ELSE Single line or begin/end block of code

  19. 流程控制（四） • WHILE • 在条件为真的情况下， WHILE命令可以循环地执行代码。与IF命令类似， WHILE命令只能循环执行其后的一条T-SQL命令。如果想循环执行一组命令，则需要使用BEGIN…END块。 • 可以使用CONTINUE命令，可以从循环体中跳回到WHILE命令，并按照正常的方式测试循环条件。 • 可以使用break命令立即终止循环，并继续执行循环体以后的命令

  20. 流程控制（五） Declare @Temp Int Set @Temp = 0 While @Temp <3 Begin Print ‘tested condition’ + Str(@Temp) Set @Temp = @Temp + 1 End

  21. 获取SQL Server的信息 • sp_help命令及其84个变体可以返回有关服务器、数据库、对象、连接以及其它更多方面的信息。 • 基本的sp_help可以列出当前数据库中的可用对象，而其它使用方法则会返回有关不同数据库或者设置的详细信息。 • 使用对象名作为参数来执行sp_help，可以返回有关该对象更进一步的详细信息。

  22. 全局变量 • 在T-SQL中，全局变量是可以被调用的、只读的系统变量，用于向当前的连接和/或批处理提供系统的状态信息。 • 不能创建全局变量。系统中全局变量的数量是固定不变的，一共有33个，所有这些全局变量都是以两个@号打头的（如表12-1所示）。其中最常用的全局变量有：@@Error、@@Identity和@@ServerName等。

  23. 临时表和表变量 • 临时表和表变量与标准的基本表不同。其生命周期很短暂。当创建它的批处理或者过程结束时，临时表就被删除了。 • 由于它们是临时的，所以很适合用来在不同的对象之间传递数据或者为临时性的任务短时间保存数据。

  24. 局部临时表 • 创建临时表的方法与创建基本表的方式基本相同，只是在创建临时表时须使用由#号打头的表名。 • 临时表是创建并保存在tempdb数据库中的。 • 临时表的作用域是有限的。只有创建局部临时表的连接才能够看得到它。即使有上千的用户都使用了同样的表名创建了临时表，他们每个人也只能看到他们自己创建的临时表。 • 例子： CREATE TABLE #ProductTemp ( ProductID INT PRIMARY KEY)

  25. 全局临时表 • 全局临时表与局部临时表类似，但其作用域更大，所有的用户都可以引用全局临时表。 • 全局临时表的生命周期比局部临时表更长，只有当最后一个引用它的会话结束之后才会将它删除。 • 要创建全局临时表，需要使用由两个#号打头的表名：##TableName • 例子： CREATE TABLE ##TempWork (PK INT, Col1 INT)

  26. 表变量 • 表变量类似于临时表，其优点在于它只存在于内存之中。 • 表变量与变量具有同样的作用域和生命周期，只有创建它们的批处理、过程或者函数才能够看到它们。当这些批处理、过程或者函数结束的时候，相应的表变量也就不再存在了。 • 表变量是作为变量来声明的，不需要使用SQL DDL语句来创建它们。当在SQL查询中引用表变量时，可以像使用普通表一样使用它，只不过这个表的名字是一个变量名而已。

  27. 表变量例子 DECLARE @WorkTable TABLE ( PK INT PRIMARY KEY, Col1 INT NOT NULL) INSERT INTO @WorkTable (PK, Col1) VALUES ( 1, 101) SELECT PK, Col1 FROM @WorkTable

  28. 游标 • SQL是用来处理行的集合的。但在一些情况下，我们却需要使用代码来逐行的处理数据。针对这些情况，SQL提供了可以逐行遍历记录行集合的游标。

  29. 游标的基础知识 • 游标首先根据select语句创建结果集，然后一次从中获取一行数据。 • 游标的生命周期包含五个阶段： • 声明游标。声明游标并不会检索任何数据；它只是为游标指定了获取数据时所使用的select语句。在declare后面指定游标的名字的时候，不需要使用@号： DECLARE CursorName CURSOR CursorOptions FOR Select Statement

  30. 游标的基础知识（一） • 打开游标，检索数据并填充游标： • OPEN CursorName • FETCH操作会使游标移动到下一条记录，并将游标返回的每个列的数据分别赋值给本地变量（这些本地变量必须预先予以声明） FETCH CursorName INTO @Variable1, @Variable2

  31. 游标的基础知识（二） • 关闭游标，释放数据，但保留select语句。游标关闭以后，还可以使用open命令再次打开它。（Close命令是与open命令相对的）。 • Close CursorName • 释放游标，释放相关的内存，并删除游标的定义（Deallocate命令是与declare命令相对的）。 • DEALLOCATE CursorName

  32. 游标的作用域 • 可以为游标指定两种作用域。一种是局部的，此时游标只能在创建它的批处理中使用；另一种是全局的，可以在同一个连接所调用的所有过程中使用游标。 • 游标的作用域是在声明游标时指定的。 • 语法： DECLARE CursorName CURSOR Local or Global FOR Select Statement

  33. 使用游标 • 对于使用游标来说，有两个全局变量非常重要。其中，@@cursor_rows将会返回游标中的行数。 • @@Fetch_Status全局变量可以返回在最近一次执行fetch命令之后游标的状态。对于操作游标时的流程控制来说，这个全局变量很有用，它可以告诉我们何时到达了游标结果集的尾部。@@Fetch_Status可以返回如下的值，它们分别具有不同的含义： • 0—最近一次fetch命令成功地获取到一行数据。 • 1—最近一次fetch命令到达了结果集的尾部。 • 2—最近一次获取的行不可用；该行已经被删除。 • 将@@Fetch_Status变量与while命令配合使用来创建循环，可以方便地对游标中的行进行遍历。 • WHILE @@Fetch_Status = 0

  34. 利用T-SQL遍历树 • 数据库：Family • 表：Person（PersonID, LastName, FirstName, MaidenName, Gender, FatherID, MotherID, DateOfBirth, DateOfDeath）

  35. 利用T-SQL遍历树（一） • 在Family数据库中，由于家族的世代数是动态的，而编写SQL select查询时却需要预先知道都要引用哪些表，所以要列出家族的全部世代是很困难的。利用select查询，我们只能查找固定数目的世代。当家族的世代数未知时，预先编码的select语句就无法胜任这项工作了。

  36. 利用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 Gen2 FROM Person LEFT JOIN Person Gen1 ON Person.PersonID = Gen1.FatherID LEFT JOIN Person Gen2 ON Gen1.PersonID = Gen2.FatherID WHERE Person.PersonID = 2 GO

  37. 利用T-SQL遍历树（三） • 目标：生成一个家族的谱系树，将孩子以缩进的形式显示在父代之下。 • 方法： • 使用游标来查找当前家族成员的所有孩子（即MotherID或者FatherID等于当前家族成员PersonID的那些人）。一旦声明并打开了这个游标，在每次调用fetch命令之后，都将会打印当前获取到的孩子的信息，并递归地调用这个程序的另一个实例来查看刚找到的这个孩子是否还有自己的孩子。如果是这样，将会对这个孩子的孩子再重复执行上面描述的过程，依此类推，不断反复。

  38. 利用T-SQL遍历树（四） CREATE PROCEDURE ExamineChild (@ParentID INT) AS SET Nocount On DECLARE @ChildID INT, @Childname VARCHAR(25) DECLARE cChild CURSOR LOCAL FAST_FORWARD FOR SELECT PersonID, Firstname + ' ' + LastName + ' ' + IsNull(SrJr,' ') as PersonName FROM Person WHERE Person.FatherID = @ParentID OR Person.MotherID = @ParentID ORDER BY Person.DateOfBirth

  39. 利用T-SQL遍历树（五） OPEN cChild FETCH cChild INTO @ChildID, @ChildName -- prime the cursor WHILE @@Fetch_Status = 0 BEGIN PRINT SPACE(@@NestLevel * 2) + '+ ' + Cast(@ChildID as VARCHAR(4)) + ' ' + @ChildName -- Recursively find the grandchildren EXEC ExamineChild @ChildID FETCH cChild INTO @ChildID, @ChildName END CLOSE cChild DEALLOCATE cChild

  40. 利用T-SQL遍历树（六） • 执行ExamineChild • EXEC ExamineChild 2

  41. 利用T-SQL遍历树（七） • 当数据量较少的时候，使用游标来解决遍历树的问题就足够了；但是，当数据量较大时，这种方法就会出现问题。这是由于两个原因造成的： • 首先，SQL Server限制存储过程最多只能嵌套32层，所以当树的层次超过32层（还需要减去调用递归游标代码的程序已经嵌套的层次数）的时候，上述代码就会出现问题； • 其次，是性能问题（这是所有使用游标的方案一般都会具有的问题）。设想一下，将具有500万行的树数据集合组织为12个层次，上面的代码虽然可以正常工作，但是它却需要对游标进行500万次的重复调用，这相当于要执行500万个返回单行的select语句。

  42. 利用T-SQL遍历树（八） • 使用基于集合的方法几乎可以取代所有的使用游标的方法。对于上面的问题，基于集合的方法可以使用单个的insert/select语句处理家族树的每个级别或者说世代，并显著地提高性能。 • 这个批处理程序首先将第一个家族成员存入#FamilyTree临时表。接着，它会遍历每一个世代，并使用多条件连接将双亲属于上一个世代的每一个家族成员都加入到这个临时表中去。 • 对于#FamilyTree临时表中的每一个家族成员，其FamilyLine列中都包含的是其双亲的FamilyLine数据与其自身的PersonID的串联结果。利用FamilyLine所提供的数据可以对于家族树进行排序。

  43. 利用T-SQL遍历树（九） CREATE TABLE #FamilyTree ( PersonID INT, Generation INT, FamilyLine VarChar(25) Default '' ) DECLARE @Generation INT, @FirstPerson INT SET @Generation = 1 SET @FirstPerson = 2 -- prime the temp table with the top person(s) in the queue INSERT #FamilyTree (PersonID, Generation, FamilyLine) SELECT @FirstPerson, @Generation, @FirstPerson

  44. 利用T-SQL遍历树（十） WHILE @@RowCount > 0 BEGIN SET @Generation = @Generation + 1 INSERT #FamilyTree (PersonID, Generation, FamilyLine) SELECT Person.PersonID, @Generation, #FamilyTree.FamilyLine + ' ' + Str(Person.PersonID,5) FROM Person JOIN #FamilyTree ON #FamilyTree.Generation = @Generation - 1 AND (Person.MotherID = #FamilyTree.PersonID OR Person.FatherID = #FamilyTree.PersonID) END

  45. 显示家族谱系树 SELECT SPACE(Generation * 2) + '+ ' + Cast(#FamilyTree.PersonID as VARCHAR(4)) + ' ' + FirstName + ' ' + LastName + IsNull(SrJr,' ') AS FamilyTree FROM #FamilyTree JOIN Person ON #FamilyTree.PersonID = Person.PersonID ORDER BY FamilyLine

  46. 性能比较 • 对于具有500万行数据和12个层次的树，基于集合的方法只需要循环执行12次，并执行12个可以优化的查询。

  47. 错误处理 • @@Error全局变量包含了代码中执行的最近一条T-SQL命令的错误状态。其中，0表示命令执行成功 • 每一个命令都会对@@Error进行更新，即使测试它的值的命令都会更新它的值。为了能够得到上一次发生错误时的状态，可以将发生错误时的状态保持在一个局部变量之中，以便对其进行适当的检查和处理。

  48. 错误处理（一） USE Family DECLARE @err INT UPDATE Person SET PersonID = 1 Where PersonID = 2 SET @err = @@Error IF @err <> 0 Begin -- error handling code Print @err End

  49. 错误处理（二） • @@RowCount记录了SQL语句所操作的行数。通过@@RowCount可以判断SQL语句是否功地操作了数据。 • 与@@Error的情况类似，每一个T-SQL命令都会改变@@RowCount的状态。

  50. 错误处理（三） USE FAMILY UPDATE Person SET LastName = 'Johnson' WHERE PersonID = 100 IF @@RowCount = 0 Begin -- error handling code Print 'no rows affected' End

More Related