第7章 Transact-SQL程序设计 本章导读：

Presentation on theme: "第7章 Transact-SQL程序设计 本章导读："— Presentation transcript:

1 第7章 Transact-SQL程序设计 本章导读：
Transact-SQL是内嵌在SQL Server中的结构化查询语言，除了具备数据查询、定义、操纵和控制功能外，还引入了程序设计的思想和过程控制结构，增加了函数、系统存储过程，触发器等。灵活运用Transact-SQL语言，可以编写基于客户/服务器模式下的数据库应用程序。 知识要点：  程序设计基础知识  流程控制语句  内置函数  用户自定义函数  事务处理 7.1 程序设计基础知识 7.2 流程控制语句 7.3 其它语句 7.4 内置函数 7.5 用户自定义函数

2 7.1 程序设计基础知识 程序设计的基础是处理数据，而数据在程序中最常见的形式是常量、变量和表达式。 7.1.1 常量 7.1.2 变量
7.1 程序设计基础知识 程序设计的基础是处理数据，而数据在程序中最常见的形式是常量、变量和表达式。 常量 变量 表达式

3 常量 常量也称字面值或标量值，是表示一个特定数据值的符号，常量的格式取决于它所表示的值的数据类型。SQL Server中常量有如下几种形式： （1）字符串常量 字符串常量分为ASCII字符串常量和Unicode字符串常量。  ASCII字符串常量 如果在字符串中嵌入单引号（'），可以使用两个连续的单引号（''）表示嵌入的一个单引号（'）；而中间没有任何字符的两个连续的单引号（''）表示空串。  Unicode字符串常量 Unicode字符串常量：则是以标识符（N）为前缀，再引导由定界符单引号（'）括起来的字符串。如N'china'，N'hefei'等。 Unicode字符串常量被解释为Unicode数据。Unicode 数据中的每个字符用两个字节存储，而ASCII字符串中的每个字符则使用一个字节存储。 注意：Unicode字符串的前缀N必须是大写字母。如：'database'是字符串常量，而N'database'是Unicode常量。

4 7.1.1 常量 （2）整型常量 按照整型常量的不同表示方式，又分为bit常量、interger常量和二进制常量。 二进制位常量（bit）
常量 （2）整型常量 按照整型常量的不同表示方式，又分为bit常量、interger常量和二进制常量。 二进制位常量（bit） 由数字 0 或 1 表示，没有定界符。如果使用一个大于1的数字，将被转换为1。 十进制常量（interger） 由正、负号和数字0~9组成，正号可以省略。例如：2006、3、-2009。 二进制常量 使用0x作为前辍，后面跟随16进制数字字符串，没有定界符。例如：0xcdE、0x12E9、0x（空二进制常量）。

5 常量 （3）日期/时间常量 日期/时间常量用定界符单引号（'）括起来的特定格式的字符。SQL Server提供并识别多种格式的日期/时间，使用set dateformat mdy|dmy|ymd命令可以设置日期/时间格式。 常见的日期格式有： 字母日期格式：'April 15, 1998'，'15-April-1998' 数字日期格式：'10/15/2004'，' '，'2009年3月22日' 未分隔的日期格式：'980415'，'04/15/98' 常见的时间格式有：'14:30:24'，'04:24 PM'

6 常量 （4）decimal常量 decimal常量由正、负号、小数点、数字0~9组成，正号可以省略。例如：91.3、 。 （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 变量 变量是指在程序运行过程中其值可以变化的量，包括变量名和变量值两部分。变量名是对变量的命名，变量值是对变量的赋值。Transact-SQL中变量有两种：全局变量和局部变量。 1．全局变量 全局变量：是SQL Server 2000系统定义并自动赋值的变量，其作用范围是所有程序，主要用来记录SQL Server服务器的活动状态。 Server 2000提供了30多个全局变量，如表7-1所示：

8 7.1.2 变量 全局变量名 功能 @@connections 返回连接或企图连接到SQL Server（最近一次启动以来）的连接次数
变量 全局变量名 功能 返回连接或企图连接到SQL Server（最近一次启动以来）的连接次数 返回自SQL Server最近一次启动以来，CPU的工作时间总量，单位为毫秒 返回当前打开的最后一个游标中还未被读取的有效数据行的行数 返回一个星期中的第一天，set datefirst命令设置datafirst参数值，取值1~7 返回当前数据库的时间戳值，数据库中时间戳值必须是惟一的 返回最近一次执行Transact-SQL语句的错误代码号，0表示成功 返回最近一次执行fetch 语句的游标状态值 返回最近一次插入行的identity（标识列）列值 返回SQL Server处于空闭状态的时间总量，单位为毫秒 返回SQL Server执行输入输出操作所花费的时间总量，单位为毫秒 返回SQL Server使用的语言的ID值 返回SQL Server使用的语言名称 返回当前会话所设置的资源锁超时时长，单位为毫秒 返回允许连接到SQL Server的最大连接数目 返回decimal 和numeric 数据类型的精确度 返回当前执行的存储过程的嵌套级数，初始值为0，最大值为16 返回当前set选项的信息 返回SQL Server 通过网络读取的输入包的数目 返回SQL Server 写给网络的输出包的数目 返回SQL Server读取网络包的错误数目 返回当前存储过程的ID值 返回远程SQL Server数据库服务器的名称 返回最近一次T-SQL语句所影响的数据行的行数，0表示不返回任何行 返回本地运行SQL Server的数据库服务器的名称

