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

thinkhk

　　用户定义函数分为：标量函数，内联表值函数，多语句表值函数。
　　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