SQL之用户自定义函数-创新互联

关于SQL Server用户自定义的函数,有标量函数、表值函数(内联表值函数、多语句表值函数)两种。

目前成都创新互联公司已为超过千家的企业提供了网站建设、域名、网页空间、网站运营、企业网站设计、港口网站维护等服务,公司将坚持客户导向、应用为本的策略,正道将秉承"和谐、参与、激情"的文化,与客户和合作伙伴齐心协力一起成长,共同发展。

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

SQL之用户自定义函数

标量函数

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

写法如下:

-- =============================================
-- Author:        
-- Create date: 
-- Description:    
-- =============================================
CREATE FUNCTION  
(
    -- Add the parameters for the function here
    <@Param1, sysname, @p1> 
)
RETURNS 
AS
BEGIN
    -- Declare the return variable here
    DECLARE <@ResultVar, sysname, @Result> 

    -- Add the T-SQL statements to compute the return value here
    SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

    -- Return the result of the function
    RETURN <@ResultVar, sysname, @Result>

END

例子:

-- =============================================
-- 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

 题外话:我们来看看上面的写法,对于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类型。

写法如下:

-- =============================================
-- Author:        
-- Create date: 
-- Description:    
-- =============================================
CREATE FUNCTION  
(    
    -- Add the parameters for the function here
    <@param1, sysname, @p1> , 
    <@param2, sysname, @p2> 
)
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    SELECT 0
)
GO

例子:

-- =============================================
-- 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

题外话:标量函数上面提过的内容,这里就不重复了。内联表函数返回的表结构由函数体内的SELECT语句来决定。

对于标量函数来讲,函数体是包含在如下结构中。

AS
BEGIN
    -- 函数体
END

但是对于内联表值函数来讲,函数体的结构则是如下的方式。内联表值函数只执行一条SQL语句后返回Table结果。

AS
RETURN
    -- 函数体
END

执行表值函数的方式如下:

select [FName],[FCity],[FAge],[FSalary] from [dbo].[GetMoreThanSalary](8000)

可以看得出,这种执行方式就跟普通表的执行方式一样了。表值函数其实相当于存储在内存空间里面的一张虚拟表。

多语句表值函数

 多语句表值函数跟内联表值函数都是表值函数,它们返回的结果都是Table类型。多语句表值函数顾名思义,就是可以通过多条语句来创建Table类型的数据。这里不同于内联表值函数,内联表值函数的返回结果是由函数体内的SELECT语句来决定。而多语句表值函数,则是需要指定具体的Table类型的结构。也就是说返回的Table,已经定义好要哪些字段返回。所以它能够支持多条语句的执行来创建Table数据。

写法如下:

-- =============================================
-- Author:        
-- Create date: 
-- Description:    
-- =============================================
CREATE FUNCTION  
(
    -- Add the parameters for the function here
    <@param1, sysname, @p1> , 
    <@param2, sysname, @p2> 
)
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> 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

例子:

-- =============================================
-- 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

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

RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
    -- Add the column definitions for the TABLE variable here
     , 
     
)

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

至此,本文完。

另外有需要云服务器可以了解下创新互联scvps.cn,海内外云服务器15元起步,三天无理由+7*72小时售后在线,公司持有idc许可证,提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案,具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势,专为企业上云打造定制,能够满足用户丰富、多元化的应用场景需求。


文章题目:SQL之用户自定义函数-创新互联
文章位置:http://myzitong.com/article/csdsdp.html