控制SQL语句语句块或存储过程的执行流程。IF…ELSE语
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
大型数据库——SQL Server 2000程序设计
6.4.1 行集函数
1 . OPENQUERY 行集函数 对连接服务器上的OLE DB数据源执行查询操作，并返 回查询结果集合，可以在查询的 FROM 子句中像引用表 名那样引用 OPENQUERY 函数。 依 据 OLE DB 提 供 程 序 的 能 力 ， 还 可 以 将 OPENQUERY 函 数 引 用 为 INSERT 、 UPDATE 或 DELETE 语句的目标表。尽管查询可能返回多个结果集， 但是OPENQUERY 只返回第一个。 语法格式： OPENQUERY （ 连接服务器名称 ， 查询命令 ）
6.4.2 聚合函数
聚合函数用于计算SELECT语句查询行的统计值，通常 与GROUP BY 语句一起使用，对表进行分组统计。集合 函数不能用于SELECT语句的WHERE子句。
大型数据库——SQL Server 2000程序设计
主要的聚合函数
聚合函数 描述
AVG(expr)
COUNT(*) MAX(expr)
else
values(‘A006’，‘JAVA程序设计’，4，4，3)
思考：现要在学生成绩表中（ XSCJ ），插入某个学生的 print 'A001号课程不存在，请先添加 A001 号课程信息！' 成绩信息（‘02010407’，‘ A006’，80，4），请根据数 else 据完整性要求，设计SQL语句。
标量函数 对单一值操作，返回单一值。
只要表达式有效即可使用标量函数。
大型数据库——SQL Server 2000程序设计
6.4.1 行集函数
从SQL Server 7.0开始，新增了行集函数，他们所返回的 对象可以像表一样被 T-SQL 语句所参照。（这些行集函数 返回对象，该对象可在 Transact-SQL 语句中用作表引用。） 行集函数主要有以下几个:
大型数据库——SQL Server 2000程序设计
6.5.3 用户函数的删除
可以用DROP FUNCTION 语句进行函数的删除。
例：删除自定义函数fn1。 DROP FUNCTION dbo.fn1
用户定义函数用 ALTER FUNCTION 修改
大型数据库——SQL Server 2000程序设计
3. CONTAINSTABLE 行集函数
对表中所有列或指定列进行包含式全文查询。 4. FREETEXTTABLE 行集函数 对表中所有列或指定列进行自由格式全文查询。即在一 个表的所有列或指定列中搜索一个自由文本格式的字符串， 并返回与该字符串匹配的数据行。
大型数据库——SQL Server 2000程序设计
6.4.3 标量函数
标量函数操作某一单一的值，并返回一个单一的值。 只要在能够使用表达式的地方，就可以使用标量函数。 常用的标量函数有： 1.数学函数 2.字符串函数 实现各种数学运算。（详见P127表6.6） 实现字符串的转换、查找等操作。 （详见P127表6.7） 3.系统函数 返回SQL Server服务器和数据库的特殊信 息。 （详见P128表6.8） 4.日期与时间函数 用于处理datetime和smalldatetime 类型的数据。 （详见P128表6.9）
列值的平均值。该列只能包含数字数据。
计算所有行数，包含空值。 列中最大的值（文本数据类型中按字母 顺序）。忽略空值。
COUNT(expr) 计算表达式中非空值的数量。
MIN(expr)
SUM(expr)
列中最小的值（文本数据类型中按字母
顺序）。忽略空值。 列值的合计。该列只能包含数字数据。
大型数据库——SQL Server 2000程序设计
大型数据库——SQL Server 2000程序设计
6.5 用户定义函数
SQL Server支持两种函数类型：
内置函数 按Transact-SQL 参考中定义的方式运行且 不能修改。只有使用Transact-SQL 参考中所定义语法的 Transact-SQL 语句才能引用这类函数。 用户定义函数 用CREATE FUNCTION语句定义自 己的Transact-SQL函数。来补充和扩展系统支持的内置 函数。
用户定义函数接受零个或更多的输入参数，并返回单 值。
大型数据库——SQL Server 2000程序设计
6.5.1 用户函数的定义
利用 CREATE FUNCTION语句创建用户自定义函数， 完整修饰的用户自定义函数名称必须唯一。 语法格式： CREATE FUNCTION 函数名 （参数定义 ） RETURNS 返回值 BEGIN
流程控制语句构成程序块的基本结构，一般用于 存储过程和触发器的定义中，具体应用我们将在存 储过程和触发器的有关章节中介绍。
大型数据库——SQL Server 2000程序设计
6.4 系统内置函数
函数可以帮助用户获取信息、执行数学计算和统计功 能、实现数据的类型转换等等。T-SQL提供了主要三种 类型的函数。 Transact-SQL 编程语言提供三种函数： 行集函数 聚合函数 可以像 SQL 语句中表引用一样使用。 对一组值操作，但返回单一的汇总值。
大型数据库——SQL Server 2000程序设计
6.3 流程控制语句
练习：在课程表（ XSKC ）中，查找课程号为‘ A006’ 的 if exists(select * from xscj where 课程号='A001'and 学号='02010407') 记录，如果有，则显示“此课程已存在”，如果无，则插 print '此学生成绩信息已存在！ ' JAVA 程序设计’， 4 ， 4 ， 入此课程信息（‘ A006’ ，‘ else 3 ）。 If exists(select * from xskc where 课程号=‘A006’)
函数体
RETURN 返回值表达式 END 用户定义函数不能用于执行一组修改全局数据库状态的 操作。
大型数据库——SQL Server 2000程序设计
6.5.1 用户函数的定义
例：下面创建一个用户自定义函数 fn1，把总学分为空 值的改为128。
CREATE FUNCTION fn1 (@inputa tinyint)
大型数据库——SQL Server 2000程序设计
函数分类 解释 •配置函数 返回当前配置信息。 •游标函数 返回游标信息。 •日期和时间函数 对日期和时间输入值执行操作，返回一个字符 串、数字或日期和时间值。 •数学函数 对作为函数参数提供的输入值执行计算，返 回一个数值。 •元数据函数 返回有关数据库和数据库对象的信息。 •安全函数 返回有关用户和角色的信息。 •字符串函数 对字符串（char 或 varchar）输入值执行操 作，返回一个字符串或数字值。 •系统函数 执行操作并返回有关 SQL 系统统计函数返回 系统的统计信息。 •文本和图像函数 对文本或图像输入值或列执行操作，返回有 关这些值的信息。
6.3 流程控制语句
流程控制语句用于控制 SQL语句、语句块或存储过程 的执行流程。 语句块是指由 BEGIN…END 所封装 的多条语句 1.IF…ELSE语句 格式：IF 布尔表达式 {SQL 语句或语句块} ELSE {SQL 语句或语句块} 例 6.35 ： 在 学 生 情 况 表 （ XSQK ） 中 ， 查 找 学 号 为 ‘ 02020112’ 的学生记录，如果有，则显示此记录，如果 无，则显示“此学生不存在！”。 If exists(select * from xsqk where 学号=‘02020112’) select * from xsqk where 学号=‘02020112’ Else print ‘此学生不存在！’
RETURNS tinyint
BEGIN IF @inputa IS NULL SET @ inputa=128 RETURN @inputa
END
大型数据库——SQL Server 2000程序设计
6.5.2 用户函数的调用
与系统函数一样，用户定义函数可以从查询中唤醒 调用。也可以像存储过程一样，通过 EXECUTE 语句 执行。 要调用用户自定义函数，要在调用的时候指明函数 的拥有者和函数的名称。 例：要使用上面定义的函数fn1，可以使用下面语句 进行调用。 SELECT 班级，专业，姓名，dbo.fn1(总学分) AS 总学分，性别 FROM XSQK
例：
大型数据库——SQL Server 2000程序设计
6.4.1 行集函数
2 . OPENROWSET 行集函数 对远程OLE DB数据源执行查询操作。与OPENQUERY 函数相似。语法格式： OPENROWST（字符串，连接参数，查询语句） 例：用 SQL Server 的 Microsoft OLE DB 提供程序访问 pubs数据库中的 authors 表，该数据库在一个名为 seattle1 的远程服务器上。 SELECT a.* FROM
if exists(select * from xsqk where 学号='02010407') print ‘ 此课程已存在！’
if Else exists(select * from xskc where 课程号='A001')
insert into xskc insert into xscj values ('02010407','A001',80,2)
大型数据库——SQL Server 2000程序设计
print '02010407号学生不存在，请先添加02010407号学生信息！'
6.3 流程控制语句
2.无条件转移语句——GOTO语句 格式：GOTO 标号 无条件转移到标号处。 3.WHILE、BREAK和CONTINUE语句
格式：WHILE 布尔表达式
பைடு நூலகம்
begin
set @sum = @sum + @I set @I = @I + 1 end 思考：将1到500的所有奇数累加求和。
大型数据库——SQL Server 2000程序设计
6.3 流程控制语句
4. RETUEN语句 用于使程序从存储过程中无条件返回。 格式：RETURN {[整数表达式]}
OPENQUERY 、 OPENROWSET 、 CONTAINSTABLE 、 FREETEXTTABLE、OPENDATASOURCE、OPENXML
所有行集函数都不具有确定性。每次用一组特定输入值 调用它们时，所返回的结果不总是相同的。有关函数确定 性的更多信息，请参见联机丛书：确定性函数和非确定性 函数。
BEGIN
BREAK ： 使 程 序 从 循 环中跳出； CONTINUE：使程序跳 过循环体内其后续语句， 结束本次循环，执行下 一次循环。
命令行或程序块
[BREAK]
[CONTINUE] END
大型数据库——SQL Server 2000程序设计
6.3 流程控制语句
例6.36：将1到100的整数累加，求和。 declare @I int , @sum int set @I=1 , @sum=0 while @I<=100
OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass', 'SELECT * FROM pubs.dbo.authors
ORDER BY au_lname, au_fname') AS a
大型数据库——SQL Server 2000程序设计
6.4.1 行集函数
6.4.1 行集函数
1 . OPENQUERY 行集函数 对连接服务器上的OLE DB数据源执行查询操作，并返 回查询结果集合，可以在查询的 FROM 子句中像引用表 名那样引用 OPENQUERY 函数。 依 据 OLE DB 提 供 程 序 的 能 力 ， 还 可 以 将 OPENQUERY 函 数 引 用 为 INSERT 、 UPDATE 或 DELETE 语句的目标表。尽管查询可能返回多个结果集， 但是OPENQUERY 只返回第一个。 语法格式： OPENQUERY （ 连接服务器名称 ， 查询命令 ）
6.4.2 聚合函数
聚合函数用于计算SELECT语句查询行的统计值，通常 与GROUP BY 语句一起使用，对表进行分组统计。集合 函数不能用于SELECT语句的WHERE子句。
大型数据库——SQL Server 2000程序设计
主要的聚合函数
聚合函数 描述
AVG(expr)
COUNT(*) MAX(expr)
else
values(‘A006’，‘JAVA程序设计’，4，4，3)
思考：现要在学生成绩表中（ XSCJ ），插入某个学生的 print 'A001号课程不存在，请先添加 A001 号课程信息！' 成绩信息（‘02010407’，‘ A006’，80，4），请根据数 else 据完整性要求，设计SQL语句。
标量函数 对单一值操作，返回单一值。
只要表达式有效即可使用标量函数。
大型数据库——SQL Server 2000程序设计
6.4.1 行集函数
从SQL Server 7.0开始，新增了行集函数，他们所返回的 对象可以像表一样被 T-SQL 语句所参照。（这些行集函数 返回对象，该对象可在 Transact-SQL 语句中用作表引用。） 行集函数主要有以下几个:
大型数据库——SQL Server 2000程序设计
6.5.3 用户函数的删除
可以用DROP FUNCTION 语句进行函数的删除。
例：删除自定义函数fn1。 DROP FUNCTION dbo.fn1
用户定义函数用 ALTER FUNCTION 修改
大型数据库——SQL Server 2000程序设计
3. CONTAINSTABLE 行集函数
对表中所有列或指定列进行包含式全文查询。 4. FREETEXTTABLE 行集函数 对表中所有列或指定列进行自由格式全文查询。即在一 个表的所有列或指定列中搜索一个自由文本格式的字符串， 并返回与该字符串匹配的数据行。
大型数据库——SQL Server 2000程序设计
6.4.3 标量函数
标量函数操作某一单一的值，并返回一个单一的值。 只要在能够使用表达式的地方，就可以使用标量函数。 常用的标量函数有： 1.数学函数 2.字符串函数 实现各种数学运算。（详见P127表6.6） 实现字符串的转换、查找等操作。 （详见P127表6.7） 3.系统函数 返回SQL Server服务器和数据库的特殊信 息。 （详见P128表6.8） 4.日期与时间函数 用于处理datetime和smalldatetime 类型的数据。 （详见P128表6.9）
列值的平均值。该列只能包含数字数据。
计算所有行数，包含空值。 列中最大的值（文本数据类型中按字母 顺序）。忽略空值。
COUNT(expr) 计算表达式中非空值的数量。
MIN(expr)
SUM(expr)
列中最小的值（文本数据类型中按字母
顺序）。忽略空值。 列值的合计。该列只能包含数字数据。
大型数据库——SQL Server 2000程序设计
大型数据库——SQL Server 2000程序设计
6.5 用户定义函数
SQL Server支持两种函数类型：
内置函数 按Transact-SQL 参考中定义的方式运行且 不能修改。只有使用Transact-SQL 参考中所定义语法的 Transact-SQL 语句才能引用这类函数。 用户定义函数 用CREATE FUNCTION语句定义自 己的Transact-SQL函数。来补充和扩展系统支持的内置 函数。
用户定义函数接受零个或更多的输入参数，并返回单 值。
大型数据库——SQL Server 2000程序设计
6.5.1 用户函数的定义
利用 CREATE FUNCTION语句创建用户自定义函数， 完整修饰的用户自定义函数名称必须唯一。 语法格式： CREATE FUNCTION 函数名 （参数定义 ） RETURNS 返回值 BEGIN
流程控制语句构成程序块的基本结构，一般用于 存储过程和触发器的定义中，具体应用我们将在存 储过程和触发器的有关章节中介绍。
大型数据库——SQL Server 2000程序设计
6.4 系统内置函数
函数可以帮助用户获取信息、执行数学计算和统计功 能、实现数据的类型转换等等。T-SQL提供了主要三种 类型的函数。 Transact-SQL 编程语言提供三种函数： 行集函数 聚合函数 可以像 SQL 语句中表引用一样使用。 对一组值操作，但返回单一的汇总值。
大型数据库——SQL Server 2000程序设计
6.3 流程控制语句
练习：在课程表（ XSKC ）中，查找课程号为‘ A006’ 的 if exists(select * from xscj where 课程号='A001'and 学号='02010407') 记录，如果有，则显示“此课程已存在”，如果无，则插 print '此学生成绩信息已存在！ ' JAVA 程序设计’， 4 ， 4 ， 入此课程信息（‘ A006’ ，‘ else 3 ）。 If exists(select * from xskc where 课程号=‘A006’)
函数体
RETURN 返回值表达式 END 用户定义函数不能用于执行一组修改全局数据库状态的 操作。
大型数据库——SQL Server 2000程序设计
6.5.1 用户函数的定义
例：下面创建一个用户自定义函数 fn1，把总学分为空 值的改为128。
CREATE FUNCTION fn1 (@inputa tinyint)
大型数据库——SQL Server 2000程序设计
函数分类 解释 •配置函数 返回当前配置信息。 •游标函数 返回游标信息。 •日期和时间函数 对日期和时间输入值执行操作，返回一个字符 串、数字或日期和时间值。 •数学函数 对作为函数参数提供的输入值执行计算，返 回一个数值。 •元数据函数 返回有关数据库和数据库对象的信息。 •安全函数 返回有关用户和角色的信息。 •字符串函数 对字符串（char 或 varchar）输入值执行操 作，返回一个字符串或数字值。 •系统函数 执行操作并返回有关 SQL 系统统计函数返回 系统的统计信息。 •文本和图像函数 对文本或图像输入值或列执行操作，返回有 关这些值的信息。
6.3 流程控制语句
流程控制语句用于控制 SQL语句、语句块或存储过程 的执行流程。 语句块是指由 BEGIN…END 所封装 的多条语句 1.IF…ELSE语句 格式：IF 布尔表达式 {SQL 语句或语句块} ELSE {SQL 语句或语句块} 例 6.35 ： 在 学 生 情 况 表 （ XSQK ） 中 ， 查 找 学 号 为 ‘ 02020112’ 的学生记录，如果有，则显示此记录，如果 无，则显示“此学生不存在！”。 If exists(select * from xsqk where 学号=‘02020112’) select * from xsqk where 学号=‘02020112’ Else print ‘此学生不存在！’
RETURNS tinyint
BEGIN IF @inputa IS NULL SET @ inputa=128 RETURN @inputa
END
大型数据库——SQL Server 2000程序设计
6.5.2 用户函数的调用
与系统函数一样，用户定义函数可以从查询中唤醒 调用。也可以像存储过程一样，通过 EXECUTE 语句 执行。 要调用用户自定义函数，要在调用的时候指明函数 的拥有者和函数的名称。 例：要使用上面定义的函数fn1，可以使用下面语句 进行调用。 SELECT 班级，专业，姓名，dbo.fn1(总学分) AS 总学分，性别 FROM XSQK
例：
大型数据库——SQL Server 2000程序设计
6.4.1 行集函数
2 . OPENROWSET 行集函数 对远程OLE DB数据源执行查询操作。与OPENQUERY 函数相似。语法格式： OPENROWST（字符串，连接参数，查询语句） 例：用 SQL Server 的 Microsoft OLE DB 提供程序访问 pubs数据库中的 authors 表，该数据库在一个名为 seattle1 的远程服务器上。 SELECT a.* FROM
if exists(select * from xsqk where 学号='02010407') print ‘ 此课程已存在！’
if Else exists(select * from xskc where 课程号='A001')
insert into xskc insert into xscj values ('02010407','A001',80,2)
大型数据库——SQL Server 2000程序设计
print '02010407号学生不存在，请先添加02010407号学生信息！'
6.3 流程控制语句
2.无条件转移语句——GOTO语句 格式：GOTO 标号 无条件转移到标号处。 3.WHILE、BREAK和CONTINUE语句
格式：WHILE 布尔表达式
பைடு நூலகம்
begin
set @sum = @sum + @I set @I = @I + 1 end 思考：将1到500的所有奇数累加求和。
大型数据库——SQL Server 2000程序设计
6.3 流程控制语句
4. RETUEN语句 用于使程序从存储过程中无条件返回。 格式：RETURN {[整数表达式]}
OPENQUERY 、 OPENROWSET 、 CONTAINSTABLE 、 FREETEXTTABLE、OPENDATASOURCE、OPENXML
所有行集函数都不具有确定性。每次用一组特定输入值 调用它们时，所返回的结果不总是相同的。有关函数确定 性的更多信息，请参见联机丛书：确定性函数和非确定性 函数。
BEGIN
BREAK ： 使 程 序 从 循 环中跳出； CONTINUE：使程序跳 过循环体内其后续语句， 结束本次循环，执行下 一次循环。
命令行或程序块
[BREAK]
[CONTINUE] END
大型数据库——SQL Server 2000程序设计
6.3 流程控制语句
例6.36：将1到100的整数累加，求和。 declare @I int , @sum int set @I=1 , @sum=0 while @I<=100
OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass', 'SELECT * FROM pubs.dbo.authors
ORDER BY au_lname, au_fname') AS a
大型数据库——SQL Server 2000程序设计
6.4.1 行集函数