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

第,11,章 用户自定义函数,大型数据库,SQL Server 2005,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,*,浙江财经学院,第,11,章,用户自定义函数设计,11.1,用户自定义函数概述,11.2,创建用户定义函数,11.3,用户定义函数的调用,11.4,修改和删除用户定义函数,11.5,用户定义函数实例分析,10/3/2024,1,11.1,用户自定义函数概述,11.1.1,用户自定义函数的特点,1,、重复使用编程代码，减少编程开发时间，提高工作效率。,2,、隐藏,SQL,细节，把,SQL,繁琐的工作留给数据库开发人员，而程序开发员则集中处理高级编程语言。,3,、维修集中化，可以在一个地方做业务上的逻辑修改，然后让这些修改自动应用到所有相关程序中。,4,、可在另一个,SQL,语句中直接调用。,5,、函数必须始终返回一个值,(,一个标量值或一个表格,),10/3/2024,2,11.1,用户自定义函数概述,除了使用系统提供的函数外，用户还可以根据需要自定义函数。用户自定义函数（,User Defined Functions,）是,SQL Server 2000,以后新增的数据库对象，是,SQL Server,的一大改进。,用户自定义函数不能用于执行一系列改变数据库状态的操作，但它可以像系统函数一样在查询或存储过程等的程序段中使用，也可以像存储过程一样通过,EXECUTE,命令来执行。用户自定义函数中存储了一个,Transact-SQL,例程，可以返回一定的值,。,10/3/2024,3,可使用,CREATE FUNCTION,语句创建、使用,ALTER FUNCTION,语句修改、以及使用,DROP FUNCTION,语句除去用户定义函数。每个完全合法的用户定义函数名必须唯一。,必须被授予,CREATE FUNCTION,权限才能创建、修改或除去用户定义函数。不是所有者的用户在,Transact-SQL,语句中使用某个函数之前，必须先给此用户授予该函数的适当权限。若要创建或更改在,CHECK,约束、,DEFAULT,子句或计算列定义中引用用户定义函数的表，还必须具有函数的,REFERENCES,权限。,10/3/2024,4,在,SQL Server 2000,中根据函数返回值形式的不同将用户自定义函数分为三种类型：,标量型函数（,Scalar functions,）,内嵌表值型函数（,Inline table-valued functions,）,多语句表值型函数（,Multi-statement table-valued functions,）。,10/3/2024,5,标量型函数（,Scalar functions,）,标量型函数返回在,RETURNS,子句中定义的类型的单个数据值。可以使用所有标量数据类型，包括,bigint,和,sql_variant,。,不支持,timestamp,数据类型、用户定义数据类型和非标量类型（如,table,或,cursor,）。在,BEGIN.END,块中定义的函数主体包含返回该值的,Transact-SQL,语句系列。返回类型可以是除,text,、,ntext,、,image,、,cursor,、,timestamp,和,table,之外的任何数据类型。,10/3/2024,6,内嵌表值型函数（,Inline table-valued functions,）,内嵌表值型函数以表的形式返回一个返回值，即它返回的是一个表。内嵌表值型函数没有由,BEGIN-END,语句括起来的函数体。其返回的表由一个位于,RETURN,子句中的,SELECT,命令段从数据库中筛选出来。内联表值型函数功能相当于一个参数化的视图。,10/3/2024,7,多语句表值型函数,（,Multi-statement table-valued functions,）,多语句表值型函数可以看作标量型和内嵌表值型函数的结合体。它的返回值是一个表，但它和标量型函数一样有一个用,BEGIN-END,语句括起来的函数体，返回值的表中的数据是由函数体中的语句插入的。由此可见，它可以进行多次查询，对数据进行多次筛选与合并，弥补了内嵌表值型函数的不足。,用户定义函数采用零个或更多的输入参数并返回标量值或表。函数最多可以有,1024,个输入参数。当函数的参数有默认值时，调用该函数时必须指定默认,DEFAULT,关键字才能获取默认值。该行为不同于在存储过程中含有默认值的参数，而在这些存储过程中省略该函数也意味着省略默认值。用户定义函数不支持输出参数。,10/3/2024,8,函数中的有效语句类型包括：,（,1,）,DECLARE,语句，该语句可用于定义函数局部的数据变量和游标。,（,2,）为函数局部对象赋值，如使用,SET,给标量和表局部变量赋值。,（,3,）游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。不允许使用,FETCH,语句将数据返回到客户端。仅允许使用,FETCH,语句通过,INTO,子句给局部变量赋值。,（,4,）控制流语句。,（,5,）,SELECT,语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。,（,6,）,INSERT,、,UPDATE,和,DELETE,语句，这些语句修改函数的局部,table,变量。,（,7,）,EXECUTE,语句，该语句调用扩展存储过程。,10/3/2024,9,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,10/3/2024,10,创建内嵌表值型函数,（,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 ,10/3/2024,11,创建多语句表值型函数,（,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 ),10/3/2024,12,参数说明：,owner_name,：,拥有该用户定义函数的用户,ID,的名称。,function_name,：,用户定义函数的名称。函数名称必须符合标识符的规则，对其所有者来说，该名称在数据库中必须是唯一的。,10/3/2024,13,参数说明,：,parameter_name,：,用户定义函数的参数。,CREATE FUNCTION,语句中可以声明一个或多个参数。函数最多可以有,1 024,个参数。函数执行时每个已声明参数的值必须由用户指定，除非该参数的默认值已经定义。如果函数的参数有默认值，在调用该函数时必须指定“,default,”,关键字才能获得默认值。这种行为不同于存储过程中有默认值的参数，在存储过程中省略参数也意味着使用默认值。使用,符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个函数的参数仅用于该函数本身；相同的参数名称可以用在其它函数中。参数只能代替常量；而不能用于代替表名、列名或其它数据库对象的名称。,10/3/2024,14,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,变量，用于存储和累积应作为函数值返回的行。,参数说明,：,10/3/2024,15,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,语句）该函数引用的数据库对象。,参数说明,：,10/3/2024,16,11.2,创建用户定义函数,11.2.1,使用对象资源管理器,10/3/2024,17,11.2,创建用户定义函数,11.2.1,使用,CREATE FUNCTION,命令创建用户自定义函数,1,、创建标量值用户自定义函数（,Scalar functions,）,创建成绩转换标量值函数，实现百分制成绩与优、良、中、及格、不及格五个等级的换算。,USE,教学管理,GO,CREATE FUNCTION F_,成绩分级,(,成绩,FLOAT),RETURNS CHAR(16),AS,10/3/2024,18,11.2,创建用户定义函数,BEGIN,DECLARE ,等级,CHAR(16),SELECT ,等级,= CASE,WHEN ,成绩,IS NULL THEN ,还没参加考试,WHEN ,成绩,= 60 and ,成绩,= 70 and ,成绩,= 80 and ,成绩,=60 then,学分,ELSE 0,END,10/3/2024,40,FROM,学生表,S,选课表,E,开课表,O,课程表,C,WHERE S.,学号,=,学号,AND S.,学号,=E.,学号,AND E.,开课号,=O.,开课号,AND O.,课号,=C.,课号,-,插入当前学生所获得总学分数,INSERT INTO ,成绩学分表,(,学号,姓名,课名,学分,),VALUES(,学号,姓名,获取总学分为：,学分,),10/3/2024,41,FETCH NEXT FROM CUR_,学生学分,INTO ,学号,姓名,学分,END,CLOSE CUR_,学生学分,DEALLOCATE CUR_,学生学分,RETURN,END,GO,10/3/2024,42,要显示信息学院学生的选课及学分获取情况，则调用函数：,SELECT * FROM SC_GPA_INFO(,信息学院,),10/3/2024,43,小 结,用户自定义函数是包行一个或多个,Transact-SQL,语句的子程序，使用函数的主要目的是将我们经常需要使用的代码封装起来，以便在需要时多次使用而无需重复编程，同时还可以提供参数化的用户视图，简化程序设计。本章介绍了三种自定义函数的创建、修改和删除，并列举了各种类型的用户自定义函数的实例，以帮助读者掌握如何在实际应用过程中使用和设计用户自定义函数。,10/3/2024,44,Thank you very much!,谢谢您的光临！,下一章,10/3/2024,45,