MS SQL 数据库游标的用法实例！

实例为根据商品分类更新商品代码。

DECLARE @sortcode varchar(20), @i int, @templetesortcode varchar(20) ,@goodscode varchar(20),@flowno char(3)

select  @templetesortcode = '', @i = 0,@flowno = ''

DECLARE c_base_goods CURSOR FOR SELECT goodscode,rtrim(goodssortcode) as goodssortcode FROM bak_goods where len(goodssortcode)=3 ORDER BY  goodssortcode,found_date

OPEN c_base_goods

FETCH next FROM c_base_goods

INTO @goodscode, @sortcode 

WHILE @@fetch_status = 0

BEGIN

   IF @sortcode = @templetesortcode  

        begin

           SELECT @i = @i+1

           SELECT @flowno = convert(char(3), @i)

           if @i < 10

              begin

                SELECT @flowno = '00'+@flowno

              end

           else if @i >= 10 and  @i < 100

              begin

                SELECT @flowno = '0'+@flowno

              end

           else if @i >= 100

              begin

                SELECT @flowno = @flowno

              end

           UPDATE bak_goods SET goodscode = @sortcode + @flowno  where goodscode = @goodscode                  

        end

   ELSE

       begin

        SET @templetesortcode = @sortcode

        UPDATE bak_goods SET  goodscode = @sortcode + '001'  where goodscode = @goodscode

        SET @i = 1

       end

   FETCH next FROM c_base_goods INTO @goodscode, @sortcode 

END

CLOSE c_base_goods

DEALLOCATE c_base_goods