《ERP沙盘模拟管理综合实训手册 教学课件 ppt 作者 高冬梅第10章 用户定义函数》由会员分享，可在线阅读，更多相关《ERP沙盘模拟管理综合实训手册 教学课件 ppt 作者 高冬梅第10章 用户定义函数（21页珍藏版）》请在金锄头文库上搜索。
1、第10章 用户定义函数,本章学习目标: 熟悉使用函数的优点及函数的类型 掌握创建和管理标量用户定义函数 掌握创建和管理多语句表值函数 掌握创建和管理内联表值函数,开 始,第10章 用户定义函数,10.1 用户定义函数 10.2 创建用户定义函数 10.3 管理用户定义函数,10.1 用户定义函数,SQL Server用户定义函数是接受参数、执行操作（例如复杂计算），并将操作结果以值的形式返回的例程。 可以通过SQL Server设计用户定义函数，来补充和扩展系统支持的内置函数。用户定义函数可接受零个或多个输入参数，返回标量值或表。 在 SQL Server中使用用户定义函数有以下优点： 允许模
2、块化程序设计。 执行速度更快。 减少网络流量。,SQL Server 2008支持用户定义函数和内置系统函数。 标量函数 用户定义标量函数返回在RETURNS子句中定义的类型的单个数据值。 表值函数 用户定义表值函数返回table数据类型。 表值函数可以分为内联表值函数或多语句表值函数。内联表值函数，没有函数主体，表是单个SELECT语句的结果集。多语句表值函数，在BEGIN.END语句块中定义的函数体包含一系列Transact-SQL语句，这些语句可生成行并将其插入将返回的表中。 内置函数 SQL Server提供了内置函数来帮助执行各种操作。这些函数不能修改。可以在 Transact-SQ
3、L语句中使用内置函数，完成以下操作： 从SQL Server系统表中访问信息而不直接访问系统表。例如函数DB_ID、DB_NAME或OBJECT_ID等。 执行常见任务，例如SUM、GETDATE或IDENTITY。 内置函数返回标量数据类型或table数据类型。,10.2 创建用户定义函数,用户定义函数的结构有两部分组成：标题和正文。 标题定义： 具有可选架构/所有者名称的函数名称； 输入参数名称和数据类型； 可以用于输入参数的选项； 返回参数数据类型和可选名称； 可以用于返回参数的选项。,正文定义了函数将要执行的操作或逻辑。包括执行函数逻辑的一个或多个Transact-SQL语句。 在函数
4、定义中可使用的有效语句包括： DECLARE语句，该语句可用于定义函数局部的数据变量和游标。 为函数局部对象的赋值，如使用SET为标量和表局部变量赋值。 游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。不允许使用FETCH语句将数据返回到客户端；仅允许使用FETCH语句通过INTO子句给局部变量赋值。 TRY.CATCH语句以外的流控制语句。 SELECT语句，该语句包含具有为函数的局部变量赋值的表达式的选择列表。 INSERT、UPDATE和DELETE语句，这些语句修改函数的局部表变量。 EXECUTE语句，该语句调用扩展存储过程。,10.2.1 创建用户定义标量函数 语法格
5、式为： CREATE FUNCTION schema_name. function_name ( parameter_name AS type_schema_name. parameter_data_type = default READONLY ,.n ) RETURNS return_data_type WITH ENCRYPTION | SCHEMABINDING | RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ,.n AS BEGIN function_body RETURN scalar_expression END ; ,sc
6、hema_name：用户定义函数所属的架构的名称。 function_name：用户定义函数的名称。 parameter_name：用户定义函数的参数。 type_schema_name. parameter_data_type：参数的数据类型及其所属的架构。 = default ：参数的默认值。 READONLY：指示不能在函数定义中更新或修改参数。 return_data_type：标量用户定义函数的返回值的数据类型。 ENCRYPTION：指示数据库引擎 对包含CREATE FUNCTION语句文本的目录视图列进行加密。 SCHEMABINDING：指定将函数绑定到其引用的数据库对象。
7、RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT：指定标量值函数的 OnNULLCall属性。如果未指定，则默认为CALLED ON NULL INPUT，这意味着即使传递的参数为NULL，也将执行函数体。 function_body：指定一系列定义函数值的Transact-SQL语句，这些语句一起使用的计算结果为标量值。 scalar_expression：指定标量函数返回的标量值。,【例10-1】 在数据库CJMS中创建用户定义标量函数fn_EvaluateOneStudent，要求：每次输入一个学号，计算该学生的所有课程的平均分，如果是8
8、5100分，返回“优”；如果是7584分，返回“良”；如果是6574分，返回“中”；如果是064分，返回“差”；如果该学生没有成绩，返回“无成绩”。,CREATE FUNCTION fn_EvaluateOneStudent(StudentID char(10) RETURNS varchar(10) AS BEGIN DECLARE 平均分 decimal(3,1), 等级 varchar(10) SELECT 平均分=AVG(Grade) FROM tblScore WHERE StudentID=StudentID SET 等级=CASE WHEN 平均分 BETWEEN 85 AND
9、100 THEN 优 WHEN 平均分 BETWEEN 75 AND 84 THEN 良 WHEN 平均分 BETWEEN 65 AND 74 THEN 中 WHEN 平均分 BETWEEN 0 AND 64 THEN 差 ELSE 无成绩 END RETURN 等级 END; GO,10.2.2 创建用户定义表值函数 返回table数据类型的用户定义表值函数功能强大，可以替代视图。 表值用户定义函数还可以替换返回单个结果集的存储过程。 创建多语句表值函数的语法格式为： CREATE FUNCTION schema_name. function_name ( parameter_name AS
10、 type_schema_name. parameter_data_type = default READONLY ,.n ) RETURNS return_variable TABLE WITH ENCRYPTION | SCHEMABINDING ,.n AS BEGIN function_body RETURN END ; ,return_variable：是TABLE变量，用于存储和汇总应作为函数值返回的行。 TABLE：指定表值函数的返回值为表。 ：表变量的定义。类同于使用CREATE TABLE语句创建表时，表结构的定义。 function_body：指定一系列定义函数值的Tran
11、sact-SQL语句，这些语句将填充TABLE返回变量。 【例10-2】 创建多语句表值函数fn_NameStyle，该函数根据提供的参数返回所有学生的姓或姓名。,CREATE FUNCTION fn_NameStyle(length char(9) RETURNS fn_Students TABLE (StudentID char(10) PRIMARY KEY NOT NULL, Student Name char(8) NOT NULL) AS BEGIN IF length=ShortName INSERT fn_Students SELECT StudentID,LEFT(Sname
12、,1) FROM tblStudents ELSE IF length=LongName INSERT fn_Students SELECT StudentID,Sname FROM tblStudents RETURN END; GO,内联用户定义函数是返回table数据类型的用户定义函数的子集。内联函数可用于获得参数化视图的功能。 创建内联表值函数的语法格式为： CREATE FUNCTION schema_name. function_name ( parameter_name AS type_schema_name. parameter_data_type = default READ
13、ONLY ,.n ) RETURNS TABLE WITH ENCRYPTION | SCHEMABINDING ,.n AS RETURN ( select_stmt ) ; TABLE：指定表值函数的返回值为表。在内联表值函数中，TABLE返回值是通过单个SELECT语句定义的。 select_stmt：定义内联表值函数的返回值的单个SELECT语句。,【例10-3】 创建内联表值函数fn_StudentInDepartment，该函数根据提供的系别返回该系学生的学号，姓名，性别及出生日期。 【例10-4】 编写用户自定义函数fn_RetrieveOneStudent，每次输入一个学号，返
14、回该学生姓名、选修课程的名称和分数。,CREATE FUNCTION fn_StudentInDepartment (department varchar(40) RETURNS TABLE AS RETURN (SELECT StudentID,Sname,Sex,Birthday FROM tblStudents WHERE Department=department ); GO,CREATE FUNCTION fn_RetrieveOneStudent(StudentID char(10) RETURNS TABLE AS RETURN ( SELECT Sname AS 学生姓名,Cn
15、ame AS 选修课程名称,Grade AS 成绩 FROM tblStudents AS St INNER JOIN tblScore AS Sc ON St.StudentID=Sc.StudentID INNER JOIN tblCourses AS c ON Sc.CourseID=c.CourseID WHERE St.StudentID=StudentID ); GO,10.3 管理用户定义函数,10.3.1 查看用户定义函数 使用sys.sql_modules、OBJECT_DEFINITION或sp_helptext等工具查看用户定义函数的定义（用于创建用户定义函数的 Transact-SQL 语句）； 使用sys.objects、sys.parameters或sp_help等工具查看有关用户定义函数的信息（例如函数的架构、创建时间及其参数）； 使用sys.sql_expression_dependencies、sys.dm_sql_referenced_entities或sys.dm_sql_referencing_entities等工具查看用户定义函数的依赖关系（指定函数所使用的对象及使用指定函数的对象）。,10.3.2 执行用户定义函数 可以在查询、其
