自定义函数

多语句表值函数
对于多语句表值函数，在 BEGIN...END 语句块中 定义的函数体包含一系列 Transact-SQL 语句， 这些语句可生成行并将其插入将返回的表中。
多语句表值函数语法
create function [ owner_name ] function_name ( [参数列表]) returns @return_variable table < table_type_definition > [ as ] begin function_body return end 其中： < 1 > . < table_type_definition > :({column_definition | table_constraint} [ , n ] ) < 2 > . @return_variable :一个table类型的变量，用于存 储和累积返回的表中的数据行。
分析一
有以下表结构 create table Number ( Id varchar(20), --该字段的数据使用流水号 createDate datetime ) 自定的函数需要以下参数
@preFix 单据前缀 @dateFormate 业务日期格式o varchar(20) --定义保存流水号 的变量 -- 查询表中当天最大的流水号,如果是当天的第 一个流水 号,测用isNull将Null替换成0 select @myNo=isnull(convert(int, right(max(id),@length)),0)+1 from number where datediff(dd,createdate,getdate())=0
其中： < 1 > . table :批定返回值为一个表。 < 2 > . select - stmt:单个select语句，确定返回 的表的数据。
示例
以下示例创建了一个内联表值函数。此函数的输 入参数为CardId，而返回该卡号信息的其它列值 。
create function fn_GetCardInfoByCardId(@cardId varchar(10)) returns table as return ( select * from bank where cardId=@cardId ) go
内联表值函数
用户定义表值函数返回 table 数据类型。对于内 联表值函数，没有函数主体；表是单个 SELECT 语句的结果集。
内联表值函数语法
create function [ owner_name ] function_name ( [参数列表 参数列表]) 参数列表 returns table [ as ] return ( select - stmt)
分析三
得到顺序编号 set @myno = right(replicate('0',@length) + @myno ,@ length) 单据前缀+业务日期 set @myno = @preFix + @dateFormate +@myno
实现代码
create function createNumber(@prifix varchar(20),@dateFormat varchar(20),@length int) returns varchar(20) begin declare @myNo varchar(20) select @myNo=isnull(convert(int, right(max(id),@length)),0)+1 from number where datediff(dd,createdate,getdate())=0 set @myNo=convert(int,@myNo) set @myNo=@prifix +@dateFormat+replicate(0,@lengthlen(@myNo))+@MyNo return @myno end --测试语句 select dbo.createNumber('IX',convert(varchar(20),getdate(),112),4) insert into number values(dbo.createNumber('IX',convert(varchar(20),getdate(),112),4),g etdate())
自定义函数的优点
允许模块化程序设计 执行速度更快 减少网络流量提高数据库运行性能
自定义函数的分类
在SQL Server 中根据函数返回值形式的不同将用户 自定义函数分为二种类型： 第一种类型：标量函数（Scalar functions） 第二种类型：表值函数（table-valued functions ）
示例
以下示例创建了一个标量函数。此函数输入一个 值 cardId，返回单个数据值（该卡号的当前余额 ）。
create function fn_GetBalanceByCardId(@cardId varchar(10)) returns money as begin declare @balance money select @balance=currentMoney from bank where cardId=@cardId return @balance end go
第二种类型：内联表值函数 （Inline table-valued functions） 第三种类型：多语句表值函数 （Multi-statement table-valued functions）
标量函数
用户定义标量函数返回在 RETURNS 子句中定义 的类型的单个数据值。函数体语句定义在 BEGIN...END 块中的函数体包含一系列返回单个 值的 Transact-SQL 语句。返回类型可以是除 text、ntext、image等外的任何数据类型。
存储过程和函数的区别
一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针 对性比 较强。 对于存储过程来说可以返回参数，而函数只能返回值或者表对象。 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语 句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在 查询语句中位于FROM关键字的后面。 当存储过程和函数被执行的时候，SQL Manager会到procedure cache中去取相应的查询语句，如果在procedure cache里没有相应的 查询语句，SQL Manager就会对存储过程和函数进行编译。
多语句表值函数示例
以下示例创建了一个多语句表值函数。此函数具 有一个输入参数 CardID 而返回该卡号的交易次 数和交易总金额。
多语句表值函数示例
create function fn_GetTransByCardId(@cardId varchar(10)) returns @Bank table ( num int not null, tolMoney money not null ) as begin declare @num int,@tolMoney money select @num=count(*),@tolMoney=sum(transMoney) from transInfo where cardId=@cardId insert into @bank select @num ,@tolMoney return end go
标量函数语法
create function [ owner_name] function_name ( [参数列表 参数列表]) 参数列表 returns scalar_return_data_type [ as ] begin function_body return [ scalar_expression ] end
自定义函数的综合应用
使用自定函数实现业务流水号的生成 流水号是现在各类系统中单据的必备字段,因为流 水号很容易标识一个新的单据.例如流水号的格式 为:单据前缀+业务日期+几位顺序编号.知道了流 水号的固定格式,设计流水号就非常方便了.在 SqlServer中,可以通过客户端程序来生成新的流 水号,也可以利用存储过程来生成.在实际的项目中 ,利用自定义函数来生成非常方便,方便存储过程调 用,也方便客户端的调用.
自定义函数
实训补充资料
什么是自定义函数
用户自定义函数（User Defined Functions）是 SQL Server 的数据库对象，它不能用于执行一系 列改变数据库状态的操作，但它可以像系统函数 一样在查询或存储过程等的程序段中使用，也可 以像存储过程一样通过EXECUTE 命令来执行。
自定义函数的优点