游标镶套学习(二)

create or replace procedure weekport as

    weekcount     number;---------周数
    monthday      date;------------本月第一天
    yearday       date;-------------本年第一天
    lastday       date;-------------开始时间
    endday        date;--------------结束时间
    currentday    date;--------------时间
    currentyear   number;-------当前年
    currentweek   number;-------当前周
    tempyixun     number;---------已巡
    mes           VARCHAR2(50);----消息
begin

------------游标1：获取获得需要计算的周目、开始时间、结束时间
    declare
        cursor time_cur is select distinct to_char(patraldate,'yyyy') as currentyear
                                          ,(select weeknumber from weekreport where  patraldate>=startdate and patraldate <=enddate)  as currentweek
                                          ,(select startdate from weekreport where  patraldate>=startdate and patraldate <=enddate) as  startdate                                                      
                                          ,(select enddate from weekreport where  patraldate>=startdate and patraldate <=enddate) as enddate  
                                          ,trunc(add_months(last_day(patraldate), -1) + 1) as monthday
                                          ,trunc(patraldate,'yyyy') as yearday
                           from PATROLPOLETABLE  where patraldate >=sysdate-6; ---巡视日期的最大值小于等于结束日期
------------游标2：获取各部门人员信息
        cursor departpeople_cur is select a.department, a.entercode
                                   from usertable a, departmenttable b
                                   where a.department = b.department and b.workstatus = 1
                                                                     and b.isshudi = 0;----游标2：遍历巡视人员 
------------游标3：获取巡视日期、人员信息、线路名称、杆塔号

        cursor xunshi_cur is select c.lastpatraltime, a.patrolpeople, a.circuitry, a.poleid
                             from manunpatrolweektable a,weekreport b,v_patral_pole c
                             where patrolyear = currentyear and patrolweek = currentweek
                                                            and patrolyear = b.yearnumber
                                                            and b.weeknumber = patrolweek
                                                            and a.circuitry = c.circuitryname
                                                            and a.poleid = c.poleid
                                                            and c.people = a.patrolpeople
                                                            and c.lastpatraltime >= b.startdate
                                                            and c.lastpatraltime < b.enddate+1; -----+ 1 - 1 / 86400;
------------游标4：获取部门信息
        cursor depart_cur is select b.department from departmenttable b where b.workstatus = 1 and b.isshudi = 0;

        var_time_cur           time_cur%rowtype;--------游标1变量
        var_departpeople_cur   departpeople_cur%rowtype;--------游标2变量
        var_xunshi_cur         xunshi_cur%rowtype; --------游标3变量
        var_depart_cur         depart_cur%rowtype; --------游标3变量
    begin 
/*===============1.更新人员巡视表报ManPatrolweektable的yixun、monthyixun、yearyixun的数据===========*/
        mes := '1.开始统计巡视人员数据';
        dbms_output.put_line(mes);
        open time_cur;----打开游标1
        loop-----开始游标1的循环
            fetch time_cur into var_time_cur;-----获得游标1的值
            exit when time_cur%notfound;-----游标1的出口
            
            currentyear:=var_time_cur.currentyear;-----------获得currentyear（当前年）
            currentweek:=var_time_cur.currentweek;-----------获得currentweek（当前周）
            lastday:=var_time_cur.startdate;-----------获得lastday（本周开始日期）
            endday:=var_time_cur.enddate;-----------获得endday（本周结束日期）-----------这里采用结束日期+1的原因是oracgle中data数据中时分秒默认格式是00:00:00,已“2019-02-25”为例直接取值的话就是“2019-02-25 00:00:00”,这样就少了一天的数据
            monthday:=var_time_cur.monthday;-----------获得monthday（本月第一天）
            yearday:=var_time_cur.yearday;-----------获得yearday（本年第一天）

            mes :='当前年'||currentyear;
            dbms_output.put_line(mes);------------------------------------------输出currentyear
            mes :='当前周'||currentweek;
            dbms_output.put_line(mes);------------------------------------------输出currentweek
            mes :='本周开始日期'||lastday;
            dbms_output.put_line(mes);------------------------------------------输出lastday
            mes :='本周结束日期'||endday;
            dbms_output.put_line(mes);------------------------------------------输出ENDDATE
            mes :='本月第一天'||monthday;
            dbms_output.put_line(mes);------------------------------------------输出monthday
            mes :='本年第一天'||yearday;
            dbms_output.put_line(mes);------------------------------------------输出yearday
            
            open departpeople_cur;-----打开游标2
            loop-----开始游标2的循环
                exit when departpeople_cur%notfound; -----游标2出口
                fetch departpeople_cur into var_departpeople_cur; -----游标2值赋值到rowtype 
                dbms_output.put_line(var_departpeople_cur.entercode);-----输出
                
  -----------计算本周已巡------------------,TO_CHAR(b.patraldate,'yyyy-mm-dd')
                select count(*) into tempyixun
                from( select distinct b.circuitryname, b.poleid
                      from patrolpoletable b
                      where b.circuitryname not like '%备份%' and b.patraldate >= lastday
                                                             and b.patraldate < = endday 
                                                             and b.people = var_departpeople_cur.entercode --2019-01-29改为只计算本人已巡
                     );
 
                dbms_output.put_line(tempyixun); 
                     
