《SQL server数据库》课件第7章 流程控制与函数.ppt

1、7.1任务描述：任务描述：本章完成项目的第本章完成项目的第7个任务：在大学生选课管理个任务：在大学生选课管理数据库数据库Student中，创建如下几个函数：中，创建如下几个函数：1创建名称为创建名称为Age的标量值函数。的标量值函数。2创建名称为创建名称为Xscj的内联表值函数。的内联表值函数。3创建名称为创建名称为Kcxf的多语句表值函数。的多语句表值函数。7.2.1批处理批处理 批处理是一条或多条批处理是一条或多条SQL语句的集合，这些语句作为一语句的集合，这些语句作为一个整体一起提交给个整体一起提交给SQL Server，SQL Server将一个批处理将一个批处理作为一个整体进行分析、

2、编译和执行。作为一个整体进行分析、编译和执行。7.2.2脚本脚本 脚本是存储在文件中的一系列脚本是存储在文件中的一系列SQL语句，即一系列按顺语句，即一系列按顺序提交的批处理。序提交的批处理。7.2.3注释注释 注释是指程序中用来对程序内容解释说明的语句，编译注释是指程序中用来对程序内容解释说明的语句，编译器在编译程序时会忽略注释语句。器在编译程序时会忽略注释语句。注释语句的格式：注释语句的格式：/*注释内容注释内容*/7.2 程序中的批处理、脚本、注释程序中的批处理、脚本、注释 7.3 SQL Server 变量变量 1、定义局部变量、定义局部变量 使用一个局部变量之前，必须先定义（声明）这

3、个局部变量。定义局部变量的语法格式：DECLARE 变量 数据类型,n例如：DECLARE x int,y char(10),z decimal(4,1)7.3 SQL Server 变量变量 2、给局部变量赋值（赋值语句）、给局部变量赋值（赋值语句）给局部变量赋值的语法格式：SET 变量=表达式 用SELECT查询语句给局部变量赋值的格式：SELECT 变量=表达式,n7.3 SQL Server 变量变量 3、输出变量或表达式的值、输出变量或表达式的值 输出变量或表达式值的语法格式：PRINT 表达式用SELECT查询语句给输出变量或表达式的值的格式：SELECT 表达式,n7.3 SQL

4、 Server 变量变量 例：定义三个局部变量例：定义三个局部变量name，borth，score，并给它们赋值，然后输出变量的值。并给它们赋值，然后输出变量的值。DECLARE name char(10),borth date DECLARE score decimal(5,1)SET name=孙一然 SET borth=1990-8-23 SET score=97.6 SELECT name 姓名,borth 出生日期,score 成绩 GO7.4 程序中的流程控制程序中的流程控制 复合语句复合语句 简单分支语句简单分支语句 多路分支语句多路分支语句 循环语句循环语句 其他语句其他语句

5、7.4.1 复合语句复合语句 将多个简单的语句组合成一个整体，即形成一个复合语句，语法格式：BEGIN 语句1 语句2 END7.4.2 简单分支语句简单分支语句 简单分支语句的语法格式：IF 条件 语句 或者：IF 条件 语句1 ELSE 语句2 其中，条件就是指一个逻辑表达式。7.4.3 多路分支语句多路分支语句 多路分支语句的语法格式：CASE WHEN 条件1 THEN 表达式1 WHEN 条件2 THEN 表达式2 WHEN 条件n THEN 表达式n ELSE 表达式n+1 END7.4.4 循环语句循环语句 循环语句的语法格式：WHILE 循环条件 BEGIN 语句组 END其中

6、，在循环体中还可使用如下两个语句：CONTINUE：使程序忽略该CONTINUE语句之后的语句，提前结束本次循环，重新开始下一次循环。BREAK：使程序提前退出循环，并将控制权转给该循环语句的后一语句。7.4.5 其他语句其他语句暂停语句的语法格式暂停语句的语法格式：WAITFOR DELAY hh:mm:ss例如：WAITFOR delay 00:00:05 使程序暂停5秒钟，再执行其下一语句。返回语句的语法格式：返回语句的语法格式：RETURN功能：无条件中止查询、存储过程或批处理等，即结束当前运行的程序或存储过程返回。7.5 SQL Server函数函数 函数可以由系统提供，也可以由用户

