存储过程、动态sql、for、if

create or replace procedure ---- is

    COLLECTDATE varchar2(8) := to_char(sysdate-1,'yyyymmdd');     --    统计日期  yyyymmdd
    INIT_STATE VARCHAR2(2) ;--当天初始化状态
    END_STATE VARCHAR2(2); --当天结束状态
    FAULT_TIME NUMBER(10) DEFAULT 0; --当天故障时间
    EFFECT_TIME NUMBER(10) DEFAULT 0; --当天有效时间
--4
     THIRD_FAULT_TIME NUMBER(10) DEFAULT 0; --当天第三方故障时间
--8
     third_party_flag VARCHAR2(2) default 0; -- 最后一条数据的第三方标识
    OUTSIDE_FAULT_TIME NUMBER(10) DEFAULT 0; --外场设备故障时间
    OUTSIDE_EFFECT_TIME NUMBER(10) DEFAULT 0; --外场设备有效时间
    BEFORECOLLECTDATE varchar2(8) := to_char(sysdate-2,'yyyymmdd');     --统计日期  yyyymmdd
    FAULT_START_DATE DATE DEFAULT TRUNC(SYSDATE)-1;
   -- EFFECT_START_DATE DATE DEFAULT TRUNC(SYSDATE)-1;
     v_update_sql varchar2(1000);
     DATA_INIT_FLAG number default 0 ;
begin

  begin
     --前端设备
   select count(*) into DATA_INIT_FLAG from EQUIP_DATA_ANALYSIS where collect_date=COLLECTDATE and EQUIP_SERVICE_FLAG='1';
   if DATA_INIT_FLAG=0 then
      FOR C IN (SELECT  ei.DEVICEID,ei.DEVICESTATE,ei.DEVICETYPE,ei.devicename, ei.pointid,
          ei.belongtoproj,ei.direction,ei.departmentid,ei.area,ei.thirdsyscode,ei.VOLUMNTYPE
                   FROM EQUIPMENT_INFO ei
--1-- and ei.devicetype = '10' and ei.deviceid = '370203000000101060'
                  WHERE ei.DEVICESTATE IN('1','2','3','4','5')) LOOP

        INSERT INTO EQUIP_DATA_ANALYSIS
          (id, deviceid, collect_date, device_type, device_state, pointid, departmentid, direction, devicename, area, projectid,thirdsyscode, sub_type,EQUIP_SERVICE_FLAG)
        VALUES
          (sys_guid(),c.deviceid, COLLECTDATE, c.devicetype, c.devicestate, c.pointid,  c.departmentid, c.direction, c.devicename, c.area, c.belongtoproj, c.thirdsyscode,  c.volumntype,'1');

      END LOOP;

      DBMS_OUTPUT.PUT_LINE('前端设备基础数据初始化！');
      commit;
    end if;
    --2
--    DBMS_OUTPUT.PUT_LINE('DATA_INIT_FLAG:'||DATA_INIT_FLAG);
     FOR P IN (SELECT  e.id, e.deviceid, e.collect_date, e.device_type, e.device_state, e.pointid, e.departmentid, e.direction,
          e.devicename, e.area, e.projectid
         FROM EQUIP_DATA_ANALYSIS e WHERE e.collect_date=COLLECTDATE AND E.EQUIP_SERVICE_FLAG='1') LOOP
         begin
           --获取设备的初始化状态和结束状态
           END_STATE:=P.DEVICE_STATE;
           INIT_STATE:='1';
           --SELECT e.device_state INTO INIT_STATE FROM EQUIP_DATA_ANALYSIS e WHERE E.DEVICEID=P.DEVICEID AND E.COLLECT_DATE=BEFORECOLLECTDATE;

           for B in (SELECT e.device_state FROM EQUIP_DATA_ANALYSIS e WHERE E.DEVICEID=P.DEVICEID AND E.COLLECT_DATE=BEFORECOLLECTDATE) loop
             begin
               INIT_STATE:=B.DEVICE_STATE;--昨天的结束状态等于今天的初始状态
            end;
           end loop;

           FAULT_START_DATE:=trunc(sysdate)-1;
           FAULT_TIME:=0;
           EFFECT_TIME:=0;
