千万数据查询时基本上就死了，大家帮看看怎样优化这个存储过程。
================================================
CREATE OR REPLACE PROCEDURE "SP_BATCHINSELLSTORAGE"
(
p_StartDate in Varchar2,
p_EndDate in varchar2,
p_ItemId in int,
p_ProBigClassId in int,
p_ProSmallClassId in int,
p_ProNameId in int,
p_ProSpecId in int,
p_BalanceType in int,
p_wh in int,
p_subwh in int,
p_sp in int,
p_OwnerShip In int,
p_Cause In int,
p_Major in int,
p_WareHouseType in int,
p_HtCode in varchar2,
p_InvoiceCode in varchar2,
p_CompanyId in int,
p_CustomerId in int,
p_pageIndex In Int,
p_pageCount In Int,
p_totalRowCount Out Int,
p_result out PG_DATATABLE.DataTableList)
Is
p_BeginDate date;
p_FinishDate date;
OptDate1 date;
preRowIndex Int;
nextRowIndex Int;
Begin
p_BeginDate:=to_Date(p_StartDate,'YYYY-MM-DD');
p_FinishDate:=to_Date(p_EndDate,'YYYY-MM-DD')+1;
preRowIndex:=(p_pageIndex-1)*p_pageCount+1;
nextRowIndex:=preRowIndex+p_pageCount;
--计算查询结束时间前的
OptDate1:=to_Date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss');
insert into t_JXC_TEMPTABLE3 select ItemId,StorePartId,ProSpecId,BalanceId,Ph,ProStatusId,0,0,0,0,0,0,0,0,0,0,0,0,0,0,sum((InNum-ThNum)-(OutNum-TkNum)+(XDrNum-
XDcNum + CDrNum-CDcNum)),sum((InPrice - ThPrice)-(OutPrice - TkPrice)+ (XDrPrice-XDcPrice + CDrPrice-CDcPrice)),OptDate1,CompanyId From v_JXC
Where HsDate<p_FinishDate and (CompanyId=p_CompanyId or p_CompanyId=0)
group by ItemId,StorePartId,ProSpecId,BalanceId,Ph,ProStatusId,CompanyId;
--计算查询时间段内的
Update t_JXC_TEMPTABLE3 set (InNum,InPrice,OutNum,OutPrice,DrNum,DrPrice,DrNum1,DrPrice1,DcNum,DcPrice,DcNum1,DcPrice1)=(Select nvl(sum(v_jxc.InNum),0)-nvl(sum(v_jxc.ThNum),0),
nvl(sum(v_jxc.InPrice),0)-nvl(sum(v_jxc.ThPrice),0),nvl(sum(v_jxc.OutNum),0)-nvl(sum(v_jxc.TkNum),0),nvl(sum(v_jxc.OutPrice),0)-nvl(sum(v_jxc.TkPrice),0),nvl(sum(v_jxc.XDrNum),0),nvl(sum(v_jxc.XDrPrice),0),nvl(sum(v_jxc.CDrNum),0),nvl(sum(v_jxc.CDrPrice),0),
nvl(sum(v_jxc.XDcNum),0),nvl(sum(v_jxc.XDcPrice),0),nvl(sum(v_Jxc.CDcNum),0),nvl(sum(v_jxc.CDcPrice),0)
from v_jxc where (v_jxc.CompanyId=p_CompanyId or p_CompanyId=0)
and v_jxc.HsDate>=p_BeginDate
and v_jxc.HsDate<p_FinishDate
and t_JXC_TEMPTABLE3.Itemid=v_jxc.ItemId and t_JXC_TEMPTABLE3.Storepartid=v_jxc.StorePartId and
t_JXC_TEMPTABLE3.Prospecid=v_jxc.ProSpecId and t_JXC_TEMPTABLE3.Balanceid=v_jxc.BalanceId
and t_JXC_TEMPTABLE3.Ph=v_jxc.Ph and t_JXC_TEMPTABLE3.prostatusid=v_jxc.ProStatusId
--group by ItemId,StorePartId,ProSpecId,BalanceId,Ph,ProStatusId,CompanyId
)
where t_jxc_temptable3.optdate=OptDate1;--更新出期末
Update t_JXC_TEMPTABLE3 set QcNum=QmNum - ((InNum-OutNum)+DrNum-DcNum+DrNum1-DcNum1),
QcPrice=QmPrice - ((InPrice-OutPrice)+(DrPrice-DcPrice+DrPrice1-DcPrice1)) where t_jxc_temptable3.optdate=OptDate1;--清除全为0的记录
Delete From t_JXC_TEMPTABLE3 Where QcNum=0 and QcPrice=0 and InNum=0 and InPrice=0 and OutNum = 0 and OutPrice = 0 and DrNum=0 and DrPrice=0 and
DcNum = 0 and DcPrice=0 and QmNum=0 and QmPrice = 0 and DrNum1=0 and DrPrice1=0 and DcNum1=0 and DcPrice1=0 and OptDate=OptDate1;
--返回数据集
select count(*) into p_totalRowCount From t_JXC_TEMPTABLE3 t
left join v_proinfo on t.prospecid = v_proinfo.specId
left join v_SortPart on t.storepartid=v_SortPart.storeid
left join t_base_Item on t.ItemId=t_base_Item.PKId
left join t_dic on t.balanceid=t_dic.pkid and t_dic.dicheaderid=18
inner join t_base_ownership on t_base_Item.Ownershipid=t_base_ownership.pkid
inner join t_base_major on t_base_item.majorid=t_base_major.pkid
inner join t_base_cause on t_base_item.causeid=t_base_cause.pkid
left join v_prohtinfo on t.ph=v_prohtinfo.Ph
left join t_base_customer on v_prohtinfo.Supplierid=t_base_customer.pkid
Where (t.ItemId = p_ItemId or p_ItemId = 0) and
(v_SortPart.WareHouseId = p_wh or p_wh = 0) and
(v_SortPart.WareHouseTypeId=p_WareHouseType or p_WareHouseType=0) and
(v_SortPart.subwarehouseid = p_subwh or p_subwh = 0) and
(v_SortPart.storeid = p_sp or p_sp = 0) and
(v_proinfo.probigclassid=p_ProBigClassId or p_ProBigClassId=0) and
(v_proinfo.smallclassid=p_ProSmallClassId or p_ProSmallClassId=0) and
(v_proinfo.pronameid=p_ProNameId or p_ProNameId=0) and
(v_Proinfo.specId=p_ProSpecId OR p_ProSpecId=0) and
(t_base_Item.Ownershipid = p_OwnerShip or p_OwnerShip = 0) and
(t_base_Item.CauseId = p_Cause or p_Cause= 0) and
(t_base_Item.MajorId = p_Major or p_Major = 0) and
(t_base_customer.pkid=p_CustomerId or p_CustomerId=0)and
(t.BalanceId = p_BalanceType or p_BalanceType = 0) and
(t.CompanyId=p_CompanyId or p_CompanyId=0) and
(v_prohtinfo.Htno like '%'||p_HtCode||'%' or p_HtCode is null) and
(v_prohtinfo.Invoiceno like '%'||p_InvoiceCode||'%' or p_InvoiceCode is null) and
t.OptDate=OptDate1;
Open p_result For select * from
(Select RowNum as rn,t.ItemId,t.storepartid,t.ph,t.BalanceId,t.QcNum,t.QcPrice,
t.InNum,t.InPrice,t.OutNum,t.OutPrice,t.DrNum,t.DrPrice,t.DrNum1,t.DrPrice1,t.DcNum,t.DcPrice,t.DcPrice1,t.DcNum1,t.QmNum,t.QmPrice,t_base_Item.ItemCode||'-'||t_base_Item.ItemName as ItemName,v_proinfo.bcname as BCName,v_proinfo.scname as SCName,v_proinfo.proname as ProName,
v_proinfo.specname as SpecName,v_ProInfo.unitname as UnitName,v_sortpart.whname||'/'||v_sortpart.subwhname||'/'||v_sortpart.storepartname as StorePartName,
t_base_customer.cstname as CstName,v_prohtinfo.Htno as HtCode,v_prohtinfo.Invoiceno as InvoiceCode,t_dic.dicname as BalanceType,t_base_ownership.osname as OwnerShipName,
t_base_cause.causename as CauseName,t_base_major.majorname as MajorName,v_prohtinfo.OrderCode,v_Prohtinfo.HsDate,v_Proinfo.bccode||'-'||v_Proinfo.sccode||'-'||v_proinfo.procode||'-'||v_proinfo.speccode as ProCode
From t_JXC_TEMPTABLE3 t
left join v_proinfo on t.prospecid = v_proinfo.specId
left join v_SortPart on t.storepartid=v_SortPart.storeid
left join t_base_Item on t.ItemId=t_base_Item.PKId
left join t_dic on t.balanceid=t_dic.pkid and t_dic.dicheaderid=18
inner join t_base_ownership on t_base_Item.Ownershipid=t_base_ownership.pkid
inner join t_base_major on t_base_item.majorid=t_base_major.pkid
inner join t_base_cause on t_base_item.causeid=t_base_cause.pkid
left join v_prohtinfo on t.ph=v_prohtinfo.Ph
left join t_base_customer on v_prohtinfo.Supplierid=t_base_customer.pkid
Where (t.ItemId = p_ItemId or p_ItemId = 0) and
(v_SortPart.WareHouseId = p_wh or p_wh = 0) and
(v_SortPart.WareHouseTypeId=p_WareHouseType or p_WareHouseType=0) and
(v_SortPart.subwarehouseid = p_subwh or p_subwh = 0) and
(v_SortPart.storeid = p_sp or p_sp = 0) and
(v_proinfo.probigclassid=p_ProBigClassId or p_ProBigClassId=0) and
(v_proinfo.smallclassid=p_ProSmallClassId or p_ProSmallClassId=0) and
(v_proinfo.pronameid=p_ProNameId or p_ProNameId=0) and
(v_Proinfo.specId=p_ProSpecId OR p_ProSpecId=0) and
(t_base_Item.Ownershipid = p_OwnerShip or p_OwnerShip = 0) and
(t_base_Item.CauseId = p_Cause or p_Cause= 0) and
(t_base_Item.MajorId = p_Major or p_Major = 0) and
(t_base_customer.pkid=p_CustomerId or p_CustomerId=0)and
(t.BalanceId = p_BalanceType or p_BalanceType = 0) and
(t.CompanyId=p_CompanyId or p_CompanyId=0) and
(v_prohtinfo.Htno like '%'||p_HtCode||'%' or p_HtCode is null) and
(v_prohtinfo.Invoiceno like '%'||p_InvoiceCode||'%' or p_InvoiceCode is null) and
t.OptDate=OptDate1
order by t_base_item.ItemName,v_proinfo.BCName,v_proinfo.SCName,v_proinfo.ProName,v_proinfo.SpecName)
Where (rn>=preRowIndex And rn<nextRowIndex);--清空表
Delete from t_JXC_TEMPTABLE3 where OptDate=OptDate1;End SP_BATCHINSELLSTORAGE;
=====================================================================================
================================================
CREATE OR REPLACE PROCEDURE "SP_BATCHINSELLSTORAGE"
(
p_StartDate in Varchar2,
p_EndDate in varchar2,
p_ItemId in int,
p_ProBigClassId in int,
p_ProSmallClassId in int,
p_ProNameId in int,
p_ProSpecId in int,
p_BalanceType in int,
p_wh in int,
p_subwh in int,
p_sp in int,
p_OwnerShip In int,
p_Cause In int,
p_Major in int,
p_WareHouseType in int,
p_HtCode in varchar2,
p_InvoiceCode in varchar2,
p_CompanyId in int,
p_CustomerId in int,
p_pageIndex In Int,
p_pageCount In Int,
p_totalRowCount Out Int,
p_result out PG_DATATABLE.DataTableList)
Is
p_BeginDate date;
p_FinishDate date;
OptDate1 date;
preRowIndex Int;
nextRowIndex Int;
Begin
p_BeginDate:=to_Date(p_StartDate,'YYYY-MM-DD');
p_FinishDate:=to_Date(p_EndDate,'YYYY-MM-DD')+1;
preRowIndex:=(p_pageIndex-1)*p_pageCount+1;
nextRowIndex:=preRowIndex+p_pageCount;
--计算查询结束时间前的
OptDate1:=to_Date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss');
insert into t_JXC_TEMPTABLE3 select ItemId,StorePartId,ProSpecId,BalanceId,Ph,ProStatusId,0,0,0,0,0,0,0,0,0,0,0,0,0,0,sum((InNum-ThNum)-(OutNum-TkNum)+(XDrNum-
XDcNum + CDrNum-CDcNum)),sum((InPrice - ThPrice)-(OutPrice - TkPrice)+ (XDrPrice-XDcPrice + CDrPrice-CDcPrice)),OptDate1,CompanyId From v_JXC
Where HsDate<p_FinishDate and (CompanyId=p_CompanyId or p_CompanyId=0)
group by ItemId,StorePartId,ProSpecId,BalanceId,Ph,ProStatusId,CompanyId;
--计算查询时间段内的
Update t_JXC_TEMPTABLE3 set (InNum,InPrice,OutNum,OutPrice,DrNum,DrPrice,DrNum1,DrPrice1,DcNum,DcPrice,DcNum1,DcPrice1)=(Select nvl(sum(v_jxc.InNum),0)-nvl(sum(v_jxc.ThNum),0),
nvl(sum(v_jxc.InPrice),0)-nvl(sum(v_jxc.ThPrice),0),nvl(sum(v_jxc.OutNum),0)-nvl(sum(v_jxc.TkNum),0),nvl(sum(v_jxc.OutPrice),0)-nvl(sum(v_jxc.TkPrice),0),nvl(sum(v_jxc.XDrNum),0),nvl(sum(v_jxc.XDrPrice),0),nvl(sum(v_jxc.CDrNum),0),nvl(sum(v_jxc.CDrPrice),0),
nvl(sum(v_jxc.XDcNum),0),nvl(sum(v_jxc.XDcPrice),0),nvl(sum(v_Jxc.CDcNum),0),nvl(sum(v_jxc.CDcPrice),0)
from v_jxc where (v_jxc.CompanyId=p_CompanyId or p_CompanyId=0)
and v_jxc.HsDate>=p_BeginDate
and v_jxc.HsDate<p_FinishDate
and t_JXC_TEMPTABLE3.Itemid=v_jxc.ItemId and t_JXC_TEMPTABLE3.Storepartid=v_jxc.StorePartId and
t_JXC_TEMPTABLE3.Prospecid=v_jxc.ProSpecId and t_JXC_TEMPTABLE3.Balanceid=v_jxc.BalanceId
and t_JXC_TEMPTABLE3.Ph=v_jxc.Ph and t_JXC_TEMPTABLE3.prostatusid=v_jxc.ProStatusId
--group by ItemId,StorePartId,ProSpecId,BalanceId,Ph,ProStatusId,CompanyId
)
where t_jxc_temptable3.optdate=OptDate1;--更新出期末
Update t_JXC_TEMPTABLE3 set QcNum=QmNum - ((InNum-OutNum)+DrNum-DcNum+DrNum1-DcNum1),
QcPrice=QmPrice - ((InPrice-OutPrice)+(DrPrice-DcPrice+DrPrice1-DcPrice1)) where t_jxc_temptable3.optdate=OptDate1;--清除全为0的记录
Delete From t_JXC_TEMPTABLE3 Where QcNum=0 and QcPrice=0 and InNum=0 and InPrice=0 and OutNum = 0 and OutPrice = 0 and DrNum=0 and DrPrice=0 and
DcNum = 0 and DcPrice=0 and QmNum=0 and QmPrice = 0 and DrNum1=0 and DrPrice1=0 and DcNum1=0 and DcPrice1=0 and OptDate=OptDate1;
--返回数据集
select count(*) into p_totalRowCount From t_JXC_TEMPTABLE3 t
left join v_proinfo on t.prospecid = v_proinfo.specId
left join v_SortPart on t.storepartid=v_SortPart.storeid
left join t_base_Item on t.ItemId=t_base_Item.PKId
left join t_dic on t.balanceid=t_dic.pkid and t_dic.dicheaderid=18
inner join t_base_ownership on t_base_Item.Ownershipid=t_base_ownership.pkid
inner join t_base_major on t_base_item.majorid=t_base_major.pkid
inner join t_base_cause on t_base_item.causeid=t_base_cause.pkid
left join v_prohtinfo on t.ph=v_prohtinfo.Ph
left join t_base_customer on v_prohtinfo.Supplierid=t_base_customer.pkid
Where (t.ItemId = p_ItemId or p_ItemId = 0) and
(v_SortPart.WareHouseId = p_wh or p_wh = 0) and
(v_SortPart.WareHouseTypeId=p_WareHouseType or p_WareHouseType=0) and
(v_SortPart.subwarehouseid = p_subwh or p_subwh = 0) and
(v_SortPart.storeid = p_sp or p_sp = 0) and
(v_proinfo.probigclassid=p_ProBigClassId or p_ProBigClassId=0) and
(v_proinfo.smallclassid=p_ProSmallClassId or p_ProSmallClassId=0) and
(v_proinfo.pronameid=p_ProNameId or p_ProNameId=0) and
(v_Proinfo.specId=p_ProSpecId OR p_ProSpecId=0) and
(t_base_Item.Ownershipid = p_OwnerShip or p_OwnerShip = 0) and
(t_base_Item.CauseId = p_Cause or p_Cause= 0) and
(t_base_Item.MajorId = p_Major or p_Major = 0) and
(t_base_customer.pkid=p_CustomerId or p_CustomerId=0)and
(t.BalanceId = p_BalanceType or p_BalanceType = 0) and
(t.CompanyId=p_CompanyId or p_CompanyId=0) and
(v_prohtinfo.Htno like '%'||p_HtCode||'%' or p_HtCode is null) and
(v_prohtinfo.Invoiceno like '%'||p_InvoiceCode||'%' or p_InvoiceCode is null) and
t.OptDate=OptDate1;
Open p_result For select * from
(Select RowNum as rn,t.ItemId,t.storepartid,t.ph,t.BalanceId,t.QcNum,t.QcPrice,
t.InNum,t.InPrice,t.OutNum,t.OutPrice,t.DrNum,t.DrPrice,t.DrNum1,t.DrPrice1,t.DcNum,t.DcPrice,t.DcPrice1,t.DcNum1,t.QmNum,t.QmPrice,t_base_Item.ItemCode||'-'||t_base_Item.ItemName as ItemName,v_proinfo.bcname as BCName,v_proinfo.scname as SCName,v_proinfo.proname as ProName,
v_proinfo.specname as SpecName,v_ProInfo.unitname as UnitName,v_sortpart.whname||'/'||v_sortpart.subwhname||'/'||v_sortpart.storepartname as StorePartName,
t_base_customer.cstname as CstName,v_prohtinfo.Htno as HtCode,v_prohtinfo.Invoiceno as InvoiceCode,t_dic.dicname as BalanceType,t_base_ownership.osname as OwnerShipName,
t_base_cause.causename as CauseName,t_base_major.majorname as MajorName,v_prohtinfo.OrderCode,v_Prohtinfo.HsDate,v_Proinfo.bccode||'-'||v_Proinfo.sccode||'-'||v_proinfo.procode||'-'||v_proinfo.speccode as ProCode
From t_JXC_TEMPTABLE3 t
left join v_proinfo on t.prospecid = v_proinfo.specId
left join v_SortPart on t.storepartid=v_SortPart.storeid
left join t_base_Item on t.ItemId=t_base_Item.PKId
left join t_dic on t.balanceid=t_dic.pkid and t_dic.dicheaderid=18
inner join t_base_ownership on t_base_Item.Ownershipid=t_base_ownership.pkid
inner join t_base_major on t_base_item.majorid=t_base_major.pkid
inner join t_base_cause on t_base_item.causeid=t_base_cause.pkid
left join v_prohtinfo on t.ph=v_prohtinfo.Ph
left join t_base_customer on v_prohtinfo.Supplierid=t_base_customer.pkid
Where (t.ItemId = p_ItemId or p_ItemId = 0) and
(v_SortPart.WareHouseId = p_wh or p_wh = 0) and
(v_SortPart.WareHouseTypeId=p_WareHouseType or p_WareHouseType=0) and
(v_SortPart.subwarehouseid = p_subwh or p_subwh = 0) and
(v_SortPart.storeid = p_sp or p_sp = 0) and
(v_proinfo.probigclassid=p_ProBigClassId or p_ProBigClassId=0) and
(v_proinfo.smallclassid=p_ProSmallClassId or p_ProSmallClassId=0) and
(v_proinfo.pronameid=p_ProNameId or p_ProNameId=0) and
(v_Proinfo.specId=p_ProSpecId OR p_ProSpecId=0) and
(t_base_Item.Ownershipid = p_OwnerShip or p_OwnerShip = 0) and
(t_base_Item.CauseId = p_Cause or p_Cause= 0) and
(t_base_Item.MajorId = p_Major or p_Major = 0) and
(t_base_customer.pkid=p_CustomerId or p_CustomerId=0)and
(t.BalanceId = p_BalanceType or p_BalanceType = 0) and
(t.CompanyId=p_CompanyId or p_CompanyId=0) and
(v_prohtinfo.Htno like '%'||p_HtCode||'%' or p_HtCode is null) and
(v_prohtinfo.Invoiceno like '%'||p_InvoiceCode||'%' or p_InvoiceCode is null) and
t.OptDate=OptDate1
order by t_base_item.ItemName,v_proinfo.BCName,v_proinfo.SCName,v_proinfo.ProName,v_proinfo.SpecName)
Where (rn>=preRowIndex And rn<nextRowIndex);--清空表
Delete from t_JXC_TEMPTABLE3 where OptDate=OptDate1;End SP_BATCHINSELLSTORAGE;
=====================================================================================
解决方案 »
免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货