9 变量 2．局部变量 格式：declare [as] 数据类型} [,…n] 说明： （1）局部变量名必须符合标识符命名规则； （2）数据类型可以是系统数据类型，也可以是用户自定义数据类型，但不能定义为text，ntext或image数据类型。如有需要，还需指定数据宽度及小数精度； （3）声明多个局部变量名时，各变量名之间用逗号隔开； （4）局部变量声明后，系统自动初始化赋值为null，局部变量声明时不能同时赋值； （5）给局部变量赋值要用赋值语句，赋值语句有两种：set语句和select语句。

10 7.1.2 变量  Set语句 格式：set {<@局部变量名>=<表达式>}
变量  Set语句 格式：set 【例7-1】 计算两数之和。 as as int set

11 变量  select语句 格式：select [from<表名>[,…n] where<条件表达式>] 说明： （2）select既可以查询数据又可以赋值变量，但不能同时使用，如果select语句返回多个数值（多行记录），则局部变量只取最后一个返回值； （3）一条语句可以给多个变量分别赋值。 【例7-2】 计算“选修”表中男生平均成绩和总成绩。 use jxgl float select from 学生,选修 where 学生.学号=选修.学号 and 性别=’男’

12 变量 2．全局变量 全局变量：是SQL Server 2000系统定义并自动赋值的变量，其作用范围是所有程序，主要用来记录SQL Server服务器的活动状态。 Server 2000提供了30多个全局变量，如表7-1所示：

13 运算符 运算符是用来连接运算对象（或操作数）的符号，表达式是指用运算符将运算对象（或操作数）连接起来式子。Transact-SQL提供7类运算符及其对应表达式，分别是算术运算符及表达式、字符串连接运算符及表达式、赋值运算符及表达式、比较运算符及表达式、逻辑运算符及表达式、位运算符及表达式、一元运算符及表达式。 1．运算符 （1）算术运算符 算术运算符用于数值型数据的算数运算，算术运算符及其适用数据类型如表7-2所示。

14 7.1.3 运算符 表7-2 算术运算符及其含义 算术运算符 含义 数据类型 +、-、*、/ 加，减，乘，除
运算符 表7-2 算术运算符及其含义 算术运算符 含义 数据类型 +、-、*、/ 加，减，乘，除 int、smallint、tinyint、decimal、float、real、money、smallmoney % 求余 int、smallint、tinyin

15 7.1.3 表达式 算术运算符 含义 数据类型 +、-、*、/ 加，减，乘，除
表达式 （2）关系运算符 关系运算符用来比较两个表达式的值是否相同，比较的结果：值相同时为true，否则为false，当参与比较操作数含有null时，结果为unknown。关系运算符及其适用数据类型如表7-3所示。 表7-3 关系运算符及其含义 算术运算符 含义 数据类型 +、-、*、/ 加，减，乘，除 int、smallint、tinyint、decimal、float、real、money、smallmoney % 求余 int、smallint、tinyin 注意：关系运算符又称为比较运算符，关系运算符不能用于text，ntext，image数据类型运算。另外。有时也把all、any、some、between…and、in、like当作关系运算符。

16 7.1.3 表达式 （3）位运算符 位运算符用于对数据进行按位运算。位运算符及其含义如表7-4所示。 表7-4 位运算符及其含义
表达式 （3）位运算符 位运算符用于对数据进行按位运算。位运算符及其含义如表7-4所示。 表7-4 位运算符及其含义 位运算符 含义 & 位与，双目运算，参与运算的两个位值都是1时，结果为1，否则为0 | 位或，双目运算，参与运算的两个位值都是0时，结果为0，否则为1 ^ 位异或，双目运算，参与运算的两个位值不同时，结果为1，否则为0 ~ 位取反，单目运算，即~1=0，~0=1 在进行整数数据的位运算时，先对整数转换为二进制数据，然后再进行按位计算；也可以对整数和二进制数据进行混合运算，但不能同时为二进制数据类型。位运算所支持的数据类型如表7-5所示。

17 7.1.3 表达式 （3）位运算符 位运算符用于对数据进行按位运算。位运算符及其含义如表7-4所示。 表7-5 参与位运算的左右操作数
表达式 （3）位运算符 位运算符用于对数据进行按位运算。位运算符及其含义如表7-4所示。 表7-5 参与位运算的左右操作数 位运算符 含义 & 位与，双目运算，参与运算的两个位值都是1时，结果为1，否则为0 | 位或，双目运算，参与运算的两个位值都是0时，结果为0，否则为1 ^ 位异或，双目运算，参与运算的两个位值不同时，结果为1，否则为0 ~ 位取反，单目运算，即~1=0，~0=1

18 表达式 （4）逻辑运算符 逻辑运算符用来将多个关系表达式连接起来进行组合运算，返回值true或false。逻辑运算符及其含义如表7-6所示。 表7-6 逻辑运算符及其含义 逻辑运算符 含义 not 非运算，单目运算，对关系表达式的值取反，即not(true)=false，not(false)=true and 与运算，双目运算，参与运算的两个关系表达式值都是true时，才为true，否则为false or 或运算，双目运算，参与运算的两个关系表达式值都是false时，才为false，否则为true

