第11章 用户自定义函数与数据类型

11.1
自定义函数概述
自定义函数是由用户自己根据需要使用SQL语句编写 语句编写 自定义函数是由用户自己根据需要使用 的函数。它可以提供系统函数无法提供的功能。 的函数。它可以提供系统函数无法提供的功能。
11.1.1 自定义函数与存储过程的区别
用户自定义函数与存储过程非常相似，不同之处如下。 用户自定义函数与存储过程非常相似，不同之处如下。 返回值的数据类型包括大部分SQL Server数据类型，但 数据类型， 返回值的数据类型包括大部分 数据类型 不包括以下类型： 不包括以下类型：TEXT、NTEXT、IMAGE、CURSOR和 、 、 、 和 TIMESTAMP。 。 没有“副作用” 用户自定义函数不能进行如更改表、 没有“副作用”。用户自定义函数不能进行如更改表、 发送电子邮件、更改系统或数据库参数的操作， 发送电子邮件、更改系统或数据库参数的操作，而存储过程 则没有这些限制。 则没有这些限制。 用户自定义函数类似于标准编程语言（ 用户自定义函数类似于标准编程语言（如VB.NET或C++ 或 中使用的函数。函数可以有多个输入变量， ）中使用的函数。函数可以有多个输入变量，并且有一个值 输出，在使用时就像使用系统函数一样， 输出，在使用时就像使用系统函数一样，而存储过程必须要 命令来执行。 用EXEC命令来执行。 命令来执行
第11章 11章
用户自定义函数和数据类型
用户自定义函数（User Defined Functions，UDF）和自 用户自定义函数（ ， ） 定义数据类型是SQL Server提供的强大功能。借助用户自定义 提供的强大功能。 定义数据类型是 提供的强大功能 函数，数据库开发人员可以实现复杂的运算操作。 函数，数据库开发人员可以实现复杂的运算操作。而自定义数 据类型，可以让用户更方便地设计与维护数据库。实际上， 据类型，可以让用户更方便地设计与维护数据库。实际上，前 面介绍的存储过程和触发器概念都可以归为用户自定义函数。 面介绍的存储过程和触发器概念都可以归为用户自定义函数。
11.4.1 使用向导创建自定义数据类型
用户自定义数据类型的使用既简单又广泛， 用户自定义数据类型的使用既简单又广泛，数据库管 理人员甚至可以在设计一个数据表的架构时， 理人员甚至可以在设计一个数据表的架构时，将其中的某个 字段定义为自定义数据类型。下面通过示例来说明。 字段定义为自定义数据类型。下面通过示例来说明。
11.4.4 删除自定义数据类型
当自定义数据类型不再满足需要时，可以从系统中删 当自定义数据类型不再满足需要时， 除它。 除它。下面就以删除学生信息库中的密码自定义数据类型 为例，讲解在SQL Server Management Studio中删除自 为例，讲解在 中删除自 定义数据类型的操作步骤。 定义数据类型的操作步骤。 对象资源管理器】面板中展开【 在【对象资源管理器】面板中展开【StudentMan】| 】 【可编程性】|【类型】|【用户自定义数据类型】分支，就 可编程性】 【类型】 【用户自定义数据类型】分支， 可以看到自定义数据类型列表了， 可以看到自定义数据类型列表了，右击要删除的自定义数 据类型，在弹出的快捷菜单中选择【删除】命令，弹出【 据类型，在弹出的快捷菜单中选择【删除】命令，弹出【 删除对象】对话框，单击【确定】按钮， 删除对象】对话框，单击【确定】按钮，自定义数据类型 就从系统中删除了。 就从系统中删除了。 除了通过向导删除自定义数据类型， 除了通过向导删除自定义数据类型，SQL Server 2008还提供了系统存储过程来实现用命令方式删除自定义 还提供了系统存储过程来实现用命令方式删除自定义 数据类型。 数据类型。
11.3.3 删除自定义函数
当自定义函数不再满足需要，可以从系统中删除它。 当自定义函数不再满足需要，可以从系统中删除它。下面 就以删除11.2.1中定义的标量值函数 中定义的标量值函数SystemDate为例，讲解 为例， 就以删除 中定义的标量值函数 为例 在SQL Server Management Studio中删除自定义函数的操作 中删除自定义函数的操作 步骤。 步骤。 （1） 启动并登录 ） 启动并登录SQL Server Management Studio，在 ， 对象资源管理器】面板中展开【StudentMan】|【 【对象资源管理器】面板中展开【StudentMan】|【可编程性 】|【函数】|【标量值函数】分支。 【函数】 【标量值函数】分支。 】，在弹出的快捷菜单中 （2） 右击函数【SystemDate】，在弹出的快捷菜单中 ） 右击函数【 】， 选择【删除】命令，弹出【删除对象】对话框。单击【确定】 选择【删除】命令，弹出【删除对象】对话框。单击【确定】 按钮，函数就被删除了。 按钮，函数就被删除了。
11.2.1 创建标量函数
在SQL Server Management Studio中创建自定义函数的 中创建自定义函数的 方法都是类似的。 方法都是类似的。SQL Server Management Studio只起到了 只起到了 提供代码编辑环境的作用，具体代码需要用户自己完成。 提供代码编辑环境的作用，具体代码需要用户自己完成。在 SQL Server Management Studio中创建标量函数的操作步骤 中创建标量函数的操作步骤 如下。 如下。 （1）启动并登录 ）启动并登录SQL Server Management Studio，在 ， 对象资源管理器】面板中展开【 【对象资源管理器】面板中展开【StudentMan】|【可编程性 】【 分支。 】分支。 （2）右击【函数】分支，在弹出的快捷菜单中选择【新 ）右击【函数】分支，在弹出的快捷菜单中选择【 建】|【标量值函数】命令，弹出函数编辑窗口。系统已经给出 【标量值函数】命令，弹出函数编辑窗口。 了函数的基本语句模板 （3）输入函数语句，单击【执行】按钮将函数保存在系 ）输入函数语句，单击【执行】 统中。 统中。
11.3
使用和维护用户自定义函数
在自定义函数需要改变或不再使用时， 在自定义函数需要改变或不再使用时，就需要进行自 定义函数的维护操作了。 定义函数的维护操作了。
11.3.1 查看与修改自定义函数
如果要对自定义函数进行修改，需要使用ALTER 如果要对自定义函数进行修改，需要使用 FUNCTION语句。 语句。 语句
11.1.2 自定义函数类型
根据函数返回值形式的不同， 根据函数返回值形式的不同，用户自定义函数分为两 种类型：标量型函数和表值函数。其中， 种类型：标量型函数和表值函数。其中，表值函数又可分为 内联表值函数和多语句表值函数。 内联表值函数和多语句表值函数。
11.2
创建自定义函数
创建自定义函数时，不论什么类型的函数， 创建自定义函数时，不论什么类型的函数，除语法外的 创建过程完全相同。下面讲解创建自定义函数的操作步骤。 创建过程完全相同。下面讲解创建自定义函数的操作步骤。
11.2.3 创建多语句表值函数
多语句表值函数比内联表值函数更为灵活。 多语句表值函数比内联表值函数更为灵活。虽然返回类型也 是表类型，但是这个表可以由用户自定义结构。表内容的填充可 是表类型，但是这个表可以由用户自定义结构。 以在函数体中完成。 以在函数体中完成。在SQL Server Management Studio中创建 中创建 多语句表值函数的操作步骤如下。 多语句表值函数的操作步骤如下。 （1） 启动并登录 ） 启动并登录SQL Server Management Studio，在【 ， 对象资源管理器】面板中展开【 对象资源管理器】面板中展开【StudentMan】|【可编程性】分 】 【可编程性】 右击【函数】分支，在弹出的快捷菜单中选择【新建】 【 支，右击【函数】分支，在弹出的快捷菜单中选择【新建】|【多 语句表值函数】命令，弹出函数编辑窗口。 语句表值函数】命令，弹出函数编辑窗口。系统已经给出了函数 的基本语句模板。 的基本语句模板。 （2） 输入函数语句，单击【执行】按钮将函数保存在系统 ） 输入函数语句，单击【执行】 中。 （3） 在生成的模板中有一处是与内联表值函数的模板不同 ） 就是RETURNS语句中返回的表类型，默认存放的是要设计的 语句中返回的表类型， 的，就是 语句中返回的表类型 表结构。 表结构。
11.4.2 使用存储过程创建自定义数据类型
wk.baidu.com除了通过向导创建自定义数据类型， 除了通过向导创建自定义数据类型，SQL Server 2008 还提供了系统存储过程来实现用命令方式创建自定义数据类 型。
11.4.3 使用自定义数据类型
前面定义了一个密码的自定义类型， 前面定义了一个密码的自定义类型，下面就以学生信息 表为例，讲解自定义数据类型的用法。 表为例，讲解自定义数据类型的用法。 对象资源管理器】面板中展开【数据库】 【 （1）在【对象资源管理器】面板中展开【数据库】|【 ） StudentMan】|【表】分支，右击【StudentList】分支，在 分支，右击【 】【 】分支， 弹出的快捷菜单中选择【设计】命令，弹出表结构设计窗口。 弹出的快捷菜单中选择【设计】命令，弹出表结构设计窗口。 在表结构中增加一个名为pass的字段，数据类型设置为新建 的字段， 在表结构中增加一个名为 的字段 的自定义数据类型，单击【保存】 保存修改。 的自定义数据类型，单击【保存】按钮 保存修改。 （2）在SQL Server Management Studio的工具栏中 ） 的工具栏中 单击【新建查询】按钮，弹出新的查询窗口， 单击【新建查询】按钮，弹出新的查询窗口，在代码编辑区输 入如下代码，查询表StudentList，然后单击【执行】按钮。 入如下代码，查询表 ，然后单击【执行】按钮。
11.4
自定义数据类型
自定义数据类型（ 自定义数据类型（User Defined Data Types，UDDTs）使 ， ） 得数据库开发人员能够根据需要定义符合自己应用开发需求的数 据类型，使得数据应用程序的开发更加得心应手。 据类型，使得数据应用程序的开发更加得心应手。自定义函数本 身实际上还提供了数据的完整性检验功能。 身实际上还提供了数据的完整性检验功能。例如可以使用自定义 数据类型为输入的数据制定一个固定的格式， 数据类型为输入的数据制定一个固定的格式，从而规范用户输入 的数据等。用户自定义数据类型几乎可定义任意数据。 的数据等。用户自定义数据类型几乎可定义任意数据。 注意：自定义数据类型虽然用起来很方便， 注意：自定义数据类型虽然用起来很方便，但需要大量的性 能开销，所以也需要谨慎使用。 能开销，所以也需要谨慎使用。
11.3.2 使用自定义函数
调用自定义函数和调用内置函数的方式基本相同， 调用自定义函数和调用内置函数的方式基本相同，但是需 要注意如下事项。 要注意如下事项。 当调用标量值函数时，必须加上“所有者” 通常是dbo 当调用标量值函数时，必须加上“所有者”，通常是 表值函数无此限制。 。表值函数无此限制。 执行用户自定义函数时，所有参数都不能省略， 执行用户自定义函数时，所有参数都不能省略，包括有默 认值的参数。默认值用DEFAULT关键字指定。 关键字指定。 认值的参数。默认值用 关键字指定
11.2.2 创建内联表值函数
在SQL Server Management Studio中新建内联表值的操 中新建内联表值的操 作步骤如下。 作步骤如下。 （1） 启动并登录 ） 启动并登录SQL Server Management Studio，在【 ， 对象资源管理器】面板中展开【 对象资源管理器】面板中展开【StudentMan】|【可编程性】分 】 【可编程性】 右击【函数】分支，在弹出的快捷菜单中选择【新建】 【 支，右击【函数】分支，在弹出的快捷菜单中选择【新建】|【内 联表值函数】命令，弹出函数编辑窗口。 联表值函数】命令，弹出函数编辑窗口。系统已经给出了函数的 基本语句模板。 基本语句模板。 （2） 输入函数语句，单击【执行】按钮将函数保存在系统 ） 输入函数语句，单击【执行】 中。 （3）在生成的模板中有一处是与标量函数的模板不同的， ）在生成的模板中有一处是与标量函数的模板不同的， 就是RETURNS语句中返回的类型被固定为 语句中返回的类型被固定为TABLE类型。 类型。 就是 语句中返回的类型被固定为 类型