第9章 用T-SQL编程

中文版SQLServer2000实用基础教程172第9章用T-SQL编程本章学习目标Transact-SQL相对于ANSISQL最大的不同在于它提供了丰富的编程结构。灵活使用这些编程的控制结构，用户就可以实现任意复杂的应用规则，从而可以编出任意复杂的查询控制语句。在SQL-Server2000中，用户还可以使用T-SQL语句编写服务器端的程序，这些程序由批处理、注释、程序中使用的变量、改变批中语句的执行顺序的流控制语言、错误和消息的处理等成分组成。本章主要对使用T-SQL语句编程方面的内容进行详细的讲解，在讲解的过程中采用循序渐进的方式，首先对多条语句的执行过程进行讲解，以激发读者的学习兴趣，接下来才对编程中具体涉及到的变量、语句、函数以及事务进行详细讲解。通过对本章的学习，读者应熟悉在SQLServer中执行多条语句的方式，熟悉变量以及事务等的定义、应用或调用，并能掌握使用各种流控制语句进行编程的方法。学习重点与难点执行多条T-SQL语句变量和流控制语句系统函数和用户自定义函数事务及SQL-Server的锁机制9．1执行多条T-SQL语句当要完成的任务不能由单独的T-SQL语句来完成时，SQLServer提供了批、脚本、存储过程以及触发器等几种方式来组织多条T-SQL语句。本节着重介绍批和脚本的内容，而关于存储过程和触发器的内容将在后面两章讨论。9.1.1批所谓批，是从客户机传递到服务器上的一组完整的数据和SQL指令。一个批是由一条或多条T-SQL语句组成的语句集，这些语句一起提交给服务器并在服务器端作为一个整体来执行。SQLServer将批中的语句作为一个整体编译为一个执行计划。因为批中的语句是作为一个整体提交给服务器的，所以可以节省系统开销。在查询分析器中，使用“GO”命令标志一个批的结束。GO不是通用的T_SQL语句，它的作用只是通知查询分析器有多少语句包含在当前批中，查询分析器将两个GO之间的语句组成一个字符串交给服务器去执行。基于ODBC或OLEDB应用程序编程接口的应用程序在试图执行GO语句时，会产生一个错误。例如，下面的例子包括三个批：第9章用T-SQL编程173CREATETABLEdoc_exd(column_aINT)GOINSERTINTOdoc_exdVALUES(-1)GOALTERTABLEdoc_exdWITHNOCHECKADDCONSTRAINTexd_checkCHECK(column_a1)因为SQLServer为一个批生成一个单独的执行计划，所以一个批本身应该是完整的。不能在一个批中引用其他批定义的变量，也不能将注释从一个批开始，在另一个批中结束。下面的一组语句执行时就会出错：USENorthwindGODECLARE@MyVarINT/*Startofthesplitcomment.GOEndofthesplitcomment*/SELECT@MyVar=29GO在查询分析器中执行这组语句会返回以下错误信息：缺少注释的结尾标记'*/'。在关键字'End'附近有语法错误。必须声明变量'@MyVar'。SQLServer统一优化、编译和执行一个批中的语句。如果在批中的语句出现编译错误（如语法错误），那么将不能生成执行计划，该批中的任何一个语句都不会被执行。有些情况下，如果在SQL指令中存放着一些像数据类型无法自动转换等错误的时候，编译器无法识别这些错误。在这种情况下，这个批处理只有在执行过程中才会出错。一般说来在这个错误指令以前的所有指令都会执行成功。在错误指令以后的指令将会视错误指令所引起的错误严重程度而决定是否执行下去。如果出现了运行时期的错误（比如违反约束或数字溢出），这时可能会有两个结果：多数运行时错误将停止执行批处理中当前语句和它之后的语句。少数运行时错误（如违反约束）仅停止执行当前语句，而继续执行批中其它语句。无论是哪种运行时期错误，在出错语句之前的语句的执行结果不会受到影响。惟一的例外是如果批处理在事务中而且错误导致事务回滚。批有如下一些限制：CREATEDEFAULT、CREATEPROCEDURE、CREATERULE、CREATETRIGGER和CREATEVIEW语句不能与其他语句位于同一个批中。不能在同一个批处理中修改一个表的结构，然后引用刚修改的新列。如果批的第一条语句是EXECUTE（执行）语句，则EXECUTE关键字可以省略，否则，不能省略。9.1.2脚本脚本是一系列顺序提交的批，由这些批组成的一系列T-SQL语句存储在一个文件中，该文件可以在查询分析器中执行。执行脚本就是依次执行其中的T-SQL语句。脚本用于保存重新创建数据库对象或重复执行的语句，可以使用查询分析器或任何文本中文版SQLServer2000实用基础教程174编辑器编写，带.sql扩展名保存。一个脚本可以包含一个或多个批，脚本中的GO命令标志一个批的结束，如果一个脚本中没有包括任何GO命令，那么它被视为整个是一个批。脚本一般可以用于两个方面：将服务器上创建一个数据库的步骤永久地记录在脚本文件中。将语句保存为脚本文件，从一台计算机传递到另一台，这样可以方便地使两台计算机执行同样的操作。9．2变量变量用于临时存放数据，其中的数据随着程序的运行而变化，变量有名字及数据类型两个属性。变量名用于标识该变量，数据类型确定了该变量存放值的格式以及允许的运算。变量名必须是一个合法的标识符。在SQLServer中标识符分为两类：（1）常规标识符。以ASCII字母、Unicode字母、下划线（_）、@或#开头，后继可跟一个或若干个ASCII字符、Unicode字符、下划线（_）、美元符号（$）、@或#，但不能全为下划线（_）、@或#。常规标识符不能是T-SQL保留字。常规标识符中不允许嵌入空格或其他特殊字符。（2）分隔标识符。包括在双引号（“”）或方括号（[]）内的常规标识符或不符合常规标识符规则的标识符。标识符允许的最大长度为128个字符。符合常规标识符规则的标识符可以分隔，也可以不分隔。对不符合标识符规则的标识符必须进行分隔。在SQLServer中变量可分为两类：全局变量和局部变量。9.2.1局部变量局部变量是作用域局限在一定范围内的T-SQL对象。一般来说，局部变量在一个批处理（也可以是存储过程或触发器）中被声明或定义，然后这个批处理内的T-SQL语句就可以设置这个变量的值，或者是引用这个变量已经被赋予的值。当这个批处理结束后，这个局部变量的生命周期也就随之消亡。局部变量是用户定义的变量，其名字必须以@开始。局部变量用于保存单个数据值。局部变量用DECLARE语句声明，所有局部变量在声明后均初始化为NULL。语法格式为：DECLARE{@varaible_namedatatype[,…n]}其中@varaible_name为局部变量名，并以@开头。Datatype是为该局部变量指定的数据类型。局部变量使用的数据类型可以是除text，ntext或image类型外所有的系统数据类型和用户定义数据类型。一般来说，如果没有特殊的用途，建议在应用时尽量使用系统提供的数据类型。这样做可以减少维护应用程序的工作量。例1、声明一个字符形变量@E-mail，使用如下语句：DECLARE@E_mailvarchar(50)在一条DECLARE语句中可以声明多个局部变量，变量之间用逗号分隔。例2、下面语句声明三个局部变量：DECLARE@lastnamevarchar(30),@firstnamevarchar(20),@telvarchar(30)第9章用T-SQL编程175当声明局部变量后，可用SET或SELECT语句为其赋值。1、用SET语句为局部变量赋值一个SET语句只能给一个变量赋值。其语法格式为：SET@varaible_name=expression其中@varaible_name为局部变量名。Expression为任何有效的SQLServer表达式。例3、创建两个局部变量@pub_id和@hire_date并赋值，然后在employee表中进行查询。USEpubsGODECLARE@pub_idchar(4),@hire_datedatetimeSET@pub_id='0877'SET@hire_date='1/01/93'SELECTfname,lnameFROMemployeeWHEREpub_id=@pub_idandhire_date=@hire_date例4、使用查询给变量赋值。USEPubsGODECLARE@rowsintSET@rows=(SELECTCOUNT(*)FROMauthors)GO2、用SELECT语句为局部变量赋值使用SELECT语句为局部变量赋值的语法格式为：SELECT@varaible_name=expression[,…n]其中n表示可以给多个变量赋值。关于SELECT语句，需要说明以下几点：SELECT@varaible_name通常用于将单个值返回到变量中，如果expression为列名，则返回多个值，此时将返回的最后一个值赋给变量。如果SELECT语句没有返回值，变量将保留当前值。如果expression是不返回值的标量子查询，则将变量设为NULL。一个SELECT语句可以初始化多个局部变量。例5、先声明一个局部变量然后赋值，再将查询到的结果赋值给变量。由于查询语句没有返回值，所以变量保留当前值。USESalesDECLARE@var1nchar(20)SELECT@var1='刘三'SELECT@var1=姓名FROMCustomersWHERE电话='85697451'SELECT@var1在查询分析器中执行这组语句，返回结果如图9-1所示：中文版SQLServer2000实用基础教程176图9-1SELECT语句没有返回值时，变量将保留当前值例6、当返回多个值时将最后一个值赋给变量。USESalesDECLARE@var1nchar(20)SELECT@var1='刘三'SELECT@var1=姓名FROMCustomersWHERE电话LIKE'8%'SELECT@var1返回结果如图9-2所示：图9-2返回多个值时将最后一个值赋给变量例7、赋值语句是不返回值的子查询，将变量赋值为NULL。DECLARE@var1nchar(20)SELECT@var1='刘三'SELECT@var1=(SELECT姓名FROMCustomersWHERE电话='86957458')SELECT@var1返回结果如图9-3所示：图9-3赋值语句是不返回值的子查询，将变量赋值为NULL9.2.2全局变量全局变量是用来记录SQLServer服务器活动状态的一组数据，是SQLServer系统提供并赋值的变量，用户不能建立全局变量，也不能给全局变量赋值或直接更改全局变量的值。通常将全局变量的值赋给局部变量，以便保存和处理。全局变量的名字以@@开始。第9章用T-SQL编程177SQLServer提供的全局变量分为两类：与每次处理相关的全局变量。如@@rowcount表示最近一个语句影响的行数。与系统内部信息有关的全局变量。如@@version表示SQLServer的版本号。SQLServer一共提供了30多个全局变量。下面是一些常用全局变量的功能和使用方法。@@CONNECTIONS：记录自最后一次服务器启动以来，所有针对此服务器进行连接数目，包括没有连接成功的尝试。使用@@CONNECTIONS可以让系统管理员很容易地得到今天所有试图连接本服务器的连接数目，例如：SELECTGETDATE()AS时间,@@CONNECTIONSAS连接数目系统返回结果为：图9-4用@@CONNECTIONS查询连接次数@@CPU_BUSY：记录自最后一次服务器启动后，以ms为单位的CPU工作时间。@@CURSOR_ROWS：返回在本次服务器连接中，打开游标取出数据行的数目。@@DBTS：返回当前服务器中timestamp数据类型的当前