SQL第10章函数省名师优质课赛课获奖课件市赛课一等奖课件.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,本资料仅供参考，不能作为科学依据。谢谢,Sql server,程序设计与管理基础,Sql server,程序设计与管理基础,第10章 函数,第1页,1,第,10,章 函数,10.1,惯用系统函数,10.2,用户自定义函数,第2页,2,10.1,惯用系统函数,函数是由一条或多条Sql 语句组成代码段 ，其,形式为函数名（），普通来讲括号里面包含参数，它,能够接收参数值，处理逻辑，然后返回一些数据。,Sql 提供了大量内置参数，如：数学函,数、字符串函数、日期时间函数、聚合函数等，这些,函数用于数学计算、类型转换、日期时间格式化、系,统统计等，除了系统提供函数外，还允许用户创建,自定义函数，以实现用户需求。,第3页,3,10.1,惯用系统函数,1 数学函数,1)ceiling(数值型表示式),2)floor(数值型表示式),3)rand(整型表示式),4)round(数值型表示式，长度),第4页,4,10.1,惯用系统函数,2 日期和时间函数,1）getdate(),2）dateadd(datepart,number,date),3）datediff(datepart,startdate,enddate),4）datename(datepart,date),5）datepart(datepart,date),6）year(date),7）month(date),8）day(date),第5页,5,10.1,惯用系统函数,例1：查看今天年月日，并以格式化形式显示。,命令以下：,SELECT 今天是+datename(YY,getdate()+年+,datename(MM,getdate()+,月,+datename(dd,getdate()+,日,第6页,6,10.1,惯用系统函数,3 字符串函数,1）len(字符串表示式):,2）char(整型表示式),3）Ltrim(字符串表示式),4）left(字符串表示式 整型表示式),5）lower(字符串表示式),6）Str(float型表示式,length,decimal),第7页,7,10.1,惯用系统函数,4 数据类型转换函数,当要对不一样类型数据进行运算时，就必须将其,转换成相同数据类型才能进行运算。在SQL Server,里提供了两个函数能够进行数据类型转换，其中一,个是CAST，另外一个是CONVERT。,(1)CAST函数语法格式为：,CAST（expression AS data_type(length)）,(2)CONVERT函数语法格式为：,CONVERT（data_type(length)，expression,style）,第8页,8,10.1,惯用系统函数,例2：查看northwind数据库products表中产品及单价，并在一列中显示出来。,代码以下：,SELECT productname+单价为：,+CAST(unitprice as varchar(10)+元 as 产品介绍,FROM products,第9页,9,第,10,章 函数,10.1,惯用系统函数,10.2,用户自定义函数,第10页,10,10.2,用户自定义函数,10.2.1,标量函数,10.2.2,内嵌表值函数,10.2.3,多语句表值函数,10.2.4 函数调用语法格式,10.2.5,查看用户自定义函数,10.2.6,删除用户自定义函数,第11页,11,10.2.1,标量函数,SQL Server 允许用户设计自己函数，以补充和扩展系统提供（内置）函数功效,用户定义函数采取零或多个输入参数并返回标量值或表,SQL Server 支持三种用户定义函数：,标量函数,多语句表值函数,内嵌表值函数,第12页,12,10.2.1,标量函数,标量函数,标量函数返回,一个标量（,单值,）结果,可在与标量函数返回数据类型相同值所能使用任何位置使用该标量函数，包含,SELECT 语句,中列列表和,WHERE 子句,、表示式、表定义中,约束,表示式，甚至作为表中列数据类型,第13页,13,10.2.1,标量函数,标量函数,T-SQL,语句格式:,CREATE FUNCTION,函数名,(形式参数 数据类型,n),Returns,返回值数据类型,BEGIN,函数体,RETURN,标量表示式,END,第14页,14,10.2.1,标量函数,标量函数返回 RETURNS 子句中定义数据类型单个数据值,在 BEGIN END 块之间定义了函数体，包含返回值一系列 Transact-SQL 语句,返回值能够是除了 text、ntext、image、cursor、table或 timestamp 之外任何数据类型,函数调用语法格式为：,拥有者名.函数名(参数列表),第15页,15,10.2.1,标量函数,例,3,:在学生数据库中创建一个用户自定义标量函数wujifen，,该函数经过输入成绩来计算学生成绩五级分。,Use student,Go,Create function wujifen（zz int）,returns nvarchar（6）,as,Begin,Declare returnstr nvarchar（6）,If zz=60 and zz=70 and izz=80 and zz=90 and zz100,Set returnstr=优异,Else,Set returnstr=不及格,Return returnstr,End,第16页,16,10.2.1,标量函数,调用该标量函数试语句：,SELECT kid,sscore,dbo.wujifen(sscore)AS 五级分制,FROM score,WHERE sid=0691B002,GO,第17页,17,10.2.1,标量函数,例4：在学生数据库中创建一个用户自定义标量函数AGE，该函数经过输入出生年月返回年纪。,实现语句：,USE student,GO,CREATE FUNCTION AGE（birth datetime）,RETURNS tinyint,AS,BEGIN,RETURN year（getdate（）-year（birth）,END,第18页,18,10.2.1,标量函数,调用该标量函数测试语句：,SELECT 姓名,dbo.age(出生年月),FROM stud_info,GO,第19页,19,10.2,用户自定义函数,10.2.1,标量函数,10.2.2,内嵌表值函数,10.2.3,多语句表值函数,10.2.4 函数调用语法格式,10.2.5,查看用户自定义函数,10.2.6,删除用户自定义函数,第20页,20,10.2.2,内嵌表值函数,内嵌表值函数,内嵌表值函数,返回一个单条 SELECT 语句产生结果表,，类似于视图,相对于视图，,内嵌表值函数可使用参数,，提供了更强适应性，扩展了索引视图功效,函数调用语法格式为：,拥有者名.函数名(参数列表),第21页,21,10.2.2,内嵌表值函数,内嵌表值函数格式,CREATE FUNCTION,函数名,(形式参数 数据类型,n),Returns table,AS,RETURN,（,select,函数体）,说明：,RETURNS 子句仅包含关键字 table,BEGIN 和END 不包含在函数体中,RETURNS 子句在括号汇总包含单个SELECT语句，其 结果集组成其返回表。,第22页,22,10.2.2,内嵌表值函数,例5：在学生数据库中建立一个内嵌表值函数xuesheng，该函数经过输入班级代码返回全部该班学生信息。,实现T-SQL语句：,CREATE function xuesheng(bjdm char(8),RETURNS TABLE,AS,RETURN(SELECT*FROM stud_info WHERE,所属班级,=bjdm),GO,第23页,23,10.2.2,内嵌表值函数,调用该标量函数测试语句：,SELECT*FROM dbo.xuesheng(rj0621),GO,第24页,24,10.2.2,内嵌表值函数,例6：在学生数据库中创建一个内嵌表值函数XSCJ，该函数经过输入课程号kid返回全部选修此课程学生学号、姓名、课程名和成绩。,实现T-SQL语句：,USE student,GO,CREATE FUNCTION XSCJ(kch char(10),RETURNS TABLE,AS,RETURN(SELECT A.学号,A.姓名,C.k_name 课程名,B.sscore 成绩,FROM stud_info AS A JOIN score AS B,ON A.学号=B.sid,JOIN course AS C,on B.kid=C.kid,WHERE C.kid=kch),GO,第25页,25,10.2.2,内嵌表值函数,调用该标量函数测试语句：,SELECT*FROM dbo.XSCJ(0901002),GO,第26页,26,10.2,用户自定义函数,10.2.1,标量函数,10.2.2,内嵌表值函数,10.2.3,多语句表值函数,10.2.4 函数调用语法格式,10.2.5,查看用户自定义函数,10.2.6,删除用户自定义函数,第27页,27,10.2.3,多语句表值函数,1、多语句表值函数,多语句表值函数返回,一个由一条或多条 Transact-SQL 语句建立,表,，类似于存放过程,与存放过程不一样是，多语句表值函数能够在 SELECT 语句,FROM 子句中被引用,，好像视图一样,2、,使用表值用户定义函数,拥有者名.函数名(参数列表),第28页,28,10.2.3,多语句表值函数,多语句表值函数格式,CREATE FUNCTION,函数名,(形式参数 数据类型,n),Returns,表名,table,(表结构),AS,BEGIN,函数体,RETURN,END,第29页,29,10.2.3,多语句表值函数,说明：,BEGIN 和 END 分隔了函数体,RETURNS 子句指定 table 作为返回数据类型,RETURNS 子句定义了返回表名字和格式。返回变量名使用域限定于函数局部,第30页,30,10.2.3,多语句表值函数,例7：在学生数据库中建立一个多语句表值函数chengji，该函数经过输入课程名称返回选修该课程学生姓名与成绩。,实现T-SQL语句：,USE student,GO,CREATE FUNCTION chengji(kcmc AS varchar(20),RETURNS cj TABLE,(,课程名varchar(20),姓名 char(8),成绩 tinyint,),第31页,31,10.2.3,多语句表值函数,AS,BEGIN,INSERT cj,SELECT c.k_name 课程名,s.姓名,k.sscore 成绩,FROM stud_info AS s,JOIN score AS k ON s.学号=k.sid,JOIN course AS c ON c.kid=k.kid,WHERE c.k_name=kcmc,RETURN,END,GO,调用该标量函数测试语句：,SELECT*FROM dbo.Chengji(操作系统原理),GO,第32页,32,10.2.3,多语句表值函数,例8：在学生数据库中建立一个多语句表值函数KCXF，该函数经过输入课程名称返回选修该课程学生姓名、成绩与五级分。,实现T-SQL语句：,USE student,GO,CREATE FUNCTION KCXF(kcmc AS varchar(20),RETURNS cj TABLE,(,课程名varchar(20),姓名 char(8),成绩 tinyint,五级分char(20),),第33页,33,10.2.3,多语句表值函数,AS,BEGIN,INSERT cj,SELECT c.k_name,s.姓名,k.sscore,dbo.wujifen(sscore),FROM stud_info AS s JOIN score AS k,ON s.学号=k.sid JOIN course AS c ON c.kid=k.kid,WHERE c.k_name=kcmc,RETURN,END,GO,调用该标量函数测试语句：,SELECT*FROM dbo.KCXF(数据库),GO,第34页,34,10.2,用户自定义函数,10.2.1,标量函数,10.2.2,内嵌表值函数,10.2.3,多语句表值函数,10.2.4 函数调用语法格式,10.2.5,查看用户自定义函数,10.2.6,删除用户自定义函数,第35页,35,10.2.4 函数调用语法格式,函数调用语法格式：,拥有者名.函数名(参数列表),注意：在调用时，拥有者名必须要写，不能省略,第36页,36,10.2,用户自定义函数,10.2.1,标量函数,10.2.2,内嵌表值函数,10.2.3,多语句表值函数,10.2.4 函数调用语法格式,10.2.5,查看用户自定义函数,10.2.6,删除用户自定义函数,第37页,37,10.2.5,查看用户自定义函数,1 使用系统存放过程查看用户自定义函数,Sp_helptext 函数名称,2 使用sp_help查看用户自定义函数,Sp_help 函数名称,第38页,38,10.2,用户自定义函数,10.2.1,标量函数,10.2.2,内嵌表值函数,10.2.3,多语句表值函数,10.2.4 函数调用语法格式,10.2.5,查看用户自定义函数,10.2.6,删除用户自定义函数,第39页,39,10.2.6,更改和删除用户定义函数,更改函数,语法：,ALTER FUNCTION,使用新函数定义代替原来函数定义,保留全部已分配权限,删除函数,语法：,DROP FUNCTION,删除一个用户定义函数,第40页,40,小结,掌握,1、掌握标量函数创建和应用,2、掌握内嵌表值函数创建和应用,3、掌握多语句表值函数创建和应用,了解,1、了解系统惯用内置函数,2、了解创建不一样用户自定义函数需要注意事项,第41页,41,谢谢！,第42页,42,