-----------更新数据库本周已巡      
                update manpatrolweektable b set b.yixun = tempyixun
                where b.patrolpeople = var_departpeople_cur.entercode and b.patrolyear = currentyear
                                                                      and b.patrolweek = currentweek;

                mes :='本周已巡'||tempyixun;
                dbms_output.put_line(mes);-----输出
-----------计算本月已巡：本月第一天到当前节点的巡视结果         
                select count(*) into tempyixun
                from ( select distinct b.circuitryname,b.poleid
                       from patrolpoletable b
                       where b.circuitryname not like '%备份%' and b.patraldate >= monthday
                                                              and b.patraldate <= endday 
                                                              and b.people = var_departpeople_cur.entercode --2019-01-29改为只计算本人已巡
                      );
                      
------------更新数据库本月已巡      
                update manpatrolweektable b set b.monthyixun = tempyixun
                where b.patrolpeople = var_departpeople_cur.entercode and b.patrolyear = currentyear
                                                                      and b.patrolweek = currentweek;

                mes :='本月已巡'||tempyixun;
                dbms_output.put_line(mes); 
                
------------计算本年已巡：本年第一天到当前节点的巡视结果    ,TO_CHAR(b.patraldate,'yyyy-mm-dd')
                select count(*) into tempyixun
                from(select distinct b.circuitryname,b.poleid
                     from patrolpoletable b
                     where b.circuitryname not like '%备份%' and b.patraldate >= yearday
                                                            and b.patraldate <= endday
                                                            and b.people = var_departpeople_cur.entercode--2019-01-29改为只计算本人已巡
                     );
                     
------------更新数据库本年已巡
                update manpatrolweektable b set b.yearyixun = tempyixun
                where b.patrolpeople = var_departpeople_cur.entercode and b.patrolyear = currentyear
                                                                      and b.patrolweek = currentweek;
                mes:='本年已巡'||tempyixun;
                dbms_output.put_line(mes);
                
            end loop;-----结束游标2的循环
            close departpeople_cur;-----关闭游标2
        end loop;-----结束游标1的循环
        close time_cur;-----关闭游标1

/*==============2.更新ManUnPatrolweektable的patrold(巡视日期)==============================*/
        mes:='2.更新巡视日期';
        dbms_output.put_line(mes);
        open time_cur;----打开游标1
        loop--------开启游标1的循环
            fetch time_cur into var_time_cur;-----获得游标1的值
            exit when time_cur%notfound;-----游标1的出口
            
            select var_time_cur.currentyear into currentyear from dual;-----------获得currentyear（当前年）
            select var_time_cur.currentweek into currentweek from dual;-----------获得currentweek（当前周）

            dbms_output.put_line(currentyear);
            dbms_output.put_line(currentweek);
            open xunshi_cur;-----打开游标3
            loop-----------开启游标3的循环
                exit when xunshi_cur%notfound;--------------游标3的出口
                fetch xunshi_cur into var_xunshi_cur;-----------获得游标3的值
---------更新数据库巡视日期      
                update manunpatrolweektable b set b.patroldate = var_xunshi_cur.lastpatraltime
                where b.patrolpeople = var_xunshi_cur.patrolpeople and b.patrolyear = currentyear
                                                                   and b.patrolweek = currentweek
                                                                   and b.circuitry = var_xunshi_cur.circuitry
                                                                   and b.poleid = var_xunshi_cur.poleid;

            end loop;------结束游标3的循环
            close xunshi_cur;----关闭游标3
        end loop;---关闭游标1的循环
        close time_cur;---关闭游标1

