第 10 章 Transact-SQL 程序设计

1. 第10章 Transact-SQL程序设计

2. 本章内容 10.1 Transact-SQL语言基础 10.2 表达式 10.3 函数 10.4 流程控制语句 10.5 游标

3. 10.1 Transact-SQL语言基础 10.1.1 Transact-SQL语言的编程功能 10.1.2 标识符 10.1.3 注释 10.1.4 语句块

4. 10.1.1 Transact-SQL语言的编程功能 Transact-SQL语言是在微软公司的SQL Server中使用的编程语言，它是一个数据定义、操作和控制的语言。 (1)基本功能 支持ANSI SQL-92标准：DDL数据定义，DML数据操纵，DCL数据控制，DD数据字典。 (2)扩展功能 • 加入程序流程控制结构。 • 加入局部变量和系统变量等

5. 10.1.2 标识符 数据库对象的名称即为其标识符 。服务器、数据库和数据库对象（例如表、视图、列、索引、触发器、过程、约束及规则等）都可以有标识符 。 1. 标识符命名规则 标识符包含的字符数必须在1到128之间，标识符的命名需要满足以下规则： (1)标识符的第一个字符必须是：大写字母、小写字母、下划线、@和# 。其中以@符号开头的常规标识符表示局部变量或参数，并且不能用作任何其他类型的对象的名称。以#开头的标识符表示临时表或过程，以##开头的标识符表示全局临时对象。

6. (2)后续字符必须是符合Unicode 2.0(统一码)标准的字母，或者是十进制数字，或是特殊字符@、#、_、$。 (3)标识符不能与任何SQL Server保留字匹配。标识符不能包含空格或别的特殊字符。 2. 对象命名规则 所有数据库对象的引用由下面四部分构成 ： server_name.[database_name].[schema_name].object_name | database_name.[schema_name].object_name | schema_name.object_name | object_name 其中： • server_name：指定链接的服务器名称或远程服务器名称。

7. database_name：如果对象驻留在SQL Server的本地实例中，则指定SQL Server数据库的名称。如果对象在链接服务器中，则database_name将指定OLE DB目录 。 • schema_name：如果对象在SQL Server数据库中，则指定包含对象的架构的名称。如果对象在链接服务器中，则schema_name将指定OLE DB架构名称 。 • object_name：对象的名称 。 数据库架构是一个独立于数据库用户的非重复命名空间可以将架构视为对象的容器。可以在数据库中创建和更改架构，并且可以授予用户访问架构的权限。任何用户都可以拥有架构，并且架构所有权可以转移 。 在SQL Server 2005中，每个对象都属于一个数据库架构 。

8. 10.1.3 注释 注释是程序代码中不执行的文本字符串，也称为备注 。注释通常用于记录程序名、作者姓名和主要代码更改的日期，注释可用于描述复杂的计算或解释编程方法。 SQL Server支持两种类型的注释字符： • --（双连字符） ：这些注释字符可与要执行的代码处在同一行，也可另起一行。对于多行注释，必须在每个注释行的前面使用双连字符。 • /* ... */（正斜杠-星号字符对）：对于多行注释，必须使用开始注释字符“/*”来开始注释，并使用结束注释字符“*/”来结束注释。

9. 10.1.4 语句块 语句块是由BEGIN 和 END括起来的一系列的Transact-SQL语句，作为一个逻辑单元执行。 语法格式如下： BEGIN { sql_statement | statement_block } END 其中：{ sql_statement | statement_block }是使用语句块定义的任何有效的Transact-SQL语句或语句组 。 BEGIN...END语句块允许嵌套。

10. 10.2 表达式 10.2.1 常量 10.2.2 变量 10.2.3 运算符

