第11章SQL Server的程序设计2-自定义函数

《第11章SQL Server的程序设计2-自定义函数》由会员分享，可在线阅读，更多相关《第11章SQL Server的程序设计2-自定义函数（35页珍藏版）》请在装配图网上搜索。

1、自定义函数自定义函数 SQL Server 2005安徽财贸职业学院 电子信息系教学提示：SQL Server程序设计是读者学习SQL Server数据库的一个重要环节，它对以后程序开发有着直接的决定因素。教学目标：通过本章的学习，读者应该掌握编程的基础知识、基本语句；SQL Server 2005安徽财贸职业学院 电子信息系 为了扩展T-SQL的编程能力，除了提供的内部函数，SQL-Server 2005还允许用户自定义函数。用户可以使用CREATE FUNCTION语句编写自己的函数，以满足特殊需要。可用用户自定义函数来传递0个或多个参数，并返回一个简单的数值。用户自定义函数一般来说返回的

2、都是数值或字符型的数据，如int、char、decimal等，但SQL Server2005也支持返回Table数据类型的数据。SQL Server 2005安徽财贸职业学院 电子信息系 SQL Server 2005安徽财贸职业学院 电子信息系标量函数表值函数多语句表值函数 SQL Server 2005安徽财贸职业学院 电子信息系 SQL Server 2005安徽财贸职业学院 电子信息系标量函数表值函数多语句表值函数 SQL Server 2005安徽财贸职业学院 电子信息系 标量用户自定义函数返回一个简单的数值，如int、char、decimal等其语法格式如下：P231CREATE

3、FUNCTIONowner_name.function_name(parameter_name AS scalar_parameter_data_type=default，n)RETURNS scalar_return_data_typeWITH，nASEEGIN Function_body RETURN scalar_expressionEND SQL Server 2005安徽财贸职业学院 电子信息系1.1.用用CREATE FUNCTION 语句创建语句创建标量函数标量函数返回RETURNS子句中定义的类型、RETURN子句指定数值的单个数据值，如同使用系统内置的函数一样。语法格式：语法

4、格式：GOCREATE FUNCTION 所有者所有者.自定义函数名自定义函数名(参参数数n)RETURNS 返回参数的类型返回参数的类型 AS BEGIN 函数体函数体 RETURN 函数返回的标量值函数返回的标量值END 此类函数在系此类函数在系统对象表中的类统对象表中的类型是：型是：FN SQL Server 2005安徽财贸职业学院 电子信息系 SQL Server 2005安徽财贸职业学院 电子信息系 SQL Server 2005安徽财贸职业学院 电子信息系 SQL Server 2005安徽财贸职业学院 电子信息系 例如：例如：-调用自定义函数 表名前一定要有所有者名称selec