19 7.1.3 表达式 （5）字符串运算符 字符串运算符是用来将两个字符串连接成一个新的字符串的运算符。字符串运算符只有一个，即加号（+）。
表达式 （5）字符串运算符 字符串运算符是用来将两个字符串连接成一个新的字符串的运算符。字符串运算符只有一个，即加号（+）。 （6）赋值运算符 赋值运算符是将表达式的值赋给变量的运算符号。赋值运算符只有一个，即等号（=）。 （7）一元运算符 一元运算符只对一个表达式进行运算的运算符号，这个表达式的值可以是数值数据类型中的任何一种数据类型。一元运算符及其含义如表7-7所示。 表7-7 一元运算符及其含义 一元运算符 含义 + 表示数据的正号 - 表示数据的负号 ~ 求一个数字的补数

20 表达式 2．运算符的优先级 当混合使用多种运算符构成一个复杂的表达式时，表达式中有括号先算括号内，再算括号外；无括号时，运算符的优先级决定了运算的先后顺序，并影响计算的结果。运算符的优先级从高到底排列顺序如表7-8所示。 注意：表中同一行各运算符优先级相同，当表达式中含有优先级相同的多个运算符时，根据它们在表达式中的位置，二元运算符按照从左到右的顺序执行，一元运算符按照从右到左的顺序执行。

21 7.1.3 表达式 运算符 优先级 +（正）、-（负）、~（按位取反） 1 *（乘）、/（除）、%（模） 2
表达式 表7-8 运算符及其优先级 运算符 优先级 +（正）、-（负）、~（按位取反） 1 *（乘）、/（除）、%（模） 2 +（加）、（+ 串联）、−（减） 3 =、>、<、>=、<=、<>、!=、!>、!<（比较运算符） 4 ^（位异或）、&（位与）、|（位或） 5 not 6 and 7 all、any、between、in、like、or、some 8 =（赋值） 9

22 7.2 流程控制语句 SQL Server程序结构用来控制T-SQL语句，语句块和存储过程等的运行流程，主要包括块语句，分支语句和循环语句等。 块语句 二分支语句 多分支表达式

23 7.2.1 块语句 begin…end语句的作用是将多条T-SQL语句合成一个语句块，并将它们视为一个单元整体处理。语法格式如下：
块语句 begin…end语句的作用是将多条T-SQL语句合成一个语句块，并将它们视为一个单元整体处理。语法格式如下： begin {sql语句|语句块} end 说明： （1）将多条语句封装成一个语句块，服务器在处理时，整个语句块等同一条语句； （2）begin…end语句也可以嵌套。 【例7-3】 从“选修”表中求出学号为“ ”的平均成绩，如果此平均成绩大于或等于60分，则输出“该同学全部通过考试，没有挂考”信息。 if (select avg(成绩) from 选修 where 学号=' ')>=60 print '该同学全部通过考试，没有挂考'

24 二分支语句 if…else语句和if [not] exists…else语句是T-SQL语句提供的两种二分支结构。使用分支结构可以编写进行判断和选择操作的SQL语句（块）代码。 1．if…else语句 if…else语句用于判断条件是true或false，并且根据判断结果指定要执行的语句。通常，条件是使用比较运算符对值或变量进行比较的表达式。语法格式如下： if<条件> {sql语句1|语句块1} [else {sql语句2|语句块2}] 说明：条件表达式为true时，则运行if…else之间的“sql语句1|语句块1”，否则，如果有else分支，则运行else之后的“sql语句2|语句块2”。 【例7-4】 根据给定教师的姓名，查询出该教师的本校工龄是否在30年以上。 if (select datediff(year,工作日期,getdate()) from 教师 where 姓名='李教师')>=30 print '该教师的工龄至少30年，可以提出退休申请' else print '该教师的工龄不足30年，不可以提出退休申请'

25 7.2.2 二分支语句 2．if [not] exists…else语句
二分支语句 2．if [not] exists…else语句 if [not] exists…else语句用于检测数据是否存在，而不考虑与之匹配的行数，对于存在性检测而言，使用if [not] exists要比count(*)>0好，效率高。语法格式如下： if [not] exist<条件> {sql语句1|语句块1} [else {sql语句2|语句块2}] 说明：条件表达式为true时，则运行if…else之间的“sql语句1|语句块1”，否则，如果有else分支，则运行else之后的“sql语句2|语句块2”。 【例7-5】 根据给定课程的课程类型，如果存在就计算该种课程类型的门数。 if exists(select * from 课程 where 课程类型='考查') select count(*) as '考查课门数' from 课程 where 课程类型='考查' else print '没有考查课' 运行结果如图7-1所示。

26 多分支表达式 case表达式是计算多个条件的表达式，并返回其中一个符合条件的表达式结果。case表达式不是语句，不能独立运行，必须嵌入其它语句中才能起作用。case表达式提供两种格式：简单case表达式和搜索case表达式.

27 多分支表达式 1．简单case表达式 简单case表达式是将表达式与when子句中的值依次进行比较，直到发现第一个与表达式相等的值，便返回该when子句关联then子句中的值，不再判断后续when子句中的值。其语法格式如下： case <表达式> when <值1> then <结果1> [when <值2> then <结果2> … when <值n> then <结果n> ] [else <结果n+1>] end [as 表达式别名] 说明： （1）case函数在其开始处使用一个只计算一次的简单测试表达式。 （2）表达式的结果依次与分支中的when子句值进行比较，一旦匹配，就返回when子句关联的then子句值，然后执行end后面的子句。 （3）若所有when子句值都不满足表达式的结果，如果有else子句，则返回else子句值。

