【文档说明】大型数据库技术第11章用户自定义函数课件.ppt，共(45)页，200.002 KB，由小橙橙上传

转载请保留链接：https://www.ichengzhen.cn/view-92310.html

以下为本文档部分文字说明：

第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»1第11章用户自定义函数设计11.1用户自定义函数概述11.2创建用户定义函数11.3用户定义函数的调用11.4修改和删除用户定义函数11.5用户定义函数实例分析第11章用户

自定义函数大型数据库SQLServer2005»2022/12/5»211.1用户自定义函数概述11.1.1用户自定义函数的特点1、重复使用编程代码，减少编程开发时间，提高工作效率。2、隐藏SQL细节，把SQL繁琐的工作留给数据库开发人员，而程序开发员则集中处理高级编程语言。3、维修集中化，

可以在一个地方做业务上的逻辑修改，然后让这些修改自动应用到所有相关程序中。4、可在另一个SQL语句中直接调用。5、函数必须始终返回一个值(一个标量值或一个表格)第11章用户自定义函数大型数据库SQLServer2005

»2022/12/5»311.1用户自定义函数概述除了使用系统提供的函数外，用户还可以根据需要自定义函数。用户自定义函数（UserDefinedFunctions）是SQLServer2000以后新增的数据库对象，是SQLServer的一大改进。用户自定义函数不能用

于执行一系列改变数据库状态的操作，但它可以像系统函数一样在查询或存储过程等的程序段中使用，也可以像存储过程一样通过EXECUTE命令来执行。用户自定义函数中存储了一个Transact-SQL例程，可以返回一定的值

。第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»4可使用CREATEFUNCTION语句创建、使用ALTERFUNCTION语句修改、以及使用DROPFUNCTION语句除去用户定义函数。每个完全合法的用户定义函数名必须唯一。必

须被授予CREATEFUNCTION权限才能创建、修改或除去用户定义函数。不是所有者的用户在Transact-SQL语句中使用某个函数之前，必须先给此用户授予该函数的适当权限。若要创建或更改在CHECK约束、DEFAULT子句或计算列定义中引用用户定义函数的表，还必须具有函数的REFERE

NCES权限。第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»5在SQLServer2000中根据函数返回值形式的不同将用户自定义函数分为三种类型：标量型函数（Scalarfunctions）内嵌表值型函数（Inlinetab

le-valuedfunctions）多语句表值型函数（Multi-statementtable-valuedfunctions）。第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»6标量型

函数（Scalarfunctions）标量型函数返回在RETURNS子句中定义的类型的单个数据值。可以使用所有标量数据类型，包括bigint和sql_variant。不支持timestamp数据类型、用户定义数据类型和非标量类型（如table或c

ursor）。在BEGIN...END块中定义的函数主体包含返回该值的Transact-SQL语句系列。返回类型可以是除text、ntext、image、cursor、timestamp和table之外的任何数据类型。第11章

用户自定义函数大型数据库SQLServer2005»2022/12/5»7内嵌表值型函数（Inlinetable-valuedfunctions）内嵌表值型函数以表的形式返回一个返回值，即它返回的是一个表。内嵌表值型函数没有

由BEGIN-END语句括起来的函数体。其返回的表由一个位于RETURN子句中的SELECT命令段从数据库中筛选出来。内联表值型函数功能相当于一个参数化的视图。第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»8多语句表值型函数（

Multi-statementtable-valuedfunctions）多语句表值型函数可以看作标量型和内嵌表值型函数的结合体。它的返回值是一个表，但它和标量型函数一样有一个用BEGIN-END语句括起来的函数体，返回值的表中的数据是由函数体中的语句插入的。由此可见，它可以进行多次查询，

对数据进行多次筛选与合并，弥补了内嵌表值型函数的不足。用户定义函数采用零个或更多的输入参数并返回标量值或表。函数最多可以有1024个输入参数。当函数的参数有默认值时，调用该函数时必须指定默认DEFAULT关键字才能获取默认值。该

行为不同于在存储过程中含有默认值的参数，而在这些存储过程中省略该函数也意味着省略默认值。用户定义函数不支持输出参数。第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»9函数中的有效语句类型包括：（1）DECLARE语句

，该语句可用于定义函数局部的数据变量和游标。（2）为函数局部对象赋值，如使用SET给标量和表局部变量赋值。（3）游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。不允许使用FETCH语句将数据返回到客户端。仅允许使用FETCH语句通过

INTO子句给局部变量赋值。（4）控制流语句。（5）SELECT语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。（6）INSERT、UPDATE和DELETE语句，这些语句修改函数的局部table变量。（7）EXECUTE语句，

