第十章T-SQL程序设计1本章主题v 将两个或更多的表联接起来以看到更多的结果信息 v 获取一种通过变量以临时保存信息的方法 v 如何将行信息保存到非永久的表中 v 如何聚合值 v 组织输出数据到相关的信息分组中 v 返回唯一和独特的值 v 错误处理：如何创建你自己的错误，捕捉错误，并让代码 更安全T-SQL程序设计3T-SQL的目的在于为处理大量数据提供必要的结构化处理能力。 前面的章节中已经介绍了一些处理简单查询的T-SQL语句，但是当面 对更为复杂的应用时，前面所学的知识就远远不够了。因此，在本 章中将主要讨论如何使用T-SQL提供的丰富编程结构，编写出复杂的 例行程序。第一节 T-SQL简介4T-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编程基础512 345局部变量注释批处理全局变量运算符(一) 注释6T-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)102局部变量的赋值局部变量被创建之后，系统将其初始值设为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 GO12局部变量本例主要完成了两个操作，一是根据局部变量的初值在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联机丛书进行学习。全局变量名称全局变量含义CONNECTIONS返回 SQL Server 自上次启动以来所有针对此服务器的尝试的连接数，无论连接是成功还是失败ERROR返回执行的上一个 T-SQL 语句的错误号IDENTITY返回最后插入的标识列的列值NESTLEVEL返回对本地服务器上执行的当前存储过程的嵌套级别（初始值为 0）SERVERNAME返回运行SQL Server的本地服务器名称SPID返回当前用户进程的会话 IDVERSION返回当前SQL Server 的安装版本、处理器体系结构、生成日期和操作系统表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.Tea