• sql游标


    在数据库中的数据查询过程中,有时候需要用到游标。

    考虑到游标在查询过程中是面向逐行的思维和我们查询的思维是面向集合的思维产生了歧义。

    同样的,在性能上,游标会吃更多的内存,减少可用的并发,占用宽带,锁定资源。

    在有些地方不能避开使用游标的地方,我采用了临时表的方式,来代替游标,这样也避免了游标每次打开关闭释放占用的资源。

    1.这是我用双重游标的语句:

    begin tran 
    declare @cus varchar(20)
    set @cus=:cus
    declare @cus1 varchar(20)
    set @cus1=:cus1
    declare @sdd datetime
    declare @edd datetime
    set @sdd=:sd
    set @edd=:ed
    declare @wh_yl varchar(20)
    create table mc_tmp_tb
    (ml_no varchar(50),tz_no varchar(50),prd_no varchar(50),est_itm integer,co integer,
    wh varchar(50),qty float,qty_over float,qty_lc float,cus_no varchar(20)
    )
    /*厂商遍历*/
    DECLARE cucus cursor for 
    select cus_no from cust where cus_no>=@cus and cus_no<=@cus1
    open cucus 
    declare @cusneed varchar(20)
    fetch next from cucus into @cusneed
    while @@fetch_status=0
    begin
    select @wh_yl=wh from my_wh where cus_no=@cusneed
    declare mycu cursor for
    select tf_ml.ml_no,tf_ml.tz_no ,tf_ml.prd_no ,tf_ml.est_itm ,count(*) as co from tf_ml 
    left join mf_ml on tf_ml.ml_no=mf_ml.ml_no
    where 
    mf_ml.cus_no=@cusneed
    and mf_ml.ml_dd>=@sdd
    and mf_ml.ml_dd<=@edd
    AND tf_ml.mlid='M4'
    --测试+领料单号
    group by tf_ml.ml_no,tf_ml.tz_no ,prd_no ,est_itm
    declare @ml_no varchar(50)
    declare @tz_no varchar(50)
    declare @prd_no varchar(50)
    declare @est_itm integer
    /*游标保存数据*/
    declare @co integer
    declare @wh varchar(20)
    declare @qty float
    declare @qty_over float
    declare @qty_lc float
    declare @cus_tb varchar(20)
    open mycu
    fetch next from mycu into @ml_no,@tz_no,@prd_no,@est_itm,@co
    /*查询出仓库*/
    while @@fetch_status=0
    begin
    /*查询仓库*/
    /*单独领余料仓或者没有领余料仓的料*/
    if(@co=1)
    begin
    select @wh=wh,@qty=tf_ml.qty,@qty_over=qty_over,@qty_lc=qty_lc,@cus_tb=cus_no 
    from tf_ml left join mf_ml on tf_ml.ml_no=mf_ml.ml_no
    AND tf_ml.mlid<>'M5' 
    where tf_ml.ml_no=@ml_no and tf_ml.tz_no=@tz_no and prd_no=@prd_no and est_itm=@est_itm
    end
    /*领了余料仓的料*/
    if(@co=2)
    begin
    select @wh=wh,@qty=tf_ml.qty,@qty_over=qty_over,@qty_lc=qty_lc,@cus_tb=cus_no 
    from tf_ml left join mf_ml on tf_ml.ml_no=mf_ml.ml_no 
    where tf_ml.ml_no=@ml_no and tf_ml.tz_no=@tz_no and prd_no=@prd_no and est_itm=@est_itm and wh<>@wh_yl
    and tf_ml.mlid<>'M5' 
    end
    /*插入数据表*/
    insert into mc_tmp_tb values (@ml_no,@tz_no,@prd_no,@est_itm,@co,@wh,@qty,@qty_over,@qty_lc,@cus_tb)
    fetch next from mycu into @ml_no,@tz_no,@prd_no,@est_itm,@co
    end
    close mycu
    deallocate mycu
    --select * from mc_tmp_tb
    fetch next from cucus into @cusneed
    end
    close cucus
    deallocate cucus
    select a.cus_no,a.ml_no,a.prd_no,a.wh,sum(a.qty) as 本地仓领出量 ,sum(a.qty_over) as 超发量,sum(a.qty_lc) as 余料仓使用量,case when a.wh=@wh_yl then sum(qty_lc) else sum(a.qty+a.qty_lc) end as 本次领出量小计,sum(a.qty+a.qty_over) as 本地仓出库量小计 ,sum(b.qty_rsv) as 托工需求量小计
    from mc_tmp_tb a
    left join tf_tw b on a.tz_no=b.tw_no and a.prd_no=b.prd_no and a.est_itm=b.itm
    
    group by a.ml_no,a.prd_no,a.wh,a.cus_no
    order by a.cus_no,a.ml_no,a.wh 
    drop table mc_tmp_tb
    if @@error=0
    begin
    commit
    end
    else
    begin
    rollback
    end

    2.这是我去掉游标,使用临时表的方式:

    begin tran                             
    declare @cus varchar(20)
    set @cus=:cus
    declare @cus1 varchar(20)
    set @cus1=:cus1
    declare @sdd datetime
    declare @edd datetime
    set @sdd=:sd
    set @edd=:ed
    declare @wh_yl varchar(20)
    --查询临时表保存数据
    declare @ml_no varchar(50)
    declare @tz_no varchar(50)
    declare @prd_no varchar(50)
    declare @est_itm integer
    declare @co integer
    --保存查询出的数据,用于插入数据
    declare @wh varchar(20)
    declare @qty float
    declare @qty_over float
    declare @qty_lc float
    declare @cus_tb varchar(20)
    declare @rowid1 int
    create table #t_mc_tmp_tb
    (ml_no varchar(50),tz_no varchar(50),prd_no varchar(50),est_itm integer,co integer,
    wh varchar(50),qty float,qty_over float,qty_lc float,cus_no varchar(20)
    )
    /*厂商遍历*/
    declare @cusneed varchar(20)
    select rowid=identity(int,1,1),flag=0,cus_no into #tmp_cus from cust where cus_no>=@cus and cus_no<=@cus1 and cus_no in (select cus_no from mf_ml where ml_dd>=@sdd and ml_dd<=@edd and mlid='M4')
    declare @rowid int
    select @rowid=min(rowid) from #tmp_cus where flag=0
    while @rowid is not null
    BEGIN
        select @cusneed=cus_no from #tmp_cus where rowid=@rowid
        --余料仓
        select @wh_yl=wh from my_wh where cus_no=@cusneed
        --保存领料信息
        --变临时表循环
        select rowid1=identity(int,1,1),flag1=0, tf_ml.ml_no,tf_ml.tz_no ,tf_ml.prd_no ,tf_ml.est_itm ,count(*) as co into #tmp_dat from tf_ml                               
                left join mf_ml on tf_ml.ml_no=mf_ml.ml_no
                where mf_ml.cus_no=@cusneed and mf_ml.ml_dd>=@sdd and mf_ml.ml_dd<=@edd AND tf_ml.mlid='M4' group by tf_ml.ml_no,tf_ml.tz_no ,prd_no ,est_itm
        
        select @rowid1=min(rowid1) from #tmp_dat where flag1=0
        --内部循环
        while @rowid1 is not null 
        begin
            --对应行的数据查询保存
            select @ml_no=ml_no,@tz_no=tz_no,@prd_no=prd_no,@est_itm=est_itm,@co=co from #tmp_dat where rowid1=@rowid1
            /*单独领余料仓或者没有领余料仓的料*/
            if(@co=1)
            begin
                select @wh=wh,@qty=tf_ml.qty,@qty_over=qty_over,@qty_lc=qty_lc,@cus_tb=cus_no 
                    from tf_ml left join mf_ml on tf_ml.ml_no=mf_ml.ml_no                                                
                    where tf_ml.ml_no=@ml_no and tf_ml.tz_no=@tz_no and prd_no=@prd_no and est_itm=@est_itm
                    AND tf_ml.mlid<>'M5'   
            end
            /*领了余料仓的料*/
            if(@co=2)
            begin
                select @wh=wh,@qty=tf_ml.qty,@qty_over=qty_over,@qty_lc=qty_lc,@cus_tb=cus_no 
                    from tf_ml left join mf_ml on tf_ml.ml_no=mf_ml.ml_no  
                    where tf_ml.ml_no=@ml_no and tf_ml.tz_no=@tz_no and prd_no=@prd_no and est_itm=@est_itm and wh<>@wh_yl
                    and tf_ml.mlid<>'M5'                                                  
            end
            /*插入数据表*/
            insert into #t_mc_tmp_tb values (@ml_no,@tz_no,@prd_no,@est_itm,@co,@wh,@qty,@qty_over,@qty_lc,@cus_tb)
            update #tmp_dat set flag1=1 where rowid1=@rowid1
            select @rowid1=min(rowid1) from #tmp_dat where flag1=0
        end
        --内部循环结束
        update #tmp_cus set flag=1 where rowid=@rowid
        select @rowid=min(rowid) from #tmp_cus where flag=0
        drop table #tmp_dat
    end
    select  a.cus_no,
    a.ml_no,
    a.prd_no,
    a.wh,sum(a.qty) as 本地仓领出量 ,
    sum(a.qty_over) as 超发量,sum(a.qty_lc) as 余料仓使用量,
    case when a.wh=@wh_yl then sum(qty_lc) else  sum(a.qty+a.qty_lc) end as 本次领出量小计,
    sum(a.qty+a.qty_over) as 本地仓出库量小计 ,
    sum(b.qty_rsv) as 托工需求量小计
        --collate Compatibility_198_804_30001
        from #t_mc_tmp_tb a
        left join tf_tw b on a.tz_no COLLATE Compatibility_198_804_30001 =b.tw_no 
        and a.prd_no COLLATE Compatibility_198_804_30001 =b.prd_no and a.est_itm =b.itm
        group by a.ml_no,a.prd_no,a.wh,a.cus_no
        order by a.cus_no,a.ml_no,a.wh                     
    drop table #t_mc_tmp_tb
    drop table #tmp_cus
    if @@error=0
    begin
        commit
    end
    else
    begin
        rollback
    end

    ******************************************************

    在游标的使用过程中使用不当会导致锁堵塞。所以在必须使用游标的情况下,正确的使用游标。

    **********初心不改,方得始终。
  • 相关阅读:
    TSQL Beginners Challenge 1
    SQL拾遗
    简易实体生成方式
    CTE-递归[2]
    编号处理
    行列转换/横转竖
    OUTPUT、Merge语句的使用
    关于SQL IO的一些资料
    对左值(lvalue)和右值(rvalue)的两种理解方式
    跨平台判断64位和32位开发的一些宏定义
  • 原文地址:https://www.cnblogs.com/salv/p/9922105.html
Copyright © 2020-2023  润新知