5、t dbo.(110004)SQL Server 2005安徽财贸职业学院 电子信息系【例11.7】创建一个自定义函数，返回特定出版社所出书的平均定价。CREATE FUNCTION Avgdingji_book1(出版社 nchar(20)RETURNS FLOATASBEGINDECLARE 平均定价 FLOATSET 平均定价=(SELECT AVG(定价)FROM book1 WHERE 出版社=出版社)RETURN 平均定价END方法一方法一 SQL Server 2005安徽财贸职业学院 电子信息系【例11.7】创建一个自定义函数，返回特定出版社所出书的平均定价。CREATE FU

6、NCTION Avgdingji_book1(出版社 nchar(20)RETURNS FLOATASBEGINDECLARE 平均定价 FLOAT SELECT 平均定价=AVG(定价)FROM book1 WHERE 出版社=出版社RETURN 平均定价END方法二方法二 SQL Server 2005安徽财贸职业学院 电子信息系【例11.7】创建一个自定义函数，返回特定出版社所出书的平均定价。CREATE FUNCTION Avgdingji_book1(出版社 nchar(20)RETURNS FLOATASBEGIN RETURN(SELECT AVG(定价)FROM book1 W

7、HERE 出版社=出版社)END方法三方法三 SQL Server 2005安徽财贸职业学院 电子信息系【例11.7】创建一个自定义函数，返回特定出版社所出书的平均定价。使用下面语句对刚创建的函数进行调用操作：SELECT dbo.Avgdingji_book1(中国长安)AS 平均成绩 SQL Server 2005安徽财贸职业学院 电子信息系思考设计：1.查询某学号的学生的平均成绩的函数，并调用此函数？2.查询某学号、某课程的成绩？提示：参数几个？返回值是什么？涉及几个表？SQL Server 2005安徽财贸职业学院 电子信息系 表值函数返回一个Table型数据，对直接表值用户定义函数而

8、言，返回的结果只是一系列表值，没有明确的函数体。其语法格式为：P233CREATE FUNCTION owner_name.function_name(parameter_name AS scalar_parameter_data_type=default，.n)RETURNS TABLEWITH，.nASRETURN(select-statement)创建直接表值用户定义函数创建直接表值用户定义函数 SQL Server 2005安徽财贸职业学院 电子信息系内嵌表值函数内嵌表值函数是返回一个Select语句查询结果的表。CREATE FUNCTION 所有者所有者.自定义自定义函数名函数名(

9、参数参数n RETURNS TABLE AS RETURN(SELECT查询语句查询语句)【例】创建一个自定义函数：某班学生信息表输入参数班级名称，返回：表。此类函数在系此类函数在系统对象表中的类统对象表中的类型是：型是：IF SQL Server 2005安徽财贸职业学院 电子信息系Use 简单学生信息管理库goIF EXISTS(SELECT *FROM sysobjects WHERE name=and type=IF )Drop Function SQL Server 2005安徽财贸职业学院 电子信息系200303 SQL Server 2005安徽财贸职业学院 电子信息系【例11.

10、8】创建一个函数返回同一个出版社所出书的部分信息。USE bookGOCREATE FUNCTION f_书的信息(出版社nchar(20)RETURNS TABLEASRETURN(SELECT 书名，定价，出版社FROM book1WHERE 出版社=出版社)创建直接表值用户定义函数创建直接表值用户定义函数 SQL Server 2005安徽财贸职业学院 电子信息系【例11.8】创建一个函数返回同一个出版社所出书的部分信息。使用下面语句对刚创建的函数进行操作：USE bookGOSELECT *FROM dbo.书的信息(湖南美术)GO创建直接表值用户定义函数创建直接表值用户定义函数 SQ

11、L Server 2005安徽财贸职业学院 电子信息系思考设计：1.查询每个民族的学生的统计数表的函数及调用代码？2.查询某个班级的某门课的不及格信息表及调用代码？思考提示：参数几个？返回值是什么？涉及几个表？SQL Server 2005安徽财贸职业学院 电子信息系提问：提问：返回表的函数与视图的区别？内嵌表值函数与视图的比较内嵌表值函数与视图的比较:视图不能接受参数，只能返回结果集；视图不能接受参数，只能返回结果集；而而内嵌表值函数内嵌表值函数可以接受参数，这样可大可以接受参数，这样可大大地提高应用的灵活性。即：一个函数，大地提高应用的灵活性。即：一个函数，通过不同的参数调用，可以完成多个

12、视图通过不同的参数调用，可以完成多个视图显示的结果。显示的结果。SQL Server 2005安徽财贸职业学院 电子信息系 多语句表值用户自定义函数是以BEGIN语句开始，END语句结束的函数体，这些语句可将行插入返回的表中。其语法格式为：P234CREATE FUNCTION owner_name.function_name(parameter_name AS scalar_parameter_data_type=default，.n)RETURNS return_variable TABLE WITH，.nASBEGINFunction_bodyRRTURNEND创建多语句表值用户自定义函

13、数创建多语句表值用户自定义函数 SQL Server 2005安徽财贸职业学院 电子信息系其中：return_variable：指一个TABLE类型的变量用于存储和累积返回的表中的数据行。其余参数与标量型用户自定义函数相同。创建多语句表值用户自定义函数创建多语句表值用户自定义函数 SQL Server 2005安徽财贸职业学院 电子信息系多语句返回表自定义函数多语句返回表自定义函数是返回一个临时创建表，在函数中插入值，返回该表。CREATE FUNCTION 所有者所有者.自定义函数名自定义函数名(参数参数n RETURNS 临时表名变量临时表名变量 TABLE(表中的列表中的列的定义的定义)

14、ASBEGIN INSERT 临时表名变量临时表名变量 与临时表定义列相对应的查询语句与临时表定义列相对应的查询语句 RETURN END此类函数在系统对象表此类函数在系统对象表中的类型是：中的类型是：TF SQL Server 2005安徽财贸职业学院 电子信息系【例11.9】创建一个函数返回定价高于一定价格的书的信息。use bookgoIF EXISTS(SELECT *FROM sysobjects WHERE name=f_money_higher and type=TF )drop function f_money_higher SQL Server 2005安徽财贸职业学院 电子

15、信息系【例11.9】创建一个函数返回定价高于一定价格的书的信息。CREATE FUNCTION f_money_higher(highermoney money)RETURNS money_higher TABLE(编号nvarchar(255),书名 nvarchar(255),定价 money,出版社 nvarchar(255)ASBEGIN INSERT money_higher SELECT 编号,书名,定价,出版社 FROM book1 WHERE 定价 highermoney RETURNEND SQL Server 2005安徽财贸职业学院 电子信息系【例11.9】创建一个函数返

16、回定价高于一定价格的书的信息。使用下面语句对刚创建的函数进行操作：USE bookGOSELECT *FROM dbo.f_money_higher(1000)GO SQL Server 2005安徽财贸职业学院 电子信息系实训准备作业（写实训作业本）：1.阅读理解例题11.7-11.92.自定义函数设计题返回值的函数设计及调用该函数的语句：改写：输入月份，返回该月份的季节，要求使用对月份加以约束（1-12）。输入参数：学号 返回值：姓名（1个表）输入参数：学号，课程名称 返回值：成绩（2个表）SQL Server 2005安徽财贸职业学院 电子信息系返回表的函数设计及调用该函数的语句：输入参数：班级编号返回值：本班学生基本信息（1个表）输入参数：班级编号返回值：本班学生的成绩信息（2个表）3.P256 1）函数的运用 （选做）SQL Server 2005安徽财贸职业学院 电子信息系