SQL Server游标的使用

1、 SQL Server中游标的使用

1.1  游标和游标的优点

在数据库中，游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条T_SQL 选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。

1.2 游标的种类

Microsoft SQL Server支持三种类型的游标：Transact_SQL 游标，API 服务器游标和客户游标。

1.2 游标的操作

使用游标有四种基本的步骤:声明游标、打开游标、提取数据、关闭游标。

2、 游标的使用

实例要求：使用游标获取前20位用户信息和购买记录。

DECLARE @UserId INT, @PetName VARCHAR(50), @BuyCound INT
DECLARE User_Cursor CURSOR	--声明游标
FOR
	SELECT TOP 20 UserId , PetName FROM UserInfo 
OPEN User_Cursor	--打开游标
FETCH NEXT FROM User_Cursor INTO @UserID,@PetName	--提取数据（将游标向下移1行，获取的数据保存到定义的变量@UserID,@PetName中）
WHILE (@@FETCH_STATUS = 0)	--判断是否成功获取数据
BEGIN
	SELECT @BuyCound = COUNT(*) FROM UserPrdBuy WHERE UserID = @UserID
	PRINT '用户编号：' + CONVERT(VARCHAR(10),@UserId) + ' 用户名称：' + @PetName + ' 购买数量：' + CONVERT(VARCHAR(10),@BuyCound)
	FETCH NEXT FROM User_Cursor INTO @UserID,@PetName	--将游标向下移1行，获取的数据保存到定义的变量@UserID,@PetName中
END
CLOSE User_Cursor	--关闭游标
DEALLOCATE User_Cursor	--删除游标

3、游标的案例

案例要求：同步库存系统的用户表到服务平台的用户表中

-----游标循环同步---------------------------------------------------------------
DECLARE @UserName NVARCHAR(50)
DECLARE @CompanyID INT
DECLARE @result int
DECLARE tnames_cursor CURSOR
FOR
	SELECT  TOP 10 UserID FROM UserAccount 
	WHERE UserName NOT IN
	(SELECT UserName FROM [172.168.16.22].MyDB.dbo.USerInfo)
	AND UserType=1
OPEN tnames_cursor
DECLARE @UserID sysname
FETCH NEXT FROM tnames_cursor INTO @UserID
WHILE (@@FETCH_STATUS = 0)
BEGIN
	SELECT @CompanyID = CompanyID FROM UserAccount WHERE UserID = @UserID
     exec proc_SynchronizeToHqew @result output,@UserID,@CompanyID
     print @result
     FETCH NEXT FROM tnames_cursor INTO @UserID
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
----------------------------------------------------------------------------------

----存储过程-------------------------------------------------------------------------------
if exists (select * from sysobjects where name='proc_SynchronizeToHqew')
drop procedure proc_SynchronizeToHqew
go
Create Procedure proc_SynchronizeToHqew
@result int output,
@sm_UserID int,
@sm_CompanyID int
as
--Begin Transaction
Declare @errorSum int
Declare @UserName NVARCHAR(100)
Declare @Password NVARCHAR(100)
Declare @RegisterTime datetime
Declare @companyName NVARCHAR(100)
Declare @Address NVARCHAR(100)
Declare @parentID int
Declare @HqewUserName NVARCHAR(100)
Declare @Info_UserID int 
Declare @RealName NVARCHAR(50)
Declare @Phone NVARCHAR(50)

set @errorSum =0
set @result =1
set @parentID =0
set @Info_UserID=0

--获取parentID
select @HqewUserName=HqewUserName,@companyName=CompanyName,@Address=Address from CompanyCustomer where CompanyID = @sm_CompanyID
select @parentID = UserID from [172.168.16.22].MyDB.dbo.USerInfo where UserName = @HqewUserName
if(@parentID<>0)
begin
	--获取用户信息并同步UserInfo表
	select  @UserName=UserName,@Password=UserPwd,@RegisterTime=CreateTime,@RealName=RealName,@Phone=phone from UserAccount where UserID = @sm_UserID 
	insert into [172.168.16.22].MyDB.dbo.USerInfo(UserName,Password,ParentUserID,UserGUID,FailureCount,LoginCount,LoginCount,NoLawWordsCount,UserScore,UserStatus,UserType,groupid,RegisterTime) 
	values(@UserName,@Password,@parentID,newid(),0,0,0,0,0,1,2,11,@RegisterTime)
	set @errorSum =@errorSum+@@error
	
	--获取用户信息并同步UserInfoAssis表
	select @Info_UserID = UserID from [172.168.16.22].MyDB.dbo.USerInfo where UserName = @UserName and parentUserID = @parentID
	set @errorSum =@errorSum+@@error
	if(@Info_UserID<>0)
	begin
		--获取用户信息并同步USerInfoAssis表
		insert into [172.168.16.22].MyDB.dbo.USerInfoAssis(UserID,CompanyName,Address,LinkMan,Nickname,Phone,FinancePassword,FinanceAmount,FinanceEnabledAmount,FinanceErrorTimes,FinanceFrozenAmount,ICAmount,IsValidateMail,PostedPromotionInfoAmount,PromotionInfoAmount,ISCPStatus,ISCPType,CommisionAccountToal,CommisionAccountFrozen,CommisionAccountAvailable,HonestyNum,HonestyLevel,HonestyStatus,IsEscrow,PersonGender,BuyType)
		values (@Info_UserID,@companyName,@Address,@RealName,@RealName,@Phone,@Password,0,0,0,0,0,0,0,0,4,0,0,0,0,-1,-1,0,1,1,4)		
		set @errorSum =@errorSum+@@error
	end
	Else
	begin
		set @result =0
	end 	
end

else
begin
	set @result =0
	print '失败同步! 子账号ID' + convert(varchar(10),@sm_UserID) + '找不到ParentID'
end

if(@errorSum =0 and @result=1 )
begin
	 print '成功同步!  子账号ID' + convert(varchar(10),@sm_UserID)
--	Commit Transaction
end
else
begin 
	print '失败同步! 子账号ID' + convert(varchar(10),@sm_UserID) + '数据回滚'
--	Rollback Transaction
end

go