1 / 67

第十章 T-SQL 程序设计

第十章 T-SQL 程序设计. 本章主题. 将两个或更多的表联接起来以看到更多的结果信息 获取一种通过变量以临时保存信息的方法 如何将行信息保存到非永久的表中 如何聚合值 组织输出数据到相关的信息分组中 返回唯一和独特的值 错误处理：如何创建你自己的错误，捕捉错误，并让代码更安全. T-SQL 程序设计. T-SQL 的目的在于为处理大量数据提供必要的结构化处理能力。前面的章节中已经介绍了一些处理简单查询的 T-SQL 语句，但是当面对更为复杂的应用时，前面所学的知识就远远不够了。因此，在本章中将主要讨论如何使用 T-SQL 提供的丰富编程结构，编写出复杂的例行程序。.

roza
Download Presentation

第十章 T-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. 第十章T-SQL程序设计

  2. 本章主题 • 将两个或更多的表联接起来以看到更多的结果信息 • 获取一种通过变量以临时保存信息的方法 • 如何将行信息保存到非永久的表中 • 如何聚合值 • 组织输出数据到相关的信息分组中 • 返回唯一和独特的值 • 错误处理：如何创建你自己的错误，捕捉错误，并让代码更安全

  3. T-SQL程序设计 T-SQL的目的在于为处理大量数据提供必要的结构化处理能力。前面的章节中已经介绍了一些处理简单查询的T-SQL语句，但是当面对更为复杂的应用时，前面所学的知识就远远不够了。因此，在本章中将主要讨论如何使用T-SQL提供的丰富编程结构，编写出复杂的例行程序。

  4. 第一节 T-SQL简介 T-SQL是Microsoft SQL Server提供的查询语言，它是Microsoft公司对于ANSI SQL的一个扩展，它不仅提供了对SQL标准的支持，另外还提供了类似于C等编程语言的基本功能。T-SQL的目的在于为事务型数据库开发提供一套过程化的开发工具。 T-SQL对于使用SQL Server非常重要，它是SQL Server功能的核心，使用T-SQL编写程序可以完成所有的数据库管理工作，与SQL Server通信的所有程序都通过向数据库服务器发送T-SQL语句来进行通信，而与应用程序的用户界面是什么形式无关。

  5. 2 3 1 4 5 第二节 T-SQL编程基础 批处理 局部变量 注释 全局变量 运算符

  6. (一) 注释 T-SQL中的注释语句，也称为注解，注释内容通常是一些说明性文字，对程序的结构及功能给出简要的解释。注释语句不是可执行语句，不被系统编译，也不被程序执行。使用注释语句的目的是为了使程序代码易读易分析，也便于日后的管理和维护。 SQL Server支持两种形式的程序注释语句： 1．单行注释语句：使用ANSI标准的注释符 “--”，注释语句写在“--”的后面，只能书写单行。 2．多行注释语句：使用与C语言相同的程序注释符“/* */”，注释语句写在“/*”和“*/”之间，可以连续书写多行。 例10-1：合法的注释语句。 --打开Student数据库 USE Student GO /*查询Classes表中所有记录的Class_id, Class_name以及Class_department三个字段的内容*/

  7. 注释 SELECT Class_id, Class_name, Class_department FROM Classes GO 当然，连续多行注释也可以使用注释符“--”，只要每行注释开头都加上注释符“--”。单行注释语句也可以使用注释符“/* */”，只要将注释语句写在“/*”和“*/”之间。 例如上面的例子中注释语句也可以写成： /*打开Student数据库*/ USE Student GO --本条SQL语句用来查询Classes表中所有记录的Class_id, Class_name, -- Class_department三个字段的内容 SELECT Class_id, Class_name, Class_department FROM Classes GO

  8. (二) 批处理 所谓批是指从客户机传送到服务器上的一组完整数据和SQL指令，批中的所有SQL语句做为一个整体编译成一个执行单元后从应用程序一次性地发送到SQL Server服务器进行执行，称之为批处理。 所有的批处理命令都使用GO作为结束标志，当T-SQL的编译器扫描到某行的前两个字符是GO的时候，它会把GO前面的所有语句作为一个批处理送往服务器。 由于批处理中的所有语句被当作是一个整体，因此若其中一个语句出现了编译错误，则该批处理内所有语句的执行都将被取消。 例10-2：正确的批处理。 --第一个批处理打开Student数据库 USE Student GO --第二个批处理在Teachers表中查询姓王的教师的记录 SELECT * FROM Teachers WHERE SUBSTRING(Teacher_name,1,1)= '王' GO

  9. (三) 局部变量 • 变量是执行程序中必不可少的部分，它主要是用来在程序运行过程中存储和传递数据。变量其实就是内存中的一个存储区域，存储在这个区域中的数据就是变量的值。在T-SQL语句中变量有两种，局部变量与全局变量。这两种变量在使用方法和具体意义上均不相同，本部分主要介绍局部变量。 • 局部变量是作用域局限在一定范围内的变量，是用户自定义的变量。一般来说，局部变量的使用范围局限于定义它的批处理内。定义它的批处理中的SQL语句可以引用这个局部变量，直到批处理结束，这个局部变量的生命周期也就结束了。 • 1．局部变量的声明 • 在使用一个局部变量之前，必须先声明该变量。声明一个局部变量的语法格式如下： • DECLARE @变量名变量类型[，@变量名变量类型] …… …… • 声明语句中的各部分说明如下： • 局部变量名的命名必须遵循SQL Server的标识符命名规则，并且必须以字符“@”开头。 • 局部变量的类型可以是系统数据类型，也可以是用户自定义的数据类型。 • DECLARE语句可以声明一个或多个局部变量，变量被声明以后初值都是NULL。 • 如语句DECLARE @MyScore INT声明了一个整型局部变量@MyScore 。也可以在同一语句中声明多个局部变量，例如： • DECLARE @Student_Name VARCHAR(20), @Student_Address VARCHAR (60)

  10. 局部变量 2．局部变量的赋值 局部变量被创建之后，系统将其初始值设为NULL。若要改变局部变量的值，可以使用SET语句或SELECT语句给局部变量重新赋值。 SELECT语句的语法格式为： SELECT @变量名= 表达式[，@变量名= 表达式] …… …… SET语句的语法格式为： SET @变量名= 表达式 赋值语句中的各部分说明如下： @变量名是准备为其赋值的局部变量。 表达式是有效的SQL Server表达式，且其类型应与局部变量的数据类型相匹配。 从语法格式中可看出，SELECT语句和SET语句的区别在于使用SET语句一次只能给一个变量赋值，而在SELECT语句中可以一次给多个变量赋值。

  11. 局部变量 例10-3：局部变量的赋值。 USE Student GO --声明局部变量 DECLARE @sname VARCHAR(30), @sclassid VARCHAR(30) --使用SET语句给局部变量赋值 SET @sname = '李佳佳' SET @sclassid = '2005011' --根据局部变量的值查询符合条件的记录的姓名和性别 SELECT Student_name,Student_sex, Student_classid FROM Students WHERE Student_name =@sname OR Student_classid =@sclassid --将Student_id为'11001'的记录的姓名和班级赋值给局部变量 SELECT @sname =Student_name, @sclassid =Student_classid FROM Students WHERE Student_id='11001' --输出变量@sname和@sclassid的值 SELECT @sname AS sname, @sclassid AS sclassid GO

  12. 局部变量 本例主要完成了两个操作，一是根据局部变量的初值在Students表中查询符合条件的记录，二是将指定记录的姓名及班级号的值赋给局部变量，改变局部变量原来的值并输出其新值。程序的运行结果如图10.1所示。 图10.1 例10-3执行结果

  13. 局部变量 3．局部变量的输出 例10-3中使用了SELECT语句来输出局部变量的值，除此之外局部变量的输出还可以使用PRINT语句。PRINT语句的语法格式如下： PRINT @局部变量名 如例10-3中最后的输出语句SELECT @sname AS sname, @sclassid AS sclassid也可以改写成如下的两条PRINT语句： PRINT @sname PRINT @sclassid

  14. (四) 全局变量 全局变量是以“@@”开头，由系统预先定义并负责维护的变量，也可以把全局变量看成是一种特殊形式的函数。全局变量不可以由用户随意建立和修改，作用范围也并不局限于某个程序，而是任何程序均可调用。常用的全局变量有三十多个，通常用来存储一些SQL Server的配置值和效能统计数字，用户可以通过查询全局变量来检测系统的参数值或者执行查询命令后的状态值。 在全局变量的使用过程中要注意，全局变量是由SQL Server服务器定义的，不是用户自定义的，用户只能使用预先定义的全局变量，且在引用全局变量时，必须以“@@”开头。另外，局部变量的名称不能与全局变量的名称相同，否则就会在应用程序中出错。 表10-1中列出了SQL Server的几个常用全局变量及其含义，对于其他全局变量，读者可通过自行查阅SQL Server 2005联机丛书进行学习。 表10-1 SQL Server的常用全局变量

  15. 全局变量 例10-4：全局变量@@ERROR的使用。 USE Student GO --在Teachers表中插入一条新记录 INSERT INTO Teachers (Teacher_id, Teacher_name ,Teacher_department) VALUES ('JS006', '张海涛', '002') --使用全局变量@@ERROR输出错误代码的值 SELECT @@ERROR AS 错误 GO 图10.2例10-4执行结果

  16. (五) 运算符 • 使用T-SQL来编写例行程序时，不可避免的要使用到运算符。运算符用来完成一个或多个表达式中指定的操作，SQL Server中的运算符包括有：算术运算符、比较运算符、逻辑运算符、位运算符、赋值运算符、连接运算符。 • 1．算术运算符 • 算术运算符用来执行算术运算，T-SQL中的算术运算符包括： • +（加法运算符） • -（减法运算符） • *（乘法运算符） • /（除法运算符） • %（模运算符或取余运算符，返回一个除法的整数余数，要求数据类型为INT、 • SMALLINT和TINYINT）

  17. 运算符 例10-5：计算算术表达式的值。 DECLARE @Value NUMERIC SET @Value =132%7 PRINT @Value 程序的运行结果如图10.3所示。 2．赋值运算符 T-SQL中的赋值运算符只有一个，就是“＝”（等号）。和其它语言中的赋值运算符一样，T-SQL中的赋值运算符的作用就是将数据值指派给特定的对象。此外，也可以使用赋值运算符在列标题和列定义值的表达式之间建立关系。 图10.3 例10-5执行结果

  18. 运算符 例10-6：查询经济信息系所有教师的信息，要求输出时的列标题为：教师号、教师姓名、教师所在系，且要求教师所在系名显示为“信息系”。 USE Student GO SELECT 教师号= Teachers.Teacher_id, 教师姓名= Teachers.Teacher_name, 教师所在系=SUBSTRING(Departments.Department_name,3,3) FROM Teachers INNER JOIN Departments ON Teachers.Teacher_department= Departments.Department_id WHERE Departments. Department_name ='经济信息系' GO 图10.4例10-6执行结果

  19. 运算符 3．比较运算符 比较运算符用于比较两个表达式的大小，T-SQL中的比较运算符包括：＞（大于）、＞=（大于等于）、＜（小于）、＜=（小于等于）、=（等于）、！=，＜＞（不等于）、！＞（不大于）、！＜（不小于）。比较运算符的运算结果是布尔数据类型，它有三种可能的结果：TRUE、FALSE以及UNKNOWN。 例10-7：查询Student_course表中成绩的70%仍然大于60分的学生学号和原来的成绩。 USE Student GO SELECT Student_id,Student_grade FROM Student_course WHERE Student_grade*0.7>60 ORDER BY Student_id ASC GO 图10.5 例10-7执行结果

  20. 运算符 4．逻辑运算符 逻辑运算符用来测试某些条件是否成立，T-SQL中的逻辑运算符包括： NOT（非运算符） AND（与运算符） OR（或运算符） 非运算符，用于表示对条件的否定。与运算符用于连接查询条件，只有AND两边的条件的值都为真时，其结果值才为真。或运算符用于连接查询条件，只要OR两边的条件中有一个为真，其结果值就为真。 逻辑运算符和比较运算符一样，运算结果是布尔数据类型。那些返回布尔数据类型的表达式称为布尔表达式，T-SQL中的布尔表达式有三种可能的值，分别是TRUE、FALSE以及UNKNOWN，其中UNKNOWN是由值为NULL的数据参加运算得到的结果。表10-2、表10-3、表10-4列出了进行各种逻辑运算时不同情况得到的结果。

  21. 运算符 表10-2 NOT运算的各种结果 表10-3 AND运算的各种结果 表10-4 OR运算的各种结果

  22. 运算符 例10-8：请查询出所有家庭所在地为山西的男同学。 USE Student GO SELECT Student_id, Student_name, Student_sex, Student_home FROM Students WHERE Student_home= '山西' AND Student_sex='男' GO 图10.6 例10-8执行结果

  23. 运算符 5．位运算符 位运算符用来对两个表达式进行位操作，位运算符的操作数允许是整型数据或者二进制数据（但image数据类型除外）。此外，位运算还要求两个操作数不能同时是二进制数据。T-SQL中的位运算符包括: &（按位与） |（按位或） ^（按位异或） ~（按位取反） 例10-9：计算位运算表达式的值。 SELECT 15&16 AS 按位与, 15|16 AS 按位或, 15^16 AS 按位异或, ~15 AS 按位取反 图10.7 例10-9执行结果

  24. 运算符 6．连接运算符 T-SQL中的连接运算符“+”用于连接字符串或二进制数据串、列名或列的混合体，其实质就是将一个串加入到另一个串的尾部。 例10-10：在输出Classes表中班级名称时前面加上班级所在系别，如“经济信息系电子商务1班”。 USE Student GO SELECT Classes.Class_id,Departments.Department_name+Classes.Class_name FROM Classes INNER JOIN Departments ON Classes. Class_department = Departments.Department_id GO 图10.8 例10-10执行结果

  25. 运算符 7．运算符的优先级 T-SQL中的运算符的处理顺序如表10-5所示。 表10-5 T-SQL运算符优先级

  26. 第三节 函数 为了让用户更方便的对数据库进行操作，SQL Server 2005在T-SQL中提供了许多内置函数。函数其实就是一段程序代码，用户可以通过调用内置函数并为其提供所需的参数来执行一些特殊的运算或完成复杂的操作。T-SQL提供的函数有系统函数、字符串函数、日期和时间函数、数学函数、转换函数等。 字符串函数 日期和时间函数 系统函数 函数 数学函数 用户自定义函数 转换函数

  27. (一) 系统函数 系统函数可以返回有关当前环境的信息，例如有关服务器、用户、数据库对象的系统信息。常用的系统函数及其功能如表10-6所示。 表10-6 常用系统函数

  28. 系统函数 例10-11：系统函数的使用。 SELECT 用户名=USER_NAME(),登陆名= SUSER_SID(),工作站名=HOST_NAME() 该语句的运行结果如图10.9所示。 例10-12：使用系统函数查询指定表的第一列的长度。 USE Student GO DECLARE @col_name VARCHAR(30) SELECT @col_name=COL_NAME(OBJECT_ID('Students'),1) SELECT COL_LENGTH('Students', @col_name) AS 第一列长度 GO 图10.10例10-12执行结果 图10.9例10-11执行结果

  29. (二) 字符串函数 字符串函数主要是为了方便用户对二进制数据、字符串和表达式进行操作。常用的字符串函数及其功能如表10-7所示。

  30. 字符串函数例题 例10-13：在Students表中查询所有2005级学生的学号、姓名、性别、出生日期及所在班级号。 USE Student GO SELECT Student_id,Student_name,Student_sex,Student_birthday,Student_classid FROM Students WHERE SUBSTRING(Student_classid,1,4) ='2005' GO 图10.11例10-13执行结果

  31. 字符串函数例题 例10-14：在Users表中使用CHARINDEX()函数查询所有User_name中包含了字符串“ia”的记录的User_id。 USE Student GO SELECT User_id,User_name FROM Users WHERE CHARINDEX('ia', User_name)<>0 GO 程序的运行结果如图10.12所示。 例10-15：在Users表中使用PATINDEX()函数查询所有User_name中包含了字符串“ia”的记录的User_id。 USE Student GO SELECT User_id,User_name FROM Users WHERE PATINDEX ('%_ia_%', User_name)<>0 GO 程序的运行结果如图10.13所示。 例10-14和例10-15可以反映PATINDEX()和CHARINDEX()的不同之处，虽然都是查找User_name中包含了字符串“ia”的记录的User_id，但是PATINDEX()函数中包含了指定字符串的格式，所以查找的结果就不相同。 图10.12例10-14执行结果 图10.13例10-15执行结果

  32. (三) 日期和时间函数 日期和时间函数用于对日期和时间数据进行运算和操作，其返回值为字符串、数字值或日期和时间值。常用的日期和时间函数及其功能如表10-8所示。 表10-8中出现的参数datepart ，是指定要返回的日期部分的参数。下表列出了SQL Server 2005 可识别的日期部分及其缩写。 表10-9中的weekday (dw) 日期部分返回星期几（如星期日、星期一等）。 表10-8 常用日期时间函数 表10-9 日期部分及缩写

  33. 日期和时间函数例题 例10-16：返回系统时间。 SELECT GETDATE() AS 系统时间 该语句的运行结果如图10.14所示。 例10-17：使用DATEDIFF()函数查询Students表中所有学生的年龄。 USE Student GO SELECT Student_name,Student_sex, DATEDIFF(year, Student_birthday, GETDATE()) AS Student_age FROM Students GO 程序的运行结果如图10.15所示。 图10.14例10-16执行结果 图10.15 例10-17执行结果

  34. (四) 数学函数 数学函数能够对数字型表达式进行数学运算，然后将结果或结果集返回给用户。能够在数学函数中使用的数据类型有decimal、integer、float、real、money、smallnoney、smallint和tinyint。常用的数学函数及其功能如表10-10所示。 例10-18：使用SIN()函数和PI()函数。 SELECT SIN(28.62) AS 'SIN(28.62)的值', PI() AS 'PI()的值' 该语句的运行结果如图10.16所示。 图10.16 例10-18执行结果 表10-10 常用数学函数

  35. (五) 转换函数 一般情况下，将数据从一种数据类型转换为另一种数据类型的工作是由SQL Server自动完成的，这种转换称为隐性转换或自动转换。但也有SQL Server无法自动完成的转换，这时可以使用转换函数进行显式的转换。显式转换函数及其功能如表10-11所示。 CAST()函数和CONVERT()函数都可以将一种数据类型的数据强制的转换为另一种数据类型，但是两者也有差别。CAST()函数不是使用逗号，而是使用AS来分隔两个参数。而CONVERT()函数的功能比CAST()函数更为细化，它可以按照参数style指定的格式将日期型数据转换成不同的样式，参数style可以使用的日期样式读者可自行查阅SQL Server 2005联机丛书。 例10-19：查询Students表中6月出生的学生信息。 USE Student GO SELECT Student_id,Student_name,Student_birthday FROM Students WHERE CONVERT(char(30), Student_birthday,101) LIKE '06%' GO 程序的运行结果如图10.17所示。 表10-11 数据类型转换函数 图10.17 例10-19执行结果

  36. (六) 用户自定义函数 • 在T-SQL中，除了可以直接使用前面介绍的系统函数之外，还允许用户编写自己的函数，以扩展T-SQL的编程能力。用户自行编写的函数称之为用户自定义函数，和系统内置函数一样，用户自定义函数中可以包含零个或多个参数，并返回相应的数据。 • 1．用户自定义函数的创建 • 用户可以使用CREATE FUNCTION语句来创建自定义函数，其语法格式如下： • CREATE FUNCTION 函数名 • （形式参数名称AS 数据类型，[形式参数名称AS 数据类型]，……） • RETURNS 返回数据类型AS • BEGIN • 函数内容 • RETURN 表达式 • END • 格式中各部分说明如下： • 函数名的定义必须遵循SQL Server的标识符命名规则。 • RETURNS子句指定自定义函数的返回数据类型。根据返回数据类型的不同用户自定义函数可以分为三类：标量值用户自定义函数、内联表值用户自定义函数和多语句表值用户自定义函数。如果RETURNS 子句指定的是一种标量数据类型，则函数为标量值函数，即标量值用户自定义函数的返回值为单个数据值。如果RETURNS 子句指定TABLE，则函数为内联表值用户自定义函数或多语句表值用户自定义函数。 • BEGIN..END部分为函数体，标量值用户自定义函数和多语句表值用户自定义函数的函数体都被封装在以BEGIN开始，END结束的范围内，而直接表值用户自定义函数没有明确的函数体，只是一个单个的SELECT语句。

  37. 用户自定义函数 例10-20：编写一个标量值用户自定义函数，根据输入的半径计算圆的面积。 CREATE FUNCTION Circle_Area (@Radius AS DECIMAL(5,1)) RETURNS DECIMAL(13,4) AS BEGIN RETURN(PI()*@Radius*@Radius) END GO 例10-21：编写一个内联表值用户自定义函数，根据输入的课程号，查询该课程的基本信息。 USE Student GO CREATE FUNCTION Course_Information (@CourseNumber AS char(4)) RETURNS TABLE AS RETURN (SELECT * FROM Courses WHERE Course_id=@CourseNumber)

  38. 用户自定义函数 2．用户自定义函数的调用 可以使用SELECT语句或PRINT语句调用用户自定义函数。其基本语法格式为： SELECT 用户名．函数名称（实际参数表） 或 PRINT 用户名．函数名称（实际参数表） 如果调用的是标量值用户自定义函数，一定要在函数名称的前面加上用户名，如果是表值型用户自定义函数，则没有这个要求。 例10-22：调用自定义函数Circle_Area，求给定半径的圆面积。 SELECT dbo.Circle_Area(1) AS 圆面积 程序的运行结果如图10.18所示。 例10-23：调用自定义函数Course_Information，输出相应班级的信息。 USE Student SELECT * FROM Course_Information('4001') 程序的运行结果如图10.19所示。 图10.19 例10-23执行结果 图10.18 例10-22执行结果

  39. 用户自定义函数 3．用户自定义函数的修改和删除 用户自定义函数的修改使用的是ALTER FUNCTION语句，ALTER FUNCTION语句的其余部分语法格式与CREATE FUNCTION语句相同，在此不做赘述。 用户自定义函数的删除使用DROP FUNCTION语句，其语法格式如下： DROP FUNCTION [用户名．]函数名 4．使用Microsoft SQL Server Management Studio创建用户自定义函数 除了按照上述方法在查询文档中使用代码来创建、修改和删除用户自定义函数，还可以使用Microsoft SQL Server Management Studio直接创建、修改和删除用户自定义函数，步骤如下： （1）打开Microsoft SQL Server Management Studio。 （2）打开相应的数据库前的“+”号，再打开“可编程性”前的“+”号。 （3）右击【函数】，选择【新建】命令，如图10.20所示，即可创建不同类型的用户 自定义函数。 （4）要修改或删除已有自定义函数，只需在相应类型的函数下找到要修改或删除的具体函数，如图10.21所示，使用右键快捷菜单选择相应命令即可。

  40. 用户自定义函数 图10.20 创建用户自定义函数 图10.21 修改、删除用户自定义函数

  41. IF…ELSE语句 BEGIN…END语句 RETURN语句 CASE语句 WAITFOR语句 WHILE…CONTINUE…BREAK语句 GOTO语句 第四节 流程控制语句 SQL Server支持结构化的编程方法，结构化编程中程序流程控制的三大结构是顺序结构、选择结构、循环结构。T-SQL语言提供了可以实现这三种结构的流程控制语句，使用这些流程控制语句可以控制命令的执行顺序，以便更好的组织程序。SQL Server中的流程控制语句有BEGIN…END、IF…ELSE、WHILE…CONTINUE…BREAK、GOTO、WAITFOR、RETURN等。

  42. (一) BEGIN…END语句 BEGIN…END语句相当于其他语言中的复合语句，如C语言中的{}。它用于将多条T-SQL语句封装为一个整体的语句块，即将BEGIN…END内的所有T-SQL语句视为一个单元执行。在实际应用中，BEGIN…END语句一般与IF..ELSE、WHILE等语句联用，当判断条件符合需要执行两个或者多个语句时，就需要使用BEGIN…END语句将这些语句封装为一个语句块。BEGIN...END 语句块允许嵌套。 BEGIN…END语句的基本语法格式为： BEGIN T-SQL命令行 END

  43. (二) IF…ELSE语句 • IF…ELSE语句是条件判断语句，用以实现选择结构。当IF后的条件成立时就执行其后的T-SQL语句，条件不成立时执行ELSE后的T-SQL语句。其中，ELSE子句是可选项，如果没有ELSE子句，当条件不成立则执行IF语句后的其他语句。 • IF…ELSE语句的语法格式为： • IF 条件表达式 • 程序块 • [ELSE • 程序块] • 格式中各部分说明如下： • 条件表达式是作为执行和判断条件的布尔表达式，返回TRUE 或FALSE，如果布尔表达式中含有SELECT 语句，必须用圆括号将SELECT 语句括起来。 • 程序块是一条T-SQL语句或者是一个BEGIN…END语句块。 • IF..ELSE语句允许嵌套使用，可以在IF之后或在ELSE 下面，嵌套另一个IF语句，嵌套级数的限制取决于可用内存。

  44. IF…ELSE语句例题 例10-24：在Student_course 表中查询课程“电子商务基础”的平均成绩，并输出相应的提示信息。 USE Student GO /*若学生平均成绩大于70，输出“平均成绩达到一般水平”，否则输出“平均成绩有待提高”*/ IF (SELECT AVG (Student_grade) FROM Student_course WHERE Course_id='1001')>70 BEGIN PRINT '电子商务基础的平均成绩达到一般水平' SELECT AVG (Student_grade) AS 平均成绩 FROM Student_course WHERE Course_id='1001' END ELSE BEGIN PRINT '电子商务基础的平均成绩还有待提高' SELECT AVG (Student_grade) AS 平均成绩 FROM Student_course WHERE Course_id='1001' END GO 图10.22 例10-24执行结果

  45. (三) CASE语句 • CASE语句和IF..ELSE语句一样，也用来实现选择结构。但是它与IF..ELSE语句相比，可以更方便的实现多重选择的情况，从而可以避免多重的IF..ELSE语句的嵌套，使得程序的结构更加简练、清晰。T-SQL中的CASE语句可分为简单CASE语句和搜索CASE语句两种。 • 1．简单CASE语句 • 简单CASE语句的语法格式为： • CASE 表达式 • WHEN 表达式THEN 结果表达式 • …… …… • [ELSE 结果表达式] • END • 格式中各部分说明如下： • CASE后的表达式用于和WHEN后的表达式逐个进行比较，两者数据类型必须相同，或必须是可以进行隐式转换的数据类型。 • …… ……表示可以有多个“WHEN 表达式THEN 结果表达式”结构。 • THEN后面给出当CASE后的表达式值与WHEN后的表达式相等时，要返回的结果表达式。 • 简单CASE语句的执行过程为：首先计算CASE后面表达式的值，然后按指定顺序对每个WHEN子句后的表达式进行比较，当遇到与CASE后表达式值相等的，则执行对应的THEN后的结果表达式，并退出CASE结构。若CASE后的表达式值与所有WHEN后的表达式均不相等，则返回ELSE后的结果表达式。若CASE后的表达式值与所有WHEN后的表达式均不相等，且“ELSE 结果表达式”部分被省略，则返回NULL值。

  46. CASE语句例题 例10-25：查询Students表中所有男生的基本情况，输出学号、姓名、性别及班级名称。 USE Student GO --使用CASE语句将学生的班级号替换为班级名称 SELECT 学号=Student_id, 姓名=Student_name , 性别=Student_sex,班级名= CASE Student_classid WHEN '2005011' THEN '电子商务1班' WHEN '2005012' THEN '电子商务2班' WHEN '2005013' THEN '电子商务3班' WHEN '2004014' THEN '会计1班' WHEN '2004015' THEN '会计2班' END FROM Students WHERE Student_sex ='男' ORDER BY Student_id GO 图10.23 例10-25执行结果

  47. CASE语句 • 2．搜索CASE语句 • 搜索CASE语句的语法格式为： • CASE • WHEN 条件表达式THEN 结果表达式 • …… …… • ELSE 结果表达式 • END • 格式中各部分说明如下： • CASE后无表达式。 • WHEN后的条件表达式是作为执行和判断条件的布尔表达式。 • …… ……表示可以有多个“WHEN 条件表达式THEN 结果表达式”结构。 • 搜索CASE语句的执行过程为：首先测试WHEN后的条件表达式，若为真，则执行THEN后的结果表达式，否则进行下一个条件表达式的测试。若所有WHEN后的条件表达式都为假，则执行ELSE后的结果表达式。若所有WHEN后的条件表达式都为假，且“ELSE 结果表达式”部分被省略，则返回NULL值。

  48. CASE语句例题 例10-26：根据Student_course表中的学生成绩，判断学生成绩是否达到优秀（85分及以上为优秀）。 USE Student SELECT 学号= Student_id,成绩= Student_grade,优秀否= CASE WHEN Student_grade >=85 THEN '优秀' WHEN Student_grade <85 THEN ' ' END FROM Student_course GO 程序的运行结果如图10.24所示。 从本例中可以看出搜索CASE语句和简单CASE语句的主要区别在于，搜索CASE语句中WHEN后的表达式允许是各种的比较操作或多种条件的测试，而简单CASE语句中WHEN后的表达式只能用来和CASE后的表达式值进行相等的比较。 图10.24 例10-26执行结果

  49. (四) WHILE…CONTINUE…BREAK语句 • WHILE…CONTINUE…BREAK语句用以实现循环结构，其功能是在满足条件的情况下会重复执行T-SQL语句或语句块。当WHILE后面的条件为真时，就重复执行BEGIN…END之间的语句块。WHILE语句中的CONTINUE和BREAK可以是可选项。若有CONTINUE语句，其功能是使程序跳出本次循环，开始执行下一次循环。而执行到BREAK语句时，会立即终止循环，结束整个WHILE语句的执行，并继续执行WHILE语句后的其他语句。 • WHILE…CONTINUE…BREAK语句的语法格式为： • WHILE 条件表达式 • BEGIN • 程序块 • [BREAK] • 程序块 • [CONTINUE] • 程序块 • END • 格式中各部分说明如下： • 条件表达式是作为执行和判断条件的布尔表达式，返回TRUE 或FALSE，如果布尔表达式中含有SELECT 语句，必须用圆括号将SELECT 语句括起来。 • 程序块是一条T-SQL语句或者是一个BEGIN…END语句块。

  50. WHILE…CONTINUE…BREAK语句例题 例10-27：在查询到Student_course表中没有学生成绩超过95分的情况下，将所有成绩提高3%，反复执行，直到存在成绩超过95分的情况下，退出循环。 USE Student GO WHILE NOT EXISTS (SELECT Student_grade FROM Student_course WHERE Student_grade >95) BEGIN UPDATE Student_course SET Student_grade= Student_grade*1.03 IF (SELECT MAX(Student_grade) FROM Student_course)>95 BREAK ELSE CONTINUE END SELECT Student_id, Student_grade FROM Student_course GO 程序的运行结果如图10.25所示。 图10.25 例10-27执行结果

More Related