大型数据库技术第11章用户自定义函数课件.ppt

1、第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005第第11章章 用户自定义函数设计用户自定义函数设计 11.1 用户自定义函数概述用户自定义函数概述 11.2 创建用户定义函数创建用户定义函数 11.3 用户定义函数的调用用户定义函数的调用 11.4 修改和删除用户定义函数修改和删除用户定义函数 11.5 用户定义函数实例分析用户定义函数实例分析 11/10/20221第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 200511.1 用户自定义函数概述用户自定义函数概述 11.1.1用户自定义函数的特点用户自

2、定义函数的特点1 1、重复使用编程代码，减少编程开发时间，提高工作效、重复使用编程代码，减少编程开发时间，提高工作效率。率。2 2、隐藏、隐藏SQLSQL细节，把细节，把SQLSQL繁琐的工作留给数据库开发人员，繁琐的工作留给数据库开发人员，而程序开发员则集中处理高级编程语言。而程序开发员则集中处理高级编程语言。3 3、维修集中化，可以在一个地方做业务上的逻辑修改，、维修集中化，可以在一个地方做业务上的逻辑修改，然后让这些修改自动应用到所有相关程序中。然后让这些修改自动应用到所有相关程序中。4 4、可在另一个、可在另一个SQLSQL语句中直接调用。语句中直接调用。5 5、函数必须始终返回一个值

3、、函数必须始终返回一个值(一个标量值或一个表格一个标量值或一个表格)11/10/20222第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 200511.1 用户自定义函数概述用户自定义函数概述 除了使用系统提供的函数外，用户还可以根据需要自定除了使用系统提供的函数外，用户还可以根据需要自定义函数。用户自定义函数（义函数。用户自定义函数（User Defined FunctionsUser Defined Functions）是）是SQL SQL Server 2000Server 2000以后新增的数据库对象，是以后新增的数据库对象，是SQL Serve

4、rSQL Server的一大改的一大改进。进。用户自定义函数不能用于执行一系列改变数据库状态的用户自定义函数不能用于执行一系列改变数据库状态的操作，但它可以像系统函数一样在查询或存储过程等的程序操作，但它可以像系统函数一样在查询或存储过程等的程序段中使用，也可以像存储过程一样通过段中使用，也可以像存储过程一样通过EXECUTE命令来执命令来执行。用户自定义函数中存储了一个行。用户自定义函数中存储了一个Transact-SQL例程，可以例程，可以返回一定的值返回一定的值。11/10/20223第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005 可使用

5、可使用CREATE FUNCTIONCREATE FUNCTION语句创建、使用语句创建、使用ALTER ALTER FUNCTIONFUNCTION语句修改、以及使用语句修改、以及使用DROP FUNCTIONDROP FUNCTION语句除语句除去用户定义函数。每个完全合法的用户定义函数名必去用户定义函数。每个完全合法的用户定义函数名必须唯一。须唯一。必须被授予必须被授予CREATE FUNCTION权限才能创建、权限才能创建、修改或除去用户定义函数。不是所有者的用户在修改或除去用户定义函数。不是所有者的用户在Transact-SQL语句中使用某个函数之前，必须先给此语句中使用某个函数之前

6、，必须先给此用户授予该函数的适当权限。若要创建或更改在用户授予该函数的适当权限。若要创建或更改在CHECK约束、约束、DEFAULT子句或计算列定义中引用子句或计算列定义中引用用户定义函数的表，还必须具有函数的用户定义函数的表，还必须具有函数的REFERENCES权限。权限。11/10/20224第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005 在在SQL Server 2000中根据函数返回值形式的不同中根据函数返回值形式的不同将用户自定义函数分为三种类型：将用户自定义函数分为三种类型：l标量型函数（标量型函数（Scalar functions

7、）l内嵌表值型函数（内嵌表值型函数（Inline table-valued functions）l多语句表值型函数（多语句表值型函数（Multi-statement table-valued functions）。）。11/10/20225第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005 标量型函数（标量型函数（Scalar functionsScalar functions）标量型函数返回在标量型函数返回在RETURNSRETURNS子句中定义的类型的子句中定义的类型的单个数据值。可以使用所有标量数据类型，包括单个数据值。可以使用所有标量数据类

