業(yè)務系統(tǒng)需要一個關于合同狀態(tài)的報表，主要顯示合同的狀態(tài)，地區(qū)，合同客戶類型，合同金額，利息金額等信息.
在數(shù)據(jù)庫中存在4個表
crec01c,sysc01,sysc03d,crmc02分別是'合同主表','區(qū)域及業(yè)務伙伴','系統(tǒng)狀態(tài)代碼','法人信息表'
下面第一個存儲過程是我第一次寫的,執(zhí)行時間是5秒
------------------效率較差的存儲過程---------------------------
create table #tmptba --創(chuàng)建一個臨時表，用于儲存我們的結(jié)果
(
colid int identity(1,1) primary key clustered,
khlx varchar(20),
dq varchar(20),
ywhb varchar(40),
htzt varchar(20),
htbs int ,
htje numeric(13,2),
lxje numeric(13,2)
)
declare @bkhlx char(1), @ikhid int, @cywdbd char(10), @chtzt char(1), @njkje numeric(13,2), @iqx int, @nhtyll numeric(8,6) --for progress
declare @t_khlx varchar(20), @t_dq varchar(20), @t_ywhb varchar(40), @t_htzt varchar(20), @t_htbs int, @t_htje numeric(13,2), @t_lxje numeric(13,2)--for insert into #tmptba
--declare @index int
--set @index = 1
declare cur1 cursor for select bkhlx, ikhid, cywdbd, chtzt, njkje, iqx, nhtyll from crec01c
open cur1
fetch next from cur1 into @bkhlx, @ikhid, @cywdbd, @chtzt, @njkje, @iqx, @nhtyll
while @@fetch_status = 0
begin
if @bkhlx = '1'
begin
set @t_khlx = '自然人'
set @t_dq = (select vjgmc from sysc01 where cjgdm = left(@cywdbd,6))
end
else
begin
set @t_khlx = '法人'
set @t_dq = (select vjgmc from sysc01 where cjgdm = (select cbmdm from crmc02 where frid = @ikhid ) )
end
set @t_htbs = 1-- @index--合同筆數(shù)
set @t_ywhb = (select vjgmc from sysc01 where cjgdm = @cywdbd)--業(yè)務伙伴
set @t_htzt = (select vsjxc from sysc03d where czddm = 'htzt' and csjxm = @chtzt)--合同狀態(tài)
set @t_htje = @njkje
set @t_lxje = @njkje * @iqx * @nhtyll * 0.001
insert into #tmptba (khlx, dq, ywhb, htzt, htbs, htje, lxje) values (@t_khlx, @t_dq, @t_ywhb, @t_htzt, @t_htbs, @t_htje, @t_lxje)
--set @index 1
fetch next from cur1 into @bkhlx, @ikhid, @cywdbd, @chtzt, @njkje, @iqx, @nhtyll
end
close cur1
deallocate cur1
select * from #tmptba
go
------------------效率較高的存儲過程執(zhí)行時間是1秒---------------------------
create table #tmptbl --創(chuàng)建一個臨時表，用于儲存我們的結(jié)果
(
colid int identity(1,1) primary key clustered,
khlx varchar(20),
dq varchar(20),
ywhb varchar(40),
htzt varchar(20),
htbs int ,
htje numeric(13,2),
lxje numeric(13,2)
)
insert into #tmptbl select case when a.bkhlx = '1' then '自然人' else '法人' end as khlx, dq = (select vjgmc from sysc01 where cjgdm = left(a.cywdbd,6)), ywhb = (select vjgmc from sysc01 where cjgdm = a.cywdbd), htzt = (select vsjxc from sysc03d where czddm = 'htzt' and csjxm = a.chtzt), htbs = 1, htje = a.njkje, lxje = a.njkje * a.iqx * a.nhtyll * 0.001 from crec01c a where a.bkhlx = '1'
insert into #tmptbl select case when a.bkhlx = '1' then '自然人' else '法人' end as khlx, dq = (select vjgmc from sysc01 where cjgdm = (select cbmdm from crmc02 where frid = a.ikhid)), ywhb = '無', htzt = (select vsjxc from sysc03d where czddm = 'htzt' and csjxm = a.chtzt), htbs = 1, htje = a.njkje, lxje = a.njkje * a.iqx * a.nhtyll * 0.001 from crec01c a where a.bkhlx = '2'
select * from #tmptbl
go
可以看到，第二個存儲過程只用了兩條sql語句就完成了第一個存儲過程的工作。