该语句调用扩展存储过程。第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»1011.2创建用户定义函数创建标量型用户自定义函数（Scalarfunctions）：CREATEF

UNCTION[owner_name.]function_name([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])RETURNSscal

ar_return_data_type[WITH<function_option>[[,]...n]]ASBEGINfunction_bodyRETURNscalar_expressionEND第11章用户自定义函数大

型数据库SQLServer2005»2022/12/5»11创建内嵌表值型函数（Inlinetable-valuedfunctions）：CREATEFUNCTION[owner_name.]function_name([{@parameter_name[AS]scalar_para

meter_data_type[=default]}[,...n]])RETURNSTABLE[WITH<function_option>[[,]...n]]ASRETURN[()select-stmt[]]第11章用户自定义函数大型数据库SQ

LServer2005»2022/12/5»12创建多语句表值型函数（Multi-statementtable-valuedfunctions）：CREATEFUNCTION[owner_name.]function_name([{@parameter_name[AS]scalar_paramet

er_data_type[=default]}[,...n]])RETURNS@return_variableTABLE<table_type_definition>[WITH<function_option>[[,]...n]]ASBEGINfunction_bodyRETU

RNEND<function_option>::={ENCRYPTION|SCHEMABINDING}<table_type_definition>::=({column_definition|table_constraint}[,...n])第11章用户自定义函数大型数据库SQLServer2

005»2022/12/5»13参数说明：owner_name：拥有该用户定义函数的用户ID的名称。function_name：用户定义函数的名称。函数名称必须符合标识符的规则，对其所有者来说，该名称在数据库中必须是

唯一的。第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»14参数说明：@parameter_name：用户定义函数的参数。CREATEFUNCTION语句中可以声明一个或多个参数。函数最多可以有1024个参数。函数执行时每个已声明参数的值必须由用户指定，除非该

参数的默认值已经定义。如果函数的参数有默认值，在调用该函数时必须指定“default”关键字才能获得默认值。这种行为不同于存储过程中有默认值的参数，在存储过程中省略参数也意味着使用默认值。使用@符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个函数的参数仅用于该函数本

身；相同的参数名称可以用在其它函数中。参数只能代替常量；而不能用于代替表名、列名或其它数据库对象的名称。第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»15scalar

_parameter_data_type：参数的数据类型。所有标量数据类型（包括bigint和sql_variant）都可用作用户定义函数的参数。不支持timestamp数据类型和用户定义数据类型。不能

指定非标量类型（例如cursor和table）。scalar_return_data_type：是标量用户定义函数的返回值。scalar_return_data_type可以是SQLServer支持的任何标量

数据类型（text、ntext、image和timestamp除外）。scalar_expression：指定标量型函数返回的标量值。TABLE：指定表值型函数的返回值为表。在内嵌表值型函数中，通过单个SELECT语句定义TABLE返回值。内嵌函数没有

相关联的返回变量。在多语句表值型函数中，@return_variable是TABLE变量，用于存储和累积应作为函数值返回的行。参数说明：第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»16func

tion_body：指定一系列Transact-SQL语句定义函数的值，这些语句合在一起不会产生副作用。function_body只用于标量型函数和多语句表值型函数。在标量型函数中，function_body是一系列合起来求得标量值的Transact

-SQL语句。在多语句表值型函数中，function_body是一系列填充表返回变量的Transact-SQL语句。select-stmt：是定义内嵌表值型函数返回值的单个SELECT语句。ENCRYPTION指出SQL

Server加密包含CREATEFUNCTION语句文本的系统表列。使用ENCRYPTION可以避免将函数作为SQLServer复制的一部分发布。SCHEMBINDING：指定将函数绑定到它所引用的数据库对象。如果函数是用SCHEMABINDING选项创建的

，则不能更改（使用ALTER语句）或除去（使用DROP语句）该函数引用的数据库对象。参数说明：第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»1711.2创建用户定义函数11.2.1使用对象资源管理器第11章用户自定义函数大型数据库SQLServer

2005»2022/12/5»1811.2创建用户定义函数11.2.1使用CREATEFUNCTION命令创建用户自定义函数1、创建标量值用户自定义函数（Scalarfunctions）创建成绩转换标量值函数，实现百分制成绩与优、良、中、及格、不及格五个等级的换算。USE教学管理GOCREAT

EFUNCTIONF_成绩分级(@成绩FLOAT)RETURNSCHAR(16)AS第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»1911.2创建用户定义函数BEGINDECLARE@等级CHAR(16)SELECT

@等级=CASEWHEN@成绩ISNULLTHEN'还没参加考试'WHEN@成绩<60THEN'不及格'WHEN@成绩>=60and@成绩<70THEN'及格'WHEN@成绩>=70and@成绩<80THEN'中等'WHEN@成绩>=80and@成绩<90T