8、型，包括bigintbigint和和sql_variantsql_variant。不支持不支持timestamptimestamp数据类型、数据类型、用户定义数据类型和非标量类型（如用户定义数据类型和非标量类型（如tabletable或或cursorcursor）。在）。在BEGIN.ENDBEGIN.END块中定义的函数主体包块中定义的函数主体包含返回该值的含返回该值的 Transact-SQLTransact-SQL语句系列。返回类型语句系列。返回类型可以是除可以是除texttext、ntextntext、imageimage、cursorcursor、timestamptimestamp

9、和和table table 之外的任何数据类型。之外的任何数据类型。11/10/20226第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005 内嵌表值型函数（内嵌表值型函数（Inline table-valued functions）内嵌表值型函数以表的形式返回一个返回值，内嵌表值型函数以表的形式返回一个返回值，即它返回的是一个表。内嵌表值型函数没有由即它返回的是一个表。内嵌表值型函数没有由BEGIN-END语句括起来的函数体。其返回的表语句括起来的函数体。其返回的表由一个位于由一个位于RETURN子句中的子句中的SELECT命令段从命令段从数据库

10、中筛选出来。内联表值型函数功能相当于数据库中筛选出来。内联表值型函数功能相当于一个参数化的视图。一个参数化的视图。11/10/20227第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005多语句表值型函数（Multi-statement table-valued functions）多语句表值型函数可以看作标量型和内嵌表值型函数的结合多语句表值型函数可以看作标量型和内嵌表值型函数的结合体。它的返回值是一个表，但它和标量型函数一样有一个用体。它的返回值是一个表，但它和标量型函数一样有一个用BEGIN-ENDBEGIN-END语句括起来的函数体，返回值的

11、表中的数据是由函数语句括起来的函数体，返回值的表中的数据是由函数体中的语句插入的。由此可见，它可以进行多次查询，对数据进体中的语句插入的。由此可见，它可以进行多次查询，对数据进行多次筛选与合并，弥补了内嵌表值型函数的不足。行多次筛选与合并，弥补了内嵌表值型函数的不足。用户定义函数采用零个或更多的输入参数并返回标量值或用户定义函数采用零个或更多的输入参数并返回标量值或表。函数最多可以有表。函数最多可以有1024个输入参数。当函数的参数有默认值时，个输入参数。当函数的参数有默认值时，调用该函数时必须指定默认调用该函数时必须指定默认DEFAULT关键字才能获取默认值。关键字才能获取默认值。该行为不同

12、于在存储过程中含有默认值的参数，而在这些存储过该行为不同于在存储过程中含有默认值的参数，而在这些存储过程中省略该函数也意味着省略默认值。用户定义函数不支持输出程中省略该函数也意味着省略默认值。用户定义函数不支持输出参数。参数。11/10/20228第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005函数中的有效语句类型包括：函数中的有效语句类型包括：（1 1）DECLAREDECLARE语句，该语句可用于定义函数局部的数据变量和游标。语句，该语句可用于定义函数局部的数据变量和游标。（2 2）为函数局部对象赋值，如使用）为函数局部对象赋值，如使用SET

13、SET给标量和表局部变量赋值。给标量和表局部变量赋值。（3 3）游标操作，该操作引用在函数中声明、打开、关闭和释放的局）游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。不允许使用部游标。不允许使用FETCHFETCH语句将数据返回到客户端。仅允许使语句将数据返回到客户端。仅允许使用用FETCHFETCH语句通过语句通过INTOINTO子句给局部变量赋值。子句给局部变量赋值。（4 4）控制流语句。）控制流语句。（5 5）SELECTSELECT语句，该语句包含带有表达式的选择列表，其中的表达语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。式将值赋予函数的局

