实现功能——从其他表中统计数据后,插入到另一张表by_train_count中:
参考链接:http://wzhiju.iteye.com/blog/1123157
create or replace procedure up_insert_by_train_count
is
yzTotal number;--邮政车
zbTotal number;--企业自备车
gTdTTotal number;--无代管关系的国铁、地铁
zycTotal number;--专运处客车
dpsTotal number;--待配属客车
tkyTotal number;--铁科院客车
dcTotal number;--DC600V客车
acTotal number;--AC380V客车
dcbTotal number;--DC48V客车
countTotal number;--保有量总计
begin
delete from by_train_count;
select count(*) into yzTotal FROM DB_traininfo
where buy_type_id ='04' and kmis.PKG_TRAIN.UF_ISVALID_PS(T_ID) = '1';
insert into by_train_count(c_id,type_id,type_name,train_num,STATISTICS_DATE)
values(1,'01','邮政车',yzTotal,to_date('2016-04-01','yyyy-MM-dd'));
select count(*) into zbTotal FROM DB_traininfo
where buy_type_id ='05' and kmis.PKG_TRAIN.UF_ISVALID_PS(T_ID) = '1';
insert into by_train_count(c_id,type_id,type_name,train_num,STATISTICS_DATE)
values(2,'02','企业自备车',zbTotal,to_date('2016-04-01','yyyy-MM-dd'));
select count(*) into gTdTTotal from DB_TRAININFO_STATIC_BYL_t
where is_dg='0' and (t_ps='01' or t_ps='02')
and STATISTICS_DATE = to_date('2016-04-01','yyyy-MM-dd');
insert into by_train_count(c_id,type_id,type_name,train_num,STATISTICS_DATE)
values(3,'03','无代管关系的国铁、地铁',gTdTTotal,to_date('2016-04-01','yyyy-MM-dd'));
select count(*) into zycTotal from DB_TRAININFO_STATIC_BYL_t
where station_id='00ZYC' and STATISTICS_DATE = to_date('2016-04-01','yyyy-MM-dd');
insert into by_train_count(c_id,type_id,type_name,train_num,STATISTICS_DATE)
values(4,'04','专运处客车',zycTotal,to_date('2016-04-01','yyyy-MM-dd'));
select count(*) into dpsTotal from DB_TRAININFO_STATIC_BYL_t
where station_id='000J0' and STATISTICS_DATE = to_date('2016-04-01','yyyy-MM-dd');
insert into by_train_count(c_id,type_id,type_name,train_num,STATISTICS_DATE)
values(5,'05','待配属客车',dpsTotal,to_date('2016-04-01','yyyy-MM-dd'));
select count(*) into tkyTotal from DB_TRAININFO_STATIC_BYL_t
where station_id='00TKY' and STATISTICS_DATE = to_date('2016-04-01','yyyy-MM-dd');
insert into by_train_count(c_id,type_id,type_name,train_num,STATISTICS_DATE)
values(6,'06','铁科院客车',tkyTotal,to_date('2016-04-01','yyyy-MM-dd'));
select count(*) into dcTotal from DB_TRAININFO_STATIC_BYL_t where power_volt_kind='08'
and station_id in('000J1','000J2','000J3','000J4','000J5','000J6','000J7','000J8','000J9',
'00J10','00J11','00J12','00J13','00J14','00J15','00J18','00J19','00J20')
and STATISTICS_DATE = to_date('2016-04-01','yyyy-MM-dd');
insert into by_train_count(c_id,type_id,type_name,train_num,STATISTICS_DATE)
values(7,'07','DC600V客车',dcTotal,to_date('2016-04-01','yyyy-MM-dd'));
select count(*) into acTotal from DB_TRAININFO_STATIC_BYL_t where power_volt_kind='05'
and station_id in('000J1','000J2','000J3','000J4','000J5','000J6','000J7','000J8','000J9',
'00J10','00J11','00J12','00J13','00J14','00J15','00J18','00J19','00J20')
and STATISTICS_DATE = to_date('2016-04-01','yyyy-MM-dd');
insert into by_train_count(c_id,type_id,type_name,train_num,STATISTICS_DATE)
values(8,'08','AC380V客车',acTotal,to_date('2016-04-01','yyyy-MM-dd'));
select count(*) into dcbTotal from DB_TRAININFO_STATIC_BYL_t where power_volt_kind='01'
and station_id in('000J1','000J2','000J3','000J4','000J5','000J6','000J7','000J8','000J9',
'00J10','00J11','00J12','00J13','00J14','00J15','00J18','00J19','00J20')
and STATISTICS_DATE = to_date('2016-04-01','yyyy-MM-dd');
insert into by_train_count(c_id,type_id,type_name,train_num,STATISTICS_DATE)
values(09,'09','DC48V客车',dcbTotal,to_date('2016-04-01','yyyy-MM-dd'));
select count(*) into countTotal from DB_TRAININFO_STATIC_BYL_t
where STATISTICS_DATE = to_date('2016-04-01','yyyy-MM-dd');
insert into by_train_count(c_id,type_id,type_name,train_num,STATISTICS_DATE)
values(10,'10','保有量总计',countTotal,to_date('2016-04-01','yyyy-MM-dd'));
commit;
end up_insert_by_train_count;