有这样一个存储过程:
CREATE OR REPLACE PROCEDURE FIND_PRJCOUNT(
P_prjid IN integer--,p_curres out SYS_REFCURSOR
)
is
P_dptname varchar(50); --地区
p_openCarrier integer; --开通载频数
p_sumBase integer; --基站总数
p_comAddress integer; --站址落实
p_comExploration integer; --勘探完成
p_comDesign integer; --设计完成
--(还有十几个)
Cursor cursor_prjname is
select dptname from bas_dptlist dp1 where dp1.vieworder in (select substr(dp.vieworder,0,4) from bas_dptlist dp where dp.dptid in
(select substr(P.itemvalue,0,instr(P.itemvalue,'#')-1) from prj_pivalues P, prj_class C where P.prjid = C.prjid and C.upprjid = P_prjid and P.itemid = 10557));
begin
delete from TMP_PRJCOUNT;
OPEN cursor_prjname;
loop
FETCH cursor_prjname INTO P_dptname;
EXIT WHEN cursor_prjname%NOTFOUND;
select sum(t.itemvalue) into p_openCarrier from prj_pivalues t where t.prjid in (select p.prjid from bas_dptlist dp,prj_pivalues P, prj_class C
where dp.dptid in substr(P.itemvalue,0,instr(P.itemvalue,'#')-1) and P.prjid = C.prjid and C.upprjid = P_prjid and P.itemid = 10557 and substr(dp.vieworder,0,4) =
(select vieworder from bas_dptlist where dptname = P_dptname)) and t.itemid = 10566;
--union
select count(p.prjid) into p_sumBase from bas_dptlist dp,prj_pivalues P, prj_class C
where dp.dptid in substr(P.itemvalue,0,instr(P.itemvalue,'#')-1) and P.prjid = C.prjid and C.upprjid = P_prjid and P.itemid = 10557 and substr(dp.vieworder,0,4) =
(select vieworder from bas_dptlist where dptname = P_dptname);
select count(pp.stageid) into p_comAddress from prj_prjstages pp where prjid in (select p.prjid from bas_dptlist dp,prj_pivalues P, prj_class C
where dp.dptid in substr(P.itemvalue,0,instr(P.itemvalue,'#')-1) and P.prjid = C.prjid and C.upprjid = P_prjid and P.itemid = 10557 and substr(dp.vieworder,0,4) =
(select vieworder from bas_dptlist where dptname = P_dptname)) and pp.groupid=10076 and pp.flowstate=8;
select count(pp.stageid) into p_comExploration from prj_prjstages pp where prjid in (select p.prjid from bas_dptlist dp,prj_pivalues P, prj_class C
where dp.dptid in substr(P.itemvalue,0,instr(P.itemvalue,'#')-1) and P.prjid = C.prjid and C.upprjid = P_prjid and P.itemid = 10557 and substr(dp.vieworder,0,4) =
(select vieworder from bas_dptlist where dptname = P_dptname)) and pp.groupid=10520 and pp.flowstate=8;
select count(pp.stageid) into p_comDesign from prj_prjstages pp where prjid in (select p.prjid from bas_dptlist dp,prj_pivalues P, prj_class C
where dp.dptid in substr(P.itemvalue,0,instr(P.itemvalue,'#')-1) and P.prjid = C.prjid and C.upprjid = P_prjid and P.itemid = 10557 and substr(dp.vieworder,0,4) =
(select vieworder from bas_dptlist where dptname = P_dptname)) and pp.groupid=10515 and pp.flowstate=8;
--还有十几条
insert into TMP_PRJCOUNT (city,Opencarrier,Sumbase,Comaddress,COMEXPLORATION,Comdesign,......) values (P_dptname,p_openCarrier,p_sumBase,p_comAddress,p_comExploration,p_comDesign,......);
end loop;
close cursor_prjname;
--open p_curres for select * from TMP_PRJCOUNT;
delete from TMP_PRJCOUNT;
end FIND_PRJCOUNT;
一共有19个字段的统计,其中(select p.prjid from bas_dptlist dp,prj_pivalues P, prj_class C
where dp.dptid in substr(P.itemvalue,0,instr(P.itemvalue,'#')-1) and P.prjid = C.prjid and C.upprjid = P_prjid and P.itemid = 10557 and substr(dp.vieworder,0,4) =
(select vieworder from bas_dptlist where dptname = P_dptname))给出的是N个PRJID,这条语句反复出现,我想把他的结果放一数组中,下面调用,但是没有能实现,求方法;下面统计语句大部分区别就在pp.groupid=10515 这个条件上,求优化!
CREATE OR REPLACE PROCEDURE FIND_PRJCOUNT(
P_prjid IN integer--,p_curres out SYS_REFCURSOR
)
is
P_dptname varchar(50); --地区
p_openCarrier integer; --开通载频数
p_sumBase integer; --基站总数
p_comAddress integer; --站址落实
p_comExploration integer; --勘探完成
p_comDesign integer; --设计完成
--(还有十几个)
Cursor cursor_prjname is
select dptname from bas_dptlist dp1 where dp1.vieworder in (select substr(dp.vieworder,0,4) from bas_dptlist dp where dp.dptid in
(select substr(P.itemvalue,0,instr(P.itemvalue,'#')-1) from prj_pivalues P, prj_class C where P.prjid = C.prjid and C.upprjid = P_prjid and P.itemid = 10557));
begin
delete from TMP_PRJCOUNT;
OPEN cursor_prjname;
loop
FETCH cursor_prjname INTO P_dptname;
EXIT WHEN cursor_prjname%NOTFOUND;
select sum(t.itemvalue) into p_openCarrier from prj_pivalues t where t.prjid in (select p.prjid from bas_dptlist dp,prj_pivalues P, prj_class C
where dp.dptid in substr(P.itemvalue,0,instr(P.itemvalue,'#')-1) and P.prjid = C.prjid and C.upprjid = P_prjid and P.itemid = 10557 and substr(dp.vieworder,0,4) =
(select vieworder from bas_dptlist where dptname = P_dptname)) and t.itemid = 10566;
--union
select count(p.prjid) into p_sumBase from bas_dptlist dp,prj_pivalues P, prj_class C
where dp.dptid in substr(P.itemvalue,0,instr(P.itemvalue,'#')-1) and P.prjid = C.prjid and C.upprjid = P_prjid and P.itemid = 10557 and substr(dp.vieworder,0,4) =
(select vieworder from bas_dptlist where dptname = P_dptname);
select count(pp.stageid) into p_comAddress from prj_prjstages pp where prjid in (select p.prjid from bas_dptlist dp,prj_pivalues P, prj_class C
where dp.dptid in substr(P.itemvalue,0,instr(P.itemvalue,'#')-1) and P.prjid = C.prjid and C.upprjid = P_prjid and P.itemid = 10557 and substr(dp.vieworder,0,4) =
(select vieworder from bas_dptlist where dptname = P_dptname)) and pp.groupid=10076 and pp.flowstate=8;
select count(pp.stageid) into p_comExploration from prj_prjstages pp where prjid in (select p.prjid from bas_dptlist dp,prj_pivalues P, prj_class C
where dp.dptid in substr(P.itemvalue,0,instr(P.itemvalue,'#')-1) and P.prjid = C.prjid and C.upprjid = P_prjid and P.itemid = 10557 and substr(dp.vieworder,0,4) =
(select vieworder from bas_dptlist where dptname = P_dptname)) and pp.groupid=10520 and pp.flowstate=8;
select count(pp.stageid) into p_comDesign from prj_prjstages pp where prjid in (select p.prjid from bas_dptlist dp,prj_pivalues P, prj_class C
where dp.dptid in substr(P.itemvalue,0,instr(P.itemvalue,'#')-1) and P.prjid = C.prjid and C.upprjid = P_prjid and P.itemid = 10557 and substr(dp.vieworder,0,4) =
(select vieworder from bas_dptlist where dptname = P_dptname)) and pp.groupid=10515 and pp.flowstate=8;
--还有十几条
insert into TMP_PRJCOUNT (city,Opencarrier,Sumbase,Comaddress,COMEXPLORATION,Comdesign,......) values (P_dptname,p_openCarrier,p_sumBase,p_comAddress,p_comExploration,p_comDesign,......);
end loop;
close cursor_prjname;
--open p_curres for select * from TMP_PRJCOUNT;
delete from TMP_PRJCOUNT;
end FIND_PRJCOUNT;
一共有19个字段的统计,其中(select p.prjid from bas_dptlist dp,prj_pivalues P, prj_class C
where dp.dptid in substr(P.itemvalue,0,instr(P.itemvalue,'#')-1) and P.prjid = C.prjid and C.upprjid = P_prjid and P.itemid = 10557 and substr(dp.vieworder,0,4) =
(select vieworder from bas_dptlist where dptname = P_dptname))给出的是N个PRJID,这条语句反复出现,我想把他的结果放一数组中,下面调用,但是没有能实现,求方法;下面统计语句大部分区别就在pp.groupid=10515 这个条件上,求优化!
insert into .... select... 来实现
select from prj_pivalues t where ....
(select substr(P.itemvalue,0,instr(P.itemvalue,'#')-1) from prj_pivalues P, prj_class C where P.prjid = C.prjid and C.upprjid = P_prjid and P.itemid = 10557));这条语句作为条件,获取的是一个集合,我不用游标怎么拿它做条件来查询?
select count(pp.stageid) into p_comExploration from prj_prjstages pp where prjid in (select p.prjid from bas_dptlist dp,prj_pivalues P, prj_class C
where dp.dptid in substr(P.itemvalue,0,instr(P.itemvalue,'#')-1) and P.prjid = C.prjid and C.upprjid = P_prjid and P.itemid = 10557 and substr(dp.vieworder,0,4) =
(select vieworder from bas_dptlist where dptname = (select dptname from bas_dptlist dp1 where dp1.vieworder in (select substr(dp.vieworder,0,4) from bas_dptlist dp where dp.dptid in
(select substr(P.itemvalue,0,instr(P.itemvalue,'#')-1) from prj_pivalues P, prj_class C where P.prjid = C.prjid and C.upprjid = P_prjid and P.itemid = 10557))))) and pp.groupid=10520 and pp.flowstate=8;
红字部分的结果是一个长度未定的集合,这条语句可以自动循环查询?