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

第,11章 用户自定义函数,大型数据库,SQL Server 2005,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,*,浙江财经学院,第11章,用户自定义函数设计,11.1,用户自定义函数概述,11.2,创建用户定义函数,11.3,用户定义函数的调用,11.4,修改和删除用户定义函数,11.5,用户定义函数实例分析,11/15/2024,1,第11章 用户自定义函数设计 11.1 用户自定义函数概述,11.1,用户自定义函数概述,11.1.1,用户自定义函数的特点,1,、重复使用编程代码，减少编程开发时间，提高工作效率。,2,、隐藏,SQL,细节，把,SQL,繁琐的工作留给数据库开发人员，而程序开发员则集中处理高级编程语言。,3,、维修集中化，可以在一个地方做业务上的逻辑修改，然后让这些修改自动应用到所有相关程序中。,4,、可在另一个,SQL,语句中直接调用。,5,、函数必须始终返回一个值,(,一个标量值或一个表格,),11/15/2024,2,11.1 用户自定义函数概述 11.1.1用户自定义函数的,11.1,用户自定义函数概述,除了使用系统提供的函数外，用户还可以根据需要自定义函数。用户自定义函数（,User Defined Functions,）是,SQL Server 2000,以后新增的数据库对象，是,SQL Server,的一大改进。,用户自定义函数不能用于执行一系列改变数据库状态的操作，但它可以像系统函数一样在查询或存储过程等的程序段中使用，也可以像存储过程一样通过,EXECUTE,命令来执行。用户自定义函数中存储了一个,Transact-SQL,例程，可以返回一定的值,。,11/15/2024,3,11.1 用户自定义函数概述 除了使用系,可使用,CREATE FUNCTION,语句创建、使用,ALTER FUNCTION,语句修改、以及使用,DROP FUNCTION,语句除去用户定义函数。每个完全合法的用户定义函数名必须唯一。,必须被授予,CREATE FUNCTION,权限才能创建、修改或除去用户定义函数。不是所有者的用户在,Transact-SQL,语句中使用某个函数之前，必须先给此用户授予该函数的适当权限。若要创建或更改在,CHECK,约束、,DEFAULT,子句或计算列定义中引用用户定义函数的表，还必须具有函数的,REFERENCES,权限。,11/15/2024,4,可使用CREATE FUNCTION语句创建、,在,SQL Server 2000,中根据函数返回值形式的不同将用户自定义函数分为三种类型：,标量型函数（,Scalar functions,）,内嵌表值型函数（,Inline table-valued functions,）,多语句表值型函数（,Multi-statement table-valued functions,）。,11/15/2024,5,在SQL Server 2000中根据函数返回值,标量型函数（Scalar functions）,标量型函数返回在RETURNS子句中定义的类型的单个数据值。可以使用所有标量数据类型，包括bigint和sql_variant。不支持timestamp数据类型、用户定义数据类型和非标量类型（如table或cursor）。在BEGIN.END块中定义的函数主体包含返回该值的 Transact-SQL语句系列。返回类型可以是除text、ntext、image、cursor、timestamp和table 之外的任何数据类型。,11/15/2024,6,标量型函数（Scalar functions）10/,内嵌表值型函数（,Inline table-valued functions,）,内嵌表值型函数以表的形式返回一个返回值，即它返回的是一个表。内嵌表值型函数没有由,BEGIN-END,语句括起来的函数体。其返回的表由一个位于,RETURN,子句中的,SELECT,命令段从数据库中筛选出来。内联表值型函数功能相当于一个参数化的视图。,11/15/2024,7,内嵌表值型函数（Inline table-valued,多语句表值型函数,（,Multi-statement table-valued functions,）,多语句表值型函数可以看作标量型和内嵌表值型函数的结合体。它的返回值是一个表，但它和标量型函数一样有一个用,BEGIN-END,语句括起来的函数体，返回值的表中的数据是由函数体中的语句插入的。由此可见，它可以进行多次查询，对数据进行多次筛选与合并，弥补了内嵌表值型函数的不足。,用户定义函数采用零个或更多的输入参数并返回标量值或表。函数最多可以有,1024,个输入参数。当函数的参数有默认值时，调用该函数时必须指定默认,DEFAULT,关键字才能获取默认值。该行为不同于在存储过程中含有默认值的参数，而在这些存储过程中省略该函数也意味着省略默认值。用户定义函数不支持输出参数。,11/15/2024,8,多语句表值型函数（Multi-statement tabl,函数中的有效语句类型包括：,（1）,DECLARE,语句，该语句可用于定义函数局部的数据变量和游标。,（2）为函数局部对象赋值，如使用,SET,给标量和表局部变量赋值。,（3）游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。不允许使用,FETCH,语句将数据返回到客户端。仅允许使用,FETCH,语句通过,INTO,子句给局部变量赋值。,（4）控制流语句。,（5）,SELECT,语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。,（6）,INSERT,、,UPDATE,和,DELETE,语句，这些语句修改函数的局部,table,变量。,（7）EXECUTE语句，该语句调用扩展存储过程。,11/15/2024,9,函数中的有效语句类型包括：10/6/20239,11.2 创建用户定义函数,创建标量型用户自定义函数（,Scalar functions,）：,CREATE 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,END,11/15/2024,10,11.2 创建用户定义函数 创建标量型用户自定义函数（Sc,创建内嵌表值型函数,（,Inline table-valued functions,）：,CREATE FUNCTION,owner_name.,function_name,(parameter_name AS scalar_parameter_data_type =default ,.n ),RETURNS TABLE,WITH ,.n ,AS,RETURN ()select-stmt ,11/15/2024,11,创建内嵌表值型函数10/6/202311,创建多语句表值型函数,（,Multi-statement table-valued functions,）：,CREATE FUNCTION,owner_name.,function_name,(parameter_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/15/2024,12,创建多语句表值型函数10/6/202312,参数说明：,owner_name,：拥有该用户定义函数的用户,ID,的名称。,function_name,：用户定义函数的名称。函数名称必须符合标识符的规则，对其所有者来说，该名称在数据库中必须是唯一的。,11/15/2024,13,参数说明：owner_name：拥有该用户,参数说明,：,parameter_name,：用户定义函数的参数。,CREATE FUNCTION,语句中可以声明一个或多个参数。函数最多可以有,1 024,个参数。函数执行时每个已声明参数的值必须由用户指定，除非该参数的默认值已经定义。如果函数的参数有默认值，在调用该函数时必须指定“,default,”关键字才能获得默认值。这种行为不同于存储过程中有默认值的参数，在存储过程中省略参数也意味着使用默认值。使用,符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个函数的参数仅用于该函数本身；相同的参数名称可以用在其它函数中。参数只能代替常量；而不能用于代替表名、列名或其它数据库对象的名称。,11/15/2024,14,参数说明：parameter_name：用户,scalar_parameter_data_type：参数的数据类型。所有标量数据类型（包括bigint和sql_variant）都可用作用户定义函数的参数。不支持timestamp数据类型和用户定义数据类型。不能指定非标量类型（例如cursor和table）。,scalar_return_data_type：是标量用户定义函数的返回值。scalar_return_data_type 可以是 SQL Server 支持的任何标量数据类型（text、ntext、image和timestamp除外）。,scalar_expression：指定标量型函数返回的标量值。,TABLE：指定表值型函数的返回值为表。在内嵌表值型函数中，通过单个SELECT语句定义TABLE返回值。内嵌函数没有相关联的返回变量。在多语句表值型函数中，return_variable 是TABLE变量，用于存储和累积应作为函数值返回的行。,参数说明,：,11/15/2024,15,scalar_parameter_data_type：参数的,function_body,：指定一系列,Transact-SQL,语句定义函数的值，,这些语句合在一起不会产生副作用。,function_body,只用于标量型函数和多语句表值型函数。在标量型函数中，,function_body,是一系列合起来求得标量值的,Transact-SQL,语句。在多语句表值型函数中，,function_body,是一系列填充表返回变量的,Transact-SQL,语句。,select-stmt,：是定义内嵌表值型函数返回值的单个,SELECT,语句。,ENCRYPTION,指出,SQL Server,加密包含,CREATE FUNCTION,语句文本的系统表列。使用,ENCRYPTION,可以避免将函数作为,SQL Server,复制的一部分发布。,SCHEMBINDING,：指定将函数绑定到它所引用的数据库对象。如果函数是用,SCHEMABINDING,选项创建的，则不能更改（使用,ALTER,语句）或除去（使用,DROP,语句）该函数引用的数据库对象。,参数说明,：,11/15/2024,16,function_body：指定一系列Tran,11.2,创建用户定义函数,11.2.1,使用对象资源管理器,11/15/2024,17,11.2 创建用户定义函数 11.2.1 使用对象资源管理,11.2,创建用户定义函数,11.2.1 使用CREATE FUNCTION命令创建用户自定义函数,1、创建标量值用户自定义函数（Scalar functions）,创建成绩转换标量值函数，实现百分制成绩与优、良、中、及格、不及格五个等级的换算。,USE,教学管理,GO,CRE