HEN'良好'ELSE'优秀!'ENDRETURN(@等级)END第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»2011.2创建用户定义函数创建内联表值型函数，返回指定学院学生的信息USE教学管理GO

CREATEFUNCTIONF_学生信息(@院系CHAR(20))RETURNSTABLEASRETURN(SELECT学号,姓名,性别,院系,专业,籍贯FROM学生表WHERE所在院系=@院系)第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»211

1.2创建用户定义函数创建多语句表值型函数，返回指定教师某学年的开课信息USE教学管理GOCREATEFUNCTIONF_教师课表(@教师姓名CHAR(8),@开课学年char(9))RETURNS@教师课表TABLE(课名

varchar(30),开课地点char(6),开课学年char(9),开课学期int,开课周数int,开课时间varchar(20),已选人数int)第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»22ASBEGININSERT@教师课表SELECT课名,开课地

点,开课学年,开课学期,开课周数,开课时间,已选人数FROM教师表T,开课表O,课程表CWHERET.工号=O.工号ANDO.课号=C.课号AND姓名=@教师姓名AND开课学年=@开课学年RETURNEND第11章用户自定义函数大型数据库SQLServer2005

»2022/12/5»2311.3用户定义函数的调用当引用或唤醒调用用户定义函数时，应指定函数名。而在括号内可指定称为参数的表达式，以提供将传递给参数的数据。当唤醒调用函数时不能在参数中指定参数名。当唤醒调用函数时，必须提供所有参数的参数值，并且必须以CREATEFUNCTION语句定

义参数的相同序列指定参数值。例如，如果使用整型参数和nchar(20)参数定义名为fn_MyIntFunc的函数以返回整数，可利用以下方法唤醒调用该函数：第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»2411.3用户定义函数的调用调用创建的函

数，按五等成绩显示信电学院每个学生所选课程的成绩SELECTE.学号,姓名,O.开课号,O.课号,课名,DBO.F_成绩分级(成绩)AS'成绩等级'FROM选课表E,学生表S,开课表O,课程表CWHERES.学号=E.学号ANDE.开课号=O.开课号ANDC.课号=O.课号第11

章用户自定义函数大型数据库SQLServer2005»2022/12/5»2511.3用户定义函数的调用调用创建的函数F_学生信息，返回指定学院的学生的基本信息--下面调用该函数DECLARE@院系CHAR(20)SET@院系='信电学院'SELECT*FROMF_学生信息(@院系)第11章

用户自定义函数大型数据库SQLServer2005»2022/12/5»2611.3用户定义函数的调用调用创建的函数F_教师课表，能返回指定老师某学年所上的课程信息。--下面调用该函数DECLARE@姓名CHAR(10)SET@姓

名='黄中天'SELECT*FROMF_教师课表(@姓名,'2007-2008')第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»27函数的好处：由上面例子可以看出，用户自定义函数可以用来提供参数化的用户视

图，比直接定义用户视图具有更大的灵活性。第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»2811.4修改和删除用户定义函数11.4.1修改用户定义函数11.4.2删除用户定义函数第11章用户自定义函数大型数据库SQLServer2005»2022/

12/5»2911.4.1修改用户定义函数用ALTERFUNCTION命令可以修改先前由CREATEFUNCTION语句创建的现有用户定义函数，但不会更改权限，也不影响相关的函数、存储过程或触发器。第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»

30修改标量型用户自定义函数：ALTERFUNCTION[owner_name.]function_name([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])RETURNSscalar_return_data_

type[WITH<function_option>[[,]...n]][AS]BEGINfunction_bodyRETURNscalar_expressionEND第11章用户自定义函数大型数据库SQLServer2

005»2022/12/5»31修改内嵌表值型函数：CREATEFUNCTION[owner_name.]function_name([{@parameter_name[AS]scalar_parameter_data_type[=de

fault]}[,...n]])RETURNSTABLE[WITH<function_option>[[,]...n]][AS]RETURN[(]select-stmt[)]第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»32创建

多语句表值型函数：CREATEFUNCTION[owner_name.]function_name([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])RETURNS@return_variabl

eTABLE<table_type_definition>[WITH<function_option>[[,]...n]][AS]BEGINfunction_bodyRETURNEND<function_option>::={ENCRYPTION|SCHEMABINDIN

G}<table_type_definition>::=({column_definition|table_constraint}[,...n])第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»33修改命令的语法与CREATEFUNCTION相同，各参数含义

见创建用户自定义函数一节。使用ALTERFUNCTION命令其实相当于重建了一个同名的函数。注释不能用ALTERFUNCTION将标量值函数更改为表值型函数，反之亦然。同样地，也不能用ALTERFUNCTION将行内函数更改为多语句函数，反之亦然。权限ALTERFUNCTION权限默

