用户定义函数分为:标量函数,内联表值函数,多语句表值函数。
1、标量函数
create table dbo.t
(id int not null primary key clustered,
mStr varchar(300)
)
create table dbo.tt
(main_id int not null,
v varchar(100) not null,
foreign key(main_id) references t(id)
)
insert into dbo.t(id)
values(1),
(2),
(3)
insert into dbo.tt(main_id,v)
values(1,'a'),
(1,'b'),
(2,'c'),
(2,'d'),
(2,'efg'),
(3,'higklmn')
--创建标量函数
create function dbo.mergeStr(@id int,@split varchar(10))
returns varchar(300)
as
begin
declare @str varchar(300);
set @str = '';
select @str = @str + v + @split
from dbo.tt
where main_id = @id
set @str = left(@str , len(@str) - LEN(@split) )
return @str --返回值
end
go
--使用函数
select t.id,
dbo.mergeStr(t.id,',')
from dbo.t
--通过函数更新
update dbo.t
set mstr = dbo.mergeStr(t.id,',')
2、内联表值函数
--内嵌表值函数,只包含一个return,不能包含其他语句
--注意:各视图或函数中的列名必须唯一。
create function dbo.display(@id int )
returns table
as
return(
with t
as
(
select t.id,
t.mstr,
m.v
from dbo.t
cross apply dbo.splitstr(t.mstr,',') m
where t.id = @id
)
select t.id,
t.mstr,
t.v as splitV,
tt.main_id,
tt.v
from t
inner join dbo.tt
on t.id = tt.main_id
and t.v = tt.v
)
go
--使用内联表值函数
select *
from dbo.display(1)
3、多语句表值函数
--创建多语句表值函数
create function dbo.splitStr(@str varchar(300),@split varchar(10))
returns @t table(v varchar(50))
as
begin
declare @temp varchar(50);
set @temp = '';
while charindex(@split,@str) > 0
begin
set @temp = LEFT(@str,CHARINDEX(@split,@str) - 1 );
insert into @t(v)
values(@temp);
set @str = STUFF(@str,1,charindex(@split,@str),'')
end
insert into @t(v)
values(@str)
return; --返回
end
go
--使用表值函数
select t.id,
t.v,
m.x
from dbo.t
cross apply dbo.splitStr(t.mstr,',')
4、函数的元数据
select o.name,
type,
type_desc
from sys.objects o
inner join sys.sql_modules s
on s.object_id = o.object_id
where type in ('fn','tf','if')
5、用户定义类型
--1.创建用户定义类型
create type dbo.user_defined_type
from varchar(20) not null
create table dbo.txt
(vid int not null,
v user_defined_type) --用户定义类型用在表列中
--用户定义类型用在存储过程的参数中
create procedure dbo.usp_txt(@v user_defined_type)
as
begin
select *
from dbo.txt
where v = @v
end
go
--2.定义表值用户定义类型,这样的类型是一个表
create type table_value_user_defined_type as table
(vid int ,
v user_defined_type
)
--这里的参数@t的类型是一个表
create procedure dbo.usp_tt_txt
(@t as table_value_user_defined_type readonly)
as
begin
insert into dbo.txt(vid,v)
select vid,
V
from @t
end
go
--注意这里必须定义与上面一样的表值类型
declare @tt as table_value_user_defined_type
insert into @tt
values(1,'a'),
(2,'b'),
(3,'c')
--传入参数是一个表,类型与存储过程中定义是一样的
exec dbo.usp_tt_txt @t = @tt
--3.用户定义类型的元数据
--有哪些列使用了用户定义类型
select t.name as typename,
OBJECT_NAME(c.object_id) as tablename,
c.name as columnname
from sys.types t
inner join sys.columns c
on c.user_type_id = t.user_type_id
where t.name = 'user_defined_type'
--表值类型
select *
from sys.objects
where type_desc = 'TYPE_TABLE'
--3.删除用户定义类
--要先删除引用用户定义类型的对象
drop procedure dbo.usp_tt_txt
drop table dbo.txt
drop procedure dbo.usp_txt
drop type dbo.table_value_user_defined_type
drop type dbo.user_defined_type