--5           
           THIRD_FAULT_TIME:=0;
--9
           third_party_flag:='0';
           
--3
           --逐条数据分析，累积故障时间和有效时间
           FOR HIS IN (SELECT S.DEVICESTATE, s.updatetime,ee.third_party_flag  FROM EQUIPMENT_INFO_STATE_HISTORY S left join equip_maintain ee on s.maintainid = ee.maintainid WHERE s.deviceid = P.DEVICEID and s.DEVICETYPE=P.Device_Type
               and s.deviceorserver = '1' and s.updatetime between trunc(sysdate) - 1 and trunc(sysdate) order by s.updatetime) LOOP
             begin
               IF INIT_STATE = '1' THEN
                 EFFECT_TIME:=EFFECT_TIME + (HIS.UPDATETIME-FAULT_START_DATE)*24*60*60;
               elsif INIT_STATE = '4' OR INIT_STATE = '5' then
                  EFFECT_TIME:=EFFECT_TIME + (HIS.UPDATETIME-FAULT_START_DATE)*24*60*60;
                  FAULT_TIME:=FAULT_TIME + (HIS.UPDATETIME-FAULT_START_DATE)*24*60*60;
               end if;
--6
               IF (INIT_STATE = '4' OR INIT_STATE = '5') and his.third_party_flag='1' then
                 THIRD_FAULT_TIME:=THIRD_FAULT_TIME + (HIS.UPDATETIME-FAULT_START_DATE)*24*60*60;
               end if;
               INIT_STATE:=HIS.DEVICESTATE;
               FAULT_START_DATE:= HIS.updatetime;
--10
--               if his.third_party_flag is not null then
                  third_party_flag:=his.third_party_flag;
              
--               elsif his.third_party_flag is null then
--                  third_party_flag:='0';
--               end if;
             end;
            END LOOP;
            
            -- 最后一条数据 或状态未改变的情况
            IF INIT_STATE = '1' THEN
                 EFFECT_TIME:=EFFECT_TIME + (TRUNC(SYSDATE)-FAULT_START_DATE)*24*60*60;
            elsif INIT_STATE = '4' OR INIT_STATE = '5' then
                  EFFECT_TIME:=EFFECT_TIME + (TRUNC(SYSDATE)-FAULT_START_DATE)*24*60*60;
                  FAULT_TIME:=FAULT_TIME + (TRUNC(SYSDATE)-FAULT_START_DATE)*24*60*60;
            end if;
            
--7正式修改，最后一条的处理
             IF (INIT_STATE = '4' OR INIT_STATE = '5') and third_party_flag='1' then
                 THIRD_FAULT_TIME:=THIRD_FAULT_TIME + (TRUNC(SYSDATE)-FAULT_START_DATE)*24*60*60;
               end if;
            
