在数据库中的数据查询过程中,有时候需要用到游标。
考虑到游标在查询过程中是面向逐行的思维和我们查询的思维是面向集合的思维产生了歧义。
同样的,在性能上,游标会吃更多的内存,减少可用的并发,占用宽带,锁定资源。
在有些地方不能避开使用游标的地方,我采用了临时表的方式,来代替游标,这样也避免了游标每次打开关闭释放占用的资源。
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
******************************************************
在游标的使用过程中使用不当会导致锁堵塞。所以在必须使用游标的情况下,正确的使用游标。