第04章 Transact-SQL语言基础 - 自定义函数.ppt

4.4 Transact-SQL函数 4.4.6 自定义函数 用户根据工作需要，可以创建用户定义函数，以提高程序开发和运行的质量。创建用户定义函数首先要根据业务需要选择函数类型。 创建自定义函数的有两种方法: 用户利用SQL Server Management Studio中的工具改写模板代码创建函数 使用CREATE FUNCTION语句创建函数。 用户自定义函数的创建有两种方法，一种是直接在SQL Server Management Studio中直接创建，另一种是利用代码创建。 1.在SQL Server Management Studio环境下直接创建自定义函数。 下面以创建“内联表值函数”为例列出创建的具体步骤。 （1）进入SQL Server Management Studio环境。 （2）在“对象资源管理器”窗口中依次展开如下节点：数据库/实例数据库/可编程性/函数/表值函数。 （3）在“表值函数”项上右击，在弹出菜单中选择“新建内联表值函数” 。 （4）用户选择“新建内联表值函数”后，系统自动打开新的查询分析器并将创建内联表值函数的语法模板，显示出来。 （5）用户在相关的参数处修改相关项即可。 用户定义函数 SQL Server 2005允许用户设计自己的函数，以补充和扩展系统提供（内置）函数的功能 用户定义函数采用零或多个输入参数并返回标量值或表 SQL Server 2005 支持三种用户定义函数： 标量函数、多语句表值函数、内嵌表值函数 标量函数 标量函数返回一个标量（单值）结果 返回值可为：Timestamp、Text、Ntext、Image、Table和Cursor之外的所有数据类型，但不能为用户自定义数据类型。 多语句表值函数 返回一个由一条或多条 Transact-SQL 语句建立的表，类似于存储过程 与存储过程不同的是，多语句表值函数可以在 SELECT 语句的 FROM 子句中被引用，仿佛视图一样 内嵌表值函数 返回一个表，该表是由一个位于RETURN子句中的SELECT命令段从数据库中筛选出来的，类似于视图 相对于视图，内嵌表值函数可使用参数，提供了更强的适应性，扩展了索引视图的功能 创建标量用户定义函数 标量函数返回 RETURNS 子句中定义的数据类型的单个数据值 在 BEGIN … END 块之间定义了函数体，包含返回值的一系列 Transact-SQL 语句 返回值可以是除了 text、ntext、image、cursor 或 timestamp 之外的任何数据类型 额外的限制 标量函数内的 SQL 语句不能包括任何非确定性系统函数 标量用户函数定义 CREATE FUNCTION [own_name.] function_name ( [{ @参数名 [AS] 数据类型 [= 默认值]} [,...n] ]) RETURNS 返回数据类型 [WITH 函数选项] [AS] BEGIN 函数体 RETURN 返回表达式 END T-SQL基础知识– 函数 标量函数只对一个数值进行操作，并返回单个数值。如：字符串函数、数学函数等。 创建标量函数的语法： 标量用户定义函数示例 USE pubs GO CREATE FUNCTION MyFun (@type char(12))RETURNS moneyASBEGIN DECLARE @var money SELECT @var = sum(price * ytd_sales) FROM titles WHERE type = @type RETURN @varEND USE pubs GO CREATE FUNCTION MyFun (@type char(12) = ‘business’)RETURNS moneyASBEGIN DECLARE @var money SELECT @var = sum(price * ytd_sales) FROM titles WHERE type = @type RETURN @varEND T-SQL基础知识–函数 标量函数 使用内嵌表值函数 内嵌表值函数返回表，可在 FROM 子句中被引用，就像视图一样 关于使用内嵌表值函数的依据和指导方针 RETURNS 子句在括号中包含单个 SELECT 语句。SELECT 语句的结果集构成函数所返回的表 函数体不由 BEGIN 和 END 分隔 RETURNS 指定 table 作为返回的数据类型 不必定义返回变量的格式，因为它由 RETURN 子句中的 SELECT 语句的结果集的格式设置 T-