oracle存储过程的编写
create or replace procedure device_statistic is
online_num number(20);
repair_num number(20);
alarm_num number(20);
offline_num number(20);
str varchar2(300);
begin
execute immediate 'TRUNCATE TABLE temp_office';
dbms_output.put_line('hello world');
str:='insert into temp_office (select so.id from sys_office so where id in(select distinct p.company from pw_device p where p.del_flag=0) and so.del_flag=0)';
execute immediate str;
FOR c_row IN (select d.id,b.repair_num,c.online_num,f.alarm_num,k.offline_num FROM temp_office d
left join (select companyid, NVL(count(alarmid),0) as repair_num from(
select o6.id as companyid,t.id as alarmid from pw_alarm t
left join temp_office o6 ON o6.id = t.office_id
where t.create_date is not null and t.office_id is not null order by t.office_id desc
)
group by companyid) b on b.companyid= d.id
left join (select companyid, NVL(count(deviceid),0) as online_num from(
select o6.id as companyid, t.on_line_state,t.id as deviceid,t.COMPANY as companys from pw_device t
left join temp_office o6 ON o6.id = t.COMPANY
where t.on_line_state='1' and t.del_flag='0' and t.company is not null order by t.company desc
)
group by companyid) c on c.companyid= d.id
left join (
select companyid, NVL(count(alarmid),0) as alarm_num from(
select o6.id as companyid,t.id as alarmid from pw_alarm t
left join temp_office o6 ON o6.id = t.office_id
where t.office_id is not null order by t.office_id desc
)
group by companyid
) f on f.companyid= d.id
left join (select companyid, NVL(count(deviceid),0) as offline_num from(
select o6.id as companyid, t.on_line_state,t.id as deviceid,t.COMPANY as companys from pw_device t
left join temp_office o6 ON o6.id = t.COMPANY
where t.on_line_state='0' and t.del_flag='0' and t.company is not null order by t.company desc
)
group by companyid) k on k.companyid= d.id) LOOP
if c_row.online_num is not null then
online_num := c_row.online_num;
else
online_num := 0;
end if;
if c_row.repair_num is not null then
repair_num := c_row.repair_num;
else
repair_num := 0;
end if;
if c_row.alarm_num is not null then
alarm_num := c_row.alarm_num;
else
alarm_num := 0;
end if;
if c_row.offline_num is not null then
offline_num := c_row.offline_num;
else
offline_num := 0;
end if;
dbms_output.put_line('-----'||repair_num||'----'||online_num || '---------'||offline_num );
INSERT INTO pw_company_device_statistic
(id,companyid,online_num,offline_num,alarm_num,repair_num,create_time)
VALUES(SYS_GUID(),
c_row.id,
online_num,
offline_num,
alarm_num,
repair_num,
SYSDATE());
END LOOP;
COMMIT;
end device_statistic;
Processed:
0.014, SQL:
9