7、创建。系统提供的函数称为内置函数，它为用户方便快捷地执行某些操作提供帮助。用户创建的函数称为用户自定义函数，它是用户根据自己的特殊需要而创建的，用来补充和扩展内置函数。在第三章中已经介绍过常用的内置函数，因此这里主要介绍用户自定义函数。7.5.1 创建自定义函数创建自定义函数 创建自定义函数语法格式：创建自定义函数语法格式：CREATE FUNCTION 函数名(形式参数定义)RETURNS 函数返回值数据类型 AS BEGIN 函数体语句组 RETURN 函数返回值表达式 END 其中，函数的形式参数定义格式为：形参变量 数据类型,n7.5.1 创建自定义函数创建自定义函数 1、创建用户自定

8、义标量值函数创建用户自定义标量值函数 例：在数据库Student中创建一个标量值函数pjcj，该函数通过输入学生的学号判断该学生是否进行选课，若有选修课，则返回其所有选修课程的平均成绩，否则，返回-1。USE StudentGOCREATE FUNCTION pjcj(no char(6)RETURNS decimal(5,1)BEGIN DECLARE s decimal(5,1)IF exists(Select *From sctab Where xh=no)Select s=Avg(cj)From sctab Where xh=no ELSE SET s=-1 RETURN sENDGO

9、7.5.1 创建自定义函数创建自定义函数 2、创建用户自定义内联表值函数创建用户自定义内联表值函数 例：在数据库Student中创建一个内联表值函数xst，该函数可以根据输入的系部名称返回该系学生的基本信息的表。USE StudentGOCREATE FUNCTION xst(xm varchar(20)RETURNS tableASRETURN(Select xh 学号,xm 姓名,rxsj 入学时间 From stab Where ssx=xm)GO7.5.1 创建自定义函数创建自定义函数 3、创建用户自定义多语句表值函数创建用户自定义多语句表值函数 多语句表值函数的主体中只允许使用以下语

10、句。赋值语句控制流程语句DECLARE语句SELECT语句INSERT、UPDATE和DELETE语句EXECUTE语句调用扩展存储过程 多语句表值函数需要由BEGINEND复合语句限定函数体，并且在RETURNS子句中必须定义表的名称变量和表的格式。7.5.1 创建自定义函数创建自定义函数 3、创建用户自定义多语句表值函数创建用户自定义多语句表值函数 例：在数据库Student中创建一个多语句表值函数cji，该函数可以根据输入的课程名称返回选修该课程的学生姓名和成绩。USE StudentGOCREATE FUNCTION cji(km varchar(20)RETURNS cjitab T

11、ABLE(课程名课程名 varchar(20),姓名姓名 varchar(8),成绩成绩 decimal(4,1)AS BEGIN INSERT cjitab/*上面定义的表名称变量上面定义的表名称变量*/SELECT ctab.kcm ,stab.xm ,sctab.cj From ctab ,stab ,sctab Where stab.xh=sctab.xh and ctab.kch=sctab.kch and ctab.kcm=km RETURN ENDGO1、使用用户自定义函数（、使用用户自定义函数（标量值函数标量值函数）例如：利用上述定义的标量值函数例如：利用上述定义的标量值函数p

12、jcj，查询选课平均成绩在，查询选课平均成绩在75（包含（包含75）分以上的学生学号、姓名和其所选课程平均成）分以上的学生学号、姓名和其所选课程平均成绩。绩。USE StudentGOSelect xh 学号学号,xm 姓名姓名,dbo.pjcj(xh)选课平均成绩选课平均成绩 From stab Where dbo.pjcj(xh)=75GO7.5.2 使用、查看、修改和删除自定义函数使用、查看、修改和删除自定义函数 2、使用用户自定义函数（内联表值函数）、使用用户自定义函数（内联表值函数）例如：利用上述定义的内联表值函数例如：利用上述定义的内联表值函数xst，查询计算，查询计算机系学生的基

13、本信息。机系学生的基本信息。USE StudentGOSelect *From dbo.xst(计算机系计算机系)GO7.5.2 使用、查看、修改和删除自定义函数使用、查看、修改和删除自定义函数 3、使用用户自定义函数（多语句表值函数）、使用用户自定义函数（多语句表值函数）例如：利用上述定义的多语句表值函数例如：利用上述定义的多语句表值函数cji，查询选修，查询选修程序设计程序设计这门课程的学生姓名和成绩。这门课程的学生姓名和成绩。USE StudentGOSelect *From dbo.cji(程序设计程序设计)GO7.5.2 使用、查看、修改和删除自定义函数使用、查看、修改和删除自定义函

14、数 7.5.2 使用、查看、修改和删除自定义函数使用、查看、修改和删除自定义函数 4、查看用户自定义函数的文本信息、查看用户自定义函数的文本信息 语法格式：sp_helptext 用户自定义函数名例：例：查看数据库Student中用户自定义函数pjcj的文本信息。USE StudentGOsp_helptext pjcjGO7.5.2 使用、查看、修改和删除自定义函数使用、查看、修改和删除自定义函数 5、修改用户自定义函数、修改用户自定义函数语法格式：ALTER FUNCTION 函数名(形式参数定义)RETURNS 函数返回值数据类型ASBEGIN 函数体语句组 RETURN 函数返回值表达

15、式END7.5.2 使用、查看、修改和删除自定义函数使用、查看、修改和删除自定义函数 6、删除用户自定义函数、删除用户自定义函数 语法格式：DROP FUNCTION 所有者.函数名 例：例：将数据库Student中的用户自定义标量值函数pjcj删除。USE StudentGODrop Function dbo.pjcjGO 7.6 任务实现任务实现 创建名称为创建名称为Age的标量值函数的标量值函数创建名称为创建名称为Xscj的内联表值函数的内联表值函数创建名称为创建名称为Kcxf的多语句表值函数的多语句表值函数7.6 任务实现任务实现1、创建名称为、创建名称为Age的标量值函数的标量值函数

16、 在数据库在数据库Student中创建一个标量值函数中创建一个标量值函数Age，该函数能够根据输，该函数能够根据输入学生的出生日期，返回其年龄。入学生的出生日期，返回其年龄。USE StudentGOCREATE FUNCTION Age(csrq date)RETURNS tinyintASBEGINDECLARE nl tinyintSET nl=year(getdate()-year(csrq)RETURN nlENDGO/*使用该函数使用该函数Age*/SELECT xh 学号学号,xm 姓名姓名,dbo.Age(csrq)年龄年龄 From stabGO7.6 任务实现任务实现2、创

17、建名称为、创建名称为Xscj的内联表值函数的内联表值函数 在数据库在数据库Student中创建一个内联表值函数中创建一个内联表值函数Xscj，该函数可根据输，该函数可根据输入的课程号返回由学生的学号、姓名、所选该课的课程名和成绩组成的入的课程号返回由学生的学号、姓名、所选该课的课程名和成绩组成的表表。USE StudentGOCREATE FUNCTION Xscj(kh char(3)RETURNS tableASRETURN(SELECT stab.xh 学号学号,stab.xm 姓名姓名,ctab.kcm 课程名课程名,sctab.cj 成绩成绩 From stab ,ctab ,sct

18、ab Where stab.xh=sctab.xh and ctab.kch=sctab.kch and sctab.kch=kh)GO/*使用该函数使用该函数Xscj*/Select *From dbo.Xscj(C3)GO7.6 任务实现任务实现3、创建名称为、创建名称为Kcxf的多语句表值函数的多语句表值函数 在数据库Student中创建一个多语句表值函数Kcxf，该函数可以根据输入的课程名称返回选修该课程的学生姓名、成绩和成绩等级。USE StudentGOCREATE FUNCTION Kcxf(km varchar(20)RETURNS Kcxftab TABLE (课程名课程名

19、varchar(20),姓名姓名 varchar(8),成绩成绩 decimal(4,1),等级等级 char(8)ASBEGIN INSERT Kcxftab SELECT ctab.kcm ,stab.xm ,sctab.cj ,(CASE WHEN sctab.cj=90 THEN 优秀优秀 WHEN sctab.cj=80 THEN 良好良好 WHEN sctab.cj=70 THEN 中等中等 WHEN sctab.cj=60 THEN 合格合格 ELSE 不合格不合格)From stab ,ctab ,sctab Where stab.xh=sctab.xh and ctab.kch=sctab.kch and ctab.kcm=km RETURNEND GO/*使用该函数使用该函数*/SELECT *FROM dbo.kcxf(高等数学高等数学)GO