sql server 游标的简单用法

最新推荐文章于 2024-08-08 08:00:00 发布

body13_13

最新推荐文章于 2024-08-08 08:00:00 发布

阅读量578

点赞数

分类专栏：数据库游标文章标签： sql server list 数据库 parameters date object

本文链接：https://blog.csdn.net/body13_13/article/details/7598365

版权

数据库同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

游标

1 篇文章 0 订阅

订阅专栏

USE [stureg]
GO
/****** Object:  StoredProcedure [dbo].[proc_tel_rule]    Script Date: 05/24/2012 14:38:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 2012-5-24
-- Description:	批量修改电话号码类型
-- =============================================
ALTER PROCEDURE [dbo].[proc_tel_rule] 
	-- Add the parameters for the stored procedure here
	--@tel_no varchar(20) --out, --输出参数
AS
BEGIN
	DECLARE @count int -- 计数
	------------- 定义游标1,三连号AAA或者以上(AAAA、AAAAA),第四位起的任意位置 ---------------
	declare @tel_no_1 varchar(20)
	declare tel_no_list_1 cursor for 
	select tel_no from
	(select tel_no,A = right(left(tel_no,4),1),B = right(left(tel_no,5),1),C = right(left(tel_no,6),1),D = right(left(tel_no,7),1),
	E = right(left(tel_no,8),1),F = right(left(tel_no,9),1),G = right(left(tel_no,10),1),H = right(left(tel_no,11),1)  
	from stureg.dbo.t_tel_info) TMP
	where (TMP.A = TMP.B AND TMP.A = TMP.C AND TMP.B = TMP.C) 
	OR (TMP.B = TMP.C AND TMP.B = TMP.D AND TMP.C = TMP.D)
	OR (TMP.C = TMP.D AND TMP.C = TMP.E AND TMP.D = TMP.E)
	OR (TMP.D = TMP.E AND TMP.D = TMP.F AND TMP.E = TMP.F)
	OR (TMP.E = TMP.F AND TMP.E = TMP.G AND TMP.F = TMP.G)
	OR (TMP.F = TMP.G AND TMP.F = TMP.H AND TMP.G = TMP.H)
	
	Open tel_no_list_1 -- 打开游标
    fetch next from tel_no_list_1 into @tel_no_1 -- 赋值
    SET @count = 0
    print '三连号AAA或者以上(AAAA、AAAAA),第四位起的任意位置'
    While (@@fetch_status<>-1)
		Begin
			fetch next from tel_no_list_1 into @tel_no_1
			print @tel_no_1
			-- 更新数据库
			UPDATE [stureg].[dbo].[t_tel_info] SET [aaa] = 1 WHERE tel_no = @tel_no_1
			set @count = @count + 1
		end
	print '总数:'  
	print @count 
	close tel_no_list_1; -- 关闭游标
	DEALLOCATE tel_no_list_1; -- 删除游标 
	
	------------ AABB,第四位起任意位置 -----------------------
	declare @tel_no_2 varchar(20)
	declare tel_no_list_2 cursor for 
	select tel_no from
	(select tel_no,A = right(left(tel_no,4),1),B = right(left(tel_no,5),1),C = right(left(tel_no,6),1),D = right(left(tel_no,7),1),
	E = right(left(tel_no,8),1),F = right(left(tel_no,9),1),G = right(left(tel_no,10),1),H = right(left(tel_no,11),1)  
	from stureg.dbo.t_tel_info) TMP
	where (TMP.A = TMP.B AND TMP.C = TMP.D AND TMP.B != TMP.C) 
	OR (TMP.B = TMP.C AND TMP.D = TMP.E AND TMP.C != TMP.D)
	OR (TMP.C = TMP.D AND TMP.E = TMP.F AND TMP.D != TMP.E)
	OR (TMP.D = TMP.E AND TMP.F = TMP.G AND TMP.E != TMP.F)
	OR (TMP.E = TMP.F AND TMP.G = TMP.H AND TMP.F != TMP.G)
	
	Open tel_no_list_2 -- 打开游标
    fetch next from tel_no_list_2 into @tel_no_2 -- 赋值
    SET @count = 0
    print 'AABB,第四位起任意位置'
    While (@@fetch_status<>-1)
		Begin
			fetch next from tel_no_list_2 into @tel_no_2
			print @tel_no_2
			-- 更新数据库
			UPDATE [stureg].[dbo].[t_tel_info] SET [aabb] = 1 WHERE tel_no = @tel_no_2
			set @count = @count + 1
		end
	print '总数:'  
	print @count 
	close tel_no_list_2; -- 关闭游标
	DEALLOCATE tel_no_list_2; -- 删除游标 
	
	------------ ABCD(ABCDE、ABCDEF)或以上,第四位起任意位置 ------
	declare @tel_no_3 varchar(20)
	declare tel_no_list_3 cursor for
	select tel_no from
	(select tel_no,A = right(left(tel_no,4),1),B = right(left(tel_no,5),1),C = right(left(tel_no,6),1),D = right(left(tel_no,7),1),
	E = right(left(tel_no,8),1),F = right(left(tel_no,9),1),G = right(left(tel_no,10),1),H = right(left(tel_no,11),1)  
	from stureg.dbo.t_tel_info) TMP
	where (TMP.A+1 = TMP.B AND TMP.B+1 = TMP.C AND TMP.C+1 = TMP.D)
	OR (TMP.B+1 = TMP.C AND TMP.C+1 = TMP.D AND TMP.D+1 = TMP.E)
	OR (TMP.C+1 = TMP.D AND TMP.D+1 = TMP.E AND TMP.E+1 = TMP.F)
	OR (TMP.D+1 = TMP.E AND TMP.E+1 = TMP.F AND TMP.F+1 = TMP.G)
	OR (TMP.E+1 = TMP.F AND TMP.F+1 = TMP.G AND TMP.G+1 = TMP.H)
	
	Open tel_no_list_3 -- 打开游标
    fetch next from tel_no_list_3 into @tel_no_3 -- 赋值
    SET @count = 0
    print 'ABCD(ABCDE、ABCDEF)或以上,第四位起任意位置'
    While (@@fetch_status<>-1)
		Begin
			fetch next from tel_no_list_3 into @tel_no_3
			print @tel_no_3
			-- 更新数据库
			UPDATE [stureg].[dbo].[t_tel_info] SET [abcd] = 1 WHERE tel_no = @tel_no_3
			set @count = @count + 1
		end
	print '总数:'  
	print @count 
	close tel_no_list_3; -- 关闭游标
	DEALLOCATE tel_no_list_3; -- 删除游标 
	
	------------ DCBA或以上,第四位起任意位置 --------
	declare @tel_no_4 varchar(20)
	declare tel_no_list_4 cursor for
	select tel_no from
	(select tel_no,A = right(left(tel_no,4),1),B = right(left(tel_no,5),1),C = right(left(tel_no,6),1),D = right(left(tel_no,7),1),
	E = right(left(tel_no,8),1),F = right(left(tel_no,9),1),G = right(left(tel_no,10),1),H = right(left(tel_no,11),1)  
	from stureg.dbo.t_tel_info) TMP
	where (TMP.A-1 = TMP.B AND TMP.B-1 = TMP.C AND TMP.C-1 = TMP.D)
	OR (TMP.B-1 = TMP.C AND TMP.C-1 = TMP.D AND TMP.D-1 = TMP.E)
	OR (TMP.C-1 = TMP.D AND TMP.D-1 = TMP.E AND TMP.E-1 = TMP.F)
	OR (TMP.D-1 = TMP.E AND TMP.E-1 = TMP.F AND TMP.F-1 = TMP.G)
	OR (TMP.E-1 = TMP.F AND TMP.F-1 = TMP.G AND TMP.G-1 = TMP.H)
	
	Open tel_no_list_4 -- 打开游标
    fetch next from tel_no_list_4 into @tel_no_4 -- 赋值
    SET @count = 0
    print 'ABCD(ABCDE、ABCDEF)或以上,第四位起任意位置'
    While (@@fetch_status<>-1)
		Begin
			fetch next from tel_no_list_4 into @tel_no_4
			print @tel_no_4
			-- 更新数据库
			UPDATE [stureg].[dbo].[t_tel_info] SET [dcba] = 1 WHERE tel_no = @tel_no_4
			set @count = @count + 1
		end
	print '总数:'  
	print @count 
	close tel_no_list_4; -- 关闭游标
	DEALLOCATE tel_no_list_4; -- 删除游标 
	
END