您的位置:首页 > 数据库 > 数据库管理

sql server 自定义函数

更多 2016/7/17 来源:数据库管理学习浏览量:1074
学习标签: sql server
本文导读:SQL Server中除了使用系统提供的函数外,用户还可以根据需要自定义函数。与编程语言中的函数类似,Microsoft 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()

 

收藏
141
很赞
334
您可能感兴趣