当前位置:坤哥网-kungge-sqlserver自定义函数的创建与调用

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

2015/7/30 22:45:00 坤哥网阅读(1416) 评论(0)

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

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

下面一一介绍语法。

 

标量函数:

1 Create function 函数名(参数)
2 Returns 返回值数据类型
3 [with {Encryption | Schemabinding }]
4 [as]
5 begin
6 SQL语句(return变量)
7 End 
8 注:Schemabinding将函数绑定到它引用的对象上(注:函数一旦绑定,则不能删除、修改,除非删除绑定)
View Code

表值函数-内嵌函数:

1 create function 函数名(参数)
2 returns table 
3 [with {Encryption | Schemabinding }]
4 as 
5 return(一条SQL语句)
View Code

表值函数-多语句函数:

1 create function 函数名(参数)
2    returns 表变量名 table (表变量定义)
3    [with {Encryption | Schemabinding }]
4 as 
5    begin
6     SQL语句
7    end 
View Code

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

 1 CREATE TABLE [dbo].[Classes](
 2     [ID] [int] IDENTITY(1,1) NOT NULL primary key,
 3     [ClassName] [nvarchar](50) NOT NULL,
 4     [CreateTime] [datetime] NOT NULL
 5     );
 6 
 7     CREATE TABLE [dbo].[Students](
 8     [ID] [int] IDENTITY(1,1) NOT NULL primary key,
 9     [Name] [nvarchar](50) NOT NULL,
10     [ClassId] [int] NOT NULL,
11     [Age] [int] NOT NULL,
12     [CreateTime] [datetime] NOT NULL
13     );
14 
15     CREATE TABLE [dbo].[Courses](
16     [ID] [int] IDENTITY(1,1) NOT NULL primary key,
17     [Name] [nvarchar](50) NOT NULL,
18     [Credit] [float] NOT NULL
19     );
20 
21     CREATE TABLE [dbo].[StuScores](
22     [ID] [int] IDENTITY(1,1) NOT NULL primary key,
23     [StuId] [int] NOT NULL,
24     [CourseId] [int] NOT NULL,
25     [Score] [int] NOT NULL
26     );
View Code

例子如下:

 1 --标量函数:返回某个班级的人数
 2 create function F_GetSomeClassStuCount(@classId int)
 3 returns int
 4 as
 5 begin
 6 declare @rtnCount int
 7 select @rtnCount=count(*) from Students where ClassId=@classId
 8 return @rtnCount
 9 end;
10 
11 select dbo.F_GetSomeClassStuCount(1);
12 
13 --表值函数-内嵌函数:返回某个班级的人员信息  注意此处不需begin-end包裹
14 create function F_GetSomeClassStruInfo(@classId int)
15 returns table
16 as 
17 return (select * from Students where ClassId=@classId);
18 
19 select * from dbo.F_GetSomeClassStruInfo(1);
20 
21 --表值函数-多语句函数:返回某个学生的成绩
22 create function F_GetSomStuScore(@stuName nvarchar(50))
23 returns @tmpTb table(
24 StuName nvarchar(50),
25 CourseName nvarchar(50),
26 Score int
27 )
28 as
29 begin
30 insert into @tmpTb
31 select s.Name as StuName,c.Name as CourseName,ss.Score
32 from StuScores ss
33 left join Students s on ss.StuId=s.ID
34 left join Courses c on ss.CourseId=c.ID
35 where s.Name=@stuName
36 return
37 end;
38 
39 select * from F_GetSomStuScore('杨过')
View Code


 

分类: SqlServer

有话要说? =>【不用注册,直接登录】,然后刷新本页面来发表您的观点(●'◡'●)