oracle 存储过程 临时表for 循环 添加数据
本帖最后由 ri6876099 于 2012-08-13 17:19:03 编辑 create or replace procedure JCJGMONTHCOUNT
authid current_user
is
vn_ctn number;
begin
select count(*) into vn_ctn from user_all_tables a where a.table_name like upper('JCJGMONTHTABLE');
if vn_ctn > 0 then
execute immediate 'drop table JCJGMONTHTABLE';
end if;
execute immediate 'create table JCJGMONTHTABLE as
select * from (select qii.inspectinstitutionpid,qii.inspectinstitutionName,qii.jcjgid,
qcr.approveDate,
count(*) as SAMPLEGROUPID,
sum(case when qcr.result in(''A'') then 1 else 0 end) "RESULT_A", --结论
sum(case when qcr.RESULT in(''B'') then 1 else 0 end) "RESULT_B",
sum(case when qcr.RESULT in(''D'') then 1 else 0 end) "RESULT_D",
sum(case when qcr.RESULT in(''C'') then 1 else 0 end) "RESULT_C",
sum(case when qcr.RESULT in(''E'') then 1 else 0 end) "RESULT_E",
sum(case when qcr.processstate in(''N'') then 1 else 0 end) "PROCESSSTATE",--曲线比对异常
sum(case when qcr.experimentationstate in(''N'') then 1 else 0 end) "EXPERIMENTATIONSTATE", --比对结果异常
sum(case when qcr.echosampleid is not null then 1 else 0 end) "ECHOSAMPLEID_TRUE", --复检的样品编号_是
sum(case when qcr.echosampleid is null then 1 else 0 end) "ECHOSAMPLEID_FALSE",--复检的样品编号_否
sum(case when qcr.realtimestate in(''N'') then 1 else 0 end) "REALTIMESTATE",--是否实时传输状态
sum(case when qcr.modifystate in(''Y'') then 1 else 0 end) "MODIFYSTATE", --数据被修改
sum(case when qcr.collectionstate in(''N'') then 1 else 0 end) "COLLECTIONSTATE" --未采集曲线数据
from report qcr
inner join compact jc on qcr.compactpid = jc.compactpid
inner join inspectinstitution qii on qcr.jcjgid = qii.jcjgid
where qcr.approveDate=to_date(''2012-1'',''yyyy-mm'')
group by qii.inspectinstitutionpid,qii.inspectinstitutionName,qii.jcjgid,qcr.approveDate)';
exception
when others then
null;
end JCJGMONTHCOUNT;
where qcr.approveDate=to_date(''2012-1'',''yyyy-mm'')
一月时建临时表 2-12时 添加数据怎么循环
[解决办法]
没明白意思
[解决办法]
where qcr.approveDate=to_date(to_char(trunc(sysdate),'yyyy-mm'),''yyyy-mm'')
[解决办法]
where 条件那里传的是月份 就是1月的时候是就是上面的语句2-12月的时候就是往1月建的表当中添加数据
[解决办法]
到2-12月的时候就不是创建表了 而是 insert into JCJGMONTHTABLE(
select * from (select qii.inspectinstitutionpid,qii.inspectinstitutionName,qii.jcjgid,
qcr.approveDate,
count(*) as SAMPLEGROUPID,
sum(case when qcr.result in('A') then 1 else 0 end) RESULT_A, --结论
sum(case when qcr.RESULT in('B') then 1 else 0 end) RESULT_B,
sum(case when qcr.RESULT in('D') then 1 else 0 end) RESULT_D,
sum(case when qcr.RESULT in('C') then 1 else 0 end) RESULT_C,
sum(case when qcr.RESULT in('E') then 1 else 0 end) RESULT_E,
sum(case when qcr.processstate in('N') then 1 else 0 end) PROCESSSTATE,--曲线比对异常
sum(case when qcr.experimentationstate in('N') then 1 else 0 end) EXPERIMENTATIONSTATE, --比对结果异常
sum(case when qcr.echosampleid is not null then 1 else 0 end) ECHOSAMPLEID_TRUE, --复检的样品编号_是
sum(case when qcr.echosampleid is null then 1 else 0 end) ECHOSAMPLEID_FALSE,--复检的样品编号_否
sum(case when qcr.realtimestate in('N') then 1 else 0 end) REALTIMESTATE,--是否实时传输状态
sum(case when qcr.modifystate in('Y') then 1 else 0 end) MODIFYSTATE, --数据被修改
sum(case when qcr.collectionstate in('N') then 1 else 0 end) COLLECTIONSTATE --未采集曲线数据
from report qcr
inner join compact jc on qcr.compactpid = jc.compactpid
inner join inspectinstitution qii on qcr.jcjgid = qii.jcjgid
where qcr.approveDate=to_date(''2012-2'',''yyyy-mm'')
group by qii.inspectinstitutionpid,qii.inspectinstitutionName,qii.jcjgid,qcr.approveDate));
[解决办法]
create or replace procedure JDZMONTHCOUNT(approvedate date)
authid current_user
is
vn_ctn number;
begin
select count(*) into vn_ctn from user_all_tables a where a.table_name like upper('JDZMONTHTABLE');
if vn_ctn > 0 then
execute immediate 'drop table JDZMONTHTABLE';
end if;
execute immediate 'create table JDZMONTHTABLE as
select * from (select qii.inspectinstitutionpid,qii.inspectinstitutionName,jc.districTid,qcr.approveDate,
count(*) as SAMPLEGROUPID,
sum(case when qcr.result in(''A'') then 1 else 0 end) "RESULT_A", --结论
sum(case when qcr.RESULT in(''B'') then 1 else 0 end) "RESULT_B",
sum(case when qcr.RESULT in(''D'') then 1 else 0 end) "RESULT_D",
sum(case when qcr.RESULT in(''C'') then 1 else 0 end) "RESULT_C",
sum(case when qcr.RESULT in(''E'') then 1 else 0 end) "RESULT_E",
sum(case when qcr.processstate in(''N'') then 1 else 0 end) "PROCESSSTATE",--曲线比对异常
sum(case when qcr.experimentationstate in(''N'') then 1 else 0 end) "EXPERIMENTATIONSTATE", --比对结果异常
sum(case when qcr.echosampleid is not null then 1 else 0 end) "ECHOSAMPLEID_TRUE", --复检的样品编号_是
sum(case when qcr.echosampleid is null then 1 else 0 end) "ECHOSAMPLEID_FALSE",--复检的样品编号_否
sum(case when qcr.realtimestate in(''N'') then 1 else 0 end) "REALTIMESTATE",--是否实时传输状态
sum(case when qcr.modifystate in(''Y'') then 1 else 0 end) "MODIFYSTATE", --数据被修改
sum(case when qcr.collectionstate in(''N'') then 1 else 0 end) "COLLECTIONSTATE" --未采集曲线数据
from report qcr
inner join compact jc on qcr.compactpid = jc.compactpid
inner join inspectinstitution qii on jc.districtid = qii.jcjgid
where qcr.approveDate=to_date(''2012-1'',''yyyy-mm'')
group by qii.inspectinstitutionpid,qii.inspectinstitutionName,jc.districTid,qcr.approveDate
)';
for p in 2..12
loop
if p<=12 then
insert into JDZMONTHTABLE(select * from (select qii.inspectinstitutionpid,qii.inspectinstitutionName,jc.districTid,qcr.approveDate,
count(*) as SAMPLEGROUPID,
sum(case when qcr.result in('A') then 1 else 0 end) "RESULT_A", --结论
sum(case when qcr.RESULT in('B') then 1 else 0 end) "RESULT_B",
sum(case when qcr.RESULT in('D') then 1 else 0 end) "RESULT_D",
sum(case when qcr.RESULT in('C') then 1 else 0 end) "RESULT_C",
sum(case when qcr.RESULT in('E') then 1 else 0 end) "RESULT_E",
sum(case when qcr.processstate in('N') then 1 else 0 end) "PROCESSSTATE",--曲线比对异常
sum(case when qcr.experimentationstate in('N') then 1 else 0 end) "EXPERIMENTATIONSTATE", --比对结果异常
sum(case when qcr.echosampleid is not null then 1 else 0 end) "ECHOSAMPLEID_TRUE", --复检的样品编号_是
sum(case when qcr.echosampleid is null then 1 else 0 end) "ECHOSAMPLEID_FALSE",--复检的样品编号_否
sum(case when qcr.realtimestate in('N') then 1 else 0 end) "REALTIMESTATE",--是否实时传输状态
sum(case when qcr.modifystate in('Y') then 1 else 0 end) "MODIFYSTATE", --数据被修改
sum(case when qcr.collectionstate in('N') then 1 else 0 end) "COLLECTIONSTATE" --未采集曲线数据
from report qcr
inner join compact jc on qcr.compactpid = jc.compactpid
inner join inspectinstitution qii on jc.districtid = qii.jcjgid
where qcr.approveDate=to_date('2012-'
[解决办法]
p
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html
