流程控制与函数解析.docx
- 文档编号:3817861
- 上传时间:2022-11-25
- 格式:DOCX
- 页数:16
- 大小:23.72KB
流程控制与函数解析.docx
《流程控制与函数解析.docx》由会员分享,可在线阅读,更多相关《流程控制与函数解析.docx(16页珍藏版)》请在冰豆网上搜索。
流程控制与函数解析
第7章流程控制与函数
教学目标
通过本章学习,使学生掌握SQLServer主要的流程控制结构与语句,掌握SQLServer2008三类用户自定义函数的特点、定义与用法,并能够编制脚本代码,创建自定义函数,解决实际的应用问题。
教学要求
知识要点
能力要求
关联知识
变量
(1)掌握变量的定义、赋值与应用
DECLARE,SET,SELECT语句
流程控制结构
(1)掌握SQLServer流程控制结构各个相关语句的格式、功能与用法
BEGIN…END,IF…ELSE,CASE,WHILE等语句
自定义函数
(1)掌握自定义函数概念、分类和特点
(2)掌握自定义函数的创建、使用和修改等基本操作方法
CREATEFUNCTION,ALTERFUNCTION等语句
重点难点
变量的概念、定义与应用
各个流程控制语句的格式、功能与用法
自定义函数的定义、创建、使用和修改方法
7.1任务描述
本章完成项目的第7个任务:
在大学生选课管理数据库Student中,创建如下几个函数:
1.创建名称为Age的标量值函数。
2.创建名称为Xscj的内联表值函数。
3.创建名称为Kcxf的多语句表值函数。
7.2程序中的批处理、脚本、注释
当要完成的任务不能由单独的SQL语句来完成时,SQLServer使用批处理、脚本、存储过程、触发器等来组织多条SQL语句。
本章主要介绍批处理、脚本,下一章介绍存储过程、触发器。
7.2.1批处理
批处理是一条或多条SQL语句的集合,这些语句作为一个整体一起提交给SQLServer,SQLServer将一个批处理作为一个整体进行分析、编译和执行。
使用批处理可以节省系统开销,但是如果在一个批处理中包含任何语法错误,则整个批处理就不能被成功地编译和执行。
建立批处理时,使用GO语句作为批处理的结束标记。
GO语句本身不是SQL语句的组成部分,当编译器读取到GO语句时,它会把GO语句前面所有的语句当做一个批处理,并将这些语句打包发送给服务器。
7.2.2脚本
脚本是存储在文件中的一系列SQL语句,即一系列按顺序提交的批处理。
一个脚本文件(.sql文件)中可以包含一个或多个批处理。
使用脚本文件,可以建立起可重复使用的模块化代码,还可以在不同计算机之间传送SQL语句,方便两台计算机执行同样的操作。
7.2.3注释
注释是指程序中用来对程序内容解释说明的语句,编译器在编译程序时会忽略注释语句。
在程序中使用注释是一个程序员良好的编程习惯,使用注释不仅能增强程序的可读性,而且有助于日后的管理和维护。
注释语句的格式:
/*注释内容*/
7.3SQLServer变量
变量是程序语言最基本的角色,用来存放数据。
SQLServer的变量是用来在语句之间传递数据的方式之一。
SQLServer中的变量分为两种,即全局变量和局部变量,其中,全局变量的名称以两个@@字符打头,有系统定义和维护,即系统提供的变量;局部变量的名称以一个@字符打头,由用户自定义和赋值,即用户自定义的变量。
这里主要介绍局部变量。
局部变量是指在批处理或脚本中用来保存单个数据值的对象。
局部变量常用于作为计数器计算循环执行的次数或控制循环执行的次数,也可以用于保存由存储过程代码返回的数据值。
此外,还可以使用Table数据类型的局部变量来代替临时表。
1.定义局部变量
使用一个局部变量之前,必须先定义(声明)这个局部变量。
定义局部变量的语法格式:
DECLARE@变量数据类型[,……n]
例如:
DECLARE@xint,@ychar(10),@zdecimal(4,1)
2.给局部变量赋值(赋值语句)
给局部变量赋值的语法格式:
SET@变量=表达式
除了可以使用SET语句给局部变量赋值外,还可以使用SELECT查询语句给局部变量赋值,即通过在SELECT语句的选择列表中引用一个局部变量而使它获得一个值,语法格式:
SELECT@变量=表达式[,……n]
3.输出变量或表达式的值
输出变量或表达式值的语法格式:
PRINT表达式
除了可以使用PRINT语句输出变量或表达式的值外,还可以使用SELECT语句输出变量或表达式的值,语法格式:
SELECT表达式[,……n]
【例7-1】定义三个局部变量name,borth,score,并给它们赋值,然后输出变量的值。
DECLARE@namechar(10),@borthdate,@scoredecimal(5,1)
SET@name=’孙一然’
SET@borth=’1990-8-23’
SET@score=97.6
SELECT@name姓名,@borth出生日期,@score成绩
GO
【例7-2】定义一个局部变量,把学生信息表中计算机系的学生人数赋给该变量,并输出。
UseStudent
GO
DECLARE@rsint
SELECT@rs=count(*)FromstabWheressx=’计算机系’
PRINT‘计算机系人数:
’+STR(@rs)
GO
7.4程序中的流程控制
流程控制语句是用来控制程序执行和流程分支的命令。
在SQLServer2008中可以使用的流程控制语句主要有BEGIN…END,IF…ELSE,CASE,WHILE等。
使用这些语句,使程序具有结构性和逻辑性,完成较复杂的操作。
7.4.1复合语句
将多个简单的语句组合成一个整体,即形成一个复合语句,语法格式:
BEGIN
语句1
语句2
…
END
7.4.2简单分支语句
简单分支语句的语法格式:
IF条件
语句
或者:
IF条件
语句1
ELSE
语句2
其中,条件就是指一个逻辑表达式。
【例7-3】编写一段代码,要求完成:
在教师信息表中,如果存在职称为副教授或教授的教师,就输出他们的姓名、学历、职称,否则输出“没有此条件的教师”信息。
USEStudent
GO
IFExists(SELECT*FromttabWherezc=’副教授’orzc=’教授’)
BEGIN
PRINT‘具有高级职称的教师如下:
’
SELECTxm姓名,xl学历,zc职称Fromttab
Wherezc=’副教授’orzc=’教授’
END
ELSE
PRINT‘没有此条件的教师’
GO
7.4.3多路分支语句
多路分支语句的语法格式:
CASE
WHEN条件1THEN表达式1
WHEN条件2THEN表达式2
…
WHEN条件nTHEN表达式n
[ELSE表达式n+1]
END
【例7-4】编写一段代码,要求完成:
输出每个选课学生的学号、姓名与所选课的平均成绩,并根据该平均成绩输出其等级。
USEStudent
GO
SELECTxh学号,(SelectxmFromstabWherexh=sctab.xh)姓名,
Avg(cj)平均成绩,
(CASE
WHENAvg(cj)>=90THEN‘优秀’
WHENAvg(cj)>=80THEN‘良好’
WHENAvg(cj)>=70THEN‘中等’
WHENAvg(cj)>=60THEN‘合格’
ELSE‘不合格’
END)成绩等级
Fromsctab
GroupByxh
GO
7.4.4循环语句
循环语句的语法格式:
WHILE循环条件
BEGIN
语句组
END
其中,在循环体(语句组)中还可使用如下两个语句:
CONTINUE:
使程序忽略该CONTINUE语句之后的语句,提前结束本次循环,重新开始下一次循环。
BREAK:
使程序提前退出循环,并将控制权转给该循环语句的后一语句。
【例7-5】编写一段代码,要求完成:
求100之内的偶数之和,并输出。
DECLARE@nint,@sint
SET@n=1
SET@s=0
WHILE@n<=100
BEGIN
IF@n%2=0
SET@s=@s+@n
SET@n=@n+1
END
PRINT@s
GO
7.4.5其他语句
1.暂停语句
语法格式:
WAITFORDELAY’hh:
mm:
ss’
例如:
WAITFORdelay’00:
00:
05’
使程序暂停5秒钟,再执行其下一语句。
2.返回语句
语法格式:
RETURN
功能:
无条件中止查询、存储过程或批处理等,即结束当前运行的程序或存储过程返回。
注意:
当RETURN语句用于存储过程时,格式必须是:
RETURN整形表达式
7.5SQLServer函数
函数在数据库管理和维护中经常被使用,正确地使用函数,可以为用户操作提供很大方便,如查看系统信息、进行数学计算、简化数据查询和前面用到的字符串截取等。
一般情况下,在允许使用变量、字段和表达式的地方都可以使用函数。
在使用函数时,只要提供正确的参数,就可以得到想要的结果。
函数可以由系统提供,也可以由用户创建。
系统提供的函数称为内置函数,它为用户方便快捷地执行某些操作提供帮助。
用户创建的函数称为用户自定义函数,它是用户根据自己的特殊需要而创建的,用来补充和扩展内置函数。
在第三章中已经介绍过常用的内置函数,因此这里主要介绍用户自定义函数。
7.5.1创建自定义函数
在SQLServer中,用户不仅可以使用标准的内置函数,也可以根据自己的特殊需求创建函数。
在SQLServer2008中,用户自定义函数可为三种类型:
标量值函数、内联表值函数和多语句表值函数。
这三类函数都可以使用CREATEFUNCTION语句创建,也可以使用SQLServerManagementStudio创建。
在创建时需要注意:
函数名在数据库中必须是唯一,其可以有参数,也可以没有参数,其参数只能是输入参数,最多可以有1024个参数。
创建自定义函数语法格式:
CREATEFUNCTION函数名([形式参数定义])RETURNS函数返回值数据类型
[AS]
BEGIN
函数体语句组
RETURN函数返回值表达式
END
其中,函数的形式参数定义格式为:
{@形参变量数据类型}[,……n]
1.创建用户自定义标量值函数
用户自定义标量值函数与系统内置标量函数类似,返回在RETURNS子句中定义的类型的单个数据值。
当需要在代码中的多个位置进行相同的数学计算时,用户自定义标量值函数十分有用。
下面通过以下例题来学习用户自定义标量值函数的建立和使用。
【例7-6】在数据库Student中创建一个标量值函数pjcj,该函数通过输入学生的学号判断该学生是否进行选课,若有选修课,则返回其所有选修课程的平均成绩,否则,返回-1。
USEStudent
GO
CREATEFUNCTIONpjcj(@nochar(6))RETURNSdecimal(5,1)
BEGIN
DECLARE@sdecimal(5,1)
IFexists(Select*FromsctabWherexh=@no)
Select@s=Avg(cj)FromsctabWherexh=@no
ELSE
SET@s=-1
RETURN@s
END
GO
在查询编辑器中执行以上代码,创建pjcj函数。
如果使用用户自定义函数,方法同使用系统内置的标量函数类似,只不过要在使用的时候指明函数的所有者。
例如:
利用刚才定义的pjcj函数来查看学号为‘100005’的学生所选修课程的平均成绩。
USEStudent
GO
PRINTdbo.pjcj(‘100005’)
GO
再如:
利用刚才定义的pjcj函数,查询选课平均成绩在75(包含75)分以上的学生学号、姓名和其所选课程平均成绩。
USEStudent
GO
Selectxh学号,xm姓名,dbo.pjcj(xh)选课平均成绩
FromstabWheredbo.pjcj(xh)>=75
GO
【例7-7】在数据库Student中创建一个标量值函数sx,该函数通过输入学生的出生日期来返回该学生的属相。
USEStudent
GO
CREATEFUNCTIONsx(@csrqdate)RETURNSchar(4)
BEGIN
DECLARE@yint,@fchar(4)
SET@y=year(@csrq)
SET@f=(CASE
WHEN@y%12=1THEN‘鸡’
WHEN@y%12=2THEN‘狗’
WHEN@y%12=3THEN‘猪’
WHEN@y%12=4THEN‘鼠’
WHEN@y%12=5THEN‘牛’
WHEN@y%12=6THEN‘虎’
WHEN@y%12=7THEN‘兔’
WHEN@y%12=8THEN‘龙’
WHEN@y%12=9THEN‘蛇’
WHEN@y%12=10THEN‘马’
WHEN@y%12=11THEN‘羊’
WHEN@y%12=0THEN‘猴’
END)
RETURN@f
END
GO
在查询编辑器中执行以上代码,创建sx函数。
例如:
使用该函数sx查询每个学生的学号、姓名、出生日期和属相。
USEStudent
GO
Selectxh学号,xm姓名,csrq出生日期,dbo.sx(csrq)属相
Fromstab
GO
【例7-8】在数据库Student中创建一个标量值函数maxi,该函数返回两个整数的最大数。
USEStudent
GO
CREATEFUNCTIONmaxi(@xint,@yint)RETURNSint
BEGIN
DECLARE@zint
IF@x>@y
SET@z=@x
ELSE
SET@z=@y
RETURN@z
END
GO
2.创建用户自定义内联表值函数
用户自定义内联表值函数返回的结果是表,其表由单个SELECT语句形成。
下面通过以下例题来学习用户自定义内联表值函数的建立和使用。
【例7-9】在数据库Student中创建一个内联表值函数xst,该函数可以根据输入的系部名称返回该系学生的基本信息表。
USEStudent
GO
CREATEFUNCTIONxst(@xmvarchar(20))RETURNStable
AS
RETURN
(Selectxh学号,xm姓名,rxsj入学时间
FromstabWheressx=@xm)
GO
在查询编辑器中执行以上代码,创建内联表值函数xst。
建立好该内联表值函数后,可以像使用表一样来使用它。
例如:
利用刚才定义的内联表值函数xst,查询计算机系学生的基本信息。
USEStudent
GO
Select*Fromdbo.xst(‘计算机系’)
GO
3.创建用户自定义多语句表值函数
和内联表值函数相同,多语句表值函数返回的结果也是表。
如果RETURNS子句指定的TABLE类型带有列及其数据类型,则该函数是多语句表值函数。
多语句表值函数的主体中只允许使用以下语句。
●赋值语句
●控制流程语句
●DECLARE语句
●SELECT语句
●INSERT、UPDATE和DELETE语句
●EXECUTE语句调用扩展存储过程
多语句表值函数需要由BEGIN…END复合语句限定函数体,并且在RETURNS子句中必须定义表的名称变量和表的格式。
下面通过以下例题来学习用户自定义多语句表值函数的建立和使用。
【例7-10】在数据库Student中创建一个多语句表值函数cji,该函数可以根据输入的课程名称返回选修该课程的学生姓名和成绩。
USEStudent
GO
CREATEFUNCTIONcji(@kmvarchar(20))
/*定义函数cji的表结构,表名称变量为cjitab*/
RETURNS@cjitabTABLE
(课程名varchar(20),
姓名varchar(8),
成绩decimal(4,1))
AS
BEGIN
INSERT@cjitab/*上面定义的表名称变量*/
SELECTctab.kcm,stab.xm,sctab.cj
Fromctab,stab,sctab
Wherestab.xh=sctab.xhandctab.kch=sctab.kchandctab.kcm=@km
RETURN
END
GO
在查询编辑器中执行以上代码,创建多语句表值函数cji。
建立好该多语句表值函数后,可以像使用表一样来使用它。
例如:
利用刚才定义的多语句表值函数cji,查询选修‘程序设计’这门课程的学生姓名和成绩。
USEStudent
GO
Select*Fromdbo.cji(‘程序设计’)
GO
7.5.2查看、修改和删除自定义函数
1.查看用户自定义函数的文本信息
语法格式:
sp_helptext用户自定义函数名
【例7-11】查看数据库Student中用户自定义函数pjcj的文本信息。
USEStudent
GO
sp_helptextpjcj
GO
2.修改用户自定义函数
语法格式:
ALTERFUNCTION函数名([形式参数定义])RETURNS函数返回值数据类型
[AS]
BEGIN
函数体语句组
RETURN函数返回值表达式
END
【例7-12】将数据库Student中的用户自定义标量值函数pjcj进行修改,该函数通过输入学生的学号判断该学生是否进行选课,若有选修课,则返回其所有选修课程的门数,否则,返回0。
USEStudent
GO
ALTERFUNCTIONpjcj(@nochar(6))RETURNSint
BEGIN
DECLARE@sint
IFexists(Select*FromsctabWherexh=@no)
Select@s=Count(*)FromsctabWherexh=@no
ELSE
SET@s=0
RETURN@s
END
GO
3.删除用户自定义函数
语法格式:
DROPFUNCTION[所有者.]函数名
【例7-13】将数据库Student中的用户自定义标量值函数pjcj删除。
USEStudent
GO
DropFunctiondbo.pjcj
GO
7.6任务实现
1.创建名称为Age的标量值函数
在数据库Student中创建一个标量值函数Age,该函数能够根据输入学生的出生日期,返回其年龄。
USEStudent
GO
CREATEFUNCTIONAge(@csrqdate)RETURNStinyint
AS
BEGIN
DECLARE@nltinyint
SET@nl=year(getdate())-year(@csrq)
RETURN@nl
END
GO
/*使用该函数Age*/
SELECTxh学号,xm姓名,dbo.Age(csrq)年龄Fromstab
GO
2.创建名称为Xscj的内联表值函数
在数据库Student中创建一个内联表值函数Xscj,该函数可根据输入的课程号返回由选修该课程的学生的学号、姓名、所选课程名和成绩组成的表。
USEStudent
GO
CREATEFUNCTIONXscj(@khchar(3))RETURNStable
AS
RETURN
(SELECTstab.xh学号,stab.xm姓名,ctab.kcm课程名,sctab.cj成绩
Fromstab,ctab,sctab
Wherestab.xh=sctab.xhandctab.kch=sctab.kchandsctab.kch=@kh)
GO
/*使用该函数Xscj*/
Select*Fromdbo.Xscj(‘C3’)
GO
3.创建名称为Kcxf的多语句表值函数
在数据库Student中创建一个多语句表值函数Kcxf,该函数可以根据输入的课程名称返回选修该课程的学生姓名、成绩和成绩等级。
USEStudent
GO
CREATEFUNCTIONKcxf(@kmvarchar(20))
RETURNS@KcxftabTABLE
(课程名varchar(20),
姓名varchar(8),
成绩decimal(4,1),
等级char(8))
AS
BEGIN
INSERT@Kcxftab
SELECTctab.kcm,stab.xm,sctab.cj,
(CASE
WHENsctab.cj>=90THEN‘优秀’
WHENsctab.cj>=80THEN‘良好’
WHENsctab.cj>=70THEN‘中等’
WHENsctab.cj>=60THEN‘合格’
ELSE‘不合格’)
Fromstab,ctab,sctab
Wherestab.xh=sctab.xhandctab.kch=sctab.kchandctab.kcm=@km
RETURN
END
GO
/*使用该函数Kcxf*/
SELECT*Fromdbo.Kcxf(‘高等数学’)
GO
7.7练习题
1.在数据库goods中创建以下几个自定义函数。
(1)在数据库goods中创建一个标量值函数sps,该函数通过输入的客户号判断该客户是否订购商品,若有订购商品,则返回其所订购商品的种数,否则返回0。
(2)在数据库goods中创建一个内联表值函数ksdl,该函数通过输入的商品号返回由客户号、姓名、商品名、单价和订货量组成的表。
(3)在数据库goods中创建一个多语句表值函数spkh,该函数通过输入的商品号返回订购该商品的客户姓名、该商品名、单价和订货量。
2.在数据库books中创建以下几个自定义函数。
(1)在数据库books中创建一个标量值函数age,该函数可根据输入读者的身份证号返回其年龄。
(2)在数据库books中创建一个内联表值函数dzsc,该函数可根据输入的图书号返回借阅该图书未归还的读者号、姓名、图书名和借阅数量组成的表。
(3)在数据库books中创建一个多语句表值函数tsdz,该函数可根据输入的图书号返回借阅该图书未归还的读者号、姓名、借阅时间和借阅数量。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 流程 控制 函数 解析