28 7.2.3 多分支表达式 【例7-6】 输出选修信息，并输出表中各课程号对应的课程名。 select 学号,课程号, case 课程号
多分支表达式 【例7-6】 输出选修信息，并输出表中各课程号对应的课程名。 select 学号,课程号, case 课程号 when '01' then '计算机基础' when '02' then 'ASP程序设计' when '03' then '数据库SQL Server' else '其它课程' end as 课程名称,成绩 from 选修

29 7.2.3 多分支表达式 2．搜索case表达式 case when <逻辑表达式1> then <结果值1>
多分支表达式 2．搜索case表达式 case when <逻辑表达式1> then <结果值1> [when <逻辑表达式2> then <结果值2> … when <逻辑表达式n> when <结果值n>] [else <结果值n+1>] end 说明： （1）依次计算when子句后的“逻辑表达式”，直到找到第一个值为true的“逻辑表达式”，就返回when子句关联的then子句中的结果值，不再判断后续when子句中的值 （2）若所有的when子句后的“逻辑表达式”都不满足true，如果有else子句，则返回else子句中的结果值。

30 多分支表达式 【例7-7】 查询学生的相关信息，并将成绩按照以下规则替换，60分以下替换不及格，60~85替换为合格，85分以上替换为优秀，其它替换为未考。 select 学生.学号,姓名,成绩= case when 成绩 is null then '未考' when 成绩 <60 then '不及格考' when 成绩 <85 then '合格' else '优秀' end from 学生,选修 where学生.学号=选修.学号

31 多分支表达式 【例7-8】 计算各教师的各门各班课程的课酬信息，课酬计算公式=学时*课酬标准，其中课酬标准分别如下：教授为50，副教授为45，讲师为35，助教为30。 select 教师.工号,课程.课程号,班级.班级号,课酬 = 学时* case when 职称='教授' then 50 when 职称='副教授' then 45 when 职称='讲师' then 35 else 30 end into 课酬 from 教师,课程,授课,班级 where 教师.工号=授课.工号 and 课程.课程号=授课.课程号 and 班级.班级号=授课.班级号 go select * from 课酬 运行结果如图7-2所示。

32 7.2.4 循环语句 可以使用while…continue…break语句重复执行SQL语句或语句块。语法格式如下：
循环语句 可以使用while…continue…break语句重复执行SQL语句或语句块。语法格式如下： while<条件> {sql语句1|语句块1} [break] {sql语句2|语句块2} [continue] 说明： （1）首先判断条件是否为true，如果为true，则按顺序往下执行循环体，本次循环执行完毕后，回到while<条件>开始处，再次判断条件，如果为true，继续执行循环体，重复前面的步骤，直至while<条件>为假，跳出循环体，结束循环； （2）break语句是使程序完全跳出本层循环，结束整个循环体的执行； （3）continue语句是使程序终止本次循环，结束循环体中continue后面语句的执行，返回while<条件>开始处，重新开始下一次的while循环。

33 7.2.4 循环语句 【例7-9】 求1~100之间的奇数之和。 declare @sum as int
循环语句 【例7-9】 求1~100之间的奇数之和。 as int as smallint /*外层循环从1到100*/ begin set if continue else set break end print /*输出和*/ 运行结果如图7-3所示。

34 7.2.5 其它语句 其他语句包括批处理语句，数据库切换语句，显示语句等，分别介绍如下： 1．批处理语句
其它语句 其他语句包括批处理语句，数据库切换语句，显示语句等，分别介绍如下： 1．批处理语句 批处理是成组执行一条或多条Transact-SQL语句的语句或命令集合。批处理运行时是一次性分析，编译和执行。 一系列顺序提交的批处理称为脚本。一个脚本中可以包含一个或多个批处理。批处理和批处理之间的定界是通过SQL Server的关键字go来定界。 在SQL Server中使用批处理有如下限制： （1）大多数create语句不可以在同一个批处理中使用，如create procedure，create rule，create default，create trigger，create view不能混合使用； （2）不能在同一批处理中使用alter table命令修改表结构后，又立即引用其新增的列； （3）不能在同一批处理中，删除一个对象后又立即重建它； （4）用set语句改变的选项在批处理结束时生效。 （5）如果在同一批处理中运行多个存储过程，则除第一个存储过程外，其余存储过程在调用时必须使用execute语句

35 7.2.5 其它语句 2．切换数据库语句 在代码中，使用use命令来切换数据库，其语法格式如下： 格式：use 数据库名
其它语句 2．切换数据库语句 在代码中，使用use命令来切换数据库，其语法格式如下： 格式：use 数据库名 说明：将指定的数据库切换为当前数据库，才可对其及其中的对象做进一步操作。 3．显示语句 Print语句用于向客户端输出信息，其语法格式如下： 格式：print 说明： （1）向客户端输出一个字符串，一个局部变量或全局变量； （2）如有必要，可用convert或cast函数将其它数据类型数据转换成字符串数据类型。