--11
-- DBMS_OUTPUT.PUT_LINE('--THIRD_FAULT_TIME:'||THIRD_FAULT_TIME);
          v_update_sql :=' update EQUIP_DATA_ANALYSIS eda  set eda.FAULT_TIME= :1,eda.EFFECT_TIME=:2,eda.THIRD_FAULT_TIME=:3  where eda.id=:4 ';
          execute immediate v_update_sql
          using  FAULT_TIME,EFFECT_TIME,THIRD_FAULT_TIME,P.ID;
          commit;

           EXCEPTION
           WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('系统可用率分析错误，'||sqlcode || sqlerrm );
           ROLLBACK;
         end;
      END LOOP;
   end;

   begin
   --中心服务
   delete from EQUIP_DATA_ANALYSIS where collect_date=COLLECTDATE and EQUIP_SERVICE_FLAG='2';
  FOR C IN (SELECT  ei.ID DEVICEID,ei.Server_State DEVICESTATE,ei.server_type DEVICETYPE,ei.server_name devicename,
--3-- and ei.SERVER_TYPE='01' and ei.id='370200000000011034'
        ei.Project_Code belongtoproj,ei.departmentid departmentid FROM SERVER_INFO ei WHERE ei.Server_State IN('1','2','3','4','5')) LOOP

      INSERT INTO EQUIP_DATA_ANALYSIS
        (id, deviceid, collect_date, device_type, device_state, departmentid, devicename , projectid,EQUIP_SERVICE_FLAG)
      VALUES
        (sys_guid(),c.deviceid, COLLECTDATE, c.devicetype, c.devicestate, c.departmentid, c.devicename, c.belongtoproj, '2');

    END LOOP;
       DBMS_OUTPUT.PUT_LINE('中心服务基础数据初始化！');
    commit;

    ---系统可用率分析   分析每个设备的可用时间和故障时间

     FOR P IN (SELECT  e.id, e.deviceid, e.collect_date, e.device_type, e.device_state, e.pointid, e.departmentid, e.direction,e.devicename, e.area, e.projectid
         FROM EQUIP_DATA_ANALYSIS e WHERE e.collect_date=COLLECTDATE AND E.EQUIP_SERVICE_FLAG='2') LOOP
         begin
           --获取设备的初始化状态和结束状态
           END_STATE:=P.DEVICE_STATE;
           INIT_STATE:='1';

           for B in (SELECT e.device_state FROM EQUIP_DATA_ANALYSIS e WHERE E.DEVICEID=P.DEVICEID AND E.COLLECT_DATE=BEFORECOLLECTDATE) loop
             begin
               INIT_STATE:=B.DEVICE_STATE;
            end;
           end loop;

           FAULT_START_DATE:=trunc(sysdate)-1;
           FAULT_TIME:=0;
           EFFECT_TIME:=0;
--4           
           THIRD_FAULT_TIME:=0;
--5
           third_party_flag:='0';
--6
-- DBMS_OUTPUT.PUT_LINE('P.DEVICEID:'||P.DEVICEID||'-P.Device_Type'||P.Device_Type);
           --逐条数据分析，累积故障时间和有效时间
           FOR HIS IN (SELECT S.DEVICESTATE, s.updatetime,ee.third_party_flag  FROM EQUIPMENT_INFO_STATE_HISTORY S left join equip_maintain ee on s.maintainid = ee.maintainid WHERE s.deviceid = P.DEVICEID and s.DEVICETYPE=P.Device_Type
               and s.deviceorserver = '2' and s.updatetime between trunc(sysdate) - 1 and trunc(sysdate) order by s.updatetime) LOOP
             begin
        
               IF INIT_STATE = '1' THEN
                 EFFECT_TIME:=EFFECT_TIME + (HIS.UPDATETIME-FAULT_START_DATE)*24*60*60;
               elsif INIT_STATE = '4' OR INIT_STATE = '5' then
                  EFFECT_TIME:=EFFECT_TIME + (HIS.UPDATETIME-FAULT_START_DATE)*24*60*60;
                  FAULT_TIME:=FAULT_TIME + (HIS.UPDATETIME-FAULT_START_DATE)*24*60*60;
               end if;
--7正式修改，第三方故障时间
               IF (INIT_STATE = '4' OR INIT_STATE = '5') and his.third_party_flag='1' then
                 THIRD_FAULT_TIME:=THIRD_FAULT_TIME + (HIS.UPDATETIME-FAULT_START_DATE)*24*60*60;
               end if;
               INIT_STATE:=HIS.DEVICESTATE;
               FAULT_START_DATE:= HIS.updatetime;
              
