test

create or replace procedure pc_area_pm_avg AUTHID DEFINER
is
  type cu_area_terminal IS REF CURSOR;
  area cu_area_terminal;
  areaCursor cu_area_terminal;
  currentTerminalCursor cu_area_terminal;

  v_currentnumber number;
  v_statisticweight number;
  v_sum number;
  v_weight_sum number;
  v_sql varchar2(1000);
  v_levelCode varchar2(20);
  v_areaLevelCode varchar2(20);
  v_tableName varchar2(30);
  v_isExists number;
  v_historyID number;
begin
  delete from t_area_sum;
  open area for 'select area.levelcode from t_area area where area.arealevel=1';
  loop fetch area into v_levelCode;
       exit when area%NOTFOUND;
       v_tableName:='T_AREA_HISTORY_'||v_levelCode;
       select count(1) into v_isExists from user_tables where table_name=v_tableName;
       if v_isExists=0 then
          v_sql:='create table '||v_tableName||'(HISTORYID NUMBER(10) not null,SUMTIME DATE not null,SUMDATA NUMBER(10,3) not null,LEVELCODE NVARCHAR2(20) not null)';
          execute immediate v_sql;
       end if;
       open areaCursor for 'select area.levelcode from t_area area where area.arealevel=3 and area.levelcode like '''||v_levelCode||'________''';
       loop fetch areaCursor into v_areaLevelCode;
         exit when areaCursor%NOTFOUND;
         open currentTerminalCursor for select currentnumber, statisticweight from t_terminal where t_terminal.levelcode=v_areaLevelCode;
         v_sum:=0;
         v_weight_sum:=0;
         loop fetch currentTerminalCursor into v_currentnumber,v_statisticweight;
             exit when currentTerminalCursor%NOTFOUND;
             v_sum:=v_sum+v_currentNumber*v_statisticweight;
             v_weight_sum:=v_weight_sum+v_statisticweight;
         end loop;
         close currentTerminalCursor;
         sp_nextval(v_tableName,v_historyID);
         if v_sum !=0 and v_weight_sum != 0 then
             insert into t_area_sum (LEVELCODE,PMSUMDATA,UPDATETIME) values(v_areaLevelCode,ROUND(v_sum/v_weight_sum,0),sysdate);
             v_sql:='insert into '||v_tableName||'(HISTORYID,SUMTIME,SUMDATA,LEVELCODE) values('||v_historyID||',sysdate,'||ROUND(v_sum/v_weight_sum,0)||','''||v_areaLevelCode||''')';
             execute immediate v_sql;
         end if;
    end loop;
    commit;
    close areaCursor;

    open areaCursor for 'select area.levelcode from t_area area where area.arealevel=2 and area.levelcode like '''||v_levelCode||'____''';
    loop fetch areaCursor into v_areaLevelCode;
         exit when areaCursor%NOTFOUND;
         open currentTerminalCursor for 'select pmsumdata from t_area_sum where levelcode like '''||v_areaLevelCode||'____''';
         v_sum:=0;
         v_weight_sum:=0;
         loop fetch currentTerminalCursor into v_currentNumber;
             exit when currentTerminalCursor%NOTFOUND;
             v_sum:=v_sum+v_currentNumber;
             v_weight_sum:=v_weight_sum+1;
         end loop;
         close currentTerminalCursor;
         sp_nextval(v_tableName,v_historyID);
         if v_sum !=0 and v_weight_sum != 0 then
             insert into t_area_sum (LEVELCODE,PMSUMDATA,UPDATETIME) values(v_areaLevelCode,ROUND(v_sum/v_weight_sum,0),sysdate);
             v_sql:='insert into '||v_tableName||'(HISTORYID,SUMTIME,SUMDATA,LEVELCODE) values('||v_historyID||',sysdate,'||ROUND(v_sum/v_weight_sum,0)||','''||v_areaLevelCode||''')';
             execute immediate v_sql;
         end if;
    end loop;
    commit;
    close areaCursor;
    open areaCursor for 'select area.levelcode from t_area area where area.arealevel=1 and area.levelcode='||v_levelCode;
    loop fetch areaCursor into v_areaLevelCode;
         exit when areaCursor%NOTFOUND;
         open currentTerminalCursor for 'select pmsumdata from t_area_sum where levelcode like '''||v_areaLevelCode||'____''';
         v_sum:=0;
         v_weight_sum:=0;
         loop fetch currentTerminalCursor into v_currentNumber;
             exit when currentTerminalCursor%NOTFOUND;
             v_sum:=v_sum+v_currentNumber;
             v_weight_sum:=v_weight_sum+1;
         end loop;
         close currentTerminalCursor;
         sp_nextval(v_tableName,v_historyID);
         if v_sum !=0 and v_weight_sum != 0 then
             insert into t_area_sum (LEVELCODE,PMSUMDATA,UPDATETIME) values(v_levelCode,ROUND(v_sum/v_weight_sum,0),sysdate);
             v_sql:='insert into '||v_tableName||'(HISTORYID,SUMTIME,SUMDATA,LEVELCODE) values('||v_historyID||',sysdate,'||ROUND(v_sum/v_weight_sum,0)||','''||v_levelCode||''')';
             execute immediate v_sql;
         end if;
    end loop;
    commit;
    close areaCursor;
  end loop;
end;