36 7.2.5 其它语句 4．暂停语句 waitfor语句是使程序暂停一段时长或暂停到某一时刻后继续执行。其语法格式如下：
其它语句 4．暂停语句 waitfor语句是使程序暂停一段时长或暂停到某一时刻后继续执行。其语法格式如下： 格式：waitfor {delay 'hh:mm:ss'|time 'hh:mm:ss'} 说明： （1）delay关键字表示暂停到由“hh:mm:ss”指定的时长间隔后，再继续执行其后语句，时长最大值为24小时； （2）time关键字表示暂停到由“hh:mm:ss”指定的时刻点，再继续执行其后语句。 5．注释语句 注释是用来说明程序代码的含义，提高程序的可读性，使得日后维护程序更加容易。SQL Server提供了两种形式： 格式1：--注释语句 格式2：/*注释语句*/ （1）--（双连字符）用于单行注释，从双连字符开始到结尾都是注释语句，一般放在程序后面，也可以单独另起一行； （2）/*…*/用于多行注释，位置比较自由，既可以在放在程序代码后面，也可另起一行，甚至放在程序代码内部。 （3）/*…*/不能跨越批处理，整个注释必须包含在一个批处理中。

37 7.2.5 其它语句 【例7-10】 查询学生选修成绩表。 use jxgl –-切换数据库jxgl为当前数据库
其它语句 【例7-10】 查询学生选修成绩表。 use jxgl –-切换数据库jxgl为当前数据库 select * from 选修 where left(学号,6)=’080101’ /*筛选条件*/ order by 籍贯 asc /*升序输出，默认值为ASC*/

38 其它语句 6．无条件退出语句 return语句可以出现在T-SQL语句的批处理，语句块和存储过程中的任何位置，其作用无条件地从存储过程、批处理或语句块中退出，其后的语句不会被执行。语法格式如下： 格式：return[<整数值>] 说明： （1）结束当前程序的运行，返回到调用它的上一级程序； （2）整数值是被调用的存储过程向父进程报告本进程的执行状态； （3）如果没有指定返回值，SQL Server系统会根据程序执行的结果返回一个内定值（−99~−1），常见内定值及其含义如表7-9所示。

39 7.2.5 其它语句 表7-9 内定值及其含义 返回值 含义 程序执行成功 -7 资源错误 -1 找不到对象 -8 非致命错误 -2
其它语句 表7-9 内定值及其含义 返回值 含义 程序执行成功 -7 资源错误 -1 找不到对象 -8 非致命错误 -2 数据类型错误 -9 已达到系统的极限 -3 死锁 -10，-11 致命的内部不一致错误 -4 违反权限原则 -12 表或指针错误 -5 语法错误 -13 数据库破环 -6 用户造成的一般错误 -14 硬件错误

40 其它语句 7．无条件跳转语句 goto语句改变程序的执行流程，使程序流程被无条件地转移到有标号的语句处继续执行，而位于goto语句和标号之间的语句不会被执行。语法格式如下： 格式：goto标号 … 标号: 说明： （1）goto语句和标号可以用在语句块、批处理中和存储过程中，标号可以是数字和字符的组合，但必须以冒号（:）结尾； （2）goto语句破坏了程序结构化的特点，使得程序结构变得复杂而难以理解，建议不用； （3）goto语句实现的逻辑结构完全可以使用其它语句实现，goto语句最好用于跳出深层次嵌套的控制流语句。

41 其它语句 【例7-11】 查询选修表，如果其中存在学号为’ ’的学生，那么就显示“该学生的成绩存在”，并查询出该学生所有课程的成绩，否则跳转过这些语句，显示“没有此学生的成绩”。 if (select count(*) from 选修 where 学号=' ')=0 goto noation begin print '该学生的成绩存在' select 学号,课程号,成绩 from 选修 where 学号=' ' end noation: print '该学生的成绩不存在'

42 7.2.5 其它语句 8．返回错误代码语句 将报错信息显示在屏幕上，同时记录在NT日志中，其语法格式如下：
其它语句 8．返回错误代码语句 将报错信息显示在屏幕上，同时记录在NT日志中，其语法格式如下： 格式：raiserror ({msg_id|msg_str}{,serverity,state}[,argument[,…n]])[with option[,…n]] 说明： （1）msg_id是存储于sysmessages表中的用户定义的错误信息标识号。户定义的错误信息标识号应大于50000。由特殊消息产生的错误号是第50000号； （2）msg_str是一条特殊的消息，此消息最多包含4000个字符； （3）serverity表示用户定义的与消息关联的严重级别，用户可以从0~18之间的严重级别，19~25之间的严重级别只能由系统管理员引发。严重等级在25以上的错误在使用raiserror引发时，必须选择with log选项； （4）state从1~127的任意整数，表示有关错误发生的状态信息； （5）with option给出raiserror的选项，option取值如表7-10所示。

43 7.2.5 其它语句 值 含义 log 错误记录到SQL Server错误日志中和windows NT应用程序日志中 nowait
其它语句 表7-10 option取值及其含义 值 含义 log 错误记录到SQL Server错误日志中和windows NT应用程序日志中 nowait 将错误消息发送到客户端 seterror

44 其它语句 【例7-12】 在屏幕上显示一条信息，在信息中列出当前使用的数据库标识号和名称，信息由格式化字符串直接给出。 use jxgl go int =db_id() nvarchar(128) 运行如果如图7-4所示。 图7-4 例7-12运行结果

