SQL之用户自定义函数

北风留影

　　关于SQL Server用户自定义的函数，有标量函数、表值函数（内联表值函数、多语句表值函数）两种。
　　题外话，可能有部分朋友不知道SQL Serve用户自定义的函数应该是写在哪里，这里简单提示一下，在Microsoft SQL Server Managerment Studio里面，展开具体需要创建SQL Server用户自定义函数的数据库（即每个用户自定义函数只针对具体的一个数据库有用），然后找到可编程性选项，再展开找到函数选项，在具体的函数选项里面可参照下图的方式鼠标右键选择来添加。

http://images.cnblogs.com/cnblogs_com/csdbfans/532122/o_2014-01-11_002706.gif

标量函数　　所谓标量函数简单点来讲就是返回的结果只是一个标量，对于我来讲，返回的结果就是一种类型的一个值。
　　写法如下：

http://common.cnblogs.com/images/copycode.gif

http://common.cnblogs.com/images/copycode.gif

-- =============================================　　
-- Author:        
　　
-- Create date:
　　
-- Description:   
　　
-- =============================================
　　
CREATE FUNCTION
　　
(
　　
    -- Add the parameters for the function here
　　
     
　　
)
　　
RETURNS
　　
AS
　　
BEGIN
　　
    -- Declare the return variable here
　　
    DECLARE  
　　

　　
    -- Add the T-SQL statements to compute the return value here
　　
    SELECT  =
　　

　　
    -- Return the result of the function
　　
    RETURN
　　

　　
END

http://common.cnblogs.com/images/copycode.gif

http://common.cnblogs.com/images/copycode.gif

　　例子：

http://common.cnblogs.com/images/copycode.gif

http://common.cnblogs.com/images/copycode.gif

-- =============================================　　
-- Author:        
　　
-- Create date:
　　
-- Description:   
　　
-- =============================================
　　
CREATE FUNCTION GetSum
　　
(
　　
    @firstNum int,
　　
    @secondNum int
　　
)
　　
RETURNS int
　　
AS
　　
BEGIN
　　
    -- Declare the return variable here
　　
    DECLARE @result int
　　

　　
    -- Add the T-SQL statements to compute the return value here
　　
    SELECT @result=@firstNum+@secondNum
　　

　　
    -- Return the result of the function
　　
    RETURN @result
　　

　　
END
　　
GO

http://common.cnblogs.com/images/copycode.gif

http://common.cnblogs.com/images/copycode.gif

　　题外话：我们来看看上面的写法，对于SQL　Server来讲，我们声明一个变量的方式是用@变量名，而且相对于编程来讲，SQL　Server声明的方式跟我们开了个玩笑，是先变量后面才是类型。对于需要传参跟不需要传参的方式，其实跟我们编程的方式一样。有参数则是如下方式：
CREATE FUNCTION GetSum　　
(
　　
    @firstNum int,
　　
    @secondNum int
　　
)
　　如果没有参数，则只要保留括号即可。跟我们理解的函数写法一致。
CREATE FUNCTION GetSum　　
(
　　
)
　　对于返回方式，这跟我们编程的方式又不大一样。SQL　Server函数的返回类型并不放在函数名前面，而是函数名括号的后面。而且函数的返回类型需要用到返回关键字RETURNS，而不是RETURN。
　　对于函数来讲，当然也会有所谓的函数体。标量函数也一样。它的函数体是包含在：
AS　　
BEGIN
　　
    -- 函数体
　　
END
　　对于需要在函数体里面声明变量的话，则需要使用到DECLARE关键字进行声明。函数体内的返回才是关键字RETURN。
　　好了，标量函数的例子也举完了，要存到数据库里面，还需要点击Microsoft SQL Server Management Studio工具里的执行操作。这样之后，就可以在查询窗口里面跟查询表数据一样来查询结果了。
　　使用方式好懂吧，但是需要注意的是[dbo]这个对象名在不能省，[GetSum]函数后面可也别少了()。说来也奇怪，对于表值函数来说，对象名[dbo]倒是不写也可以正确执行。
select [dbo].[GetSum]()内联表值函数　　相对于标量函数只返回一个标量值，内联表值函数返回的是表数据。当然罗，表数据就是Table类型。
　　写法如下：

http://common.cnblogs.com/images/copycode.gif

http://common.cnblogs.com/images/copycode.gif

-- =============================================　　
-- Author:        
　　
-- Create date:
　　
-- Description:   
　　
-- =============================================
　　
CREATE FUNCTION
　　
(
　　
    -- Add the parameters for the function here
　　
     ,
　　
     
　　
)
　　
RETURNS TABLE
　　
AS
　　
RETURN
　　
(
　　
    -- Add the SELECT statement with parameter references here
　　
    SELECT 0
　　
)
　　
GO

http://common.cnblogs.com/images/copycode.gif

http://common.cnblogs.com/images/copycode.gif