14、部变量。（6 6）INSERTINSERT、UPDATEUPDATE和和DELETEDELETE语句，这些语句修改函数的局部语句，这些语句修改函数的局部tabletable变量。变量。（7 7）EXECUTEEXECUTE语句，该语句调用扩展存储过程。语句，该语句调用扩展存储过程。11/10/20229第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 200511.2 11.2 创建用户定义函数创建用户定义函数 创建标量型用户自定义函数（创建标量型用户自定义函数（Scalar functions）：）：CREATE FUNCTION owner_name.

15、function_name (parameter_name AS scalar_parameter_data_type =default ,.n )RETURNS scalar_return_data_type WITH ,.n AS BEGIN function_body RETURN scalar_expression END 11/10/202210第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005 创建内嵌表值型函数创建内嵌表值型函数（Inline table-valued functions）：）：CREATE FUNCTION owne

16、r_name.function_name(parameter_name AS scalar_parameter_data_type =default ,.n )RETURNS TABLE WITH ,.n AS RETURN ()select-stmt 11/10/202211第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005创建多语句表值型函数创建多语句表值型函数（Multi-statement table-valued functions）：）：CREATE FUNCTION owner_name.function_name(parameter

17、_name AS scalar_parameter_data_type =default ,.n )RETURNS return_variable TABLE WITH ,.n AS BEGIN function_body RETURN END :=ENCRYPTION|SCHEMABINDING :=(column_definition|table_constraint ,.n )11/10/202212第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005 参数说明：owner_nameowner_name：拥有该用户定义函数的用户拥有该用户定义函

18、数的用户IDID的名称。的名称。function_namefunction_name：用户定义函数的名称。用户定义函数的名称。函数名称必须符合标识符的规则，对其所函数名称必须符合标识符的规则，对其所有者来说，该名称在数据库中必须是唯一有者来说，该名称在数据库中必须是唯一的。的。11/10/202213第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005 参数说明：parameter_name：用户定义函数的参数。用户定义函数的参数。CREATE FUNCTION语句中可以声明一个或多个参数。函数语句中可以声明一个或多个参数。函数最多可以有最多可以有1

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

20、符合标识符的规则。每个函数的参数仅用于该函数本身；相同的参数名称可以用在每个函数的参数仅用于该函数本身；相同的参数名称可以用在其它函数中。参数只能代替常量；而不能用于代替表名、列名其它函数中。参数只能代替常量；而不能用于代替表名、列名或其它数据库对象的名称。或其它数据库对象的名称。11/10/202214第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005scalar_parameter_data_typescalar_parameter_data_type：参数的数据类型。所有标参数的数据类型。所有标量数量数据类型（包括据类型（包括bigintbi

21、gint和和sql_variantsql_variant）都可用都可用作用户定义作用户定义函数的参数。不支持函数的参数。不支持timestamptimestamp数据类数据类型和用户定义数据类型和用户定义数据类型。不能指定非标量类型型。不能指定非标量类型（例如（例如cursorcursor和和tabletable）。）。scalar_return_data_type scalar_return_data_type：是标量用户定义函数的返回是标量用户定义函数的返回值。值。scalar_return_data_type scalar_return_data_type 可以是可以是 SQL SQL

22、Server Server 支持的任何支持的任何标量数据类型（标量数据类型（texttext、ntextntext、imageimage和和timestamptimestamp除外）。除外）。scalar_expressionscalar_expression：指定标量型函数返回的标量值。指定标量型函数返回的标量值。TABLETABLE：指定表值型函数的返回值为表。在内嵌表值型函指定表值型函数的返回值为表。在内嵌表值型函 数中，数中，通过单个通过单个SELECTSELECT语句定义语句定义TABLETABLE返回值。返回值。内嵌函数没有相关联内嵌函数没有相关联的返回变量。在多语句表值的返回变量

