SQL中游标的使用示例

 1       declare @email_source varchar(MAX);                       --1.原始发件人字段
 2       declare @key_name varchar(50);                            --2.我方卷号或客户代码            
 3             declare c_cur cursor for select distinct vc_your_email,vc_our_ref_or_code from #ip_special_email_total where vc_your_email like '%@%' order by vc_our_ref_or_code;
 4             open c_cur;
 5             fetch next from c_cur into @email_source,@key_name;
 6         while (@@FETCH_STATUS = 0)
 7           begin              
 8             declare @split_str varchar(50)    --分隔符
 9             declare @pos int                  --当前位置
10             declare @split_len int            --分隔符长度
11             declare @split_index int          --最近的分隔符位置
12             declare @split_last_index int     --上次分隔符位置
13             declare @avc_email varchar(500)
14               
15             set @split_str = ';'              --指定分隔符
16             set @pos = 1
17             set @split_len = len(@split_str)
18             set @split_index = 0
19             set @split_last_index = 0
20               
21             while (@pos <= len(@email_source)) 
22               begin     
23                 if @split_index > 0 set @split_last_index = @split_index
24                 set @split_index = charindex(@split_str, @email_source, @pos)
25                 set @pos = @pos + @split_len
26                 if @pos = len(@email_source) set @split_index = @pos + @split_len
27                 if @split_index > @split_last_index
28                   begin
29                     set @avc_email = substring(@email_source,(@split_last_index + @split_len),(@split_index - @split_last_index - @split_len))
30                     if isnull(@avc_email,'')<>'' insert #ip_single_email_total select @avc_email,@key_name
31                   end                   
32               end
33             fetch next from c_cur into @email_source,@key_name;
34           end
35             close c_cur
36             deallocate c_cur

 

posted @ 2017-10-09 13:33  潮崖客  阅读(666)  评论(0编辑  收藏  举报