第十章 T-SQL程序设计.ppt

1,第十章T-SQL,本章主题,将两个或更多的表联接起来以看到更多的结果信息获取一种通过变量以临时保存信息的方法如何将行信息保存到非永久的表中如何聚合值组织输出数据到相关的信息分组中返回唯一和独特的值错误处理：如何创建你自己的错误，捕捉错误，并让代码更安全,3,T-SQL程序设计,T-SQL的目的在于为处理大量数据提供必要的结构化处理能力。前面的章节中已经介绍了一些处理简单查询的T-SQL语句，但是当面对更为复杂的应用时，前面所学的知识就远远不够了。因此，在本章中将主要讨论如何使用T-SQL提供的丰富编程结构，编写出复杂的例行程序。,4,第一节T-SQL简介,T-SQL是MicrosoftSQLServer提供的查询语言，它是Microsoft公司对于ANSISQL的一个扩展，它不仅提供了对SQL标准的支持，另外还提供了类似于C等编程语言的基本功能。T-SQL的目的在于为事务型数据库开发提供一套过程化的开发工具。T-SQL对于使用SQLServer非常重要，它是SQLServer功能的核心，使用T-SQL编写程序可以完成所有的数据库管理工作，与SQLServer通信的所有程序都通过向数据库服务器发送T-SQL语句来进行通信，而与应用程序的用户界面是什么形式无关。,5,第二节T-SQL编程基础,6,(一)注释,T-SQL中的注释语句，也称为注解，注释内容通常是一些说明性文字，对程序的结构及功能给出简要的解释。注释语句不是可执行语句，不被系统编译，也不被程序执行。使用注释语句的目的是为了使程序代码易读易分析，也便于日后的管理和维护。SQLServer支持两种形式的程序注释语句：1单行注释语句：使用ANSI标准的注释符“-”，注释语句写在“-”的后面，只能书写单行。2多行注释语句：使用与C语言相同的程序注释符“/*/”，注释语句写在“/*”和“*/”之间，可以连续书写多行。例10-1：合法的注释语句。-打开Student数据库USEStudentGO/*查询Classes表中所有记录的Class_id,Class_name以及Class_department三个字段的内容*/,7,注释,SELECTClass_id,Class_name,Class_departmentFROMClassesGO当然，连续多行注释也可以使用注释符“-”，只要每行注释开头都加上注释符“-”。单行注释语句也可以使用注释符“/*/”，只要将注释语句写在“/*”和“*/”之间。例如上面的例子中注释语句也可以写成：/*打开Student数据库*/USEStudentGO-本条SQL语句用来查询Classes表中所有记录的Class_id,Class_name,-Class_department三个字段的内容SELECTClass_id,Class_name,Class_departmentFROMClassesGO,8,(二)批处理,所谓批是指从客户机传送到服务器上的一组完整数据和SQL指令，批中的所有SQL语句做为一个整体编译成一个执行单元后从应用程序一次性地发送到SQLServer服务器进行执行，称之为批处理。所有的批处理命令都使用GO作为结束标志，当T-SQL的编译器扫描到某行的前两个字符是GO的时候，它会把GO前面的所有语句作为一个批处理送往服务器。由于批处理中的所有语句被当作是一个整体，因此若其中一个语句出现了编译错误，则该批处理内所有语句的执行都将被取消。例10-2：正确的批处理。-第一个批处理打开Student数据库USEStudentGO-第二个批处理在Teachers表中查询姓王的教师的记录SELECT*FROMTeachersWHERESUBSTRING(Teacher_name,1,1)=王GO,9,(三)局部变量,变量是执行程序中必不可少的部分，它主要是用来在程序运行过程中存储和传递数据。变量其实就是内存中的一个存储区域，存储在这个区域中的数据就是变量的值。在T-SQL语句中变量有两种，局部变量与全局变量。这两种变量在使用方法和具体意义上均不相同，本部分主要介绍局部变量。局部变量是作用域局限在一定范围内的变量，是用户自定义的变量。一般来说，局部变量的使用范围局限于定义它的批处理内。定义它的批处理中的SQL语句可以引用这个局部变量，直到批处理结束，这个局部变量的生命周期也就结束了。1局部变量的声明在使用一个局部变量之前，必须先声明该变量。声明一个局部变量的语法格式如下：DECLARE变量名变量类型，变量名变量类型声明语句中的各部分说明如下：局部变量名的命名必须遵循SQLServer的标识符命名规则，并且必须以字符“”开头。局部变量的类型可以是系统数据类型，也可以是用户自定义的数据类型。DECLARE语句可以声明一个或多个局部变量，变量被声明以后初值都是NULL。如语句DECLAREMyScoreINT声明了一个整型局部变量MyScore。也可以在同一语句中声明多个局部变量，例如：DECLAREStudent_NameVARCHAR(20),Student_AddressVARCHAR(60),10,2局部变量的赋值局部变量被创建之后，系统将其初始值设为NULL。若要改变局部变量的值，可以使用SET语句或SELECT语句给局部变量重新赋值。SELECT语句的语法格式为：SELECT变量名=表达式，变量名=表达式SET语句的语法格式为：SET变量名=表达式赋值语句中的各部分说明如下：变量名是准备为其赋值的局部变量。表达式是有效的SQLServer表达式，且其类型应与局部变量的数据类型相匹配。从语法格式中可看出，SELECT语句和SET语句的区别在于使用SET语句一次只能给一个变量赋值，而在SELECT语句中可以一次给多个变量赋值。,局部变量,11,局部变量,例10-3：局部变量的赋值。USEStudentGO-声明局部变量DECLAREsnameVARCHAR(30),sclassidVARCHAR(30)-使用SET语句给局部变量赋值SETsname=李佳佳SETsclassid=2005011-根据局部变量的值查询符合条件的记录的姓名和性别SELECTStudent_name,Student_sex,Student_classidFROMStudentsWHEREStudent_name=snameORStudent_classid=sclassid-将Student_id为11001的记录的姓名和班级赋值给局部变量SELECTsname=Student_name,sclassid=Student_classidFROMStudentsWHEREStudent_id=11001-输出变量sname和sclassid的值SELECTsnameASsname,sclassidASsclassidGO,12,局部变量,本例主要完成了两个操作，一是根据局部变量的初值在Students表中查询符合条件的记录，二是将指定记录的姓名及班级号的值赋给局部变量，改变局部变量原来的值并输出其新值。程序的运行结果如图10.1所示。,图10.1例10-3执行结果,13,局部变量,3局部变量的输出例10-3中使用了SELECT语句来输出局部变量的值，除此之外局部变量的输出还可以使用PRINT语句。PRINT语句的语法格式如下：PRINT局部变量名如例10-3中最后的输出语句SELECTsnameASsname,sclassidASsclassid也可以改写成如下的两条PRINT语句：PRINTsnamePRINTsclassid,14,(四)全局变量,全局变量是以“”开头，由系统预先定义并负责维护的变量，也可以把全局变量看成是一种特殊形式的函数。全局变量不可以由用户随意建立和修改，作用范围也并不局限于某个程序，而是任何程序均可调用。常用的全局变量有三十多个，通常用来存储一些SQLServer的配置值和效能统计数字，用户可以通过查询全局变量来检测系统的参数值或者执行查询命令后的状态值。在全局变量的使用过程中要注意，全局变量是由SQLServer服务器定义的，不是用户自定义的，用户只能使用预先定义的全局变量，且在引用全局变量时，必须以“”开头。另外，局部变量的名称不能与全局变量的名称相同，否则就会在应用程序中出错。表10-1中列出了SQLServer的几个常用全局变量及其含义，对于其他全局变量，读者可通过自行查阅SQLServer2005联机丛书进行学习。,表10-1SQLServer的常用全局变量,15,全局变量,例10-4：全局变量ERROR的使用。USEStudentGO-在Teachers表中插入一条新记录INSERTINTOTeachers(Teacher_id,Teacher_name,Teacher_department)VALUES(JS006,张海涛,002)-使用全局变量ERROR输出错误代码的值SELECTERRORAS错误GO,图10.2例10-4执行结果,16,(五)运算符,使用T-SQL来编写例行程序时，不可避免的要使用到运算符。运算符用来完成一个或多个表达式中指定的操作，SQLServer中的运算符包括有：算术运算符、比较运算符、逻辑运算符、位运算符、赋值运算符、连接运算符。1算术运算符算术运算符用来执行算术运算，T-SQL中的算术运算符包括：+（加法运算符）-（减法运算符）*（乘法运算符）/（除法运算符）%（模运算符或取余运算符，返回一个除法的整数余数，要求数据类型为INT、SMALLINT和TINYINT）,17,运算符,例10-5：计算算术表达式的值。DECLAREValueNUMERICSETValue=132%7PRINTValue程序的运行结果如图10.3所示。2赋值运算符T-SQL中的赋值运算符只有一个，就是“”（等号）。和其它语言中的赋值运算符一样，T-SQL中的赋值运算符的作用就是将数据值指派给特定的对象。此外，也可以使用赋值运算符在列标题和列定义值的表达式之间建立关系。,图10.3例10-5执行结果,18,运算符,例10-6：查询经济信息系所有教师的信息，要求输出时的列标题为：教师号、教师姓名、教师所在系，且要求教师所在系名显示为“信息系”。USEStudentGOSELECT教师号=Teachers.Teacher_id,教师姓名=Teachers.Teacher_name,教师所在系=SUBSTRING(Departments.Department_name,3,3)FROMTeachersINNERJOINDepartmentsONTeachers.Teacher_department=Departments.Department_idWHEREDepartments.Department_name=经济信息系GO,图10.4例10-6执行结果,19,运算符,3比较运算符比较运算符用于比较两个表达式的大小，T-SQL中的比较运算符包括：（大于）、=（大于等于）、（小于）、=（小于等于）、=（等于）、！=，（不等于）、！（不大于）、！（不小于）。比较运算符的运算结果是布尔数据类型，它有三种可能的结果：TRUE、FALSE以及UNKNOWN。例10-7：查询Student_course表中成绩的70%仍然大于60分的学生学号和原来的成绩。USEStudentGOSELECTStudent_id,Student_gradeFROMStudent_courseWHEREStudent_grade*0.760ORDERBYStudent_idASCGO,图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-2NOT运算的各种结果,表10-3AND运算的各种结果,表10-4OR运算的各种结果,22,运算符,例10-8：请查询出所有家庭所在地为山西的男同学。USEStudentGOSELECTStudent_id,Student_name,Student_sex,Student_homeFROMStudentsWHEREStudent_home=山西ANDStudent_sex=男GO,图10.6例10-8执行结果,23,运算符,5位运算符位运算符用来对两个表达式进行位操作，位运算符的操作数允许是整型数据或者二进制数据（但image数据类型除外）。此外，位运算还要求两个操作数不能同时是二进制数据。T-SQL中的位运算符包括:&（按位与）|（按位或）（按位异或）（按位取反）例10-9：计算位运算表达式的值。SELECT15&16AS按位与,15|16AS按位或,1516AS按位异或,15AS按位取反,图10.7例10-9执行结果,24,运算符,6连接运算符T-SQL中的连接运算符“+”用于连接字符串或二进制数据串、列名或列的混合体，其实质就是将一个串加入到另一个串的尾部。例10-10：在输出Classes表中班级名称时前面加上班级所在系别，如“经济信息系电子商务1班”。USEStudentGOSELECTClasses.Class_id,Departments.Department_name+Classes.Class_nameFROMClassesINNERJOINDepartmentsONClasses.Class_department=Departments.Department_idGO,图10.8例10-10执行结果,25,运算符,7运算符的优先级T-SQL中的运算符的处理顺序如表10-5所示。,表10-5T-SQL运算符优先级,26,第三节函数,为了让用户更方便的对数据库进行操作，SQLServer2005在T-SQL中提供了许多内置函数。函数其实就是一段程序代码，用户可以通过调用内置函数并为其提供所需的参数来执行一些特殊的运算或完成复杂的操作。T-SQL提供的函数有系统函数、字符串函数、日期和时间函数、数学函数、转换函数等。,27,(一)系统函数,系统函数可以返回有关当前环境的信息，例如有关服务器、用户、数据库对象的系统信息。常用的系统函数及其功能如表10-6所示。,表10-6常用系统函数,28,系统函数,例10-11：系统函数的使用。SELECT用户名=USER_NAME(),登陆名=SUSER_SID(),工作站名=HOST_NAME()该语句的运行结果如图10.9所示。例10-12：使用系统函数查询指定表的第一列的长度。USEStudentGODECLAREcol_nameVARCHAR(30)SELECTcol_name=COL_NAME(OBJECT_ID(Students),1)SELECTCOL_LENGTH(Students,col_name)AS第一列长度GO,图10.9例10-11执行结果,图10.10例10-12执行结果,29,(二)字符串函数,字符串函数主要是为了方便用户对二进制数据、字符串和表达式进行操作。常用的字符串函数及其功能如表10-7所示。,30,字符串函数例题,例10-13：在Students表中查询所有2005级学生的学号、姓名、性别、出生日期及所在班级号。USEStudentGOSELECTStudent_id,Student_name,Student_sex,Student_birthday,Student_classidFROMStudentsWHERESUBSTRING(Student_classid,1,4)=2005GO,图10.11例10-13执行结果,31,字符串函数例题,例10-14：在Users表中使用CHARINDEX()函数查询所有User_name中包含了字符串“ia”的记录的User_id。USEStudentGOSELECTUser_id,User_nameFROMUsersWHERECHARINDEX(ia,User_name)0GO程序的运行结果如图10.12所示。例10-15：在Users表中使用PATINDEX()函数查询所有User_name中包含了字符串“ia”的记录的User_id。USEStudentGOSELECTUser_id,User_nameFROMUsersWHEREPATINDEX(%_ia_%,User_name)0GO程序的运行结果如图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，是指定要返回的日期部分的参数。下表列出了SQLServer2005可识别的日期部分及其缩写。表10-9中的weekday(dw)日期部分返回星期几（如星期日、星期一等）。,表10-8常用日期时间函数,表10-9日期部分及缩写,33,日期和时间函数例题,例10-16：返回系统时间。SELECTGETDATE()AS系统时间该语句的运行结果如图10.14所示。例10-17：使用DATEDIFF()函数查询Students表中所有学生的年龄。USEStudentGOSELECTStudent_name,Student_sex,DATEDIFF(year,Student_birthday,GETDATE()ASStudent_ageFROMStudentsGO程序的运行结果如图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()函数。SELECTSIN(28.62)ASSIN(28.62)的值,PI()ASPI()的值该语句的运行结果如图10.16所示。,表10-10常用数学函数,图10.16例10-18执行结果,35,(五)转换函数,一般情况下，将数据从一种数据类型转换为另一种数据类型的工作是由SQLServer自动完成的，这种转换称为隐性转换或自动转换。但也有SQLServer无法自动完成的转换，这时可以使用转换函数进行显式的转换。显式转换函数及其功能如表10-11所示。CAST()函数和CONVERT()函数都可以将一种数据类型的数据强制的转换为另一种数据类型，但是两者也有差别。CAST()函数不是使用逗号，而是使用AS来分隔两个参数。而CONVERT()函数的功能比CAST()函数更为细化，它可以按照参数style指定的格式将日期型数据转换成不同的样式，参数style可以使用的日期样式读者可自行查阅SQLServer2005联机丛书。例10-19：查询Students表中6月出生的学生信息。USEStudentGOSELECTStudent_id,Student_name,Student_birthdayFROMStudentsWHERECONVERT(char(30),Student_birthday,101)LIKE06%GO程序的运行结果如图10.17所示。,表10-11数据类型转换函数,图10.17例10-19执行结果,36,(六)用户自定义函数,在T-SQL中，除了可以直接使用前面介绍的系统函数之外，还允许用户编写自己的函数，以扩展T-SQL的编程能力。用户自行编写的函数称之为用户自定义函数，和系统内置函数一样，用户自定义函数中可以包含零个或多个参数，并返回相应的数据。1用户自定义函数的创建用户可以使用CREATEFUNCTION语句来创建自定义函数，其语法格式如下：CREATEFUNCTION函数名（形式参数名称AS数据类型，形式参数名称AS数据类型，）RETURNS返回数据类型ASBEGIN函数内容RETURN表达式END格式中各部分说明如下：函数名的定义必须遵循SQLServer的标识符命名规则。RETURNS子句指定自定义函数的返回数据类型。根据返回数据类型的不同用户自定义函数可以分为三类：标量值用户自定义函数、内联表值用户自定义函数和多语句表值用户自定义函数。如果RETURNS子句指定的是一种标量数据类型，则函数为标量值函数，即标量值用户自定义函数的返回值为单个数据值。如果RETURNS子句指定TABLE，则函数为内联表值用户自定义函数或多语句表值用户自定义函数。BEGIN.END部分为函数体，标量值用户自定义函数和多语句表值用户自定义函数的函数体都被封装在以BEGIN开始，END结束的范围内，而直接表值用户自定义函数没有明确的函数体，只是一个单个的SELECT语句。,37,用户自定义函数,例10-20：编写一个标量值用户自定义函数，根据输入的半径计算圆的面积。CREATEFUNCTIONCircle_Area(RadiusASDECIMAL(5,1)RETURNSDECIMAL(13,4)ASBEGINRETURN(PI()*Radius*Radius)ENDGO例10-21：编写一个内联表值用户自定义函数，根据输入的课程号，查询该课程的基本信息。USEStudentGOCREATEFUNCTIONCourse_Information(CourseNumberASchar(4)RETURNSTABLEASRETURN(SELECT*FROMCoursesWHERECourse_id=CourseNumber),38,用户自定义函数,2用户自定义函数的调用可以使用SELECT语句或PRINT语句调用用户自定义函数。其基本语法格式为：SELECT用户名函数名称（实际参数表）或PRINT用户名函数名称（实际参数表）如果调用的是标量值用户自定义函数，一定要在函数名称的前面加上用户名，如果是表值型用户自定义函数，则没有这个要求。例10-22：调用自定义函数Circle_Area，求给定半径的圆面积。SELECTdbo.Circle_Area(1)AS圆面积程序的运行结果如图10.18所示。例10-23：调用自定义函数Course_Information，输出相应班级的信息。USEStudentSELECT*FROMCourse_Information(4001)程序的运行结果如图10.19所示。,图10.18例10-22执行结果,图10.19例10-23执行结果,39,用户自定义函数,3用户自定义函数的修改和删除用户自定义函数的修改使用的是ALTERFUNCTION语句，ALTERFUNCTION语句的其余部分语法格式与CREATEFUNCTION语句相同，在此不做赘述。用户自定义函数的删除使用DROPFUNCTION语句，其语法格式如下：DROPFUNCTION用户名函数名4使用MicrosoftSQLServerManagementStudio创建用户自定义函数除了按照上述方法在查询文档中使用代码来创建、修改和删除用户自定义函数，还可以使用MicrosoftSQLServerManagementStudio直接创建、修改和删除用户自定义函数，步骤如下：（1）打开MicrosoftSQLServerManagementStudio。（2）打开相应的数据库前的“+”号，再打开“可编程性”前的“+”号。（3）右击【函数】，选择【新建】命令，如图10.20所示，即可创建不同类型的用户自定义函数。（4）要修改或删除已有自定义函数，只需在相应类型的函数下找到要修改或删除的具体函数，如图10.21所示，使用右键快捷菜单选择相应命令即可。,40,用户自定义函数,图10.20创建用户自定义函数,图10.21修改、删除用户自定义函数,41,第四节流程控制语句,SQLServer支持结构化的编程方法，结构化编程中程序流程控制的三大结构是顺序结构、选择结构、循环结构。T-SQL语言提供了可以实现这三种结构的流程控制语句，使用这些流程控制语句可以控制命令的执行顺序，以便更好的组织程序。SQLServer中的流程控制语句有BEGINEND、IFELSE、WHILECONTINUEBREAK、GOTO、WAITFOR、RETURN等。,42,(一)BEGINEND语句,BEGINEND语句相当于其他语言中的复合语句，如C语言中的。它用于将多条T-SQL语句封装为一个整体的语句块，即将BEGINEND内的所有T-SQL语句视为一个单元执行。在实际应用中，BEGINEND语句一般与IF.ELSE、WHILE等语句联用，当判断条件符合需要执行两个或者多个语句时，就需要使用BEGINEND语句将这些语句封装为一个语句块。BEGIN.END语句块允许嵌套。BEGINEND语句的基本语法格式为：BEGINT-SQL命令行END,43,(二)IFELSE语句,IFELSE语句是条件判断语句，用以实现选择结构。当IF后的条件成立时就执行其后的T-SQL语句，条件不成立时执行ELSE后的T-SQL语句。其中，ELSE子句是可选项，如果没有ELSE子句，当条件不成立则执行IF语句后的其他语句。IFELSE语句的语法格式为：IF条件表达式程序块ELSE程序块格式中各部分说明如下：条件表达式是作为执行和判断条件的布尔表达式，返回TRUE或FALSE，如果布尔表达式中含有SELECT语句，必须用圆括号将SELECT语句括起来。程序块是一条T-SQL语句或者是一个BEGINEND语句块。IF.ELSE语句允许嵌套使用，可以在IF之后或在ELSE下面，嵌套另一个IF语句，嵌套级数的限制取决于可用内存。,44,IFELSE语句例题,例10-24：在Student_course表中查询课程“电子商务基础”的平均成绩，并输出相应的提示信息。USEStudentGO/*若学生平均成绩大于70，输出“平均成绩达到一般水平”，否则输出“平均成绩有待提高”*/IF(SELECTAVG(Student_grade)FROMStudent_courseWHERECourse_id=1001)70BEGINPRINT电子商务基础的平均成绩达到一般水平SELECTAVG(Student_grade)AS平均成绩FROMStudent_courseWHERECourse_id=1001ENDELSEBEGINPRINT电子商务基础的平均成绩还有待提高SELECTAVG(Student_grade)AS平均成绩FROMStudent_courseWHERECourse_id=1001ENDGO,图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,例10-25：查询Students表中所有男生的基本情况，输出学号、姓名、性别及班级名称。USEStudentGO-使用CASE语句将学生的班级号替换为班级名称SELECT学号=Student_id,姓名=Student_name,性别=Student_sex,班级名=CASEStudent_classidWHEN2005011THEN电子商务1班WHEN2005012THEN电子商务2班WHEN2005013THEN电子商务3班WHEN2004014THEN会计1班WHEN2004015THEN会计2班ENDFROMStudentsWHEREStudent_sex=男ORDERBYStudent_idGO,CASE语句例题,图10.23例10-25执行结果,47,CASE语句,2搜索CASE语句搜索CASE语句的语法格式为：CASEWHEN条件表达式THEN结果表达式ELSE结果表达式END格式中各部分说明如下：CASE后无表达式。WHEN后的条件表达式是作为执行和判断条件的布尔表达式。表示可以有多个“WHEN条件表达式THEN结果表达式”结构。搜索CASE语句的执行过程为：首先测试WHEN后的条件表达式，若为真，则执行THEN后的结果表达式，否则进行下一个条件表达式的测试。若所有WHEN后的条件表达式都为假，则执行ELSE后的结果表达式。若所有WHEN后的条件表达式都为假，且“ELSE结果表达式”部分被省略，则返回NULL值。,48,CASE语句例题,例10-26：根据Student_course表中的学生成绩，判断学生成绩是否达到优秀（85分及以上为优秀）。USEStudentSELECT学号=Student_id,成绩=Student_grade,优秀否=CASEWHENStudent_grade=85THEN优秀WHENStudent_grade95)BEGINUPDATEStudent_courseSETStudent_grade=Student_grade*1.03IF(SELECTMAX(Student_grade)FROMStudent_course)95BREAKELSECONTINUEENDSELECTStudent_id,Student_gradeFROMStudent_courseGO程序的运行结果如图10.25所示。,WHILECONTINUEBREAK语句例题,图10.25例10-27执行结果,51,(五)GOTO语句,GOTO语句用来改变程序的执行流程，使程序无条件跳转到指定的标签处继续执行。GOTO语句可以出现在条件控制流语句、语句块或过程中，但它不能跳转到该批以外的标签。GOTO语句的语法格式为：GOTO标签GOTO语句中的跳转目标的标签定义格式为：标签：定义标签时，需要在标签的名字后面加上一个冒号。例10-28：求出1100之中所有的偶数之和。DECLARESumint,iintSETi=0SETSum=0Label_1:SETi=i+2SETSum=Sum+iIFi100GOTOLabel_1PRINT1100之中所有的偶数之和是：+cast(Sumasvarchar(50)程序的运行结果如图10.26所示。,图10.26例10-28执行结果,52,(六)WAITFOR语句,WAITFOR语句用于在达到指定时间或时间间隔之前，阻止执行批处理、存储过程或事务，直到所设定的时间已到或等待了指定的时间间隔之后才继续往下运行。WAITFOR语句的语法格式为：WAITFORDELAY等待时间|TIME完成时间格式中各部分说明如下：DELAY等待时间是指定可以继续执行批处理、存储过程或事务之前必须经过的指定时段，最长可为24小时。可使用datetime数据可接受的格式之一指定等待时间，也可以将其指定为局部变量，但不能指定日期，因此不允许指定datetime值的日期部分。TIME完成时间是指定运行批处理、存储过程或事务的具体时刻。可以使用datetime数据可接受的格式之一指定完成时间，也可以将其指定为局部变量，但不能指定日期，因此不允许指定datetime值的日期部分。,53,WAITFOR语句例题,例10-29：在晚上10:20(22:20)执行存储过程sp_update_job。USEmsdbEXECUTEsp_add_jobjob_name=TestJobBEGINWAITFORTIME22:20EXECUTEsp_update_jobjob_name=TestJob,new_name=UpdatedJobENDGO例10-30：先查询Students表中男生姓名，等待5秒后再查询男生姓名和出生日期。USEStudentGOSELECTStudent_nameFROMStudentsWHEREStudent_sex=男GO-设置等待时间为5秒钟WAITFORDELAY00:00:05SELECTStudent_name,Student_birthdayFROMStudentsWHEREStudent_sex=男GO程序的运行结果如图10.27所示。运行程序时先出现男同学姓名的查询结果，5秒钟后才出现下方的姓名和出生日期。,图10.27例10-30执行结果,54,(七)RETURN语句,RETURN语句用来从批处理、查询或存储过程中无条件退出，RETURN的执行是即时且完全的，可在任何时候用于从过程、批处理或语句块中退出。位于RETURN语句之后的语句将不会被执行。RETURN语句的语法格式为：RETURN整型表达式使用RETURN语句可以返回一个整数值，一般情况下，只有存储过程（存储过程将在下一章中进行介绍）才会向执行调用的过程或应用程序返回一个整数值，可将RETURN的返回值作为程序是否成功执行的判断标志。在SQLServer中除非另有说明，所有系统存储过程返回0值，即表示调用成功，而非零值则表示调用失败。,55,第五节游标,一.游标简介数据库中执行的大多数SQL命令，都是用来处理行的集合的，即是针对集合内所有数据的处理。但是在某些情况下，用户也需要使用代码来逐行的处理数据，为此SQL提供了可以逐行遍历记录行集合的游标。游标的本质是一种能从包括多数据记录的结果集中每次提取一条记录的机制。因此，游标总是与一条SELECT语句相关联，它可以定位于SELECT结果集中的某一指定行，从而实现对该行的操作。从这个角度上讲，数据库的游标是一种类似于C语言中指针的语言结构。游标在使用时首先根据SELECT语句创建结果集，然后一次从中获取一行数据。游标的生命周期包含以下5个阶段：声明游标。打开游标。使用游标，从游标中提取信息。关闭游标。释放游标。,56,使用游标,前面已经提出了游标有5种基本操作，即声明游标、打开游标、使用游标、关闭游标和释放游标，下面就来分别讨论一下这5种基本操作的具体实现。1声明游标使用游标前首先要声明游标，声明游标时会为游标指定获取数据时所使用的SELECT语句。声明游标的语法格式如下：DECLARE游标名INSENSITIVESCROLLCURSORFORSELECT语句FORREADONLY|UPDATEOF列名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、COMPUTEBY、FORBROWSE和INTO，该SELECT查询语句所查询的表称为游标的基础表。READONLY选项表示禁止通过该游标进行更新，即所声明的游标为只读。在UPDATE或DELETE语句的WHERECURRENTOF子句中不能引用游标。UPDATEOF列名1，列名2，列名3用来定义在所声明的游标中可以更新的列。如果指定了OF列名1，列名2，列名3，则只允许修改列出的列。如果指定了UPDATE，但未指定列的列表，则可以更新所有列。,58,使用游标,2打开游标在使用游标读取数据之前，必须要打开游标。游标打开，检索数据并填充游标，然后才可以使用游标。打开游标的语法格式如下：OPEN游标名格式中各部分说明如下：游标名必须是一个已经声明过的游标名。当执行OPEN语句时，打开T-SQL服务器游标，然后通过执行在DECLARECURSOR语句中指定的SELECT语句填充游标。如果使用INSENSITIVE选项声明了游标，那么OPEN将在tempdb中创建一个临时表以保留结果集的副本。3使用游标提取数据创建并打开了一个游标之后，就可以从游标中获取数据了。读取游标中某一行数据的语法格式如下：FETCHNEXT|PRIOR|FIRST|LAST|ABSOLUTEn|变量名|RELATIVEn|变量名FROM游标名INTO变量名1，变量名2，,59,使用游标,格式中各部分说明如下：NEXT选项表示返回结果集中当前行的下一行记录，并且当前行递增为返回行。如果FETCHNEXT为对游标的第一次提取操作，则返回结果集中的第一行而不是第二行。NEXT为默认的游标提取选项。PRIOR选项表示返回结果集中当前行的前一行记录，并且将当前行递减为返回行。如果FETCHPRIOR为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。FIRST选项表示返回结果集中的第一行并将其作为当前行。LAST选项表示返回结果集中的最后一行并将其作为当前行。ABSOLUTEn|变量名选项中，变量为一局部变量。如果n或变量为正数，则返回从游标头开始的第n行，并将返回行变成新的当前行。如果n或变量为负数，则返回从游标末尾开始的第n行，并将返回行变成新的当前行。如果n或变量为0，则不返回行。n必须是整数常量，并且变量的数据类型必须为smallint、tinyint或int。RELATIVEn|变量名选项中，变量为一局部变量。如果n或变量为正数，则返回从当前行开始的第n行，并将返回行变成新的当前行。如果n或变量为负数，则返回当前行之前第n行，并将返回行变成新的当前行。如果n或变量为0，则返回当前行。在对游标完成第一次提取时，如果在将n或变量设置为负数或0的情况下指定FETCHRELATIVE，则不返回行。n必须是整数常量，变量的数据类型必须为smallint、tinyint或int。INTO变量名1，变量名2，选项表示允许将提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果集列的数据类型匹配，或是结果集列数据类型所支持的隐式转换。变量的数目必须与游标选择列表中的列数一致。,60,使用游标,4关闭游标在游标使用完毕之后，必须关闭游标。游标关闭之后，SELECT语句仍然保留，还可以使用OPEN命令再次打开游标。关闭游标的语法格式如下：CLOSE游标名5释放游标关闭游标的操作并不会释放游标所占用的系统资源，所以为了回收游标占用的资源应当释放游标，释放相关内存。释放游标的语法格式如下：DEALLOCATE游标名6游标使用举例了解了游标的5种基本操作之后，下面通过实例来说明游标的基本操作在实际中的应用。例10-31：使用游标将所有提取出的数据存放在变量中，并将其输出。USEStudentGO-定义存放提取出的数据的变量DECLARES_idvarchar(10),S_namevarchar(30)-声明游标DECLAREStudent_curSCROLLCURSORFORSELECTStudent_id,Student_nameFROMStudentsWHEREStudent_sex=女ORDERBYStudent_id-打开游标OPENStudent_cur-第一次提取数据FETCHNEXTFROMStudent_curINTOS_id,S_name-使用WHILE循环提取数据，上一次执行FETCH命令之后游标的状态作为循环条件,61,使用游标,WHILEFETCH_STATUS=0BEGINPRINT学号：+S_id+姓名：+S_nameFETCHNEXTFROMStudent_curINTOS_id,S_nameEND-关闭游标CLOSEStudent_cur-释放游标DEALLOCATEStudent_curGO程序的运行结果如图10.28所示。本例使用了全局变量FETCH_STATUS来构