--8
               third_party_flag:=his.third_party_flag;
               
             end;
            END LOOP;
            -- 最后一条数据 或状态未改变的情况
            IF INIT_STATE = '1' THEN
                 EFFECT_TIME:=EFFECT_TIME + (TRUNC(SYSDATE)-FAULT_START_DATE)*24*60*60;
            elsif INIT_STATE = '4' OR INIT_STATE = '5' then
                  EFFECT_TIME:=EFFECT_TIME + (TRUNC(SYSDATE)-FAULT_START_DATE)*24*60*60;
                  FAULT_TIME:=FAULT_TIME + (TRUNC(SYSDATE)-FAULT_START_DATE)*24*60*60;
            end if;
--9正式修改，最后一条的处理
             IF (INIT_STATE = '4' OR INIT_STATE = '5') and third_party_flag='1' then
                 THIRD_FAULT_TIME:=THIRD_FAULT_TIME + (TRUNC(SYSDATE)-FAULT_START_DATE)*24*60*60;
               end if;
--10
--             DBMS_OUTPUT.PUT_LINE('--THIRD_FAULT_TIME:'||THIRD_FAULT_TIME);
          v_update_sql :=' update EQUIP_DATA_ANALYSIS eda  set eda.FAULT_TIME= :1,eda.EFFECT_TIME=:2,eda.THIRD_FAULT_TIME=:3  where eda.id=:4 ';
          execute immediate v_update_sql
          using  FAULT_TIME,EFFECT_TIME,THIRD_FAULT_TIME,P.ID;
          commit;

           EXCEPTION
           WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('系统可用率分析错误，'||sqlcode || sqlerrm );
           ROLLBACK;
         end;
      END LOOP;
    end;

  BEGIN
    FOR pj in (select p.project_id from faci_project p ) loop

     select sum(faultdevice),sum(alldevice) into FAULT_TIME,EFFECT_TIME from(select sum(d.fault_time) faultdevice ,sum(d.effect_time) alldevice from equip_data_analysis d WHERE d.projectid=pj.project_id and D.collect_date=COLLECTDATE union (select 0 faultdevice,0 alldevice from dual));
     select sum(faultdevice),sum(alldevice) into OUTSIDE_FAULT_TIME,OUTSIDE_EFFECT_TIME from ( (select (sum(r.inspection_record_count) - sum(r.inspection_success_count))*24*60*60 as faultdevice,sum(r.inspection_record_count)*24*60*60 alldevice
     from equip_inspection_task t left join equip_inspection_record r on t.taskid = r.taskid  where t.projectid = pj.project_id and t.start_date <= (to_date(COLLECTDATE,'yyyymmdd')+1) and t.end_date >= (to_date(COLLECTDATE,'yyyymmdd')+1) and trunc(r.inspection_time) <= (to_date(COLLECTDATE,'yyyymmdd')+1) and t.device_source = '2' group by t.projectid) union (select 0 faultdevice,0 alldevice from dual));
     DBMS_OUTPUT.PUT_LINE('inside data :'||FAULT_TIME || '   '||EFFECT_TIME ||'   project id:'||pj.project_id||'   time:'|| (to_date(COLLECTDATE,'yyyymmdd')+1));
     FAULT_TIME := FAULT_TIME+OUTSIDE_FAULT_TIME;
     EFFECT_TIME := EFFECT_TIME+OUTSIDE_EFFECT_TIME;
     DBMS_OUTPUT.PUT_LINE('outside data :'||OUTSIDE_FAULT_TIME || '   '||OUTSIDE_EFFECT_TIME ||'   project id:'||pj.project_id||'   time:'|| (to_date(COLLECTDATE,'yyyymmdd')+1));
      v_update_sql :=' update maintain_timely  set FAULT_TIME= :1,EFFECT_TIME=:2 where projectid=:3 and statisticsDate =:4 and ismerge=:5';
     execute immediate v_update_sql
     using FAULT_TIME,EFFECT_TIME,pj.project_id,TO_DATE(COLLECTDATE,'YYYYMMDD'),'0';

     commit;
    end loop;
  END;

  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('可用率汇总统计错误，'||sqlcode || sqlerrm );
      ROLLBACK;
end SERVER_Data_ANALYSIS_PRO;