create or replace procedure PCREPORT is

  startDate  DATE; --起始如期

  nowTime    DATE; --当前日期

  nowTime2   DATE; --当前日期下限用于支持跨年

  orderDate  DATE; --计算时候的当前下订单日期

  orderDate2 DATE; --跨年使用的日期范围下限

  returns          number; --退订总数

  renew            number; --续费总数

  active           number; --激活人数

  noActive         number; --未激活人数

  activeReturn     number; --激活退订人数

  noActiveReturn   number; --未激活退订人数

  reportDateActive number; --报表时间激活的人数

  tempStr      varchar2(3000);

  i            number;

  number_value number;

  --1.起始时间与当前时间减法，用结果做循环遍历的次数

  --2.每次遍历在起始时间上加，遍历次数的月值

  --3.将这个时间值作为条件，查询统计出一个统计值，将这个值插入到数据库对应的字段

begin

  startDate := to_date('2009-04-01', 'yyyy-mm-dd');

  /*  SELECT to_char(sysdate, 'yyyy-mm-dd ') into tempStr from dual;

  nowTime      := to_date(tempStr, 'yyyy-mm-dd');*/

  select to_date(to_char(sysdate, 'YYYYMM') || '', 'YYYY-MM-DD')

    into nowTime

    from dual;

  nowTime2 := add_months(nowTime, 1);

  number_value := months_between(nowTime, startDate); --月份差

  i := 0;

  for i in 0 .. number_value loop

    orderDate  := add_months(startDate, i);

    orderDate2 := add_months(orderDate, 1);

    -- 续费及激活总数的计算

    select count(*)

      into renew

      from ord_order

     where AGREE_TO_TIME between orderDate and orderDate2 --(select to_char(AGREE_TO_TIME, 'mm') from dual) =

       and (end_time = to_date('1900-01-01', 'yyyy-mm-dd') or

           end_time >= nowTime); --(select to_char(end_time, 'mm') from dual) >= '07');

    --续费用户退订总人数

    select count(*)

      into returns

      from ord_order

     where AGREE_TO_TIME between orderDate and orderDate2 --(select to_char(AGREE_TO_TIME, 'mm') from dual) = '05'

       and end_time between nowTime and nowTime2; --(select to_char(end_time, 'mm') from dual) = '07';

    --续费用户激活未退订

    select count(*)

      into noActiveReturn

      from ord_order

     where AGREE_TO_TIME between orderDate and orderDate2 -- (select to_char(AGREE_TO_TIME, 'mm') from dual) = '06'

       and end_time between nowTime and nowTime2 --(select to_char(end_time, 'mm') from dual) = '07'

       and BECOME_EFFECTIVE_TIME < nowTime --to_date('2009-07-01', 'yyyy-mm-dd')

       and BECOME_EFFECTIVE_TIME = to_date('1900-01-01', 'yyyy-mm-dd');

    -- 当前报表月在个月激活的人数

    select count(*)

      into reportDateActive

      from ord_order

     where AGREE_TO_TIME between orderDate and orderDate2 --(select to_char(AGREE_TO_TIME, 'mm') from dual) = '05'

       and (end_time = to_date('1900-01-01', 'yyyy-mm-dd') or

           end_time >= nowTime) --(select to_char(end_time, 'mm') from dual) >= '06')

       and BECOME_EFFECTIVE_TIME between nowTime and nowTime2; --(select to_char(BECOME_EFFECTIVE_TIME, 'mm') from dual) = '06';

    -----------------------------之前经过校验得到正确结果---------------------

    --续费用户报表月激活人数

    select count(*)

      into active

      from ord_order

     where AGREE_TO_TIME between orderDate and orderDate2  --(select to_char(AGREE_TO_TIME, 'mm') from dual) = '04'

       and BECOME_EFFECTIVE_TIME != to_date('1900-01-01', 'yyyy-mm-dd')

       and BECOME_EFFECTIVE_TIME < nowTime     --(select to_char(BECOME_EFFECTIVE_TIME, 'mm') from dual) < '07'

       and ((end_time = to_date('1900-01-01', 'yyyy-mm-dd')) or

             end_time >= nowTime);                       --(select to_char(end_time, 'mm') from dual) >= '07');

    activeReturn := returns - noActiveReturn;

    noActive := renew - active - reportDateActive;

    --做插入操作

    insert into report_pc51

      (REPORTDATE,

       ORDERDATE,

       RENEW,

       RETURNS,

       NOACTIVERETURN,

       REPORTDATEACTIVE,

       ACTIVERETURN,

       ACTIVE,

       NOACTIVE)

    values

      (nowTime,

       orderDate,

       renew,

       returns,

       noActiveReturn,

       reportDateActive,

       activeReturn,

       active,

       noActive);

    dbms_output.put_line(i);

  end loop;

  COMMIT;

end PCREPORT;