《程序设计》PPT课件.ppt

第十章 T-SQL程序设计,1,本章主题,将两个或更多的表联接起来以看到更多的结果信息 获取一种通过变量以临时保存信息的方法 如何将行信息保存到非永久的表中 如何聚合值 组织输出数据到相关的信息分组中 返回唯一和独特的值 错误处理：如何创建你自己的错误，捕捉错误，并让代码更安全,T-SQL程序设计,3,T-SQL的目的在于为处理大量数据提供必要的结构化处理能力。前面的章节中已经介绍了一些处理简单查询的T-SQL语句，但是当面对更为复杂的应用时，前面所学的知识就远远不够了。因此，在本章中将主要讨论如何使用T-SQL提供的丰富编程结构，编写出复杂的例行程序。,第一节 T-SQL简介,4,T-SQL是Microsoft SQL Server提供的查询语言，它是Microsoft公司对于ANSI SQL的一个扩展，它不仅提供了对SQL标准的支持，另外还提供了类似于C等编程语言的基本功能。T-SQL的目的在于为事务型数据库开发提供一套过程化的开发工具。 T-SQL对于使用SQL Server非常重要，它是SQL Server功能的核心，使用T-SQL编写程序可以完成所有的数据库管理工作，与SQL Server通信的所有程序都通过向数据库服务器发送T-SQL语句来进行通信，而与应用程序的用户界面是什么形式无关。,第二节 T-SQL编程基础,5,(一) 注释,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.760 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 按位或, 1516 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.9例10-11执行结果,图10.10例10-12执行结果,(二) 字符串函数,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-10 常用数学函数,图10.16 例10-18执行结果,(五) 转换函数,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，则函数为内联表值用户自定义函数或多语句表值用户自定义函数。 BEGINEND部分为函数体，标量值用户自定义函数和多语句表值用户自定义函数的函数体都被封装在以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.18 例10-22执行结果,图10.19 例10-23执行结果,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,SQL Server支持结构化的编程方法，结构化编程中程序流程控制的三大结构是顺序结构、选择结构、循环结构。T-SQL语言提供了可以实现这三种结构的流程控制语句，使用这些流程控制语句可以控制命令的执行顺序，以便更好的组织程序。SQL Server中的流程控制语句有BEGINEND、IFELSE、WHILECONTINUEBREAK、GOTO、WAITFOR、RETURN等。,(一) BEGINEND语句,42,BEGINEND语句相当于其他语言中的复合语句，如C语言中的。它用于将多条T-SQL语句封装为一个整体的语句块，即将BEGINEND内的所有T-SQL语句视为一个单元执行。在实际应用中，BEGINEND语句一般与IFELSE、WHILE等语句联用，当判断条件符合需要执行两个或者多个语句时，就需要使用BEGINEND语句将这些语句封装为一个语句块。BEGIN.END 语句块允许嵌套。 BEGINEND语句的基本语法格式为： BEGIN T-SQL命令行 END,(二) IFELSE语句,43,IFELSE语句是条件判断语句，用以实现选择结构。当IF后的条件成立时就执行其后的T-SQL语句，条件不成立时执行ELSE后的T-SQL语句。其中，ELSE子句是可选项，如果没有ELSE子句，当条件不成立则执行IF语句后的其他语句。 IFELSE语句的语法格式为： IF 条件表达式 程序块 ELSE 程序块 格式中各部分说明如下： 条件表达式是作为执行和判断条件的布尔表达式，返回 TRUE 或 FALSE，如果布尔表达式中含有 SELECT 语句，必须用圆括号将 SELECT 语句括起来。 程序块是一条T-SQL语句或者是一个BEGINEND语句块。 IFELSE语句允许嵌套使用，可以在IF之后或在 ELSE 下面，嵌套另一个 IF语句，嵌套级数的限制取决于可用内存。,44,IFELSE语句例题,例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执行结果,(三) CASE语句,45,CASE语句和IFELSE语句一样，也用来实现选择结构。但是它与IFELSE语句相比，可以更方便的实现多重选择的情况，从而可以避免多重的IFELSE语句的嵌套，使得程序的结构更加简练、清晰。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,例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,CASE语句例题,图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执行结果,(四) WHILECONTINUEBREAK语句,49,WHILECONTINUEBREAK语句用以实现循环结构，其功能是在满足条件的情况下会重复执行T-SQL语句或语句块。当WHILE后面的条件为真时，就重复执行BEGINEND之间的语句块。WHILE语句中的CONTINUE和BREAK可以是可选项。若有CONTINUE语句，其功能是使程序跳出本次循环，开始执行下一次循环。而执行到BREAK语句时，会立即终止循环，结束整个WHILE语句的执行，并继续执行WHILE语句后的其他语句。 WHILECONTINUEBREAK语句的语法格式为： WHILE 条件表达式 BEGIN 程序块 BREAK 程序块 CONTINUE 程序块 END 格式中各部分说明如下： 条件表达式是作为执行和判断条件的布尔表达式，返回 TRUE 或 FALSE，如果布尔表达式中含有 SELECT 语句，必须用圆括号将 SELECT 语句括起来。 程序块是一条T-SQL语句或者是一个BEGINEND语句块。,50,例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所示。,WHILECONTINUEBREAK语句例题,图10.25 例10-27执行结果,(五) GOTO语句,51,GOTO语句用来改变程序的执行流程，使程序无条件跳转到指定的标签处继续执行。GOTO语句可以出现在条件控制流语句、语句块或过程中，但它不能跳转到该批以外的标签。 GOTO语句的语法格式为： GOTO 标签 GOTO语句中的跳转目标的标签定义格式为： 标签： 定义标签时，需要在标签的名字后面加上一个冒号。 例10-28：求出1100之中所有的偶数之和。 DECLARE Sum int,i int SET i = 0 SET Sum = 0 Label_1: SET i=i+2 SET Sum =Sum +i IF i 100 GOTO Label_1 PRINT 1100之中所有的偶数之和是： +cast(Sum as varchar(50) 程序的运行结果如图10.26所示。,图10.26 例10-28执行结果,(六) WAITFOR语句,52,WAITFOR语句用于在达到指定时间或时间间隔之前，阻止执行批处理、存储过程或事务，直到所设定的时间已到或等待了指定的时间间隔之后才继续往下运行。 WAITFOR语句的语法格式为： WAITFOR DELAY 等待时间 | TIME 完成时间 格式中各部分说明如下： DELAY 等待时间是指定可以继续执行批处理、存储过程或事务之前必须经过的指定时段，最长可为24 小时。可使用 datetime 数据可接受的格式之一指定等待时间，也可以将其指定为局部变量，但不能指定日期，因此不允许指定 datetime 值的日期部分。 TIME 完成时间是指定运行批处理、存储过程或事务的具体时刻。可以使用 datetime 数据可接受的格式之一指定完成时间，也可以将其指定为局部变量，但不能指定日期，因此不允许指定 datetime 值的日期部分。,53,WAITFOR语句例题,例10-29：在晚上 10:20 (22:20) 执行存储过程 sp_update_job。 USE msdb EXECUTE sp_add_job job_name = TestJob BEGIN WAITFOR TIME 22:20 EXECUTE sp_update_job job_name = TestJob,new_name = UpdatedJob END GO 例10-30：先查询Students表中男生姓名，等待5秒后再查询男生姓名和出生日期。 USE Student GO SELECT Student_name FROM Students WHERE Student_sex =男 GO -设置等待时间为5秒钟 WAITFOR DELAY 00:00:05 SELECT Student_name, Student_birthday FROM Students WHERE Student_sex =男 GO 程序的运行结果如图10.27所示。 运行程序时先出现男同学姓名的查询结果，5秒钟后才出现下方的姓名和出生日期。,图10.27 例10-30执行结果,(七) RETURN语句,54,RETURN语句用来从批处理、查询或存储过程中无条件退出，RETURN的执行是即时且完全的，可在任何时候用于从过程、批处理或语句块中退出。位于RETURN语句之后的语句将不会被执行。 RETURN语句的语法格式为： RETURN 整型表达式 使用RETURN语句可以返回一个整数值，一般情况下，只有存储过程（存储过程将在下一章中进行介绍）才会向执行调用的过程或应用程序返回一个整数值，可将RETURN的返回值作为程序是否成功执行的判断标志。在SQL Server中除非另有说明，所有系统存储过程返回0值，即表示调用成功，而非零值则表示调用失败。,第五节 游标,55,一.游标简介 数据库中执行的大多数SQL命令，都是用来处理行的集合的，即是针对集合内所有数据的处理。但是在某些情况下，用户也需要使用代码来逐行的处理数据，为此SQL提供了可以逐行遍历记录行集合的游标。 游标的本质是一种能从包括多数据记录的结果集中每次提取一条记录的机制。因此，游标总是与一条SELECT语句相关联，它可以定位于SELECT结果集中的某一指定行，从而实现对该行的操作。从这个角度上讲，数据库的游标是一种类似于C语言中指针的语言结构。 游标在使用时首先根据SELECT语句创建结果集，然后一次从中获取一行数据。游标的生命周期包含以下5个阶段： 声明游标。 打开游标。 使用游标，从游标中提取信息。 关闭游标。 释放游标。,使用游标,56,前面已经提出了游标有5种基本操作，即声明游标、打开游标、使用游标、关闭游标和释放游标，下面就来分别讨论一下这5种基本操作的具体实现。 1声明游标 使用游标前首先要声明游标，声明游标时会为游标指定获取数据时所使用的SELECT语句。声明游标的语法格式如下： DECLARE 游标名 INSENSITIVE SCROLL CURSOR FOR SELECT 语句 FOR READ ONLY | UPDATE OF 列名1，列名2，列名3 格式中各部分说明如下： 游标名的命名必须遵守T-SQL的标识符命名规则。 使用INSENSITIVE选项定义的游标，会创建将由该游标使用的数据的临时副本。也就是说，该游标使用的结果集将被复制到临时数据库tempdb中形成一个临时表，对游标的所有请求都从 tempdb 中的这一临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基础表所做的修改，并且该游标不允许修改。如果省略 INSENSITIVE，则已提交的对基础表的删除和更新都反映在后面的提取中。 SCROLL选项指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果未指定 SCROLL，则 NEXT 是唯一支持的提取选项。,57,使用游标,各提取选项含义（具体含义见FETCH语句说明部分）： FIRST：提取第一行数据； LAST：提取最后一行数据； PRIOR：提取前一行数据； NEXT：提取后一行数据； RELATIVE：按相对位置提取数据； ABSOLUTE：按绝对位置提取数据。 SELECT语句是指定义游标结果集的标准SELECT 查询语句。在声明游标的SELECT语句内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO，该SELECT 查询语句所查询的表称为游标的基础表。 READ ONLY选项表示禁止通过该游标进行更新，即所声明的游标为只读。在 UPDATE 或 DELETE 语句的 WHERE CURRENT OF 子句中不能引用游标。 UPDATE OF 列名1，列名2，列名3用来定义在所声明的游标中可以更新的列。如果指定了OF 列名1，列名2，列名3，则只允许修改列出的列。如果指定了 UPDATE，但未指定列的列表，则可以更新所有列。,58,使用游标,2打开游标 在使用游标读取数据之前，必须要打