一个Oracle存储过程示例

下面是一个用于计算，每个月从4月起各个月的记录条数统计，直到当前月

1.打开PLSQL，新建一个存储过程，点击菜单文件|新建|程序窗口|过程，在弹出的对话框中输入存储过程名字，确定

复制下面的代码到编辑器，运行

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') || '01', '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;