1 use [nc] 2 go 3 /****** object: userdefinedfunction [dbo].[dict_url_channel] script date: 2019/5/25 16:40:17 ******/ 4 set ansi_nulls on 5 go 6 set quoted_identifier on 7 go 8 alter function [dbo].[dict_url_channel] 9 ( 10 @url varchar(max) --传入参数 11 ) 12 returns varchar(50) --返回参数类型 13 as 14 begin 15 declare @channel nvarchar(50) --定义变量 16 declare @temp varchar(250) 17 declare @charindex int 18 declare key_url_cursor cursor local --定义游标 19 for select key_url from information.dbo.url_to_渠道 order by seq --按照seq 遍历information.dbo.url_to_渠道 20 open key_url_cursor --打开游标 21 fetch next from key_url_cursor into @temp ----获取key_cursor的下一条数据，其中为字段赋值@temp 22 while @@fetch_status = 0 23 begin 24 select @charindex = charindex(@temp,@url) --判断@temp是否在@url中 25 if @charindex > 0 26 begin 27 select @channel = value_渠道 from information .dbo.url_to_渠道 where key_url = @temp; 28 break; 29 end 30 fetch next from key_url_cursor into @temp 31 end 32 close key_url_cursor --关闭游标 33 deallocate key_url_cursor --释放游标 34 if @channel is null 35 begin 36 select @channel='' 37 end 38 return @channel 39 end