　　例子：

http://common.cnblogs.com/images/copycode.gif

http://common.cnblogs.com/images/copycode.gif

-- =============================================　　
-- Author:        
　　
-- Create date:
　　
-- Description:   
　　
-- =============================================
　　
CREATE FUNCTION [GetMoreThanSalary]
　　
(
　　
    @salary int
　　
)
　　
RETURNS TABLE
　　
AS
　　
RETURN
　　
(
　　
    SELECT [FName],[FCity],[FAge],[FSalary] FROM [Demo].[dbo].[T_Person] Where [FSalary] > @salary
　　
)
　　
GO

http://common.cnblogs.com/images/copycode.gif

http://common.cnblogs.com/images/copycode.gif

　　题外话：标量函数上面提过的内容，这里就不重复了。内联表函数返回的表结构由函数体内的SELECT语句来决定。
　　对于标量函数来讲，函数体是包含在如下结构中。
AS　　
BEGIN
　　
    -- 函数体
　　
END
　　但是对于内联表值函数来讲，函数体的结构则是如下的方式。内联表值函数只执行一条SQL语句后返回Table结果。
AS　　
RETURN
　　
    -- 函数体
　　
END
　　执行表值函数的方式如下：
select [FName],[FCity],[FAge],[FSalary] from [dbo].[GetMoreThanSalary](8000)　　可以看得出，这种执行方式就跟普通表的执行方式一样了。表值函数其实相当于存储在内存空间里面的一张虚拟表。
多语句表值函数　　多语句表值函数跟内联表值函数都是表值函数，它们返回的结果都是Table类型。多语句表值函数顾名思义，就是可以通过多条语句来创建Table类型的数据。这里不同于内联表值函数，内联表值函数的返回结果是由函数体内的SELECT语句来决定。而多语句表值函数，则是需要指定具体的Table类型的结构。也就是说返回的Table，已经定义好要哪些字段返回。所以它能够支持多条语句的执行来创建Table数据。
　　写法如下：

http://common.cnblogs.com/images/copycode.gif

http://common.cnblogs.com/images/copycode.gif

-- =============================================　　
-- Author:        
　　
-- Create date:
　　
-- Description:   
　　
-- =============================================
　　
CREATE FUNCTION
　　
(
　　
    -- Add the parameters for the function here
　　
     ,
　　
     
　　
)
　　
RETURNS
　　
TABLE
　　
(
　　
    -- Add the column definitions for the TABLE variable here
　　
     ,
　　
     
　　
)
　　
AS
　　
BEGIN
　　
    -- Fill the table variable with the rows for your result set
　　

　　
    RETURN
　　
END
　　
GO

http://common.cnblogs.com/images/copycode.gif

http://common.cnblogs.com/images/copycode.gif

　　例子：

http://common.cnblogs.com/images/copycode.gif

http://common.cnblogs.com/images/copycode.gif

-- =============================================　　
-- Author:        
　　
-- Create date:
　　
-- Description:   
　　
-- =============================================
　　
ALTER FUNCTION DemoFun
　　
(
　　

　　
)
　　
RETURNS
　　
@result TABLE
　　
(
　　
    name nvarchar(20),
　　
    city nvarchar(20),
　　
    age int,
　　
    salary int
　　
)
　　
AS
　　
BEGIN
　　
    -- Fill the table variable with the rows for your result set
　　
    insert into @result(name, city, age, salary)
　　
    select FName,FCity,FAge,FSalary from dbo.T_Person where FSalary>8000
　　
    insert into @result(name, city, age, salary) values
　　
    ('测试','China', 1, 0)
　　
    RETURN
　　
END
　　
GO

http://common.cnblogs.com/images/copycode.gif

http://common.cnblogs.com/images/copycode.gif

　　题外话：可以看得出，多语句表值函数的返回结果是定义好表结构的虚拟表。这又跟标量函数一样了吧，只不过标量函数是返回一种类型的标量值而已。而且在多语句表值函数里面，你也会发现最后一句是RETURN。告诉执行程序，多语句表值函数已经执行完成。函数体结构跟标量函数的结构一样。对于类型放在变量后面这种方式确实需要好好转换一下观念。

http://common.cnblogs.com/images/copycode.gif

http://common.cnblogs.com/images/copycode.gif

RETURNS　　
TABLE
　　
(
　　
    -- Add the column definitions for the TABLE variable here
　　
     ,
　　
     
　　
)

http://common.cnblogs.com/images/copycode.gif

http://common.cnblogs.com/images/copycode.gif

　　内容倒是不多，但是要熟练使用的话，还是需要在项目中多加使用才行。网上有一些网友总结出来的常用自定义函数大家可以收集积累，就像做项目一样，好的方法要形成所谓的开发库，帮助我们在下一个项目中复用。节省我们的开发时间，提高我们的工作效率。
　　至此，本文完。