/*====================3.更新班组巡视报表TEAMPATROLWEEKTABLE=======================*/
        mes:='3.计算班组巡视';
        dbms_output.put_line(mes);
        open time_cur;----开启游标1
        loop------开启游标1的循环
            fetch time_cur into var_time_cur;-----获得游标1的值
            exit when time_cur%notfound;-----游标1的出口
            
            select var_time_cur.currentyear into currentyear from dual;-----------获得currentyear（当前年）
            select var_time_cur.currentweek into currentweek from dual;-----------获得currentweek（当前周）
            select var_time_cur.startdate into lastday from dual;-------------------获得lastday（本周开始日期）
            select var_time_cur.enddate into endday from dual;---------------------获得endday（本周结束日期）
            select var_time_cur.monthday into monthday from dual;-----------------获得monthday（本月第一天）
            select var_time_cur.yearday into yearday from dual;-------------------获得yearday（本年第一天）

            mes :='当前年'||currentyear;
            dbms_output.put_line(mes);------------------------------------------输出currentyear
            mes :='当前周'||currentweek;
            dbms_output.put_line(mes);------------------------------------------输出currentweek
            mes :='本周开始日期'||lastday;
            dbms_output.put_line(mes);------------------------------------------输出lastday
            mes :='本周结束日期'||endday;
            dbms_output.put_line(mes);------------------------------------------输出ENDDATE
            mes :='本月第一天'||monthday;
            dbms_output.put_line(mes);------------------------------------------输出monthday
            mes :='本年第一天'||yearday;
            dbms_output.put_line(mes);------------------------------------------输出yearday
            
----先清除TEAMPATROLWEEKTABLE中的数据
            delete teampatrolweektable where patrolyear = currentyear and patrolweek = currentweek
                                                                      and startdate = lastday
                                                                      and enddate = endday;
----插入TEAMPATROLWEEKTABLE数据
            insert into teampatrolweektable (patrolyear,patrolweek,startdate,enddate,patrolteam,yixun,tasktime) 
            select currentyear,currentweek,lastday,endday,b.department,0,sysdate
            from departmenttable b
            where b.workstatus = 1 and b.isshudi = 1;

            open depart_cur;---开启游标4
            loop---开启游标4的循环
                exit when depart_cur%notfound; -----游标2出口
                fetch depart_cur into var_depart_cur; -----游标2值赋值到rowtype 
                dbms_output.put_line(var_depart_cur.department); 
                
  ---------计算班组本周已巡-----------------------
                select count(*) into tempyixun
                from (select distinct b.circuitryname, b.poleid
                      from patrolpoletable b
                      where b.circuitryname not like '%备份%' and b.patraldate >= lastday
                                                             and b.patraldate <= endday 
                                                             and b.people in (select entercode from usertable where department = var_depart_cur.department)
                      );
     ------更新班组本周已巡

                update teampatrolweektable b set b.yixun = tempyixun
                where b.patrolteam = var_depart_cur.department and b.patrolyear = currentyear
                                                               and b.patrolweek = currentweek;
                
                mes:='班组本周已巡'||tempyixun;
                dbms_output.put_line(mes); 
                
    ------计算班组本月已巡
                select count(*) into tempyixun
                from(select distinct b.circuitryname,b.poleid
                     from patrolpoletable b
                     where b.circuitryname not like '%备份%' and b.patraldate >= monthday
                                                            and b.patraldate <= endday 
                                                            and b.people in (select entercode from usertable where department = var_depart_cur.department)
                    );
                    
     -------更新班组本月已巡
                update teampatrolweektable b set b.monthyixun = tempyixun
                where b.patrolteam = var_depart_cur.department and b.patrolyear = currentyear
                                                               and b.patrolweek = currentweek;
                
                mes:='班组本月已巡'||tempyixun;
                dbms_output.put_line(mes);
                
    ------计算班组本年已巡
                select count(*) into tempyixun
                from (select distinct b.circuitryname, b.poleid
                      from patrolpoletable b
                      where b.circuitryname not like '%备份%' and b.patraldate >= yearday
                                                             and b.patraldate <= endday 
                                                             and b.people in (select entercode from usertable where department = var_depart_cur.department)
                      );
                      
    -----更新数据库本年已巡
                update teampatrolweektable b set b.yearyixun = tempyixun
                where b.patrolteam = var_depart_cur.department and b.patrolyear = currentyear
                                                               and b.patrolweek = currentweek;
                
                mes:='班组本年已巡'||tempyixun;
                dbms_output.put_line(mes); 
                
   ----计算班组应巡 
                select count(*) into tempyixun
                from poleinfotable a,circuitryinfotable e
                where nvl(e.circuitrystatus,' ') <> '备份' and a.circuitryname = e.circuitryname
                                                          and instr(a.poleid,'门架',1,1) = 0
                                                          and a.weihudept = var_depart_cur.department
                order by e.pressuregrade,a.circuitryname,a.insidepoleid;
                
-------更新班组应巡
                update teampatrolweektable b set b.monthyingxun = tempyixun
                where b.patrolteam = var_depart_cur.department and b.patrolyear = currentyear
                                                               and b.patrolweek = currentweek;
                
                mes:='班组应巡'||tempyixun;
                dbms_output.put_line(mes);
                
            end loop;----结束游标4的循环
            close depart_cur;---关闭游标4
        end loop;--结束游标1的循环
        close time_cur;----关闭游标1
    end;
end weekport;