SQL Server使用游标或临时表遍历数据

转载

mob604756edad5f 2016-06-06 07:24:00

方法一：使用游标（此方法适用所有情况，对标结构没有特殊要求。）

declare @ProductName nvarchar(50)
declare pcurr cursor for select ProductName from Products
open pcurr
fetch next from pcurr into @ProductName
while (@@fetch_status = 0)
begin
 print (@ProductName)
 fetch next from pcurr into @ProductName
end
close pcurr
deallocate pcurr

--给空的sort进行赋值从1开始按顺序排

declare @id int declare cursor1 cursor for

select ClassId from [content] group by ClassId

open cursor1

fetch next from cursor1 into @id

while @@fetch_status=0

begin

select ROW_NUMBER()over(order by id) as pid,id into #ttttt from [content] where classid=@id;

update [content] set sort = pid from #ttttt where #ttttt.id = [content].id;

drop table #ttttt;

fetch next from cursor1 into @id

end close cursor1

在关系数据库中，我们对于查询的思考是面向集合的。而游标打破了这一规则，游标使得我们思考方式变为逐行进行.

对于游标一些优化建议

如果能不用游标，尽量不要使用游标
用完用完之后一定要关闭和释放
尽量不要在大量数据上定义游标
尽量不要使用游标上更新数据
尽量不要使用insensitive, static和keyset这些参数定义游标
如果可以，尽量使用FAST_FORWARD关键字定义游标
如果只对数据进行读取，当读取时只用到FETCH NEXT选项，则最好使用FORWARD_ONLY参数

方法二：使用循环（此方法适用于表带有自动增加标识的字段）

declare @ProductName nvarchar(50)
declare @ProductID int
select @ProductID=min(ProductID) from Products
while @ProductID is not null
begin
 select @ProductName=ProductName from Products where
ProductID=@ProductID
 print(@ProductName);
 select @ProductID=min(ProductID) from Products where
ProductID>@ProductID
end

--删除临时表#Tmp
create table #Tmp  --创建临时表#Tmp
(
    ID   int IDENTITY (1,1)     not null, --创建列ID,并且每次新增一条记录就会加1
    WokNo                varchar(50),   
    primary key (ID)      --定义ID为临时表#Tmp的主键      
);

--declare @temp table   
--(   
--   [id] int IDENTITY(1,1),   
--   [Name] varchar(10)   
--)   

Select * from #Tmp    --查询临时表的数据
truncate table #Tmp  --清空临时表的所有数据和约束
相关例子：
Declare @Wokno Varchar(500)  --用来记录职工号
Declare @Str NVarchar(4000)  --用来存放查询语句
Declare @Count int  --求出总记录数      
Declare @i int
Set @i = 0 
Select @Count = Count(Distinct(Wokno)) from #Tmp
While @i < @Count 
    Begin
       Set @Str = 'Select top 1 @Wokno = WokNo from #Tmp Where id not in (Select top ' + Str(@i) + 'id from #Tmp)'
       Exec Sp_ExecuteSql @Str,N'@WokNo Varchar(500) OutPut',@WokNo Output
       Select @WokNo,@i  --一行一行把职工号显示出来
       Set @i = @i + 1
    End

--drop table #temp

USE Test_DBData;
GO
--修正表中REC_CreateBy,REC_ModifyBy
CREATE TABLE #temp
    (
      id INT IDENTITY(1, 1) ,
      tablename NVARCHAR(100)
    );
DECLARE @tablename NVARCHAR(100);
DECLARE @n INT;
DECLARE @count INT;
DECLARE @str NVARCHAR(4000);
  --用来存放查询语句
DECLARE @tableCreateBy NVARCHAR(150);
DECLARE @tableModifyBy NVARCHAR(150);
SELECT  @n = 1;
INSERT  #temp
        ( tablename
        )
        SELECT  name
        FROM    sysobjects

WHERE type = 'U '

AND (name <> 'Dim_Employee')

AND (name <> 'Fct_ChannelType')

AND (name <> 'Rel_TPOCommodityMessage')
　　　　AND (name LIKE 'Dim%' OR name LIKE 'Fct%' OR name LIKE 'Rel%');

SELECT  @count = @@rowcount;
WHILE @n <= @count
    BEGIN
        SELECT  @tablename = ( SELECT   tablename
                               FROM     #temp
                               WHERE    id = @n
                             );
        SET @tableCreateBy = @tablename + '.REC_CreateBy';
        SET @tableModifyBy = @tablename + '.REC_ModifyBy';
        SET @str = 'IF EXISTS ( SELECT  *
            FROM    ( SELECT    '+@tableCreateBy+' 
                      FROM      '+@tablename+'
                                INNER JOIN dbo.Dim_Employee ON '+@tableCreateBy+' = Dim_Employee.LoginName
                    ) tb)
    BEGIN
        UPDATE  '+@tablename+'
        SET     REC_CreateBy = Dim_Employee.EmployeeId
        FROM    '+@tablename+'
                INNER JOIN dbo.Dim_Employee ON '+@tableCreateBy+' = Dim_Employee.LoginName;
    END;    
    IF EXISTS ( SELECT  *
            FROM    ( SELECT    '+@tableModifyBy+'
                      FROM      '+@tablename+'
                                INNER JOIN dbo.Dim_Employee ON '+@tableModifyBy+' = Dim_Employee.LoginName
                    ) tb )
    BEGIN
        UPDATE  '+@tablename+'
        SET     REC_ModifyBy = Dim_Employee.EmployeeId
        FROM    '+@tablename+'
                INNER JOIN dbo.Dim_Employee ON '+@tableModifyBy+' = Dim_Employee.LoginName;
    END;    
    ';
        EXEC(@str);
        SELECT  @n = @n + 1;
        DELETE  FROM #temp
        WHERE   tablename = @tablename;
    END;
--删除临时表
IF OBJECT_ID(N'tempdb.dbo.#temp') IS NOT NULL
    BEGIN
        DROP TABLE #temp;
    END

1.分批更新数据库
declare @x int
set @x=1 
while(@x<=51) 
begin 
begin tran 
update UserFavorite set UserFavorite.firstpublishtime = product.lastpublishtime
from UserFavorite,product where UserFavorite.productid = product.id 
and UserFavorite.id between (@x-1)* 10000 and @x*10000
commit tran
set @x=@x+1 
WAITFOR DELAY '00:00:30'; --等待5秒
end