45 7.3 内置函数 函数是由一条或多条Transact-SQL语句组成的集合，用于完成某个特定的功能。SQL Server提供了两种类型的函数：内置函数和用户自定义函数，用户可以直接调用这些函数。 内置函数是系统预定义的函数，是Transact-SQL语言的一部分，一般分为三大类： （1）行集函数：返回的结果是对象，该对象可在Transact-SQL语句中用作表来引用。 例如，使用openquery函数执行一个分布式查询，以便从服务器shuju中提取表“student”中的记录。 select * from openquery(shuju,'select name, id from student') （2）聚合函数：对一组值进行处理和计算，并返回一个单列值。 例如，设在当前数据库中拥有一个员工工资表“employee”，其中有一个工资列“salary”，统计所有员工的工资总和，语句为： select sum(salary) from employee （3）标量函数：对传递给它的一个或者多个值进行处理和计算，并返回一个单列值。

46 7.3.1 数学函数 常见的数学函数如表7-11所示。 函数名 功能 abs(x) 求绝对值 log10(x) 求以10为底的自然对数
数学函数 常见的数学函数如表7-11所示。 函数名 功能 abs(x) 求绝对值 log10(x) 求以10为底的自然对数 sqrt(x) 求平方根 round(x,n) n<0，对整数部分四舍五入，n>0为保留小数位 square(x) 求平方 ceiling(x) 求大于等于给定数的最小整数 power(x,y) 求x的y次方 floor(x) 求小于等于给定数的最大整数 sin(x) 求正弦值 pi() 返回圆周率 cos(x) 求余弦值 radians(x) 将角度值转换为弧度值 tan(x) 求正切值 degrees(x) 将弧度值转换为角度值 log(x) 求自然对数 sign(x) 求一个数的符号 exp(x) 求指数值 rand(x) 随机数

47 7.3.2 聚合函数 常用的系统函数如表7-12所示。 聚合函数 功能 avg([distinct|all]表达式)
聚合函数 常用的系统函数如表7-12所示。 聚合函数 功能 avg([distinct|all]表达式) 返回表达式(含列名)的平均值，distinct是去掉重复值，all是所有值 count([distinct|all]表达式) 对表达式指定的列值进行计数，忽略空值，distinct|all含义同上 count([distinct|all] *) 对表或组中的所有行进行计数，包含空值，distinct|all含义同上 max([distinct|all]表达式) 表达式中最大的值，distinct|all含义同上 min([distinct|all]表达式) 表达式中最小的值，distinct|all含义同上 sum([distinct|all]表达式) 表达式值的合计，distinct|all含义同上

48 7.3.2 聚合函数 【例7-13】 查询学生表中学生总数 select count(*) from 选修
聚合函数 【例7-13】 查询学生表中学生总数 select count(*) from 选修 【例7-14】 统计参加选修课程的学生数。 select count(distinct 学号) from 选修 【例7-15】 统计学生表中各班级现有男生学生数。 select '班号'=left(学号,6),count(*) as 人数 from 学生 where 性别='男' group by left(学号,6) 注意：只有group by 子句中列才能与聚集函数同时出现在select子句中。 【例7-16】 统计选修表中各门课程的最高分、最低分、平均分。 select 课程号,max(成绩),min(成绩),avg(成绩) from 选修 group by 课程号