11. 9.1 数据与表达式 10.2.1 常量 在程序运行中保持常值的数据，即程序本身不能改变其值的数据，称为常量，在程序中经常直接使用文字符号表示。 根据常量的类型不同分为字符串常量、整型常量、日期时间型常量、实型常量、货币常量、全局唯一标识符。 1. 字符串常量 字符串常量分为ASCII字符串常量、UNICODE字符串常量。 • ASCII字符串常量：用单引号括起来，由ASCII构成的字符串。如：‘abcde’。 • UNICODE字符串常量：以字符N开头，如N'abcde'。

12. 9.1 数据与表达式 字符串常量必须放在单引号或双引号中。由字母、数字、下划线、特殊字符(!，@，#)组成。 当单引号括住的字符串常量中包含单引号时，用两个单引号表示字符串中的单引号。如：I‘m ZYT写作’I‘’m ZYT‘。 UNICODE(统一码、万国码、单一码)是一种在计算机上使用的字符编码。它为每种语言中的每个字符设定了统一并且唯一的二进制编码，以满足跨语言、跨平台进行文本转换、处理的要求 。

13. 9.1 数据与表达式 2. 整型常量 • 二进制整型常量：由0、1组成，如111001。 • 十进制整型常量：如1982。 • 十六进制整型常量：用0x开头，如0x3e，0x，只有0x表示空十六进制数。

14. 9.1.2 常量与变量 3. 日期时间型常量 • datetime常量使用特定格式的字符日期值表示，用单引号括起来。 • 输入时，可以使用“/”、“.”、“-”作日期/时间常量的分隔符。 • 默认情况下，按照mm/dd/yy(月/日/年)的格式来处理。

15. 9.1.2 常量与变量 4. 实型常量 实型常量有纯小数和指数形式两种。如165.234，10E23。 5. 货币常量 用货币符号开头。如$12.5，$54230.25。SQL Server不强制分组，如每隔三个数字插一个逗号等。 6. 全局唯一标识符 全局唯一标识符(Globally Unique Identification Numbers，GUID)是16字节长的二进制数据类型，是SQL Server根据计算机网络适配器地址和主机时钟产生的唯一号码生成的全局唯一标识符。

16. 9.1.2 常量与变量 GUID主要用于在拥有多个节点、多台计算机的网络或系统中分配必须具有唯一性的标识符 。世界上的任何两台计算机都不会生成重复的GUID值 。 7. 逻辑数据常量 逻辑数据常量使用数字0或1表示，并且不使用引号定界。非0的数字当作1处理。 8. 空值 在定义数据列时，需定义该列是否允许空值(NULL)。允许空值意味着用户在向表中输入数据时可以忽略该列值。空值可以表示整型、实型、字符型数据。

17. 9.1.2 常量与变量 10.2.2 变量 • 变量用于临时存放数据，变量中的数据随着程序的运行而变化，变量有名字与数据类型两个属性。 • 变量的命名使用常规标识符，即以字母、下划线(_)、at符号(@)、数字符号(#)开头，后续字母、数字、at符号、美元符号($)、下划线的字符序列。不允许嵌入空格或其他特殊字符。

18. 9.1.2 常量与变量 全局变量和局部变量 • 全局变量由系统定义并维护，通过在名称前面加“@@”符号 • 局部变量的首字母为单个“@”。

19. 1. 局部变量 局部变量是作用域局限在一定范围内的Transact-SQL对象。 • 作用域：若局部变量在一个批处理、存储过程、触发器中被声明或定义，则其作用域就在批处理、存储过程或触发器内。 (1) 局部变量的声明或定义 DECLARE { local_variable [AS] data_type } [ ,...n] 其中： @local_variable：变量的名称。变量名必须以@开头。 data_type：数据类型 变量先声明或定义，然后就可以在Transact-SQL命令中使用。默认初值NULL。

20. 9.1.2 常量与变量 (2)局部变量的赋值 用SET或SELECT为局部变量赋值 SET @local_variable = expression 或 SELECT @local_variable = expression 或 SELECT @local_variable =output_value FROM table_name WHERE ... 各选项含义如下： • local_variable：是除cursor，text，ntext，image外的任何类型变量名 。 • expression：表达式是任何有效的SQL Server表达式。 • output_value：用于将单个值返回到变量中 。 注意：如果output_value为列名，则返回多个。若SELECT语句返回多个值，则将返回的最后一个值赋给变量。若SELECT语句没有返回值，变量保留当前值；若output_value是不返回值的子查询，则变量为NULL。

21. 9.1.2 常量与变量 例如：通过SELECT命令赋值，查询学号为S1的学生姓名。 USE teaching GO DECLARE @var1 varchar(8) SELECT @var1=SNAME FROM student WHERE SNO='S1' SELECT @var1 AS '学生姓名' 执行结果如图10.1所示。

22. 9.1.2 常量与变量 例如：使用SELECT命令赋值，结构为多个返回值时取最后一个值。 USE teaching GO DECLARE @var1 varchar(8) SELECT @var1=SNAME FROM student SELECT @var1 AS '读者姓名' 执行结果如图10.2所示。

23. 2. 全局变量 • 系统全局变量是SQL Server系统提供并赋值的变量。 • 用户不能建立全局变量，也不能用SET语句改变全局变量的值。 • 全局变量记录SQL Server服务器活动状态的一组数据。例如：@@ERROR 表示最后一个Transact-SQL命令错误的错误号；@@SERVERNAME 表示本地服务器的名称。具体见表10.2常用的SQL Server全局变量 • 全局变量由@@开始，由系统定义和维护，用户只能显示和读取，不能修改 。

24. 9.1.2 常量与变量 例如：显示SQL Server的版本 。 SELECT @@version AS 版本号 执行结果如图10.4所示。

25. 9.1.2 常量与变量 例如：使用@@spid返回当前用户进程的ID。 SELECT @@spid as 'ID',SYSTEM_USER AS 'Login Name',USER AS 'User Name' 运行结果： ID Login Name User Name 53 sa dbo

26. 10.2.3 运算符 • 运算符用于指定要在一个或多个表达式中执行的操作 。 • 将变量、常量和函数连接起来，构成表达式。 下表列出了SQL Server 2005的运算符

27. SQL Server 2005运算符

28. 10.3 函 数 10.3.1 内置函数 10.3.2 用户定义函数

29. 函数是—组编译好的Transact-SQL语句，它们可以带一个或一组数值做参数，也可不带参数，它返回一个数值、数值集合，或执行一些操作。函数是—组编译好的Transact-SQL语句，它们可以带一个或一组数值做参数，也可不带参数，它返回一个数值、数值集合，或执行一些操作。 • 函数能够重复执行一些操作，从而避免不断重写代码。 • SQL Server 2005支持两种函数类型： (1) 内置函数:是一组预定义的函数，是Transact-SQL语言的一部分，按Transact-SQL参考中定义的方式运行且不能修改。 (2) 用户定义函数:由用户定义的Transact-SQL函数。它将频繁执行的功能语句块封装到一个命名函数中，该函数可以由Transact-SQL语句调用。

30. 9.2.1 常用函数 10.3.1 内置函数 1．字符串函数 字符串函数用来实现对字符型数据的转换、查找、分析等操作，对字符串输入值执行操作，返回字符串或数字值 。 (1) ASCII()函数 返回字符表达式最左端字符的ASCII码值。语法格式如下。 ASCII ( character_expression ) 其中：character_expression为char或varchar类型的表达式。

31. 例如：以下示例为ASCII()函数的使用。 DECLARE @StringTest CHAR(10) SET @StringTest=ASCII('Robin ') SELECT @StringTest 执行结果为： 82

32. 9.2.1 常用函数 (2) CHAR()函数 将int型的ASCII码转换为字符的字符串函数。语法格式如下。 CHAR ( integer_expression ) 其中：integer_expression是介于0和255之间的整数。如果该整数表达式不在此范围内，将返回NULL值。 例如：以下示例为CHAR()函数的使用。 DECLARE @StringTest CHAR (10) SET @StringTest=ASCII('Robin ') SELECT CHAR(@StringTest) 执行结果为： R

33. 9.2.1 常用函数 (3) LEFT()函数 返回从字符串左边开始指定个数的字符。语法格式如下。 LEFT ( character_expression , integer_expression ) 其中： • character_expression：字符或二进制数据表达式。可以是常量、变量或列。可以是任何能够隐式转换为varchar或nvarchar的数据类型，但text或ntext除外。 • integer_expression：正整数，指定character_expression将返回的字符数。如果为负，则会返回错误。integer_expression可以是bigint类型。

34. 9.2.1 常用函数 例如：以下示例为LEFT()函数的使用。 DECLARE @StringTest CHAR (10) SET @StringTest='Robin ' SELECT LEFT(@StringTest,3) 执行结果为： Rob

35. 9.2.1 常用函数 (4) LOWER()函数 将大写字符数据转换为小写字符数据后返回字符表达式。语法格式如下。 LOWER ( character_expression ) 例如：以下示例为LOWER()函数的使用 DECLARE @StringTest CHAR (10) SET @StringTest='Robin ' SELECT LOWER(LEFT(@StringTest,3)) 执行结果为： rob

36. 9.2.1 常用函数 (5) LTRIM()函数 删除起始空格后返回字符表达式。语法格式如下。 LTRIM ( character_expression ) 例如：以下示例为LTRIM()函数的使用 DECLARE @StringTest CHAR (10) SET @StringTest=' Robin' SELECT 'Start-'+LTRIM(@StringTest),'Start-'+@StringTest 执行结果为： Start-Robin Start- Robin

37. 9.2.1 常用函数 (6) RIGHT()函数 返回字符串中从右边开始指定个数的字符。语法格式如下。 RIGHT ( character_expression , integer_expression ) 例如：以下示例为RIGHT()函数的使用。 DECLARE @StringTest CHAR (10) SET @StringTest=' Robin' SELECT RIGHT(@StringTest,3) 执行结果为： in

38. 9.2.1 常用函数 (7) RTRIM()函数 截断所有尾随空格后返回一个字符串。语法格式如下。 RTRIM ( character_expression ) 例如：以下示例为RTRIM()函数的使用。 DECLARE @StringTest CHAR (10) SET @StringTest='Robin ' SELECT @StringTest+'-End', RTRIM(@StringTest)+'-End' 执行结果为： Robin -End Robin-End

39. 9.2.1 常用函数 (8) STR()函数 将数字数据转换为字符数据。语法格式如下。 STR ( float_expression [ , length [ , decimal ] ] ) 其中： • float_expression：带小数点的近似数字(float)数据类型的表达式。 • length：总长度。它包括小数点、符号、数字以及空格。默认值为10。 • decimal：小数点后的位数。decimal必须小于或等于16。如果decimal大于16，则会截断结果，使其保持为小数点后具有十六位。

40. 9.2.1 常用函数 例如： SELECT 'A'+STR(82) 执行结果为： A 82 SELECT 'A'+LTRIM(STR(82)) 执行结果为： A82

41. 9.2.1 常用函数 (9) SUBSTRING()函数 求子串函数。语法格式如下。 SUBSTRING ( expression ,start , length ) 其中： • expression：是字符串、二进制字符串、文本、图像、列或包含列的表达式。不能使用包含聚合函数的表达式。 • start：指定子字符串开始位置的整数，start可以为bigint类型。 • length：正整数，指定要返回的expression的字符数或字节数。如果length为负，则会返回错误。length可以是bigint类型。

42. 9.2.1 常用函数 例如：以下示例为SUBSTRING()函数的使用。 DECLARE @StringTest char(10) SET @StringTest='Robin' SELECT SUBSTRING(@StringTest,3,LEN(@StringTest)) 执行结果为： bin

43. 9.2.1 常用函数 (10) UPPER()函数 返回将小写字符数据转换为大写字符的表达式。语法格式如下。 UPPER ( character_expression ) 例如：以下示例为UPPER ()函数的使用 DECLARE @StringTest CHAR(10) SET @StringTest='Robin' SELECT UPPER(@StringTest) 执行结果为： ROBIN

44. 9.2.1 常用函数 2.日期时间函数 日期时间函数对日期和时间输入值执行操作，并返回一个字符串、数字值或日期和时间值。 (1) DATEADD()函数 在指定日期加上一段时间的基础上，返回新的DATETIME类型值。语法格式如下。 DATEADD ( datepart , number, date ) 其中： • datepart：指定要返回新值的日期的组成部分。 • number：用来增加datepart的值。如果指定一个不是整数的值，则将废弃此值的小数部分。 • date：是返回DATETIME或SMALLDATETIME类型值或日期格式字符串的表达式。

45. 9.2.1 常用函数 表 10.4 SQL Server的日期部分

46. 9.2.1 常用函数 例如：以下示例为DATEADD()函数的使用。 DECLARE @OLDTime DATETIME SET @OLDTime='12-02-2004 06:30pm' SELECT DATEADD(hh,4,@OldTime) 执行结果为： 2004-12-02 22:30:00.000

47. 9.2.1 常用函数 (2) DATEDIFF()函数 两时间之差，返回跨两个指定日期的日期边界数和时间边界数。语法格式如下。 DATEDIFF ( datepart , startdate , enddate ) 其中： • datepart：指定应在日期的哪一部分计算差额的参数。 • startdate：计算的开始日期。startdate是返回DATETIME或SMALLDATETIME类型值或日期格式字符串的表达式。 • enddate：计算的结束日期。enddate是返回DATETIME或SMALLDATETIME类型值或日期格式字符串的表达式。

48. 9.2.1 常用函数 例如：以下示例为DATEDIFF()函数的使用。 DECLARE @FirstTime DATETIME, @SecondTime DATETIME SET @FirstTime='03-24-2006 6:30pm' SET @SecondTime='03-24-2006 6:33pm' SELECT DATEDIFF(ms, @FirstTime, @SecondTime) 执行结果为： 180000

49. 9.2.1 常用函数 (3) DATENAME() 返回表示指定日期的指定日期部分的字符串。语法格式如下。 DATENAME ( datepart , date ) 其中： • datepart：是指定要返回的日期部分的参数。 • date：表达式，用于返回DATETIME或SMALLDATETIME类型值，或日期格式的字符串。 例如：DECLARE @StatementDate DATETIME SET @StatementDate='2006-3-14 3:00 PM' SELECT DATENAME(dw,@StatementDate) 执行结果为： 星期二

50. 9.2 函 数 10.3.2 用户定义函数 用户定义函数(User-Defined Function，UDF)是执行计算并返回一个值(标量值或表)的一段程序。 根据函数返回值形式的不同将用户定义函数分为3种类型。 (1) 标量函数 标量函数返回一个确定类型的标量值，其函数值类型为SQL Server的系统数据类型（除text、ntext、image、cursor、timestamp、table类型外）。函数体语句定义在BEGIN…END语句内。 (2) 内嵌表值函数 内嵌表值函数返回的函数值为一个表。内嵌表值函数的函数体不使用BEGIN…END语句，其返回的表是RETURN子句中的SELECT命令查询的结果集，其功能相当于一个参数化的视图。 (3) 多语句表值函数 多语句表值函数可以看作标量函数和内嵌表值函数的结合体。其函数值也是一个表，但函数体也用BEGIN…END语句定义，返回值的表中的数据由函数体中的语句插入。