11第11章用户自定义函数

>>浙江财经学院<<
» 14
第11章 用户自定义函数
大型数据库 SQL Server 2005
参数说明：
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变量， 用于存储和累积应作为函数值返回的行。
大型数据库 SQL Server 2005
11.1 用户自定义函数概述
11.1.1用户自定义函数的特点
1、重复使用编程代码，减少编程开发时间，提高工作效
率。 2、隐藏SQL细节，把SQL繁琐的工作留给数据库开发人员， 而程序开发员则集中处理高级编程语言。 3、维修集中化，可以在一个地方做业务上的逻辑修改，
>>浙江财经学院<<
» 11
第11章 用户自定义函数
大型数据库 SQL Server 2005
创建多语句表值型函数 （Multi-statement table-valued functions）：
CREATE FUNCTION [ owner_name.] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE < table_type_definition > [ WITH < function_option > [ [,] ...n ] ] AS BEGIN function_body RETURN END < function_option > ::= { ENCRYPTION | SCHEMABINDING } < table_type_definition > ::= ( { column_definition | table_constraint } [ ,...n ] )
>>浙江财经学院<<
» 8
第11章 用户自定义函数
大型数据库 SQL Server 2005
函数中的有效语句类型包括： （1）DECLARE语句，该语句可用于定义函数局部的数据变量和游标。
（2）为函数局部对象赋值，如使用SET给标量和表局部变量赋值。
（3）游标操作，该操作引用在函数中声明、打开、关闭和释放的局 部游标。不允许使用FETCH语句将数据返回到客户端。仅允许使 用FETCH语句通过INTO子句给局部变量赋值。 （4）控制流语句。 （5）SELECT语句，该语句包含带有表达式的选择列表，其中的表达 式将值赋予函数的局部变量。 （6）INSERT、UPDATE和DELETE语句，这些语句修改函数的局部 table变量。 （7）EXECUTE语句，该语句调用扩展存储过程。
>>浙江财经学院<<
» 4
第11章 用户自定义函数
大型数据库 SQL Server 2005
在SQL Server 2000中根据函数返回值形式的不同 将用户自定义函数分为三种类型：
标量型函数（Scalar
functions） table-valued functions）
内嵌表值型函数（Inline
>>浙江财经学院<<
» 12
第11章 用户自定义函数
大型数据库 SQL Server 2005
参数说明：
owner_name：拥有该用户定义函数的用户
ID的名称。
function_name：用户定义函数的名称。
函数名称必须符合标识符的规则，对其所 有者来说，该名称在数据库中必须是唯一 的。
>>浙江财经学院<<
>>浙江财经学院<<
» 10
第11章 用户自定义函数
大型数据库 SQL Server 2005
创建内嵌表值型函数 （Inline table-valued functions）：
CREATE FUNCTION [ owner_name.] function_name ([{@parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS TABLE [WITH < function_option > [ [,] ...n ] ] AS RETURN [ ( ) select-stmt [ ] ]
多语句表值型函数（Multi-statement
table-
valued functions）。
>>浙江财经学院<<
» 5
第11章 用户自定义函数
大型数据库 SQL Server 2005
标量型函数（Scalar functions） 标量型函数返回在RETURNS子句中定义的类型的 单个数据值。可以使用所有标量数据类型，包括 bigint和sql_variant。不支持timestamp数据类型、 用户定义数据类型和非标量类型（如table或 cursor）。在BEGIN...END块中定义的函数主体包 含返回该值的 Transact-SQL语句系列。返回类型 可以是除text、ntext、image、cursor、 timestamp和table 之外的任何数据类型。
>>浙江财经学院<<
» 3
第11章 用户自定义函数
大型数据库 SQL Server 2005
可使用CREATE FUNCTION语句创建、使用ALTER FUNCTION语句修改、以及使用DROP FUNCTION语句除 去用户定义函数。每个完全合法的用户定义函数名必 须唯一。 必须被授予CREATE FUNCTION权限才能创建、 修改或除去用户定义函数。不是所有者的用户在 Transact-SQL语句中使用某个函数之前，必须先给此 用户授予该函数的适当权限。若要创建或更改在 CHECK约束、DEFAULT子句或计算列定义中引用 用户定义函数的表，还必须具有函数的 REFERENCES权限。
>>浙江财经学院<<
» 9
第11章 用户自定义函数
大型数据库 SQL Server 2005
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 < function_option> [ [,] ...n] ] AS BEGIN function_body RETURN scalar_expression END
>>浙江财经学院<<
» 15
第11章 用户自定义函数
大型数据库 SQL Server 2005
参数说明：
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语句）该函数引用的数据库对象。
» 13
第11章 用户自定义函数
大型数据库 SQL Server 2005
参数说明：
@parameter_name：用户定义函数的参数。
Байду номын сангаас
CREATE FUNCTION语句中可以声明一个或多个参数。函数 最多可以有1 024个参数。函数执行时每个已声明参数的值必 须由用户指定，除非该参数的默认值已经定义。如果函数的参 数有默认值，在调用该函数时必须指定“default”关键字才能 获得默认值。这种行为不同于存储过程中有默认值的参数，在 存储过程中省略参数也意味着使用默认值。使用@符号作为第 一个字符来指定参数名称。参数名称必须符合标识符的规则。 每个函数的参数仅用于该函数本身；相同的参数名称可以用在 其它函数中。参数只能代替常量；而不能用于代替表名、列名 或其它数据库对象的名称。