49 7.3.3 系统函数 常用的系统函数如表7-13所示。 函数名 功能 host_id() 客户进程的当前住进程的ID号
系统函数 常用的系统函数如表7-13所示。 函数名 功能 host_id() 客户进程的当前住进程的ID号 host_name() 返回服务器端的计算机的名称 suser_sid() 返回SQL Server sa登录名的安全标识号 db_id() 返回指定数据的标志id db_name() 根据数据库的标志ID返回相应的数据库的名字 datbaseproperty(数据库,属性名) 返回指定数据库在指定属性上的取值 object_id(对象名) 返回指定数据库对象的表示ID object _name(对象Id) 根据数据库的标志ID返回相应的数据库对象名字 object eproperty((对象Id,属性名) 返回指定数据库对象在指定属性上的取值值 col_length(数据库表名,列名) 返回指定表的指定列的长度 col_name(数据库表Id,列序号) 返回指定表的指定列的名字

50 7.3.4 字符串函数 常见的字符串函数如表7-14所示。 函数名 功能 upper(str) 将字符串转化大写 lower(str)
字符串函数 常见的字符串函数如表7-14所示。 函数名 功能 upper(str) 将字符串转化大写 lower(str) 将字符串转化小写 ltrim(str) 删除字符串左边的空格 rtrim(str) 删除字符串右边的空格 char(n) 求ASCII码值对应的字符 replicate(str,n) 字符串连续输出n次 left(str,n) 从左边获取n个字符串 right(str,n) 从右边获取n个字符串 space(n) 输出n个空格 nchar(n) 返回unicode字符 reverse(str) 反转输出字符串 datalength(str) 返回所占字符串的字节数 ascii(str) 求字符串中第一个字符的ASCII码值 charindex(str1,str2[,n]) 从字符串str1中指定位置n处查找字符串str2 replace(str1,str2,str3) 用字符串str3替换字符串str1中出现的字符串str2 stuff(str1,n,m,str2) 将str1从位置n到m的字符串替换为str2 str(value,n[,m]) 数字转换成长度为n的字符串，同时含m位小数 patindex(‘%subs%’,str) 查找字符串str中指定格式的字符串subs len(str) 求字符串的字符个数，不包括尾部空格 substring(str,n,m) 从字符串中指定位置n处开始取m个字符

51 7.3.4 字符串函数 【7-17】 在“教师”表中查找姓名以“李”开头的教师。
字符串函数 【7-17】 在“教师”表中查找姓名以“李”开头的教师。 select patindex('%李_%',姓名) from 教师

52 7.3.5 日期和时间函数 函数名 功能 getdate() 返回当前系统日期和时间 dateadd(间隔因子,n,d)
日期和时间函数 用于对日期和时间数据进行各种不同的处理或运算，并返回一个字符串、数字值或日期和时间值，常见的字符串表达式如表7-15所示。 表7-15 日期和时间函数及其功能 函数名 功能 getdate() 返回当前系统日期和时间 dateadd(间隔因子,n,d) 计算日期时间加d加上数字n后日期时间，间隔因子如表7-16所示 datediff (间隔因子,d1,d2) 计算d1-d2时间间隔，间隔因子如表7-16所示 datename(间隔因子,d) 返回日期时间d的的名称，如datename(month,’ ’)=03 day(d) 返回日的值 month(d) 返回月的值 year(d) 返回年的值

53 7.4.5 日期和时间函数 时间间隔因子可以使用年月日等表示日期时间的英文全称，也可以使用缩略字母，缩略为字母形式如表7-16所示。
日期和时间函数 时间间隔因子可以使用年月日等表示日期时间的英文全称，也可以使用缩略字母，缩略为字母形式如表7-16所示。 表7-16 间隔因子及其功能 全称 year month day quarter dayofyear weekday week hour minute second milliminute 简称 yyyy|yy m|mm dd|d qq|q dy|y wk|ww dw hh mi|n ss|s ms 说明 年 月 月内日数 季度 年内日数 年内周数 星期几 小时 分钟 秒 毫秒

54 转换函数 一般情况下，SQL Server会自动完成各数据类型之间的转换，有时，自动转换的结果不符合预期结果，这时可考虑利用转换函数进行转换。SQL Server 2000提供了两个转换函数：cast()函数和convert()函数。 1．cast函数 用于将某种数据类型的表达式显式转换为另一种数据类型的数据。 格式：cast(表达式 as 数据类型)

55 转换函数 2．convert函数 也用于将某种数据类型的表达式显式转换为另一种数据类型的数据，convert()函数的优点是可以格式化日期和数值型数据，在将日期时间类型的数据转换为字符类型的数据时，还可以指定转换后的字符样式。 格式：convert(数据类型[(长度)],表达式[,格式码]) 说明： （1）第1个是目标数据类型； （2）第2个是源数据； （3）第3个是可选参数，用于输出字符串格式，适用于日期时间型数据或数值型数据、货币型数据转换为字符型数据，如果格式码为NULL，则返回的结果也为NULL； （4）预定义的符合国际和特殊要求的日期时间输出格式码有30种，如表7-17所示。

56 7.4.6 转换函数 表7-17 convert()函数格式码说明及示例 格式码 年份位数 小时格式 说明 示例 2 12 默认
转换函数 表7-17 convert()函数格式码说明及示例 格式码 年份位数 小时格式 说明 示例 2 12 默认 Apr :05PM 1 美国 04/24/05 ANSI 3 英国/法国 25/04/05 4 德国 5 意大利 6 定制-仅日期 25 Apr 05 7 Apr 25,05 8 24 定制-仅时间 13:05:35 9 默认，毫秒 Apr :05:35:123 PM

57 7.4.6 转换函数 表7-17 convert()函数格式码说明及示例 10 2 美国 04-25-05 11 日本 05/04/25
转换函数 表7-17 convert()函数格式码说明及示例 10 2 美国 11 日本 05/04/25 12 ISO 050425 13 4 24 欧洲 25 Apr :05:35:123 14 定制时间，毫秒 13:05:35:123 100 默认 Apr :05PM 101 04/24/05 102 ANSI 103 英国/法国 25/04/2005 104 德国

58 7.4.6 转换函数 表7-17 convert()函数格式码说明及示例 105 4 意大利 25-04-05 106 定制-仅日期
转换函数 表7-17 convert()函数格式码说明及示例 105 4 意大利 106 定制-仅日期 25Apr2005 107 Apr25，2005 108 24 定制-仅时间 13:05:35 109 12 默认，毫秒 Apr :05:35:123PM 110 美国 111 日本 2005/04/25 112 ISO 113 欧洲 25 Apr :05:35:123 114 定制时间，毫秒 13:05:35:123

59 转换函数 注意：格式代码0，1和2也可用于数值类型，它们对小数与千位分隔符格式产生影响。而不同的数据类型所受的影响是不一样的。一般来说，使用格式代码0(或者不指定这个参数的值)，将返回该数据类型最惯用的格式。使用1或者2通常显示更为详细或者更精确的值。

60 7.4.6 转换函数 【例7-18】 用convert()转换money数据类型数据，查看格式码分别0，1，2的结果。
转换函数 【例7-18】 用convert()转换money数据类型数据，查看格式码分别0，1，2的结果。 money = select 0) select 1) select 2) 返回分别结果如下： 、1,234.56、 【例7-19】 用convert()转换float数据类型数据，查看格式码分别0，1，2的结果。 float = select 0) select 1) select 2) 返回分别结果如下： 、 e+003、 e+003

