sqlserver自定义函数的创建与调用

sqlserver中有系统提供的函数，像avg、sum、getdate()等，用户还可以自定义函数。

用户自定义的函数包括：标量函数和表值函数，其中标量函数和系统函数的用法一样，表值函数根据主体的定义方式又可分为内嵌函数和多语句函数。

下面一一介绍语法。

标量函数：

Create function 函数名（参数）
Returns 返回值数据类型
[with {Encryption | Schemabinding }]
[as]
begin
SQL语句(return变量)
End 
注：Schemabinding将函数绑定到它引用的对象上（注：函数一旦绑定，则不能删除、修改，除非删除绑定）

表值函数-内嵌函数：

create function 函数名（参数）
returns table 
[with {Encryption | Schemabinding }]
as 
return(一条SQL语句)

表值函数-多语句函数：

create function 函数名（参数）
   returns 表变量名 table (表变量定义)
   [with {Encryption | Schemabinding }]
as 
   begin
    SQL语句
   end 

下面介绍使用方法，使用前先创建几个表用于测试，表如下：

CREATE TABLE [dbo].[Classes](
    [ID] [int] IDENTITY(1,1) NOT NULL primary key,
    [ClassName] [nvarchar](50) NOT NULL,
    [CreateTime] [datetime] NOT NULL
    );

    CREATE TABLE [dbo].[Students](
    [ID] [int] IDENTITY(1,1) NOT NULL primary key,
    [Name] [nvarchar](50) NOT NULL,
    [ClassId] [int] NOT NULL,
    [Age] [int] NOT NULL,
    [CreateTime] [datetime] NOT NULL
    );

    CREATE TABLE [dbo].[Courses](
    [ID] [int] IDENTITY(1,1) NOT NULL primary key,
    [Name] [nvarchar](50) NOT NULL,
    [Credit] [float] NOT NULL
    );

    CREATE TABLE [dbo].[StuScores](
    [ID] [int] IDENTITY(1,1) NOT NULL primary key,
    [StuId] [int] NOT NULL,
    [CourseId] [int] NOT NULL,
    [Score] [int] NOT NULL
    );

例子如下：

--标量函数：返回某个班级的人数
create function F_GetSomeClassStuCount(@classId int)
returns int
as
begin
declare @rtnCount int
select @rtnCount=count(*) from Students where ClassId=@classId
return @rtnCount
end;

select dbo.F_GetSomeClassStuCount(1);

--表值函数-内嵌函数：返回某个班级的人员信息  注意此处不需begin-end包裹
create function F_GetSomeClassStruInfo(@classId int)
returns table
as 
return (select * from Students where ClassId=@classId);

select * from dbo.F_GetSomeClassStruInfo(1);

--表值函数-多语句函数：返回某个学生的成绩
create function F_GetSomStuScore(@stuName nvarchar(50))
returns @tmpTb table(
StuName nvarchar(50),
CourseName nvarchar(50),
Score int
)
as
begin
insert into @tmpTb
select s.Name as StuName,c.Name as CourseName,ss.Score
from StuScores ss
left join Students s on ss.StuId=s.ID
left join Courses c on ss.CourseId=c.ID
where s.Name=@stuName
return
end;

select * from F_GetSomStuScore('杨过')