认授予sysadmin固定服务器角色成员、db_owner和db_ddladmin固定数据库角色成员和函数的所有者且不可转让。函数的所有者对其函数具有EXECUTE权限。不过，也可将此权限授予其他用户。第11章用户自定义函数大型数据库SQ

LServer2005»2022/12/5»34创建一个内联表值型函数，返回指定学生所选的课程号、课程名、成绩，然后修改。USE教学管理GOCREATEFUNCTIONF_学生选课信息(@学号CHAR(7))RETURNSTABLEASRETURN(SELECTO.课号

,课名,成绩FROM选课表E,开课表O,课程表CWHEREE.开课号=O.开课号ANDO.课号=C.课号AND学号=@学号)第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»35ALTERfuncti

onF_学生选课信息(@学号CHAR(7))RETURNSTABLEASRETURN(SELECTO.课号,课名,‘成绩’=CASEWHEN成绩ISNULLTHEN'未完成考试'ELSECAST(成绩AS

CHAR(12))ENDFROM选课表E,开课表O,课程表CWHEREE.开课号=O.开课号ANDO.课号=C.课号AND学号=@学号)修改该函数，使得函数返回指定学生所选的课程号、课程名、成绩，如果成绩为NULL值，就显示‘未完成考试’。第11章用户自定义函数大型数据库S

QLServer2005»2022/12/5»3611.4.2删除用户定义函数使用DROPFUNCTION从当前数据库中删除一个或多个用户定义的函数。用户定义的函数通过CREATEFUNCTION创建，通过ALTERFUNCTION修改。语法格式为

：DROPFUNCTION{[owner_name.]function_name}[,...n]例：DROPFUNCTIONF_学生选课信息第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»3711.5用户定义函数实例分析问题

提出：显示指定学院每个学生选修课程情况和获取学分情况，要求先显示每个学生所选的课程，如果该门课程已经通过考试，则显示该门课程的学分，如果没有通过考试或还没有参加考试，则为0，然后显示该学生获取学分的总数。问题分析：完成上述显示功能，不能由一个

简单的查询语句完成，也不能用视图来进行定义，而须进行复杂的数据处理。我们考虑用多语句表值型函数来实现，将数据进行分布处理，然后按要求放到返回表中。第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»38CREATEFU

NCTIONF_学生获取学分(@院系CHAR(20))RETURNS@成绩学分表TABLE(学号CHAR(7),姓名CHAR(10),课名CHAR(20),成绩CHAR(12),学分INT)AS第11章用户自定义函数大型

数据库SQLServer2005»2022/12/5»39BEGINDECLARE@学号CHAR(7),@姓名CHAR(10),@学分INT--定义游标DECLARECUR_学生学分CURSORFORSELECT学号,姓名,累计学分FROM学生表WHERE所在院

系=@院系第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»40OPENCUR_学生学分FETCHNEXTFROMCUR_学生学分INTO@学号,@姓名,@学分WHILE@@f

etch_status=0BEGIN--将当前学生所选的课程及学分获取情况插入返回表INSERT@成绩学分表SELECTS.学号,姓名,课名,成绩,学分=CASEWHEN成绩>=60then学分ELSE0END第11章用户自定义函数大型数据库SQLServer200

5»2022/12/5»41FROM学生表S,选课表E,开课表O,课程表CWHERES.学号=@学号ANDS.学号=E.学号ANDE.开课号=O.开课号ANDO.课号=C.课号--插入当前学生所获得总学分数INSERTINTO@成绩学分表(学号,姓名,课名,学分)V

ALUES(@学号,@姓名,'获取总学分为：',@学分)第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»42FETCHNEXTFROMCUR_学生学分INTO@学号,@姓名,@学分ENDCLOSECUR_学生学分DEALLOCATECUR_学生学分RE

TURNENDGO第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»43要显示信息学院学生的选课及学分获取情况，则调用函数：SELECT*FROMSC_GPA_INFO('信息学院')第11章用户自

定义函数大型数据库SQLServer2005»2022/12/5»44小结用户自定义函数是包行一个或多个Transact-SQL语句的子程序，使用函数的主要目的是将我们经常需要使用的代码封装起来，以便在需要时多次使用而无需重复编程，同时还可以提供参数化的用户视图，简化程序设计。本章介

绍了三种自定义函数的创建、修改和删除，并列举了各种类型的用户自定义函数的实例，以帮助读者掌握如何在实际应用过程中使用和设计用户自定义函数。第11章用户自定义函数大型数据库SQLServer2005»2022/12/5»45Than

kyouverymuch!谢谢您的光临！下一章