61 7.4 用户自定义函数 用户自定义函数的名称和源码分别存储在系统表sysobjects和syscomments中，通过系统存储过程sp_help、sp_helptext可以查看用户自定义函数的概要信息和源码信息。用户自定义函数一经定义，就可以像调用内置函数一样来调用。用户自定义函数有3种类型： （1）返回单值的标量函数； （2）类似于视图的可更新内嵌表值函数； （3）使用代码创建结果集的多语句表值函数。

62 标量函数 标量函数是返回单个值的函数。标量函数可以接受0到多个参数进行计算，并返回单个值。标量函数类似于内置函数，可以在SQL Server的表达式中使用该函数。语法格式如下： create function <拥有者.函数名> ( [ [as] 数据类型 [ = 默认值 ] } [ ,...n ] ] ) returns 返回值数据类型 [ with <encryption|schemabinding> [ [,] ...n] ] [ as ] begin 函数语句体 return 返回值表达式 end 说明： （1）拥有者.函数名：函数命名符合标识符命名规则； （3）returns 返回值数据类型：指定返回值的数据类型； （4）with encryption：是指对函数语句进行加密，所定义的文本以加密的形式存储在系统表syscomments中； （5）with schemabinding：指定将函数绑定到它引用的数据库对象，且不能修改（alter）和删除（drop）。 （6）标量函数的函数体语句定义在begin...end语句，其中return 语句是必不可少的，用于返回函数值； （7）可以使用select语句或execute语句调用函数，调用函数时，如不为指定默认值的形参提供实参值（或指定关键字default），则表示直接引用默认值。

63 7.4.1 标量函数 【例7-20】 创建一个用户自定义标量函数fsum，求两个数的和。
标量函数 【例7-20】 创建一个用户自定义标量函数fsum，求两个数的和。 Create function dbo.fsum int=6) Returns int As Begin Return End Go int 2,8 go

64 标量函数 【例7-21】 在jxgl数据库中创建一个用户自定义标量函数fage，然后学生表查询出学生的姓名，姓名，性别和年龄；从教师表中查询出教师的工号，姓名，姓名，性别和工龄。 create function datetime) returns int as begin return end go select 学号,姓名,性别,dbo.fage(出生日期,getdate()) as 年龄 from 学生 select 工号,姓名,性别,dbo.fage(工作日期,getdate()) as 年龄 from 教师

65 内嵌表值函数 内嵌表值函数和视图相似，都包含一条select语句，其查询结果构成了内嵌表值函数的返回值——记录集（表）。内嵌表值函数可以使用参数，也可以不使用参数。语法格式如下： create function <[拥有者.]函数名> ( [ [as] 数据类型 [ = 默认值 ] } [ ,...n ] ] ) returns table [ with <encryption|schemabinding> [ [,] ...n] ] [ as ] return (select 语句) 说明： （1）拥有者.函数名：函数名命名符合标识符命名规则； （3）returns table：内嵌表值函数的返回值是一个表； （4）内嵌表值函数的函数体不使用begin…end语句，而是通过return语句返回select语句查询得到的结果集，其功能相当于一个参数化的视图，即可以当成一个虚表来使用； （5）内嵌表值函数只能通过select语句调用。

66 7.4.2 内嵌表值函数 【例7-22】 创建一个根据学号返回学生学号，姓名，性别，课程号，成绩等信息的函数。
内嵌表值函数 【例7-22】 创建一个根据学号返回学生学号，姓名，性别，课程号，成绩等信息的函数。 create function char(8)=' ') returns table as return ( select 学生.学号,姓名,性别,课程号,成绩 from 学生,选修 where 学生.学号=选修.学号 and go select * from dbo.finfo(default) 运行结果如图7-5所示。

67 多语句表值函数 多语句表值函数可以看作标量函数和内嵌表值函数的联合，它集中了这两个函数的优点。和内嵌表值函数一样，多语句表值函数的返回值也是记录集（表）。区别在于：一是多语句函数主体中的 returns 子句指定的 table 短语带有列名及其数据类型；二是函数返回值是由带子查询（select语句）的insert语句填充。语法格式如下： create function <[拥有者.]函数名> ( [ [as] 数据类型 [ = 默认值 ] } [ ,...n ] ] ) returns table (字段名 数据类型 [,…n]) [ with <encryption|schemabinding> [ [,] ...n] ] [ as ] begin insert select语句 return end 说明：（1）拥有者.函数名：函数名命名符合标识符命名规则； （4）Insert select 语句查询到的数据； （5）函数的返回值也是一个表，但函数体必须在begin…end语句块中定义；

68 7.5.3 多语句表值函数 【例7-23】 创建一个根据课程号查询返回选修该课程的学生学号，姓名，性别，课程号，成绩等信息的函数。
多语句表值函数 【例7-23】 创建一个根据课程号查询返回选修该课程的学生学号，姓名，性别，课程号，成绩等信息的函数。 create function char(2)) table( 课程号 char(2),学号 char(8),姓名 char(6),性别 char(2),成绩 tinyint) as begin select 课程号,选修.学号,姓名,性别,成绩 from 选修,学生 where 选修.学号=学生.学号 and return end go select * from score_info('02')

69 修改函数 修改函数使用alter function语句，修改函数实质是改变现有函数中存储的源代码，因而其格式与创建函数相同。

70 删除函数 删除函数的语法格式：drop function <[拥有者.]函数名>