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

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

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