1、第 7 章Transact-SQL 应用第 7 章Transact-SQL 应用7.1T-SQL 运算符与表达式运算符与表达式7.2T-SQL 的变量的变量7.3程序控制语句程序控制语句7.4自定义函数自定义函数第 7 章Transact-SQL 应用7.1T-SQL 运算符与表达式运算符与表达式1.算术运算符算术运算符算术运算符在两个表达式间执行数学运算，这两个表达式可以是任何数字数据类型。算术运算符有+(加)、-(减)、*(乘)、/(除)和%(求模)5 种运算。+(加)和-(减)运算符也可用于对 datetime 及 smalldatetime 值进行算术运算。第 7 章Transact-
2、SQL 应用2.位运算符位运算符位运算符用于对两个表达式进行的位操作，这两个表达式可为整型或与整型兼容的数据类型。位运算符及其规则如表 7-1 所示。SQL 中的位运算不但可以取出各种值,而且还可以对数据进行排序。第 7 章Transact-SQL 应用第 7 章Transact-SQL 应用例 7-1 声明两个局部变量并赋值，求它们的位运算。declare a int,b intselect a=7,b=4select a&b as a&b,a|b as a|b,ab as ab 显示结果为：4、7、3。第 7 章Transact-SQL 应用3.比较运算符比较运算符比较运算符用于测试两个表
3、达式的值是否相同，运算结果为“true”或“false”。比较运算符及名称如表 7-2 所示。第 7 章Transact-SQL 应用4.逻辑运算符逻辑运算符逻辑运算符用于对某个条件进行测试，运算结果为“true”或“false”，逻辑运算符及规则如表 7-3 所示。第 7 章Transact-SQL 应用第 7 章Transact-SQL 应用5.字符串连接运算符字符串连接运算符字符串连接运算符通过运算符“+”实现两个或多个字符串的连接运算。例 7-2 执行下面的语句连接多个字符串。运算结果为“abcdefghijk”。select(ab+cdefg+hijk)as 字符串连接第 7 章Tr
4、ansact-SQL 应用6.赋值运算符赋值运算符在给局部变量赋值的 set 和 select 语句中使用的“=”运算符称为赋值运算符。赋值运算符用于将表达式的值赋予另外一个变量，也可以使用赋值运算符在列标题和为列定义值的表达式之间建立关系。第 7 章Transact-SQL 应用当一个复杂的表达式有多个运算符时，运算符优先级决定执行运算的先后次序，执行的顺序会影响所得到的运算结果。在一个表达式中，括号最优先，其次按先高(优先级数字小)后低(优先级数字大)的顺序进行运算。运算符的优先级如表 7-4 所示。第 7 章Transact-SQL 应用第 7 章Transact-SQL 应用7.2T-
5、SQL 的变量的变量7.2.1局部变量局部变量1.概念及其用途概念及其用途局部变量是作用域局限在一定范围内的变量，是用户自定义的变量。一般来说，局部变量的使用范围局限于定义它的批处理内。定义它的批处理中的SQL 语句可以引用这个局部变量，直到批处理结束，这个局部变量的生命周期也就结束了。局部变量在程序中通常用来存储从表中查询到的数据或在程序执行过程中用于暂存变量。第 7 章Transact-SQL 应用通常将其用于下面 3 种情况：(1)作为计数器，计算循环执行的次数或控制循环执行的次数。(2)保存数据值以供控制流语句测试。(3)保存由存储过程返回代码返回的数据值。第 7 章Transact-
6、SQL 应用2.声明及其赋值声明及其赋值1)声明局部变量在使用一个局部变量之前，必须先声明该变量。其语法格式如下：declare 变量名 变量类型,.n参数说明：(1)局部变量名的命名必须遵循 SQL Server 的标识符命名规则，并且必须以字符“”开头。(2)局部变量的类型可以是系统数据类型，也可以是用户自定义的数据类型。(3)declare 语句可以声明一个或多个局部变量，变量被声明以后初值都是“null”。第 7 章Transact-SQL 应用2)局部变量赋值局部变量被创建之后，系统将其初始值设为“null”。若要改变局部变量的值，可以使用 set 语句或 select 语句给局部变
7、量重新赋值。select 语句的语法格式如下：select 变量名=表达式,.set 语句的语法格式如下：set 变量名=表达式第 7 章Transact-SQL 应用参数说明：(1)变量名是准备为其赋值的局部变量。表达式是有效的 SQL Server 表达式，且其类型应与局部变量的数据类型相匹配。(2)从语法格式中可以看出，select 语句和 set 语句的区别在于使用 set 语句一次只能给一个变量赋值，而 select 语句可以一次给多个变量赋值。第 7 章Transact-SQL 应用3.显示局部变量的值显示局部变量的值可以使用 select 或 print 语句显示局部变量的值。其
8、语法格式如下：select 变量名,.nprint 变量名两者的区别在于，select 语句以表格方式显示一个或多个变量的值，而 print 语句在消息框中显示一个变量的值。第 7 章Transact-SQL 应用第 7 章Transact-SQL 应用图 7-1局部变量的声明、赋值和显示第 7 章Transact-SQL 应用7.2.2全局变量全局变量全局变量是以“”开头，由系统预先定义并负责维护的变量。也可以把全局变量看成是一种特殊形式的函数。在全局变量的使用过程中要注意，全局变量是由 SQL Server 服务器定义的，用户只能使用全局变量，且在引用全局变量时，必须以“”开头。另外，局部
9、变量的名称不能与全局变量的名称相同，否则就会在应用程序中出错。表 7-5 列出了 SQL Server 的几个常用全局变量及其含义，其他全局变量可查阅 SQL Server 联机丛书。第 7 章Transact-SQL 应用第 7 章Transact-SQL 应用 7-6 执行 select servername，返回服务器名称，显示结果如图 7-2 所示。图 7-2服务器名称第 7 章Transact-SQL 应用执行 select*From Sys.SysServers，返回服务器名称等信息，显示结果如图 7-3 所示。图 7-3服务器名称信息第 7 章Transact-SQL 应用例 7
10、-7 执行 Select spid as ID,system_user as Login Name,user as User Name,返回用户 ID、登录名及用户名信息，如图 7-4 所示。图 7-4用户 ID、登录名及用户名信息第 7 章Transact-SQL 应用7.3程序控制语句程序控制语句SQL Server 支持结构化的编程方法，结构化编程中程序流程控制的三大结构是顺序结构、选择结构和循环结构。T-SQL 提供了实现这 3 种结构的流程控制语句，使用这些流程控制语句可以控制命令的执行顺序，以便更好地组织程序。第 7 章Transact-SQL 应用7.3.1beginend 语句
11、语句beginend语句相当于其他语言中的复合语句，如C语言中的。它用于将多条T-SQL语句封装为一个整体的语句块，即将 beginend 内的所有 T-SQL 语句视为一个单元执行。在实际应用中，beginend 语句一般与 ifelse、while 等语句联用，当判断条件符合需要执行两个或多个语句时，就需要使用 beginend 语句将这些语句封装为一个语句块。beginend 语句块允许嵌套。第 7 章Transact-SQL 应用其语法格式如下：begin SQL 语句块|程序块end该语句适用于以下情况：while 循环需要包含多条语句，case 函数的元素需要包含多条语句，if 或
12、 else 子句中需要包含多条语句。第 7 章Transact-SQL 应用例7-8 在数据库studscore_ds1的职工信息表employees中，查询001号员工是否存在，如果有则删除该员工，并显示提示信息。代码如下：第 7 章Transact-SQL 应用7.3.2单条件分支语句单条件分支语句ifelse 语句是条件判断语句，用以实现选择结构。当 if 后的条件成立时就执行其后的 T-SQL 语句，条件不成立时执行 else 语句后的 T-SQL 语句。其中，else 子句是可选项，如果没有 else 子句，当条件不成立时则执行 if 语句后的其他语句。其语法格式如下：ifSQL 语
13、句块|程序块 elseSQL 语句块|程序块 第 7 章Transact-SQL 应用语法说明：条件表达式是作为执行和判断条件的布尔表达式，返回“true”或“false”。如果布尔表达式中含有 select 语句，则必须用圆括号将 select 语句括起来。程序块是一条 T-SQL 语句或一个 beginend 语句块。ifelse 语句允许嵌套使用，可以在 if 之后或在 else 的下面嵌套另一个 if 语句。嵌套级数的限制取决于可用内存。第 7 章Transact-SQL 应用例 7-9 在数据库 studscore_ds1 的学生成绩 sc 表中，查询是否存在考 90 分及以上的学生
14、信息；有则显示结果，无则显示“没有考 90 分及以上的学生”。第 7 章Transact-SQL 应用7.3.3多条件分支语句多条件分支语句 if 多条件分支多条件分支ifelse ifelse 语句用于多条件分支执行。其语法格式如下：ifSQL 语句块|程序块 else ifSQL 语句块|程序块 elseSQL 语句块|程序块 第 7 章Transact-SQL 应用例 7-10在数据库 studscore_ds1 的 sc 表中，使用 if 多条件分支语句判断学生的成绩等级，并显示该学生的等级。代码如下：第 7 章Transact-SQL 应用2.case 多条件分支多条件分支case
15、语句和 ifelse 语句一样，用来实现选择结构，case 语句避免了多重嵌套，更加简洁清晰。T-SQL 中的 case 语句可分为简单 case 语句和搜索 case 语句两种。1)简单 case 语句其语法格式如下：case whenthen.whenthen elseend第 7 章Transact-SQL 应用参数说明：case 后的表达式用于和 when 后的表达式逐个进行比较，两者的数据类型必须是相同的，或者是可以进行隐式转换的。then 后面给出当 case 后的表达式与 when 后的表达式相等时，要返回的结果表达式。第 7 章Transact-SQL 应用例 7-11产生一个
16、 0 1 之间的随机数，然后使用简单 case 语句给出变量的值，并显示结果。代码如下：第 7 章Transact-SQL 应用2)搜索 case 语句其语法格式如下：case whenthen.whenthen elseend参数说明：case 后面没有表达式。when 后面的条件表达式是作为执行和判断条件的布尔表达式第 7 章Transact-SQL 应用例 7-12在表 employees 中，计算平均工资，然后分析判断员工工资的总体情况。代码如下：第 7 章Transact-SQL 应用7.3.4循环语句循环语句while 语句用于实现循环结构，其功能是在满足循环条件的情况下，重复执行
17、 T-SQL语句或语句块。当 while 后面的条件为真时，就重复执行 beginend 之间的语句块。while语句块中的 continue 和 break 是可选项。若有 continue 语句，则其功能是跳过 continue 后的语句，执行下一次循环条件测试。若遇到 break 语句，则其功能是立即终止循环，结束整个 while 语句的执行，并继续执行 while 语句后的其他语句。第 7 章Transact-SQL 应用参数说明：条件表达式是作为执行和判断的布尔表达式，返回“true”或“false”。如果布尔表达式中含有 select 语句，则必须用圆括号将 select 语句括起
18、来。程序块是一条T-SQL 语句或一个 beginend 语句块。第 7 章Transact-SQL 应用例 7-13进行 T-SQL 编程，输出 1 100 之间能被 7 整除的数，并且最后输出总个数。代码如下：第 7 章Transact-SQL 应用第 7 章Transact-SQL 应用第 7 章Transact-SQL 应用7.3.5goto 语句语句 goto 语句是转向语句，让程序跳转到一个指定的标签处并执行其后的代码。goto 语句和标签可以在程序、批处理和语句块中的任意位置使用，也可以嵌套使用。其语法格式如下：定义标签 label:改变执行 goto label参数说明：若有
19、goto 语句指向 label 标签，则其为处理的起点。标签必须符合标识符规则。第 7 章Transact-SQL 应用例 7-16结合使用 T-SQL 编程的 goto 语句求 10！，并显示计算结果。代码如下：第 7 章Transact-SQL 应用7.3.6return 语句语句return 语句用于结束当前程序的执行，无条件地终止一个查询、存储过程或批处理，返回到上一个调用它的程序或其他程序；在括号内可指定一个返回值。其语法格式如下：return integer_expression参数说明：integer_expression 为返回的整型值。存储过程可以给调用过程或应用程序返回整型
20、值。第 7 章Transact-SQL 应用例 7-17创建一个存储过程，通过 return 语句返回一个值，用于判断员工表employees 是否存在该员工。代码如下：第 7 章Transact-SQL 应用7.3.7waitfor 语句语句waitfor 语句用于在达到指定时间或时间间隔之前阻止执行批处理、存储过程或事务，直到所设定的时间已到或等待了指定的时间间隔之后才继续往下运行。其语法格式如下：waitfor delay 等待时间|time 完成时间第 7 章Transact-SQL 应用参数说明：(1)“delay 等待时间”是指定可以继续执行批处理、存储过程或事务之前必须经过的指定
21、时间段，最长可为 24 小时。可使用 datetime 数据可接受的格式之一指定“等待时间”，也可以将其指定为局部时间，但不能指定日期，因此不允许指定 datetime 值的日期部分。(2)“time 完成时间”是指定运行批处理、存储过程或事务的具体时刻。可以使用datetime 数据可以接受的格式之一指定“完成时间”，也可以将其指定为局部变量，但不能指定日期，因此不允许指定 datetime 值的日期部分。第 7 章Transact-SQL 应用例 7-18在 10 点盘点，从 sales 表中查询当前的销售情况。这里的时间是服务器时间，而不是客户端时间。代码如下：第 7 章Transact
22、-SQL 应用7.3.8注释语句注释语句在 T-SQL 中可以使用两类注释符。ANSI 标准的注释符“-”用于单行注释，/*/用于多行注释。1.单行注释单行注释其语法格式如下：-text_of_comment参数说明：两个连字符(-)是 SQL-92 标准的注释指示符。text_of_comment 为包含注释文本的字符串。第 7 章Transact-SQL 应用2.多行注释多行注释其语法格式如下：/*text_of_comment*/参数说明：text_of_comment 为包含注释文本的字符串。第 7 章Transact-SQL 应用7.3.9使用脚本和批处理使用脚本和批处理1.脚本脚本
23、脚本是存储在文件中的一系列 SQL 语句，即一系列按顺序提交的批处理。使用脚本可以将创建和维护数据库时的操作步骤保存为一个磁盘文件，文件的扩展名为.sql。2.批处理批处理批处理是包含一个或多个 T-SQL 语句的组，从应用程序一次性地发送到 Microsoft SQL Server 执行。SQL Server 将批处理语句编译成一个可执行单元，此单元称为执行计划。第 7 章Transact-SQL 应用运行时错误(如算术溢出或违反约束)会产生以下两种影响之一：(1)大多数运行时错误将停止执行批处理中当前语句和它之后的语句。(2)少数运行时错误(如违反约束)仅停止执行当前语句，而继续执行批处理
24、中其他所有语句。在书写批处理语句时，需要使用 go 语句作为批处理命令的结束标志。第 7 章Transact-SQL 应用例 7-19 go 示例。代码如下：use studscore_ds1 go-第一个批处理打开数据库的操作select*from booksgo-第二个批处理查询 books 表中的数据第 7 章Transact-SQL 应用7.4自定义函数自定义函数在 SQL Server 中根据函数返回值形式的不同将用户自定义函数分为以下类型：(1)标量函数(Scalar Function)：返回单一的数据值。(2)返回数据集(Rowset)的用户自定义函数：返回一个 table 类型
25、的数据集，依定义语法不同分为两类：行数据集函数和多语句数据集函数。第 7 章Transact-SQL 应用7.4.1创建自定义函数创建自定义函数标量函数返回单一的数据值，其类型可以是除了 text、ntext、image、cursor、rowversion(timestamp)之外的所有类型。第 7 章Transact-SQL 应用创建标量函数的语法格式如下：第 7 章Transact-SQL 应用参数说明：(1)parameter_nameasscalar_parameter_data_type=default 表示函数的参数，可有 0个或多个(最多 1024 个参数)，而参数的名称前要加上
26、“”。参数行必须用小括号括起来，即使没有参数，小括号也不可省略。可以用“=”来为参数指定默认值，如 create function myfunct(a char(10),b int=200)。参数的类型必须是标量类型。(2)returns scalar_return_data_type 用于声明返回值的类型，可以是所有标量类型。第 7 章Transact-SQL 应用(3)with,.n 用于设置函数的选项。指定 encryption 时表示函数的内容加密，函数建立之后即无法查看其程序内容。若指定 schemabinding(结构绑定)选项，则可限制在函数中所使用到的各数据库对象。(4)fun
27、ction_body，就是函数的程序内容，可以有一行到多行语句。(5)return scalar_expression，用来结束函数的执行，并将 scalar_expression 表达式的值返回。在函数中可以出现多个 return 语句，但函数的最后一个语句必须是 return 语句。第 7 章Transact-SQL 应用例 7-20创建一个计算阶乘的函数。代码如下：第 7 章Transact-SQL 应用7.4.2调用自定义函数调用自定义函数调用用户自定义函数和调用内置函数的方式基本相同。当调用标量值函数时，必须加上“所有者”，通常是 dbo。可以在“可编程性”“函数”“标量值函数”中查看所有者。例 7-21调用阶乘函数。代码如下：select dbo.get_jiecheng(5)第 7 章Transact-SQL 应用7.4.3删除用户自定义函数删除用户自定义函数删除用户自定义函数的语法格式如下：drop function function_name
