【SQL Server学习笔记】SQL Server 用户定义函数、用户定义类型

用户定义函数分为：标量函数，内联表值函数，多语句表值函数。

 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