oracle存储过程的编写并存入临时表

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');
  
  ---创建临时表 存储公司数据
 -- execute immediate 'CREATE TABLE temp_office AS 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';
  --str:='drop table temp_office'; 
  --execute immediate str; 
  --str:='create global temporary table temp_office(id varchar2(30))  
--on commit delete rows'; 
 -- execute immediate str; ----使用动态SQL语句来执行 
 
 ---将筛选的部门id存到临时表中
  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; 
  --commit;
  ---查询出相关数据统计
  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());
    ---删除临时表
     --execute immediate 'DROP TABLE temp_office';
    END LOOP;
    COMMIT;
end device_statistic;