23、。在多语句表值型函数中，型函数中，return_variable return_variable 是是TABLETABLE变量，变量，用于存储和累积应作为函数值返回的行。用于存储和累积应作为函数值返回的行。参数说明：11/10/202215第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005 function_bodyfunction_body：指定一系列指定一系列Transact-SQLTransact-SQL语句定义函数的值，语句定义函数的值，这些语句合在一起不会产生副作用。这些语句合在一起不会产生副作用。function_bodyfunctio

24、n_body只用于标量只用于标量型函数和多语句表值型函数。在标量型函数中，型函数和多语句表值型函数。在标量型函数中，function_bodyfunction_body是一是一系列合起来求得标量值的系列合起来求得标量值的Transact-SQLTransact-SQL语句。在多语句表值型函数语句。在多语句表值型函数中，中，function_bodyfunction_body是一系列填充表返回变量的是一系列填充表返回变量的Transact-SQLTransact-SQL语句。语句。select-stmtselect-stmt：是定义内嵌表值型函数返回值的单个是定义内嵌表值型函数返回值的单个SEL

25、ECTSELECT语句。语句。ENCRYPTIONENCRYPTION指出指出SQL ServerSQL Server加密包含加密包含CREATE FUNCTIONCREATE FUNCTION语句文本的语句文本的系统表列。使用系统表列。使用ENCRYPTIONENCRYPTION可以避免将函数作为可以避免将函数作为SQL ServerSQL Server复制的复制的一部分发布。一部分发布。SCHEMBINDINGSCHEMBINDING：指定将函数绑定到它所引用的数据库对象。如果指定将函数绑定到它所引用的数据库对象。如果函数是用函数是用SCHEMABINDINGSCHEMABINDING选项

26、创建的，则不能更改（使用选项创建的，则不能更改（使用ALTERALTER语句）语句）或除去（使用或除去（使用DROPDROP语句）该函数引用的数据库对象。语句）该函数引用的数据库对象。参数说明：11/10/202216第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 200511.2 创建用户定义函数创建用户定义函数 11.2.1 使用对象资源管理器使用对象资源管理器 11/10/202217第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 200511.2 创建用户定义函数创建用户定义函数11.2.1 11.2.1

27、使用使用CREATE FUNCTIONCREATE FUNCTION命令创建用户自定义函数命令创建用户自定义函数 1 1、创建标量值用户自定义函数（、创建标量值用户自定义函数（Scalar functionsScalar functions）创建成绩转换标量值函数，实现百分制成绩与优、良、中、创建成绩转换标量值函数，实现百分制成绩与优、良、中、及格、不及格五个等级的换算。及格、不及格五个等级的换算。USE 教学管理教学管理GOCREATE FUNCTION F_成绩分级成绩分级(成绩成绩 FLOAT)RETURNS CHAR(16)AS AS 11/10/202218第第11章章 用户自定义函

28、数用户自定义函数 大型数据库大型数据库 SQL Server 200511.2 创建用户定义函数创建用户定义函数BEGIN DECLARE 等级等级 CHAR(16)SELECT 等级等级=CASE WHEN 成绩成绩 IS NULL THEN 还没参加考试还没参加考试 WHEN 成绩成绩=60 and 成绩成绩=70 and 成绩成绩=80 and 成绩成绩 90 THEN 良好良好 ELSE 优秀优秀!END RETURN(等级等级)END 11/10/202219第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 200511.2 创建用户定义函数创建

