sql server 自定义函数

sql server 自定义函数

一、在Microsoft SQL Server Managerment Studio中找到自定义函数的地方

在Microsoft SQL Server Managerment Studio里面,展开具体需要创建SQL Server用户自定义函数的数据库(即每个用户自定义函数只针对具体的一个数据库有用),然后找到可编程性选项,再展开找到函数选项,在具体的函数选项里面可参照下图的方式鼠标右键选择来添加、修改

二、SQL Server用户自定义的函数的种类

1、标量函数

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

(1)、语法

  •  
  • SQL 代码   复制
  • 
    Create function 函数名(参数)
    
    Returns 返回值数据类型
    
    [with {Encryption | Schemabinding }]
    
    [as]
    
    begin
    
    SQL语句(必须有return 变量或值)
    
    End
    
    		
  • (2)、简单实例

  • SQL 代码   复制
  • 
    Create function AvgResult(@scode varchar(10))
    
    Returns real
    
    As
    
    Begin
    
       Declare @avg real
    
       Declare @code varchar(11)
    
       Set @code=@scode +%Select @avg=avg(result) from LearnResult_baijiali
    
    Where scode like @code
    
    Return @avg
    
    End
    
    		
  • (3)、调用方式

  • select 架构名.函数名 as 字段别名 
  •  
  •  
  • 例如上例的调用:
  •  
  • select dbo.AvgResult('s0002') as result
  •  
  •  
  •  
  • (4)、注意事项
  •  
  • [1]、如果没有参数,则只要保留括号即可
  • [2]、函数的返回类型需要用到返回关键字RETURNS,而不是RETURN
  • [3]、函数体内的返回才是关键字RETURN
  • [4]、调用时,函数前面的[dbo]这个对象名在不能省
  •  
  •  
  •  
  • (5)、函数返回值的应用
  •  
  •  
  • [1]、返回值可放到局部变量中
  •  
  • 
    declare @avg1 real ,@avg2 real ,@avg3 real
    
    select @avg1= dbo.AvgResult(‘s0002’)
    
    set @avg2= dbo.AvgResult(‘s0002’)
    
    exec @avg3= dbo.AvgResult ‘s0002’
    			
  • [2]、函数引用

    select name from class where ccode = dbo.AvgResult(‘c001’)

    2、内联表值函数

    相对于标量函数只返回一个标量值,内联表值函数返回的是表数据。表数据就是Table类型。

    (1)、语法

  •   
  • SQL 代码   复制
  • 
    create function 函数名(参数)
    
    returns table
    
    [with {Encryption | Schemabinding }]
    
    as
    
    return(一条SQL语句)
    
    				
  • (2)、简单实例

  •  
  •  
  • SQL 代码   复制
  • -- =============================================
    -- Author:        <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:    <Description,,>
    -- =============================================
    CREATE FUNCTION [GetMoreThanSalary]
    (    
        @salary int
    )
    RETURNS TABLE 
    AS
    RETURN 
    (
        SELECT [FName],[FCity],[FAge],[FSalary] FROM [Demo].[dbo].[T_Person] Where [FSalary] > @salary
    )
    GO
    
    						
  •  
  •  
  • (3)、调用方式
  •  
  •  
  • SQL 代码   复制
  • 
    select [FName],[FCity],[FAge],[FSalary] from [dbo].[GetMoreThanSalary](8000)
    
    			
  •  
  •  
  • (4)、注意事项
  •  
  • [1]、内联表函数返回的表结构由函数体内的SELECT语句来决定
  • [2]、内联表值函数只执行一条SQL语句后返回Table结果
  •  
  •  
  •  
  • 3、多语句表值函数
  •  
  • 多语句表值函数顾名思义,就是可以通过多条语句来创建Table类型的数据
  •  
  • (1)、语法

     

  • SQL 代码   复制
  •    create function 函数名(参数)
    
       returns 表格变量名table (表格变量定义)
    
       [with {Encryption | Schemabinding }]
    
    as
    
       begin
    
        SQL语句
    
       end
    
    					
  •  

    (2)、与 内联表值函数的异同

     

  • [1]、多语句表值函数跟内联表值函数都是表值函数,它们返回的结果都是Table类型。
  • [2]、内联表值函数的返回结果是由函数体内的SELECT语句来决定。而多语句表值函数,则是需要指定具体的Table类型的结构。也就是说返回的Table,已经定义好要哪些字段返回。所以它能够支持多条语句的执行来创建Table数据
  • [3]、多句表格值函数包含多条SQL语句,至少有一条在表格变量中填上数据值
  • [4]、多句表格值函数最后一句是RETURN。告诉执行程序,多语句表值函数已经执行完成
  •  
  •  
  •  
  • (3)、简单实例
  •  
  •  
  • SQL 代码   复制
  • 
    -- =============================================
    -- Author:        <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:    <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
    
    						
  •  
  •  
  • (4)、调用方式
  •  
  • Select * from DemoFun()
  • 标签: