首页 > 代码库 > SQL 游标的应用

SQL 游标的应用

----------------SQL游标应用-----------------
if object_id(‘tempdb..#test0001‘) is not null drop table #test0001---------物料临时表
create table #test0001(ItemCode nvarchar(30)----物料代码
            ,SL DEC(19,3)----物料所需数量
            ,RowsID Bigint----用于循环物料
            )
if object_id(‘tempdb..#test0002‘) is not null drop table #test0002---------容器临时表
create table #test0002(Carton nvarchar(30)----容器代码
            ,ItemCode nvarchar(30)----物料代码
            ,CSL nvarchar(30)----容器里物料数量
            ,RowsID Bigint ----用于循环容器,相同物料不同的容器进行排序
            )
if object_id(‘tempdb..#test0003‘) is not null drop table #test0003---物料在各个容器中最终要的数量表
create table #test0003(ItemCode nvarchar(30)
            ,CarTon nvarchar(30)
            ,LastSL DEC(19,3)---在容器上所得数量
            )

insert into #test0001(ItemCode,SL,RowsID)
      values(‘A‘,5,1),(‘B‘,10,2)---添加物料A、B所需数量分别为5、10个,并给予物料排序

insert into #test0002(Carton,ItemCode,CSL,RowsID)
      values(‘Q‘,‘A‘,3,1),(‘T‘,‘A‘,5,2),(‘Q‘,‘B‘,5,1) ,(‘T‘,‘B‘,3,2),(‘S‘,‘B‘,5,3)---给容器添加物料以及数量并给予物料所在不同容器排序
      -----此时容器里的物料数为Q---A----3
      -------------------------T---A----5
      -------------------------Q---B----5
      -------------------------T---B----3
      -------------------------S---B----5

      -----则此时A物料在Q中获取3个、在T中取2个
      -----B物料在Q中取5个、在T中取3个、在S中取2个


declare @total_SL DEC(19,3)----临时物料总数量
declare @CT_SL DEC(19,3)----临时容器物料数量
declare @SL DEC(19,3)----临时物料数量

declare @test_cursor cursor------建立游标变量
declare @test_rowid bigint-------作为匹配游标变量

set @test_cursor = cursor for select RowsID from #test0001 ----设置游标绑定列

open @test_cursor--开启游标

fetch next from @test_cursor into @test_rowid--取第一个游标

while @@FETCH_STATUS = 0 ---一级循环
begin

  --循环主体
  select top 1
  @total_SL = temp01.SL
  from #test0001 temp01
  where temp01.RowsID = @test_rowid

  declare @test_cursor2 cursor
  declare @test_rowid2 bigint

  set @test_cursor2 = cursor for select temp02.RowsID --设置游标绑定列;此游标是在容器表中按相同物料不同容器进行排序的
  from #test0002 temp02
  inner join #test0001 temp01 on temp01.ItemCode=temp02.ItemCode
  where temp01.RowsID=@test_rowid

  --开启游标
  open @test_cursor2
  --取第一个游标
  fetch next from @test_cursor2 into @test_rowid2

  while @@FETCH_STATUS = 0 ------二级循环
  begin
    select top 1
    @CT_SL = temp02.CSL
    from #test0002 temp02
    inner join #test0001 temp01 on temp01.ItemCode=temp02.ItemCode
    where temp02.RowsID = @test_rowid2
    and temp01.RowsID = @test_rowid

    IF @CT_SL<@total_SL
    begin -----若容器中存货量小于物料总数量则取完容器内数量
    set @SL = @CT_SL
    set @total_SL = @total_SL-@SL
  end
  else
  begin
    set @SL = @total_SL
    set @total_SL = 0
  end


  -------------将循环每条记录插入#test0003表中-----------
  insert into #test0003(ItemCode
             ,CarTon
             ,LastSL
             )
        select temp01.ItemCode
            ,temp02.Carton
            ,@SL
        from #test0001 temp01
        inner join #test0002 temp02 on temp02.ItemCode=temp01.ItemCode
        where temp01.RowsID=@test_rowid
           and temp02.RowsID=@test_rowid2

        if @total_SL = 0 break;
        --循环取游标下一个值
        fetch next from @test_cursor2 into @test_rowid2
      end
      close @test_cursor2 --关闭二级游标
      deallocate @test_cursor2 --关闭二级游标


      --循环取游标下一个值
      fetch next from @test_cursor into @test_rowid
end
close @test_cursor--关闭一级游标
deallocate @test_cursor--关闭一级游标

 

结果:

select * from #test0001;  

技术分享     

select * from #test0002;

技术分享

select * from #test0003;

技术分享

 

总结:
SQL 的游标类似于C程序里的for循环,上面的例子相当于C程序中的冒泡排序,SQL游标适用于存储过程中一张表需要循环匹配另一张表时用游标比较方便。

SQL 游标的应用