29、用户定义函数 创建内联表值型函数，返回指定学院学生的信息创建内联表值型函数，返回指定学院学生的信息 USE 教学管理教学管理GOCREATE FUNCTION F_学生信息学生信息(院系院系 CHAR(20)RETURNS TABLEASRETURN(SELECT 学号学号,姓名姓名,性别性别,院系院系,专业专业,籍贯籍贯FROM 学生表学生表 WHERE 所在院系所在院系=院系院系)11/10/202220第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 200511.2 创建用户定义函数创建用户定义函数 创建多语句表值型函数，返回指定教师某学年的开课信

30、息创建多语句表值型函数，返回指定教师某学年的开课信息USE 教学管理教学管理GOCREATE FUNCTION F_教师课表教师课表(教师姓名教师姓名 CHAR(8),开课学年开课学年 char(9)RETURNS 教师课表教师课表 TABLE(课名课名 varchar(30),开课地点开课地点 char(6),开课学年开课学年 char(9),开课学期开课学期 int,开课周数开课周数 int,开课时间开课时间 varchar(20),已选人数已选人数 int)11/10/202221第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005ASBEGI

31、N INSERT 教师课表教师课表 SELECT 课名课名,开课地点开课地点,开课学年开课学年,开课学期开课学期,开课周数开课周数,开课时间开课时间,已选人数已选人数 FROM 教师表教师表 T,开课表开课表 O,课程表课程表 C WHERE T.工号工号=O.工号工号 AND O.课号课号=C.课号课号 AND 姓名姓名=教师姓名教师姓名 AND 开课学年开课学年=开课学年开课学年 RETURN END 11/10/202222第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 200511.3 11.3 用户定义函数的调用用户定义函数的调用 当引用或唤醒

32、调用用户定义函数时，应指定函数当引用或唤醒调用用户定义函数时，应指定函数名。而在括号内可指定称为参数的表达式，以提供名。而在括号内可指定称为参数的表达式，以提供将传递给参数的数据。当唤醒调用函数时不能在参将传递给参数的数据。当唤醒调用函数时不能在参数中指定参数名。当唤醒调用函数时，必须提供所数中指定参数名。当唤醒调用函数时，必须提供所有参数的参数值，并且必须以有参数的参数值，并且必须以CREATE FUNCTION语句定义参数的相同序列指定参数值。例如，如果语句定义参数的相同序列指定参数值。例如，如果使 用 整 型 参 数 和使 用 整 型 参 数 和 n c h a r(2 0)参 数 定

33、义 名 为参 数 定 义 名 为fn_MyIntFunc的函数以返回整数，可利用以下方法的函数以返回整数，可利用以下方法唤醒调用该函数：唤醒调用该函数：11/10/202223第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 200511.3 11.3 用户定义函数的调用用户定义函数的调用 调用创建的函数，按五等成绩显示信电学院每个学生所选调用创建的函数，按五等成绩显示信电学院每个学生所选课程的成绩课程的成绩SELECT E.学号学号,姓名姓名,O.开课号开课号,O.课号课号,课课 名名,DBO.F_成绩分级成绩分级(成绩成绩)AS 成绩等级成绩等级FRO

34、M 选课表选课表 E,学生表学生表 S,开课表开课表 O,课程表课程表 C WHERE S.学号学号=E.学号学号 AND E.开课号开课号=O.开课号开课号 AND C.课号课号=O.课号课号 11/10/202224第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 200511.3 11.3 用户定义函数的调用用户定义函数的调用 调用创建的函数调用创建的函数F_学生信息学生信息，返回指定学院的学生，返回指定学院的学生的基本信息的基本信息-下面调用该函数下面调用该函数DECLARE 院系院系 CHAR(20)SET 院系院系=信电学院信电学院 SELEC

35、T*FROM F_学生信息学生信息(院系院系)11/10/202225第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 200511.3 11.3 用户定义函数的调用用户定义函数的调用 调用创建的函数调用创建的函数F_教师课表，能返回指定老师某学教师课表，能返回指定老师某学年所上的课程信息。年所上的课程信息。-下面调用该函数下面调用该函数DECLARE DECLARE 姓名姓名 CHAR(10)CHAR(10)SET SET 姓名姓名=黄中天黄中天 SELECT*FROM F_教师课表教师课表(姓名姓名,2007-2008)11/10/202226第第11

36、章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005函数的好处：函数的好处：由上面例子可以看出，用户自定义函数可以用由上面例子可以看出，用户自定义函数可以用来提供参数化的用户视图，比直接定义用户视图来提供参数化的用户视图，比直接定义用户视图具有更大的灵活性。具有更大的灵活性。11/10/202227第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 200511.4 修改和删除用户定义函数 11.4.1 11.4.1 修改用户定义函数修改用户定义函数 11.4.2 11.4.2 删除用户定义函数删除用户定义函数 11/10

37、/202228第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 200511.4.1 11.4.1 修改用户定义函数修改用户定义函数 用用ALTER FUNCTION命令可以修改先前命令可以修改先前由由CREATE FUNCTION语句创建的现有用户语句创建的现有用户定义函数，但不会更改权限，也不影响相关定义函数，但不会更改权限，也不影响相关的函数、存储过程或触发器。的函数、存储过程或触发器。11/10/202229第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005修改标量型用户自定义函数：修改标量型用户自定义

38、函数：ALTER FUNCTION owner_name.function_name (parameter_name AS scalar_parameter_data_type =default ,.n )RETURNS scalar_return_data_type WITH ,.n AS BEGIN function_body RETURN scalar_expression END11/10/202230第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005修改内嵌表值型函数：修改内嵌表值型函数：CREATE FUNCTION owner_nam

39、e.function_name (parameter_name AS scalar_parameter_data_type =default ,.n )RETURNS TABLE WITH ,.n AS RETURN (select-stmt )11/10/202231第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005创建多语句表值型函数：创建多语句表值型函数：CREATE FUNCTION owner_name.function_name (parameter_name AS scalar_parameter_data_type =default

40、 ,.n )RETURNS return_variable TABLE WITH ,.n AS BEGIN function_body RETURN END:=ENCRYPTION|SCHEMABINDING :=(column_definition|table_constraint ,.n )11/10/202232第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005 修改命令的语法与修改命令的语法与CREATE FUNCTIONCREATE FUNCTION相同，各参数含义相同，各参数含义见创建用户自定义函数一节。使用见创建用户自定义函数一节。使

41、用ALTER FUNCTIONALTER FUNCTION命令其命令其实相当于重建了一个同名的函数。实相当于重建了一个同名的函数。注释注释 不能用不能用ALTER FUNCTIONALTER FUNCTION将标量值函数更改为表值型函数，将标量值函数更改为表值型函数，反之亦然。同样地，也不能用反之亦然。同样地，也不能用ALTER FUNCTIONALTER FUNCTION将行内函数将行内函数更改为多语句函数，反之亦然。更改为多语句函数，反之亦然。权限权限 ALTER FUNCTIONALTER FUNCTION权限默认授予权限默认授予sysadminsysadmin固定服务器角色固定服务器角

42、色成员、成员、db_ownerdb_owner和和db_ddladmindb_ddladmin固定数据库角色成员和函固定数据库角色成员和函数的所有者且不可转让。数的所有者且不可转让。函数的所有者对其函数具有函数的所有者对其函数具有EXECUTE权限。不过，也权限。不过，也可将此权限授予其他用户。可将此权限授予其他用户。11/10/202233第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005 创建一个内联表值型函数，返回指定学生所选的课程号、课程名、创建一个内联表值型函数，返回指定学生所选的课程号、课程名、成绩，然后修改。成绩，然后修改。USE 教

43、学管理教学管理GOCREATE FUNCTION F_学生选课信息学生选课信息(学号学号 CHAR(7)RETURNS TABLEASRETURN(SELECT O.课号课号,课名课名,成绩成绩FROM 选课表选课表 E,开课表开课表 O,课程表课程表 CWHERE E.开课号开课号=O.开课号开课号AND O.课号课号=C.课号课号 AND 学号学号=学号学号)11/10/202234第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005ALTER function F_学生选课信息学生选课信息(学号学号 CHAR(7)RETURNS TABLEAS

44、RETURN(SELECT O.课号课号,课名课名,成绩成绩 =CASE WHEN 成绩成绩 IS NULL THEN 未完成考试未完成考试 ELSE CAST(成绩成绩 AS CHAR(12)ENDFROM 选课表选课表 E,开课表开课表 O,课程表课程表 CWHERE E.开课号开课号=O.开课号开课号AND O.课号课号=C.课号课号 AND 学号学号=学号学号)修改该函数，使得函数返回指定学生所选的课程号、课程名、成绩，如果成绩为NULL值，就显示未完成考试。11/10/202235第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 200511.4

45、.2 11.4.2 删除用户定义函数删除用户定义函数 使用使用DROP FUNCTIONDROP FUNCTION从当前数据库中删除一个或多个从当前数据库中删除一个或多个用户定义的函数。用户定义的函数通过用户定义的函数。用户定义的函数通过CREATE CREATE FUNCTIONFUNCTION创建，通过创建，通过ALTER FUNCTIONALTER FUNCTION修改。修改。语法格式为：语法格式为：DROP FUNCTION owner_name.function_name ,.n 例：例：DROP FUNCTION F_学生选课信息学生选课信息11/10/202236第第11章章 用

46、户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 200511.5 11.5 用户定义函数实例分析用户定义函数实例分析 问题提出：显示指定学院每个学生选修课程情况和问题提出：显示指定学院每个学生选修课程情况和获取学分情况，要求先显示每个学生所选的课程，如获取学分情况，要求先显示每个学生所选的课程，如果该门课程已经通过考试，则显示该门课程的学分，果该门课程已经通过考试，则显示该门课程的学分，如果没有通过考试或还没有参加考试，则为如果没有通过考试或还没有参加考试，则为0 0，然后显，然后显示该学生获取学分的总数。示该学生获取学分的总数。问题分析问题分析：完成上述显示功能，不能

47、由一个简单的：完成上述显示功能，不能由一个简单的查询语句完成，也不能用视图来进行定义，而须进行查询语句完成，也不能用视图来进行定义，而须进行复杂的数据处理。我们考虑用多语句表值型函数来实复杂的数据处理。我们考虑用多语句表值型函数来实现，将数据进行分布处理，然后按要求放到返回表中。现，将数据进行分布处理，然后按要求放到返回表中。11/10/202237第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005CREATE FUNCTION F_学生获取学分学生获取学分 (院系院系 CHAR(20)RETURNS 成绩学分表成绩学分表 TABLE (学号学号

48、CHAR(7),姓名姓名 CHAR(10),课名课名 CHAR(20),成绩成绩 CHAR(12),学分学分 INT)AS11/10/202238第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Server 2005 BEGIN DECLARE 学号学号 CHAR(7),姓名姓名 CHAR(10),学分学分 INT -定义游标定义游标 DECLARE CUR_学生学分学生学分 CURSOR FOR SELECT 学号学号,姓名姓名,累计学分累计学分 FROM 学生表学生表 WHERE 所在院系所在院系=院系院系 11/10/202239第第11章章 用户自定义函数用户

49、自定义函数 大型数据库大型数据库 SQL Server 2005OPEN CUR_学生学分学生学分 FETCH NEXT FROM CUR_学生学分学生学分 INTO 学号学号,姓名姓名,学分学分 WHILE fetch_status=0 BEGIN -将当前学生所选的课程及学分获取情况插入返回表将当前学生所选的课程及学分获取情况插入返回表 INSERT 成绩学分表成绩学分表 SELECT S.学号学号,姓名姓名,课名课名,成绩成绩,学分学分 =CASE WHEN 成绩成绩=60 then 学分学分 ELSE 0 END 11/10/202240第第11章章 用户自定义函数用户自定义函数 大型

50、数据库大型数据库 SQL Server 2005 FROM 学生表学生表 S,选课表选课表 E,开课表开课表 O,课程表课程表 C WHERE S.学号学号=学号学号 AND S.学号学号=E.学号学号 AND E.开课号开课号=O.开课号开课号 AND O.课号课号=C.课号课号-插入当前学生所获得总学分数插入当前学生所获得总学分数 INSERT INTO 成绩学分表成绩学分表(学号学号,姓名姓名,课名课名,学分学分)VALUES(学号学号,姓名姓名,获取总学分为：获取总学分为：,学分学分)11/10/202241第第11章章 用户自定义函数用户自定义函数 大型数据库大型数据库 SQL Se