查询封存数据

在数据库数据非常庞大时，往往需要进行数据封存。但是根据具体情况需要，往往需要对封存后的数据进行查询，为了解决这个问题，特制定如下解决方案：

执行步骤：

1、更新主程序
2、修改相应的想要进行封存数据查询的单据的查询语句，使其支持查询封存的单据
3、新建单据再现抬头项sql、明细项sql 存储过程
4、将单据总体中单据再现设置抬头项、明细项语句为 exec “存储过程名” :djbh
 

例：
以220单据为例
1、更新主程序 v7 scms.exe
2、修改 销售出库单查询：
数据集合：

 

销售出库单查询sql:
select b.rq,a.djbh,c.danwbh,c.dwmch,，sum(a.je) as je,sum(a.she) as she,sum(a.hsje) as

hsje,b.bm,b.ywy,b.username
from ywmxk a,cwk b,mchk c
where a.djbh=b.djbh and b.dwbh=c.dwbh and a.djbh like 'XSA%' AND b.rq>=:start_rq and

b.rq<=:end_rq and c.danwbh like :ddwbh and a.chonghdjbh=''
group by a.djbh,b.rq,c.danwbh,c.dwmch,b.bm,b.ywy,b.username
union
select b.rq,a.djbh,c.danwbh,c.dwmch,
sum(a.je) as je,sum(a.she) as she,
sum(a.hsje) as hsje,b.bm,b.ywy,b.username
from ywmxk_fc a,cwk_fc b,mchk c --ywmxk_fc、cwk_fc (封存数据库)

where a.djbh=b.djbh and b.dwbh=c.dwbh and a.djbh like 'XSA%' AND b.rq>=:start_rq and

b.rq<=:end_rq and c.danwbh like :ddwbh and a.chonghdjbh=''
group by a.djbh,b.rq,c.danwbh,c.dwmch,b.bm,b.ywy,b.username


销售出库单明细sql:
select a.rq,a.djbh,c.spbh,c.spmch,d.dwmch,b.shl,b.dj,b.je,e.huowname
from cwk a,ywmxk b,spkfk c,mchk d,huoweizl e
where a.djbh=b.djbh and a.dwbh=d.dwbh and b.spid=c.spid and
b.hw=e.hw and a.djbh like 'XSA%' and b.chonghdjbh='' and
c.beactive<>'否' and a.rq>=:start_rq and a.rq<=:end_rq and
d.danwbh like :ddwbh
union
select a.rq,a.djbh,c.spbh,c.spmch,d.dwmch,b.shl,b.dj,b.je,e.huowname
from cwk_fc a,ywmxk_fc b,spkfk c,mchk d,huoweizl e
where a.djbh=b.djbh and a.dwbh=d.dwbh and b.spid=c.spid and
b.hw=e.hw and a.djbh like 'XSA%' and b.chonghdjbh='' and
c.beactive<>'否' and a.rq>=:start_rq and a.rq<=:end_rq and
d.danwbh like :ddwbh


3、新建存储过程 sbp_220_tt（提取单据再现抬头项），sbp_220_mx（提取单据再现明细项）
CREATE PROCEDURE sbp_220_tt
@djbh char(14)
AS
if EXISTS(select * from cwk where djbh=@djbh)
select a.*,b.*,c.* from cwk a,mchk b,zhbuzl c
where a.dwbh=b.dwbh and a.djbh=@djbh and a.jsfsid*=c.jsfsid
else
select a.*,b.*,c.* from cwk_fc a,mchk b,zhbuzl c
where a.dwbh=b.dwbh and a.jsfsid*=c.jsfsid and a.djbh=@djbh
GO



CREATE PROCEDURE sbp_220_mx
@djbh char(14)
AS
if EXISTS(select * from ywmxk where djbh=@djbh)
select a.*,1 as xuanze,b.*,c.* from ywmxk a,spkfk b,huoweizl c where
a.spid=b.spid and a.hw=c.hw and a.djbh=@djbh
order by a.dj_sort
else
select a.*,1 as xuanze,b.*,c.* from ywmxk_fc a,spkfk b,huoweizl c where
a.spid=b.spid and a.hw=c.hw and a.djbh=@djbh
GO

4、设置220单据的单据总体项如下：
单据再现设置抬头项：exec sbp_220_tt :djbh
单据再现设置明细项：exec sbp_220_mx :djbh