第7章 Transact-SQL程序设计 本章导读 Transact-SQL是内嵌在 SQL Server中的结构化查询语言,除了具备数据查询、 定义、操纵和控制功能外,还引入了程序设计的思想和过程控制结构,增 加了函数、系统存储过程,触发器等。灵活运用 Transact-SQL语言,可以 编写基于客户/服务器模式下的数据库应用程序。 知识要点: 程序设计基础知识 流程控制语句 内置函数 用户自定义函数 事务处理 71程序设计基础知识 7.2流程控制语句 73其它语句 74内置函数 75用户自定义函数
第7章 Transact-SQL程序设计 本章导读： Transact-SQL是内嵌在SQL Server中的结构化查询语言，除了具备数据查询、 定义、操纵和控制功能外，还引入了程序设计的思想和过程控制结构，增 加了函数、系统存储过程，触发器等。灵活运用Transact-SQL语言，可以 编写基于客户/服务器模式下的数据库应用程序。 知识要点： 程序设计基础知识 流程控制语句 内置函数 用户自定义函数 事务处理 7.1 程序设计基础知识 7.2 流程控制语句 7.3 其它语句 7.4 内置函数 7.5 用户自定义函数
7.1程序设计基础知识 程序设计的基础是处理数据,而数据在程序中最 常见的形式是常量、变量和表达式。 7.1.1常量 7.1.2变量 7.1.3表达式
7.1 程序设计基础知识 程序设计的基础是处理数据，而数据在程序中最 常见的形式是常量、变量和表达式。 7.1.1 常量 7.1.2 变量 7.1.3 表达式
7.1.1常量 常量也称字面值或标量值,是表示一个特定数据值的符号 量的格式取决于 它所表示的值的数据类型。 SQL Server中常量有如下几种形式 (1)字符串常量 字符串常量分为ASC字符串常量和 Unicode字符串常量。 ASC字符串常量 AsC川字符串常量:是指用定界符单引号(")括起来,由英文字母(a~z,A~Z) 和数字(0~9)以及特殊符号(!,@)等ASC字符组成的字符序列。如 中国,'合肥等。 如果在字符串中嵌入单引号("),可以使用两个连续的单引号(")表示嵌入的 个单引号(");而中间没有任何字符的两个连续的单引号(")表示空串。 Unicode字符串常量 Unicode字符串常量:则是以标识符(N)为前缀,再引导由定界符单引号(") 括起来的字符串。如N" china', Hefei'等。 Unicode字符串常量被解释为 Unicode数据。 Unicode数据中的每个字符用两个 字节存储,而ASC‖字符串中的每个字符则使用一个字节存储 注意: Unicode字符串的前缀N必须是大写字母。如:" database'是字符串常量, 而 Database是 Unicode常量
7.1.1 常量 常量也称字面值或标量值，是表示一个特定数据值的符号，常量的格式取决于 它所表示的值的数据类型。SQL Server中常量有如下几种形式： （1）字符串常量 字符串常量分为ASCII字符串常量和Unicode字符串常量。 ASCII字符串常量 ASCII字符串常量：是指用定界符单引号（'）括起来，由英文字母（a~z，A~Z） 和数字（0~9）以及特殊符号（！，@）等ASCII字符组成的字符序列。如' 中国' ， '合肥'等。 如果在字符串中嵌入单引号（'），可以使用两个连续的单引号（''）表示嵌入的 一个单引号（'）；而中间没有任何字符的两个连续的单引号（''）表示空串。 Unicode字符串常量 Unicode字符串常量：则是以标识符（N）为前缀，再引导由定界符单引号（'） 括起来的字符串。如N'china'，N'hefei'等。 Unicode字符串常量被解释为Unicode数据。Unicode 数据中的每个字符用两个 字节存储，而ASCII字符串中的每个字符则使用一个字节存储。 注意：Unicode字符串的前缀N必须是大写字母。如：'database'是字符串常量， 而N'database'是Unicode常量
7.1.1常量 (2)整型常量 按照整型常量的不同表示方式,又分为b常量、 interger 常量和二进制常量。 二进制位常量(bit 由数字0或1表示,没有定界符。如果使用一个大于1 的数字,将被转换为1。 十进制常量( interger) 由正、负号和数字0~9组成,正号可以省略。例如: 2006、3、-2009。 二进制常量 使用0X作为前辍,后面跟随16进制数字字符串,没有定 界符。例如:0xcCE、0x12E9、0x(空二进制常量)
7.1.1 常量 （2）整型常量 按照整型常量的不同表示方式，又分为bit常量、interger 常量和二进制常量。 二进制位常量（bit） 由数字 0 或 1 表示，没有定界符。如果使用一个大于1 的数字，将被转换为1。 十进制常量（interger） 由正、负号和数字0~9组成，正号可以省略。例如： 2006、3、-2009。 二进制常量 使用0x作为前辍，后面跟随16进制数字字符串，没有定 界符。例如：0xcdE、0x12E9、0x（空二进制常量）
7.1.1常量 (3)日期间常量 日期/时间常量用定界符单引号(")括起来的特 定格式的字符。 SQL Server提供并识别多种 格式的日期/时间,使用 set dateformat mdydmylymd命令可以设置日期时间格式 常见的日期格式有: 字母日期格式:"Apr15,1998,15-Apr 1998 数字日期格式:'10/15/2004,"2004-10-15, 2009年3月22日 未分隔的日期格式:980415,04/15/98 常见的时间格式有:'14:30:24,"0424PM
7.1.1 常量 （3）日期/时间常量 日期/时间常量用定界符单引号（'）括起来的特 定格式的字符。SQL Server提供并识别多种 格式的日期/时间，使用set dateformat mdy|dmy|ymd命令可以设置日期/时间格式。 常见的日期格式有： 字母日期格式：'April 15, 1998'，'15-April- 1998' 数字日期格式：'10/15/2004'，'2004-10-15'， '2009年3月22日' 未分隔的日期格式：'980415'，'04/15/98' 常见的时间格式有：'14:30:24'，'04:24 PM
7.1.1常量 (4) decima常量 decimal常量由正、负号、小数点、数字0~9组成,正号可以省略。例如: 91.3、-2147483648.10。 (5)foat和rea常量 foat和rea常量使用科学记数法表示。例如:101.5E5、0.5E-2。 (6)货币常量 货币常量是以可选货币符号($)作为前缀,并可以带正、负号和小数点的 串数字,用来表示正的或负的货币值。 SQL Server提供两种数据类型, 即 money和 smallmoney来存储货币数据,存储的精确度为4位小数。例 如$20、$45、-$35、$0.22等。 (7) uniqueidentifier常量 uniqueidentifier常量表示全局唯一标识符值的字符串。可以使用字符或二进 制字符串格式指定。例如,以下这两个示例指定相同的GUD。例如: 6F9619FF-8B86-D011-B42D00c04FC964FF 0xf19966f868b11d0b42d00c04fc964f
7.1.1 常量 （4）decimal常量 decimal常量由正、负号、小数点、数字0~9组成，正号可以省略。例如： 91.3、-2147483648.10。 （5）float和real常量 float和real常量使用科学记数法表示。例如：101.5E5、0.5E-2。 （6）货币常量 货币常量是以可选货币符号（$）作为前缀，并可以带正、负号和小数点的 一串数字，用来表示正的或负的货币值。SQL Server提供两种数据类型， 即money和smallmoney来存储货币数据，存储的精确度为4位小数。例 如$20、$45、-$35、$0.22等。 （7）uniqueidentifier常量 uniqueidentifier常量表示全局唯一标识符值的字符串。可以使用字符或二进 制字符串格式指定。例如，以下这两个示例指定相同的GUID。例如： '6F9619FF-8B86-D011-B42D-00C04FC964FF'、 0xff19966f868b11d0b42d00c04fc964ff
7.12变量 变量是指在程序运行过程中其值可以变化的量, 包括变量名和变量值两部分。变量名是对变量 的命名,变量值是对变量的赋值。 Transact- SQL中变量有两种:全局变量和局部变量 1.全局变量 全局变量:是 SQL Server2000系统定义并自动 赋值的变量,其作用范围是所有程序,主要用 来记录 SQL Server服务器的活动状态 用户可以引用全局变量但不能改变它的值,全局 变量必须以“@@”开头。 SQL Server2000提 供了30多个全局变量,如表7-1所示:
7.1.2 变量 变量是指在程序运行过程中其值可以变化的量， 包括变量名和变量值两部分。变量名是对变量 的命名，变量值是对变量的赋值。TransactSQL中变量有两种：全局变量和局部变量。 1．全局变量 全局变量：是SQL Server 2000系统定义并自动 赋值的变量，其作用范围是所有程序，主要用 来记录SQL Server服务器的活动状态。 用户可以引用全局变量但不能改变它的值，全局 变量必须以“@@”开头。SQL Server 2000提 供了30多个全局变量，如表7-1所示：
7.12变量 全局变量名功能 @@ connections返回连接或企图连接到 SQL Server(最近一次启动以来) 的连接次数 @@ cpu busy返回自 SQL Server最近一次启动以来,CPU的工作时间总量, 单位为毫秒 @@ cursor rows返回当前打开的最后一个游标中还未被读取的有效数据行 的行数 @@ datefirst返回一个星期中的第一天, set datefirst命令设置 datafirst 参数值,取值17 @@dbts 返回当前数据库的时间戳值,数据库中时间戳值必须是惟 @@error 返回最近一次执行 Transact-SQL语句的错误代码号,0表示 成功 @@ fetch status返回最近一次执行 fetch语句的游标状态值 @@ Identity返回最近一次插入行的 identity(标识列)列值 @@ide返回 SQL Server处于空闭状态的时间总量,单位为毫秒
7.1.2 变量 全局变量名 功能 @@connections 返回连接或企图连接到SQL Server（最近一次启动以来） 的连接次数 @@cpu_busy 返回自SQL Server最近一次启动以来，CPU的工作时间总量， 单位为毫秒 @@cursor_rows 返回当前打开的最后一个游标中还未被读取的有效数据行 的行数 @@datefirst 返回一个星期中的第一天，set datefirst命令设置datafirst 参数值，取值1~7 @@dbts 返回当前数据库的时间戳值，数据库中时间戳值必须是惟 一的 @@error 返回最近一次执行Transact-SQL语句的错误代码号，0表示 成功 @@fetch_status 返回最近一次执行fetch 语句的游标状态值 @@identity 返回最近一次插入行的identity（标识列）列值 @@idle 返回SQL Server处于空闭状态的时间总量，单位为毫秒 @@io_busy 返回SQL Server执行输入输出操作所花费的时间总量，单
7.12变量 2.局部变量 局部变量:用户自定义的变量,其作用范围是声明它的批处理、存储过 程或触发器等程序内部,一般用来存储从表中查询到的数据,或作 为程序执行过程中暂存变量。局部变量必须以“@”开头,且必须先 用 declare命令声明后才可使用。声明局部变量的语法格式如下: 格式: declare@局部变量名as]数据类型}[,,n] 说明 (1)局部变量名必须符合标识符命名规则; (2)数据类型可以是系统数据类型,也可以是用户自定义数据类型 但不能定义为text,ntex或 Image数据类型。如有需要,还需指定数 据宽度及小数精度; (3)声明多个局部变量名时,各变量名之间用逗号隔开; (4)局部变量声明后,系统自动初始化赋值为nu‖,局部变量声明时不 能同时赋值; (5)给局部变量赋值要用赋值语句,赋值语句有两种:set语句和 select语句
7.1.2 变量 2．局部变量 局部变量：用户自定义的变量，其作用范围是声明它的批处理、存储过 程或触发器等程序内部，一般用来存储从表中查询到的数据，或作 为程序执行过程中暂存变量。局部变量必须以“@”开头，且必须先 用declare命令声明后才可使用。声明局部变量的语法格式如下： 格式：declare {@局部变量名 [as] 数据类型} [,…n] 说明： （1）局部变量名必须符合标识符命名规则； （2）数据类型可以是系统数据类型，也可以是用户自定义数据类型， 但不能定义为text，ntext或image数据类型。如有需要，还需指定数 据宽度及小数精度； （3）声明多个局部变量名时，各变量名之间用逗号隔开； （4）局部变量声明后，系统自动初始化赋值为null，局部变量声明时不 能同时赋值； （5）给局部变量赋值要用赋值语句，赋值语句有两种：set语句和 select语句
7.12变量 set语句 格式:set{≤@局部变量名>三} 说明:将“表达式”的值赋给“@局部变量名”指 定的局部变量,一条语句只能给一个变量赋值 【例7-1】计算两数之和。 declare @sum int, @a as int, @b as int set @a=10 set @b=90 set @sum=@a+@b print @sum
7.1.2 变量 Set语句 格式：set {=} 说明：将“表达式”的值赋给“@局部变量名”指 定的局部变量，一条语句只能给一个变量赋值。 【例7-1】 计算两数之和。 declare @sum int,@a as int,@b as int set @a=10 set @b=90 set @sum=@a+@b print @sum