oracle存储过程例子（实战项目）

Sql代码  

create or replace function func_get_user_by_msisdn(msisdn in number)
------------------------------------------------------------------------------
---功能描述：通过用户的电话号码,调用局方存储过程 获取用户的基本信息，      ---
---          以遍更新card_user_info本地数据表。                            ---
---参数：   msisdn in number 代表用户的电话号码（SIM卡）                   ---
---时间：2008-09-05                                                        ---
---作者：zhouyq                                                            ---
---单位：厦门新科技软件股份有限公司                                        ---
------------------------------------------------------------------------------
return pkg_gps_audit.user_record
is
  user_record_info pkg_gps_audit.user_record; --用户基本信息类型
  v_success number := 1;  --成功标志

begin

 --首先获取正常用户的基本信息
  begin
       pkg_gps_audit.proc_get_userinfo(msisdn,user_record_info);
       v_success := 1;
  exception  when others then
       v_success := 0;
  end;

  --其次，如果正常用户获取不到，再查离线用户信息
  if v_success < 1 then
  begin
       pkg_gps_audit.proc_get_cancel_userinfo(msisdn,user_record_info);
       v_success := 1;
  exception when others then
       v_success := 0;
  end;
  end if;


  if v_success > 0 then
    return user_record_info;
  else
    raise no_data_found;
  end if;


return user_record_info;
end func_get_user_by_msisdn;





create or replace function func_get_user_id(msisdn_bak in number)
------------------------------------------------------------------------------
---功能描述：通过用户的电话号码获取用户的ID，(有保证数据及时性)            ---
---参数：   msisdn in number 代表用户的电话号码（SIM卡）                   ---
---时间：2008-09-05                                                        ---
---作者：zhouyq                                                            ---
---单位：厦门新科技软件股份有限公司                                        ---
------------------------------------------------------------------------------
return number
is
user_info pkg_gps_audit.user_record; --用户基本信息

card_info card_user_info%rowtype; --用户基本信息表结构
type base_cursor is ref cursor;
cr base_cursor;

user_id number(15); --用户ID；
begin
  begin
    --如果本地card_user_info表可以找到数据，先在本地查找。
    open cr for select * from card_user_info where msisdn = msisdn_bak;
    fetch cr into card_info;
    if cr%found then

      user_id := card_info.user_id;
    else
      --获取用户基本信息
      user_info := func_get_user_by_msisdn(msisdn_bak);

      user_id := user_info.user_id;
    end if;

    close cr;

    --返回用户ID
     return user_id;
  exception
    when others then
    raise no_data_found;  --抛出异常
  end;



end;





create or replace function func_is_first_day
------------------------------------------------------------------------------
---功能描述：判断今天是否是该月的第一天（1号）                             ---
---返回参数：1代表是，0代表不是                                            ---
---时间：2008-09-05                                                        ---
---作者：zhouyq                                                            ---
---单位：厦门新科技软件股份有限公司                                        ---
------------------------------------------------------------------------------
return number
is
  v_result number := 0;

  v_day varchar2(2);
begin
  select to_char(sysdate,'dd') into v_day from dual;
  if v_day = '01' then
    v_result := 1;
  else
    v_result := 0;
  end if;

  return(v_result);
end func_is_first_day;





create or replace procedure proc_delete_day_fee_info
------------------------------------------------------------------------------
---功能描述： 只保留一个月得数据（8月的日账单10月份删除）                  ---
---时间：2008-09-08                                                        ---
---作者：zhouyq                                                            ---
---单位：厦门新科技软件股份有限公司                                        ---
------------------------------------------------------------------------------
is

type base_cursor is ref cursor;
cf base_cursor;
v_fee_id number(18);
begin
  begin
    open cf for  select a.fee_id from user_fee_info a ,day_fee_info b where a.fee_id = b.fee_id and to_char(b.start_time,'yyyy-mm') = to_char(add_months(sysdate,0),'yyyy-mm');
    fetch cf into v_fee_id;
    while cf%found loop
      delete from user_fee_info where fee_id = v_fee_id;
      delete from day_fee_info where fee_id = v_fee_id;
      commit;

      fetch cf into v_fee_id;
    end loop;

    close cf;

  exception when others then
    rollback;
    return;
  end;



end proc_delete_day_fee_info;




create or replace procedure proc_delete_month_fee_info
------------------------------------------------------------------------------
---功能描述： 只保留6个月得数据（8月的日账单1月份删除）                  ---
---时间：2008-09-08                                                        ---
---作者：zhouyq                                                            ---
---单位：厦门新科技软件股份有限公司                                        ---
------------------------------------------------------------------------------
is

type base_cursor is ref cursor;
cf base_cursor;
v_fee_id number(18);
begin
  begin
    open cf for  select a.fee_id from user_fee_info a ,month_fee_info b where a.fee_id = b.fee_id and to_char(b.start_time,'yyyy-mm') = to_char(add_months(sysdate,0),'yyyy-mm');
    fetch cf into v_fee_id;
    while cf%found loop
      delete from user_fee_info where fee_id = v_fee_id;
      delete from month_fee_info where fee_id = v_fee_id;
      commit;

      fetch cf into v_fee_id;
    end loop;

    close cf;

  exception when others then
    rollback;
    return;
  end;



end proc_delete_month_fee_info;



create or replace procedure proc_down_card_user_info
------------------------------------------------------------------------------
---功能描述：(重要)通过查询本地的group_sims表，获取sim号码集合。           ---
---          该表里面的sim字段有两种类型，1种是真实的sim号码，另外一种是   ---
---          虚拟的SIM号码，该虚拟的号码可以对应多哥真实的SIM号码。        ---
---          simtype字段代表SIM类型,0代表是虚拟的号码，1代表是真实的号码。 ---
---          当sim字段为虚拟号码时，对应的simtype为0，同时groupsim为空     ---
---          当sim字段为真实号码时，对应的simtype为1，如果groupsim为空，   ---
---          代表该号码没有对应的虚拟号。                                  ---
---          groupuserid字段代表号码所对应的本地用户，一个用户可以对应多个 ---
---          sim(虚拟的与真实的。)                                         ---
---                                                                        ---
---          遍历group_sims表，查询sim字段，如果为虚拟号码，就调用         ---
---           proc_get_payed_msisdns，获取其绑定的真实号码,然后将该批真实  ---
---           号码插入到group_sims，同时更新card_user_info表，如果sim是    ---
---          真实号码，则只更新card_user_info表 。                         ---
---时间：2008-09-05                                                        ---
---作者：zhouyq                                                            ---
---单位：厦门新科技软件股份有限公司                                        ---
------------------------------------------------------------------------------
is

group_sims_info group_sims%rowtype;--定义group_sims表类型

type type_group_sims_ref is ref cursor; --定义group_sims表游标类型
group_sims_ref type_group_sims_ref;

user_record_info pkg_gps_audit.user_record; --定义用户基本类型

msisdn_table_temp pkg_gps_audit.msisdn_table; --用户SIM号码集合
msisdn_len number(10) := 0; --用户SIM号码集合长度

v_success number(1) := 1; --成功标志

begin
  begin
    --打开group_sims游标
    open group_sims_ref for select * from group_sims where (groupsim is  null or length(ltrim(groupsim)) = 0);
    fetch group_sims_ref into group_sims_info;

    --开始遍历该游标
    while group_sims_ref%found loop
      --首先判断是否是虚拟号码,
      if group_sims_info.simtype = 0 then
        --首先通过虚拟号码，获取对应的SIM号码;
        begin
          pkg_gps_audit.proc_get_payed_msisdns(group_sims_info.sim,msisdn_table_temp,msisdn_len);

          if msisdn_len > 0 then
            for iLen in 1 .. msisdn_len loop
              --首先更新原来group_sims表中同一个虚拟卡所对应的sim卡(先删后插)
              delete from group_sims t where t.simtype = 1 and to_number(t.groupsim) = group_sims_info.sim and t.groupuserid = group_sims_info.groupuserid;


              for xLen in 1..msisdn_len loop
                begin
                  -----通过msisdn获取用户基本信息
                  user_record_info := func_get_user_by_msisdn(msisdn_table_temp(xLen));
                exception when others then
                  dbms_output.put_line('通过msisdn获取用户基本信息出现了异常！');
                  v_success := 0;
                end;


                if v_success > 0 then
                   insert into group_sims(id,sim,groupsim,groupuserid,simtype,updatetime)values(
                   SEQ_GROUP_SIMS.Nextval,to_char(msisdn_table_temp(xLen)),group_sims_info.sim,group_sims_info.groupuserid,
                  1,sysdate);

                  --更新原来在card_user_info里面的数据(先删除已经存在的再插入新的数据)
                  delete from card_user_info where msisdn =  msisdn_table_temp(xLen);

                  insert into card_user_info(user_id,msisdn,brand_name,deal_name,service_status,stop_time,status)
                  values(user_record_info.user_id,user_record_info.msisdn,user_record_info.brand_name,user_record_info.deal_name
                  ,user_record_info.service_status,user_record_info.stop_time,1);
                end if;

                --提交数据
                commit;
              end loop;

            end loop;

          end if;


          --没有与该虚拟卡对应的SIM号码
          if msisdn_len <= 0 then
            --首先更新原来group_sims表中同一个虚拟卡所对应的sim卡(先删后插)
              delete from group_sims t where t.simtype = 1 and to_number(t.groupsim) = group_sims_info.sim and t.groupuserid = group_sims_info.groupuserid;
              commit;
          end if;

        exception when others then
          --回滚数据
          rollback;
          dbms_output.put_line('首先通过虚拟号码，获取对应的SIM号码出现了异常!');
        end;
      end if;

      --如果是真实号码,并且没有附属虚拟号码，则直接更新card_user_info表
      if group_sims_info.simtype > 0 and  (group_sims_info.groupsim is null or length(group_sims_info.groupsim) = 0 ) then
        begin
          user_record_info := func_get_user_by_msisdn(group_sims_info.sim);

           --更新原来在card_user_info里面的数据(先删除已经存在的再插入新的数据)
          delete from card_user_info where msisdn =  group_sims_info.sim ;
          insert into card_user_info(user_id,msisdn,brand_name,deal_name,service_status,stop_time,status)
          values(user_record_info.user_id,user_record_info.msisdn,user_record_info.brand_name,user_record_info.deal_name
          ,user_record_info.service_status,user_record_info.stop_time,1);

          --提交数据
          commit;

        exception when others then
           --回滚数据
          rollback;
          dbms_output.put_line('更新card_user_info表出现了异常');
        end;


      end if;


      --遍历游标
      fetch group_sims_ref into group_sims_info;
    end loop;

    --关闭游标
    close group_sims_ref;



  exception
    when others then
    return;
  end;



end proc_down_card_user_info;



create or replace procedure proc_down_change_card

------------------------------------------------------------------------------
---功能描述：(重要)通过查询本地的group_sims表，获取sim号码集合。           ---
---          该表里面的sim字段有两种类型，1种是真实的sim号码，另外一种是   ---
---          虚拟的SIM号码，该虚拟的号码可以对应多哥真实的SIM号码。        ---
---          simtype字段代表SIM类型,0代表是虚拟的号码，1代表是真实的号码。 ---
---          当sim字段为虚拟号码时，对应的simtype为0，同时groupsim为空     ---
---          当sim字段为真实号码时，对应的simtype为1，如果groupsim为空，   ---
---          代表该号码没有对应的虚拟号。                                  ---
---          groupuserid字段代表号码所对应的本地用户，一个用户可以对应多个 ---
---          sim(虚拟的与真实的。)                                         ---
---                                                                        ---
---          遍历group_sims表，查询sim字段，如果为虚拟号码，就调用         ---
---           proc_get_payed_msisdns，获取其绑定的真实号码,然后再获取换补卡---
---           信息以更新change_card表，如果sim是真实号码，                 ---
---            则只更新change_card表 。                                    ---
---时间：2008-09-05                                                        ---
---作者：zhouyq                                                            ---
---单位：厦门新科技软件股份有限公司                                        ---
------------------------------------------------------------------------------

is

group_sims_info group_sims%rowtype;--定义group_sims表类型

type type_group_sims_ref is ref cursor; --定义group_sims表游标类型
group_sims_ref type_group_sims_ref;



simcard_table_temp pkg_gps_audit.simcard_table; --用户换补卡信息集合
simcard_len number(10) := 0; --用户换补卡信息集合长度

msisdn_table_temp pkg_gps_audit.msisdn_table; --用户SIM号码集合
msisdn_len number(10) := 0; --用户SIM号码集合长度

begin
  begin
    --打开group_sims游标
    open group_sims_ref for select * from group_sims where (groupsim is  null or length(ltrim(groupsim)) = 0);
    fetch group_sims_ref into group_sims_info;

    --开始遍历该游标
    while group_sims_ref%found loop
      --首先判断是否是虚拟号码,
      if group_sims_info.simtype = 0 then
        --首先通过虚拟号码，获取对应的SIM号码;
        begin
          pkg_gps_audit.proc_get_payed_msisdns(group_sims_info.sim,msisdn_table_temp,msisdn_len);

          if msisdn_len > 0 then --有数据
            for iLen in 1 .. msisdn_len loop
               begin
                 --通过用户ID，获取远程的换补卡用户信息集合
                 pkg_gps_audit.proc_get_simcard_his(func_get_user_id(msisdn_table_temp(iLen)),simcard_table_temp,simcard_len);

                 --更新原来在change_card里面的数据(先删除已经存在的再插入新的数据)
                 if simcard_len > 0 then
                   --这个步骤的删除条件 有待确认。？？
                   delete from change_card where msisdn = group_sims_info.sim ;
                   for ilen in 1 .. simcard_len loop
                     insert into change_card(user_id,msisdn,accept_time,accept_memo,status) values(
                     simcard_table_temp(ilen).user_id,simcard_table_temp(ilen).msisdn,simcard_table_temp(ilen).accept_time,
                     simcard_table_temp(ilen).accept_memo,1);
                   end loop;
                 end if;

                 if simcard_len = 0 then
                   dbms_output.put_line('通过用户IfffffffffD，获取远程的换补卡用户信息集合,出现了异常!');
                 end if;

                 commit;
               exception when others then
                 dbms_output.put_line('通过用户ID，获取远程的换补卡用户信息集合,出现了异常!');
                 rollback;
               end;


            end loop;
          end if;



        exception when others then
          --回滚数据
          rollback;
          dbms_output.put_line('首先通过虚拟号码，获取对应的SIM号码出现了异常!');
        end;
      end if;


      if  group_sims_info.simtype > 0 and  (group_sims_info.groupsim is null or length(group_sims_info.groupsim) = 0 ) then --如果是真实号码,并且没有附属虚拟号码，则直接更新change_card表
        begin
          --通过用户ID，获取远程的换补卡用户信息集合
          pkg_gps_audit.proc_get_simcard_his(func_get_user_id(group_sims_info.sim),simcard_table_temp,simcard_len);

          --更新原来在change_card里面的数据(先删除已经存在的再插入新的数据)
          if simcard_len > 0 then
            --这个步骤的删除条件 有待确认。？？
            delete from change_card where msisdn = group_sims_info.sim ;
            for ilen in 1 .. simcard_len loop
              insert into change_card(user_id,msisdn,accept_time,accept_memo,status) values(
              simcard_table_temp(ilen).user_id,simcard_table_temp(ilen).msisdn,simcard_table_temp(ilen).accept_time,
              simcard_table_temp(ilen).accept_memo,1);
            end loop;
          end if;


          --提交数据
          commit;

        exception when others then
           --回滚数据
          rollback;
           dbms_output.put_line('更新change_card表出现了异常');
        end;


      end if;


      --遍历游标
      fetch group_sims_ref into group_sims_info;
    end loop;

    --关闭游标
    close group_sims_ref;



  exception
    when others then
    return;
  end;



end proc_down_change_card;



create or replace procedure proc_down_fee_info
------------------------------------------------------------------------------
---功能描述：(重要)通过查询本地的group_sims表，获取sim号码集合。           ---
---          该表里面的sim字段有两种类型，1种是真实的sim号码，另外一种是   ---
---          虚拟的SIM号码，该虚拟的号码可以对应多哥真实的SIM号码。        ---
---          simtype字段代表SIM类型,0代表是虚拟的号码，1代表是真实的号码。 ---
---          当sim字段为虚拟号码时，对应的simtype为0，同时groupsim为空     ---
---          当sim字段为真实号码时，对应的simtype为1，如果groupsim为空，   ---
---          代表该号码没有对应的虚拟号。                                  ---
---          groupuserid字段代表号码所对应的本地用户，一个用户可以对应多个 ---
---          sim(虚拟的与真实的。)                                         ---
---                                                                        ---
---          遍历group_sims表，查询sim字段，如果为虚拟号码，就调用         ---
---           proc_get_payed_msisdns，获取其绑定的真实号码,然后获取每日消费---
---           情况，再分别保存到day_fee_Info,month_fee_info两张表，如果    ---
---           sim是 真实号码，则只更新day_fee_Info,month_fee_info表 。     ---
---时间：2008-09-05                                                        ---
---作者：zhouyq                                                            ---
---单位：厦门新科技软件股份有限公司                                        ---
------------------------------------------------------------------------------

is

group_sims_info group_sims%rowtype;--定义group_sims表类型

type type_group_sims_ref is ref cursor; --定义group_sims表游标类型
group_sims_ref type_group_sims_ref;


msisdn_table_temp pkg_gps_audit.msisdn_table; --用户SIM号码集合
msisdn_len number(10) := 0; --用户SIM号码集合长度

base_fee pkg_gps_audit.fee_record;  --用户每日消费结构信息

is_first_day number(1); --是否是每月一号标志
user_fee_info_nextval number(15); --user_fee_info表的下一个序列号

user_fee_info_temp pkg_gps_audit.fee_record;  --user_fee_info数据缓存

v_fee_id number(15);--消费ID


begin
  begin

    --打开group_sims游标
    open group_sims_ref for select * from group_sims where (groupsim is  null or length(ltrim(groupsim)) = 0);
    fetch group_sims_ref into group_sims_info;

    --开始遍历该游标
    while group_sims_ref%found loop
      --首先判断是否是虚拟号码,
      if group_sims_info.simtype = 0 then
        --首先通过虚拟号码，获取对应的SIM号码;
        begin
          pkg_gps_audit.proc_get_payed_msisdns(group_sims_info.sim,msisdn_table_temp,msisdn_len);

          if msisdn_len > 0 then --有数据
            for iLen in 1 .. msisdn_len loop

              --首先获取该用户每日消费记录,如果今天刚好是1号，那么获取到的数据刚好是上个月1-30号的总和
              --如果不是1号，那么获取到的是本月前几天的总和
              pkg_gps_audit.proc_get_userfee(func_get_user_id(msisdn_table_temp(iLen)),base_fee);

              --判断当前日期是否是每月1号
              is_first_day := func_is_first_day();
              if is_first_day = 1 then  --1号

                -----(begin)保存到user_fee_info,day_fee_info表--------------
                begin

                -----计算出上个月的总和
                begin
                  select  user_id,
                  sum(fee_base),sum(fee_pkg_month),sum(fee_keep),sum(fee_oth_month),sum(fee_vpn),
                  sum(fee_shift),sum(fee_local),sum(fee_long),sum(fee_inprov),sum(fee_inprov_long),
                  sum(fee_outprov),sum(fee_outprov_long),sum(fee_inter),sum(fee_inter_long),
                  sum(fee_cmnet),sum(fee_trust),sum(fee_ptp_sms),sum(fee_mms),sum(fee_magazine),
                  sum(fee_gprs),sum(fee_wap),sum(fee_data_month),
                  sum(fee_data),sum(fee_ring),sum(fee_display),sum(fee_ext),sum(fee_other),
                  sum(fee_all),sum(fee_for_others),sum(fee_by_others)
                  into user_fee_info_temp from user_fee_info
                  where user_id = base_fee.user_id
                  and fee_id in (select fee_id from day_fee_info where to_char(start_time,'yyyy-mm') = to_char(add_months(sysdate,-1),'yyyy-mm'))  group by user_id ;

                exception when others then
                  user_fee_info_temp.user_id := base_fee.user_id;
                  user_fee_info_temp.fee_base := 0.00;
                  user_fee_info_temp.fee_pkg_month := 0.00;
                  user_fee_info_temp.fee_keep := 0.00;
                  user_fee_info_temp.fee_oth_month := 0.00;
                  user_fee_info_temp.fee_vpn := 0.00;
                  user_fee_info_temp.fee_shift := 0.00;
                  user_fee_info_temp.fee_local := 0.00;
                  user_fee_info_temp.fee_long := 0.00;
                  user_fee_info_temp.fee_inprov := 0.00;
                  user_fee_info_temp.fee_inprov_long := 0.00;
                  user_fee_info_temp.fee_outprov := 0.00;
                  user_fee_info_temp.fee_outprov_long := 0.00;
                  user_fee_info_temp.fee_inter := 0.00;
                  user_fee_info_temp.fee_inter_long := 0.00;
                  user_fee_info_temp.fee_cmnet := 0.00;
                  user_fee_info_temp.fee_trust := 0.00;
                  user_fee_info_temp.fee_ptp_sms := 0.00;
                  user_fee_info_temp.fee_mms := 0.00;
                  user_fee_info_temp.fee_magazine := 0.00;
                  user_fee_info_temp.fee_gprs := 0.00;
                  user_fee_info_temp.fee_wap := 0.00;
                  user_fee_info_temp.fee_data_month := 0.00;
                  user_fee_info_temp.fee_data := 0.00;
                  user_fee_info_temp.fee_ring := 0.00;
                  user_fee_info_temp.fee_display := 0.00;
                  user_fee_info_temp.fee_ext := 0.00;
                  user_fee_info_temp.fee_other := 0.00;
                  user_fee_info_temp.fee_all := 0.00;
                  user_fee_info_temp.fee_for_others := 0.00;
                  user_fee_info_temp.fee_by_others := 0.00;
                end;


                --保存到user_fee_info,day_fee_info表
                select seq_user_fee_info.nextval into user_fee_info_nextval from dual;
                insert into user_fee_info(fee_id,user_id,fee_base,fee_pkg_month,fee_keep,fee_oth_month,fee_vpn,
                fee_shift,fee_local,fee_long,fee_inprov,fee_inprov_long,fee_outprov,fee_outprov_long,fee_inter,fee_inter_long,
                fee_cmnet,fee_trust,fee_ptp_sms,fee_mms,fee_magazine,fee_gprs,fee_wap,fee_data_month,
                fee_data,fee_ring,fee_display,fee_ext,fee_other,fee_all,fee_for_others,fee_by_others)
                values(
                user_fee_info_nextval,base_fee.user_id,
                decode(sign(base_fee.fee_base-user_fee_info_temp.fee_base),1,base_fee.fee_base-user_fee_info_temp.fee_base,0.00),
                decode(sign(base_fee.fee_pkg_month-user_fee_info_temp.fee_pkg_month),1,base_fee.fee_pkg_month-user_fee_info_temp.fee_pkg_month,0.00),
                decode(sign(base_fee.fee_keep-user_fee_info_temp.fee_keep),1,base_fee.fee_keep-user_fee_info_temp.fee_keep,0.00),
                decode(sign(base_fee.fee_oth_month-user_fee_info_temp.fee_oth_month),1,base_fee.fee_oth_month-user_fee_info_temp.fee_oth_month,0.00),
                decode(sign(base_fee.fee_vpn-user_fee_info_temp.fee_vpn),1,base_fee.fee_vpn-user_fee_info_temp.fee_vpn,0.00),
                decode(sign(base_fee.fee_shift-user_fee_info_temp.fee_shift),1,base_fee.fee_shift-user_fee_info_temp.fee_shift,0.00),
                decode(sign(base_fee.fee_local-user_fee_info_temp.fee_local),1,base_fee.fee_local-user_fee_info_temp.fee_local,0.00),
                decode(sign(base_fee.fee_long-user_fee_info_temp.fee_long),1,base_fee.fee_long-user_fee_info_temp.fee_long,0.00),
                decode(sign(base_fee.fee_inprov-user_fee_info_temp.fee_inprov),1,base_fee.fee_inprov-user_fee_info_temp.fee_inprov,0.00),
                decode(sign(base_fee.fee_inprov_long-user_fee_info_temp.fee_inprov_long),1,base_fee.fee_inprov_long-user_fee_info_temp.fee_inprov_long,0.00),
                decode(sign(base_fee.fee_outprov-user_fee_info_temp.fee_outprov),1,base_fee.fee_outprov-user_fee_info_temp.fee_outprov,0.00),
                decode(sign(base_fee.fee_outprov_long-user_fee_info_temp.fee_outprov_long),1,base_fee.fee_outprov_long-user_fee_info_temp.fee_outprov_long,0.00),
                decode(sign(base_fee.fee_inter-user_fee_info_temp.fee_inter),1,base_fee.fee_inter-user_fee_info_temp.fee_inter,0.00),
                decode(sign(base_fee.fee_inter_long-user_fee_info_temp.fee_inter_long),1,base_fee.fee_inter_long-user_fee_info_temp.fee_inter_long,0.00),
                decode(sign(base_fee.fee_cmnet-user_fee_info_temp.fee_cmnet),1,base_fee.fee_cmnet-user_fee_info_temp.fee_cmnet,0.00),
                decode(sign(base_fee.fee_trust-user_fee_info_temp.fee_trust),1,base_fee.fee_trust-user_fee_info_temp.fee_trust,0.00),
                decode(sign(base_fee.fee_ptp_sms-user_fee_info_temp.fee_ptp_sms),1,base_fee.fee_ptp_sms-user_fee_info_temp.fee_ptp_sms,0.00),
                decode(sign(base_fee.fee_mms-user_fee_info_temp.fee_mms),1,base_fee.fee_mms-user_fee_info_temp.fee_mms,0.00),
                decode(sign(base_fee.fee_magazine-user_fee_info_temp.fee_magazine),1,base_fee.fee_magazine-user_fee_info_temp.fee_magazine,0.00),
                decode(sign(base_fee.fee_gprs-user_fee_info_temp.fee_gprs),1,base_fee.fee_gprs-user_fee_info_temp.fee_gprs,0.00),
                decode(sign(base_fee.fee_wap-user_fee_info_temp.fee_wap),1,base_fee.fee_wap-user_fee_info_temp.fee_wap,0.00),
                decode(sign(base_fee.fee_data_month-user_fee_info_temp.fee_data_month),1,base_fee.fee_data_month-user_fee_info_temp.fee_data_month,0.00),
                decode(sign(base_fee.fee_data-user_fee_info_temp.fee_data),1,base_fee.fee_data-user_fee_info_temp.fee_data,0.00),
                decode(sign(base_fee.fee_ring-user_fee_info_temp.fee_ring),1,base_fee.fee_ring-user_fee_info_temp.fee_ring,0.00),
                decode(sign(base_fee.fee_display-user_fee_info_temp.fee_display),1,base_fee.fee_display-user_fee_info_temp.fee_display,0.00),
                decode(sign(base_fee.fee_ext-user_fee_info_temp.fee_ext),1,base_fee.fee_ext-user_fee_info_temp.fee_ext,0.00),
                decode(sign(base_fee.fee_other-user_fee_info_temp.fee_other),1,base_fee.fee_other-user_fee_info_temp.fee_other,0.00),
                decode(sign(base_fee.fee_all-user_fee_info_temp.fee_all),1,base_fee.fee_all-user_fee_info_temp.fee_all,0.00),
                decode(sign(base_fee.fee_for_others-user_fee_info_temp.fee_for_others),1,base_fee.fee_for_others-user_fee_info_temp.fee_for_others,0.00),
                decode(sign(base_fee.fee_by_others-user_fee_info_temp.fee_by_others),1,base_fee.fee_by_others-user_fee_info_temp.fee_by_others,0.00) );

                insert into day_fee_info(id,fee_id,start_time) values(seq_day_fee_info.nextval,user_fee_info_nextval,sysdate-1);

                commit;
                exception when others then
                  rollback;
                  dbms_output.put_line('保存到user_fee_info,day_fee_info表出现了异常');
                end;
                -------------(end)保存到user_fee_info,day_fee_info表-------------------------------------


                -------------------(begin)保存到user_fee_info,month_fee_info表---------------------------------
                begin

                begin
                ----首先删除再更新原来的user_fee_info,month_fee_info
                select a.fee_id  into v_fee_id  from month_fee_info a, user_fee_info b where a.fee_id = b.fee_id and b.user_id = base_fee.user_id   and to_char(a.start_time,'yyyy-mm') = to_char(add_months(sysdate,-1),'yyyy-mm');
                delete from user_fee_info x where x.fee_id = v_fee_id;
                delete from month_fee_info y where y.fee_id = v_fee_id;
                commit;


                exception when others then
                  rollback;
                end;
                ----插入新的数据
                select seq_user_fee_info.nextval into user_fee_info_nextval from dual;
                insert into user_fee_info(fee_id,user_id,fee_base,fee_pkg_month,fee_keep,fee_oth_month,fee_vpn,
                fee_shift,fee_local,fee_long,fee_inprov,fee_inprov_long,fee_outprov,fee_outprov_long,fee_inter,fee_inter_long,
                fee_cmnet,fee_trust,fee_ptp_sms,fee_mms,fee_magazine,fee_gprs,fee_wap,fee_data_month,
                fee_data,fee_ring,fee_display,fee_ext,fee_other,fee_all,fee_for_others,fee_by_others)
                values( user_fee_info_nextval,base_fee.user_id, base_fee.fee_base, base_fee.fee_pkg_month,
                base_fee.fee_keep, base_fee.fee_oth_month, base_fee.fee_vpn,
                base_fee.fee_shift, base_fee.fee_local, base_fee.fee_long, base_fee.fee_inprov,
                base_fee.fee_inprov_long, base_fee.fee_outprov, base_fee.fee_outprov_long,
                base_fee.fee_inter, base_fee.fee_inter_long, base_fee.fee_cmnet,
                base_fee.fee_trust, base_fee.fee_ptp_sms,  base_fee.fee_mms,
                base_fee.fee_magazine,  base_fee.fee_gprs,  base_fee.fee_wap,
                base_fee.fee_data_month,  base_fee.fee_data,  base_fee.fee_ring,
                base_fee.fee_display, base_fee.fee_ext,  base_fee.fee_other,
                base_fee.fee_all,  base_fee.fee_for_others, base_fee.fee_by_others);

                insert into month_fee_info(id,fee_id,start_time) values(seq_month_fee_info.nextval,user_fee_info_nextval,sysdate-1);
                commit;
                exception when others then
                  rollback;
                  dbms_output.put_line('保存到user_fee_info,month_fee_info表出现了异常');
                end;
                ----------------(end)保存到user_fee_info,month_fee_info表--------------------------------------

              else --不是本月第一天

                -------------------(begin)保存到user_fee_info,day_fee_info表-------------------
                begin

                begin
                --计算出本月的总和
                select  user_id,
                sum(fee_base),sum(fee_pkg_month),sum(fee_keep),sum(fee_oth_month),sum(fee_vpn),
                sum(fee_shift),sum(fee_local),sum(fee_long),sum(fee_inprov),sum(fee_inprov_long),
                sum(fee_outprov),sum(fee_outprov_long),sum(fee_inter),sum(fee_inter_long),
                sum(fee_cmnet),sum(fee_trust),sum(fee_ptp_sms),sum(fee_mms),sum(fee_magazine),
                sum(fee_gprs),sum(fee_wap),sum(fee_data_month),
                sum(fee_data),sum(fee_ring),sum(fee_display),sum(fee_ext),sum(fee_other),
                sum(fee_all),sum(fee_for_others),sum(fee_by_others)
                into user_fee_info_temp from user_fee_info
                where user_id =  base_fee.user_id
                and fee_id in (select fee_id from day_fee_info where to_char(start_time,'yyyy-mm') = to_char(sysdate,'yyyy-mm'))  group by user_id ;

                exception when others then
                  user_fee_info_temp.user_id := base_fee.user_id;
                  user_fee_info_temp.fee_base := 0.00;
                  user_fee_info_temp.fee_pkg_month := 0.00;
                  user_fee_info_temp.fee_keep := 0.00;
                  user_fee_info_temp.fee_oth_month := 0.00;
                  user_fee_info_temp.fee_vpn := 0.00;
                  user_fee_info_temp.fee_shift := 0.00;
                  user_fee_info_temp.fee_local := 0.00;
                  user_fee_info_temp.fee_long := 0.00;
                  user_fee_info_temp.fee_inprov := 0.00;
                  user_fee_info_temp.fee_inprov_long := 0.00;
                  user_fee_info_temp.fee_outprov := 0.00;
                  user_fee_info_temp.fee_outprov_long := 0.00;
                  user_fee_info_temp.fee_inter := 0.00;
                  user_fee_info_temp.fee_inter_long := 0.00;
                  user_fee_info_temp.fee_cmnet := 0.00;
                  user_fee_info_temp.fee_trust := 0.00;
                  user_fee_info_temp.fee_ptp_sms := 0.00;
                  user_fee_info_temp.fee_mms := 0.00;
                  user_fee_info_temp.fee_magazine := 0.00;
                  user_fee_info_temp.fee_gprs := 0.00;
                  user_fee_info_temp.fee_wap := 0.00;
                  user_fee_info_temp.fee_data_month := 0.00;
                  user_fee_info_temp.fee_data := 0.00;
                  user_fee_info_temp.fee_ring := 0.00;
                  user_fee_info_temp.fee_display := 0.00;
                  user_fee_info_temp.fee_ext := 0.00;
                  user_fee_info_temp.fee_other := 0.00;
                  user_fee_info_temp.fee_all := 0.00;
                  user_fee_info_temp.fee_for_others := 0.00;
                  user_fee_info_temp.fee_by_others := 0.00;
                end;

                --保存到user_fee_info,day_fee_info表
                select seq_user_fee_info.nextval into user_fee_info_nextval from dual;
                insert into user_fee_info(fee_id,user_id,fee_base,fee_pkg_month,fee_keep,fee_oth_month,fee_vpn,
                fee_shift,fee_local,fee_long,fee_inprov,fee_inprov_long,fee_outprov,fee_outprov_long,fee_inter,fee_inter_long,
                fee_cmnet,fee_trust,fee_ptp_sms,fee_mms,fee_magazine,fee_gprs,fee_wap,fee_data_month,
                fee_data,fee_ring,fee_display,fee_ext,fee_other,fee_all,fee_for_others,fee_by_others)
                values(
                user_fee_info_nextval,base_fee.user_id,
                decode(sign(base_fee.fee_base-user_fee_info_temp.fee_base),1,base_fee.fee_base-user_fee_info_temp.fee_base,0.00),
                decode(sign(base_fee.fee_pkg_month-user_fee_info_temp.fee_pkg_month),1,base_fee.fee_pkg_month-user_fee_info_temp.fee_pkg_month,0.00),
                decode(sign(base_fee.fee_keep-user_fee_info_temp.fee_keep),1,base_fee.fee_keep-user_fee_info_temp.fee_keep,0.00),
                decode(sign(base_fee.fee_oth_month-user_fee_info_temp.fee_oth_month),1,base_fee.fee_oth_month-user_fee_info_temp.fee_oth_month,0.00),
                decode(sign(base_fee.fee_vpn-user_fee_info_temp.fee_vpn),1,base_fee.fee_vpn-user_fee_info_temp.fee_vpn,0.00),
                decode(sign(base_fee.fee_shift-user_fee_info_temp.fee_shift),1,base_fee.fee_shift-user_fee_info_temp.fee_shift,0.00),
                decode(sign(base_fee.fee_local-user_fee_info_temp.fee_local),1,base_fee.fee_local-user_fee_info_temp.fee_local,0.00),
                decode(sign(base_fee.fee_long-user_fee_info_temp.fee_long),1,base_fee.fee_long-user_fee_info_temp.fee_long,0.00),
                decode(sign(base_fee.fee_inprov-user_fee_info_temp.fee_inprov),1,base_fee.fee_inprov-user_fee_info_temp.fee_inprov,0.00),
                decode(sign(base_fee.fee_inprov_long-user_fee_info_temp.fee_inprov_long),1,base_fee.fee_inprov_long-user_fee_info_temp.fee_inprov_long,0.00),
                decode(sign(base_fee.fee_outprov-user_fee_info_temp.fee_outprov),1,base_fee.fee_outprov-user_fee_info_temp.fee_outprov,0.00),
                decode(sign(base_fee.fee_outprov_long-user_fee_info_temp.fee_outprov_long),1,base_fee.fee_outprov_long-user_fee_info_temp.fee_outprov_long,0.00),
                decode(sign(base_fee.fee_inter-user_fee_info_temp.fee_inter),1,base_fee.fee_inter-user_fee_info_temp.fee_inter,0.00),
                decode(sign(base_fee.fee_inter_long-user_fee_info_temp.fee_inter_long),1,base_fee.fee_inter_long-user_fee_info_temp.fee_inter_long,0.00),
                decode(sign(base_fee.fee_cmnet-user_fee_info_temp.fee_cmnet),1,base_fee.fee_cmnet-user_fee_info_temp.fee_cmnet,0.00),
                decode(sign(base_fee.fee_trust-user_fee_info_temp.fee_trust),1,base_fee.fee_trust-user_fee_info_temp.fee_trust,0.00),
                decode(sign(base_fee.fee_ptp_sms-user_fee_info_temp.fee_ptp_sms),1,base_fee.fee_ptp_sms-user_fee_info_temp.fee_ptp_sms,0.00),
                decode(sign(base_fee.fee_mms-user_fee_info_temp.fee_mms),1,base_fee.fee_mms-user_fee_info_temp.fee_mms,0.00),
                decode(sign(base_fee.fee_magazine-user_fee_info_temp.fee_magazine),1,base_fee.fee_magazine-user_fee_info_temp.fee_magazine,0.00),
                decode(sign(base_fee.fee_gprs-user_fee_info_temp.fee_gprs),1,base_fee.fee_gprs-user_fee_info_temp.fee_gprs,0.00),
                decode(sign(base_fee.fee_wap-user_fee_info_temp.fee_wap),1,base_fee.fee_wap-user_fee_info_temp.fee_wap,0.00),
                decode(sign(base_fee.fee_data_month-user_fee_info_temp.fee_data_month),1,base_fee.fee_data_month-user_fee_info_temp.fee_data_month,0.00),
                decode(sign(base_fee.fee_data-user_fee_info_temp.fee_data),1,base_fee.fee_data-user_fee_info_temp.fee_data,0.00),
                decode(sign(base_fee.fee_ring-user_fee_info_temp.fee_ring),1,base_fee.fee_ring-user_fee_info_temp.fee_ring,0.00),
                decode(sign(base_fee.fee_display-user_fee_info_temp.fee_display),1,base_fee.fee_display-user_fee_info_temp.fee_display,0.00),
                decode(sign(base_fee.fee_ext-user_fee_info_temp.fee_ext),1,base_fee.fee_ext-user_fee_info_temp.fee_ext,0.00),
                decode(sign(base_fee.fee_other-user_fee_info_temp.fee_other),1,base_fee.fee_other-user_fee_info_temp.fee_other,0.00),
                decode(sign(base_fee.fee_all-user_fee_info_temp.fee_all),1,base_fee.fee_all-user_fee_info_temp.fee_all,0.00),
                decode(sign(base_fee.fee_for_others-user_fee_info_temp.fee_for_others),1,base_fee.fee_for_others-user_fee_info_temp.fee_for_others,0.00),
                decode(sign(base_fee.fee_by_others-user_fee_info_temp.fee_by_others),1,base_fee.fee_by_others-user_fee_info_temp.fee_by_others,0.00) );

                insert into day_fee_info(id,fee_id,start_time) values(seq_day_fee_info.nextval,user_fee_info_nextval,sysdate-1);

                commit;
                exception when others then
                  rollback;
                  dbms_output.put_line('保存到user_fee_info,day_fee_info表出现了异常');
                end;
                ------------------(end)保存到user_fee_info,day_fee_info表----------------------------------





                ----------------(begin)保存到user_fee_info,month_fee_info表----------------------
                begin

                begin
                ----首先删除再更新原来的user_fee_info,month_fee_info

                select a.fee_id  into v_fee_id  from month_fee_info a, user_fee_info b where a.fee_id = b.fee_id and b.user_id = base_fee.user_id   and to_char(a.start_time,'yyyy-mm') = to_char(sysdate,'yyyy-mm') ;
                delete from user_fee_info x where x.fee_id = v_fee_id;
                delete from month_fee_info y where y.fee_id = v_fee_id;
                commit;
                exception when others then
                  rollback;
                end;


                ----插入新的数据
                select seq_user_fee_info.nextval into user_fee_info_nextval from dual;
                insert into user_fee_info(fee_id,user_id,fee_base,fee_pkg_month,fee_keep,fee_oth_month,fee_vpn,
                fee_shift,fee_local,fee_long,fee_inprov,fee_inprov_long,fee_outprov,fee_outprov_long,fee_inter,fee_inter_long,
                fee_cmnet,fee_trust,fee_ptp_sms,fee_mms,fee_magazine,fee_gprs,fee_wap,fee_data_month,
                fee_data,fee_ring,fee_display,fee_ext,fee_other,fee_all,fee_for_others,fee_by_others)
                values( user_fee_info_nextval,base_fee.user_id, base_fee.fee_base, base_fee.fee_pkg_month,
                base_fee.fee_keep, base_fee.fee_oth_month, base_fee.fee_vpn,
                base_fee.fee_shift, base_fee.fee_local, base_fee.fee_long, base_fee.fee_inprov,
                base_fee.fee_inprov_long, base_fee.fee_outprov, base_fee.fee_outprov_long,
                base_fee.fee_inter, base_fee.fee_inter_long, base_fee.fee_cmnet,
                base_fee.fee_trust, base_fee.fee_ptp_sms,  base_fee.fee_mms,
                base_fee.fee_magazine,  base_fee.fee_gprs,  base_fee.fee_wap,
                base_fee.fee_data_month,  base_fee.fee_data,  base_fee.fee_ring,
                base_fee.fee_display, base_fee.fee_ext,  base_fee.fee_other,
                base_fee.fee_all,  base_fee.fee_for_others, base_fee.fee_by_others);

                insert into month_fee_info(id,fee_id,start_time) values(seq_month_fee_info.nextval,user_fee_info_nextval,sysdate);

                commit;
                exception when others then
                  rollback;
                  dbms_output.put_line('保存到user_fee_info,month_fee_info表出现了异常');
                end;
                ----------------(end)保存到user_fee_info,month_fee_info表----------------------

              end if;





            end loop;
          end if;



        exception when others then
          --回滚数据
          rollback;
          dbms_output.put_line('首先通过虚拟号码，获取对应的SIM号码出现了异常!');
        end;
      end if;


      --如果是真实号码,并且没有附属虚拟号码，则直接更新day_fee_info,month_fee_info表
      if group_sims_info.simtype > 0 and (group_sims_info.groupsim is null or length(group_sims_info.groupsim) = 0 ) then
        begin
           --首先获取该用户每日消费记录,如果今天刚好是1号，那么获取到的数据刚好是上个月1-30号的总和
           --如果不是1号，那么获取到的是本月前几天的总和
           pkg_gps_audit.proc_get_userfee(func_get_user_id(group_sims_info.sim),base_fee);

           --判断当前日期是否是每月1号
           is_first_day := func_is_first_day();
           if is_first_day = 1 then
             ----------------------(begin)-------------------
             begin

             begin
               --计算出上个月的总和
               select  user_id,
               sum(fee_base),sum(fee_pkg_month),sum(fee_keep),sum(fee_oth_month),sum(fee_vpn),
               sum(fee_shift),sum(fee_local),sum(fee_long),sum(fee_inprov),sum(fee_inprov_long),
               sum(fee_outprov),sum(fee_outprov_long),sum(fee_inter),sum(fee_inter_long),
               sum(fee_cmnet),sum(fee_trust),sum(fee_ptp_sms),sum(fee_mms),sum(fee_magazine),
               sum(fee_gprs),sum(fee_wap),sum(fee_data_month),
               sum(fee_data),sum(fee_ring),sum(fee_display),sum(fee_ext),sum(fee_other),
               sum(fee_all),sum(fee_for_others),sum(fee_by_others)
               into user_fee_info_temp from user_fee_info
               where user_id = func_get_user_id(group_sims_info.sim)
               and fee_id in (select fee_id from day_fee_info where to_char(start_time,'yyyy-mm') = to_char(add_months(sysdate,-1),'yyyy-mm'))
               group by user_id ;

             exception when others then
                  user_fee_info_temp.user_id := base_fee.user_id;
                  user_fee_info_temp.fee_base := 0.00;
                  user_fee_info_temp.fee_pkg_month := 0.00;
                  user_fee_info_temp.fee_keep := 0.00;
                  user_fee_info_temp.fee_oth_month := 0.00;
                  user_fee_info_temp.fee_vpn := 0.00;
                  user_fee_info_temp.fee_shift := 0.00;
                  user_fee_info_temp.fee_local := 0.00;
                  user_fee_info_temp.fee_long := 0.00;
                  user_fee_info_temp.fee_inprov := 0.00;
                  user_fee_info_temp.fee_inprov_long := 0.00;
                  user_fee_info_temp.fee_outprov := 0.00;
                  user_fee_info_temp.fee_outprov_long := 0.00;
                  user_fee_info_temp.fee_inter := 0.00;
                  user_fee_info_temp.fee_inter_long := 0.00;
                  user_fee_info_temp.fee_cmnet := 0.00;
                  user_fee_info_temp.fee_trust := 0.00;
                  user_fee_info_temp.fee_ptp_sms := 0.00;
                  user_fee_info_temp.fee_mms := 0.00;
                  user_fee_info_temp.fee_magazine := 0.00;
                  user_fee_info_temp.fee_gprs := 0.00;
                  user_fee_info_temp.fee_wap := 0.00;
                  user_fee_info_temp.fee_data_month := 0.00;
                  user_fee_info_temp.fee_data := 0.00;
                  user_fee_info_temp.fee_ring := 0.00;
                  user_fee_info_temp.fee_display := 0.00;
                  user_fee_info_temp.fee_ext := 0.00;
                  user_fee_info_temp.fee_other := 0.00;
                  user_fee_info_temp.fee_all := 0.00;
                  user_fee_info_temp.fee_for_others := 0.00;
                  user_fee_info_temp.fee_by_others := 0.00;
                end;

             --保存到user_fee_info,day_fee_info表
             select seq_user_fee_info.nextval into user_fee_info_nextval from dual;
             insert into user_fee_info(fee_id,user_id,fee_base,fee_pkg_month,fee_keep,fee_oth_month,fee_vpn,
             fee_shift,fee_local,fee_long,fee_inprov,fee_inprov_long,fee_outprov,fee_outprov_long,fee_inter,fee_inter_long,
             fee_cmnet,fee_trust,fee_ptp_sms,fee_mms,fee_magazine,fee_gprs,fee_wap,fee_data_month,
             fee_data,fee_ring,fee_display,fee_ext,fee_other,fee_all,fee_for_others,fee_by_others)
             values(
             user_fee_info_nextval,base_fee.user_id,
             decode(sign(base_fee.fee_base-user_fee_info_temp.fee_base),1,base_fee.fee_base-user_fee_info_temp.fee_base,0.00),
             decode(sign(base_fee.fee_pkg_month-user_fee_info_temp.fee_pkg_month),1,base_fee.fee_pkg_month-user_fee_info_temp.fee_pkg_month,0.00),
             decode(sign(base_fee.fee_keep-user_fee_info_temp.fee_keep),1,base_fee.fee_keep-user_fee_info_temp.fee_keep,0.00),
             decode(sign(base_fee.fee_oth_month-user_fee_info_temp.fee_oth_month),1,base_fee.fee_oth_month-user_fee_info_temp.fee_oth_month,0.00),
             decode(sign(base_fee.fee_vpn-user_fee_info_temp.fee_vpn),1,base_fee.fee_vpn-user_fee_info_temp.fee_vpn,0.00),
             decode(sign(base_fee.fee_shift-user_fee_info_temp.fee_shift),1,base_fee.fee_shift-user_fee_info_temp.fee_shift,0.00),
             decode(sign(base_fee.fee_local-user_fee_info_temp.fee_local),1,base_fee.fee_local-user_fee_info_temp.fee_local,0.00),
             decode(sign(base_fee.fee_long-user_fee_info_temp.fee_long),1,base_fee.fee_long-user_fee_info_temp.fee_long,0.00),
             decode(sign(base_fee.fee_inprov-user_fee_info_temp.fee_inprov),1,base_fee.fee_inprov-user_fee_info_temp.fee_inprov,0.00),
             decode(sign(base_fee.fee_inprov_long-user_fee_info_temp.fee_inprov_long),1,base_fee.fee_inprov_long-user_fee_info_temp.fee_inprov_long,0.00),
             decode(sign(base_fee.fee_outprov-user_fee_info_temp.fee_outprov),1,base_fee.fee_outprov-user_fee_info_temp.fee_outprov,0.00),
             decode(sign(base_fee.fee_outprov_long-user_fee_info_temp.fee_outprov_long),1,base_fee.fee_outprov_long-user_fee_info_temp.fee_outprov_long,0.00),
             decode(sign(base_fee.fee_inter-user_fee_info_temp.fee_inter),1,base_fee.fee_inter-user_fee_info_temp.fee_inter,0.00),
             decode(sign(base_fee.fee_inter_long-user_fee_info_temp.fee_inter_long),1,base_fee.fee_inter_long-user_fee_info_temp.fee_inter_long,0.00),
             decode(sign(base_fee.fee_cmnet-user_fee_info_temp.fee_cmnet),1,base_fee.fee_cmnet-user_fee_info_temp.fee_cmnet,0.00),
             decode(sign(base_fee.fee_trust-user_fee_info_temp.fee_trust),1,base_fee.fee_trust-user_fee_info_temp.fee_trust,0.00),
             decode(sign(base_fee.fee_ptp_sms-user_fee_info_temp.fee_ptp_sms),1,base_fee.fee_ptp_sms-user_fee_info_temp.fee_ptp_sms,0.00),
             decode(sign(base_fee.fee_mms-user_fee_info_temp.fee_mms),1,base_fee.fee_mms-user_fee_info_temp.fee_mms,0.00),
             decode(sign(base_fee.fee_magazine-user_fee_info_temp.fee_magazine),1,base_fee.fee_magazine-user_fee_info_temp.fee_magazine,0.00),
             decode(sign(base_fee.fee_gprs-user_fee_info_temp.fee_gprs),1,base_fee.fee_gprs-user_fee_info_temp.fee_gprs,0.00),
             decode(sign(base_fee.fee_wap-user_fee_info_temp.fee_wap),1,base_fee.fee_wap-user_fee_info_temp.fee_wap,0.00),
             decode(sign(base_fee.fee_data_month-user_fee_info_temp.fee_data_month),1,base_fee.fee_data_month-user_fee_info_temp.fee_data_month,0.00),
             decode(sign(base_fee.fee_data-user_fee_info_temp.fee_data),1,base_fee.fee_data-user_fee_info_temp.fee_data,0.00),
             decode(sign(base_fee.fee_ring-user_fee_info_temp.fee_ring),1,base_fee.fee_ring-user_fee_info_temp.fee_ring,0.00),
             decode(sign(base_fee.fee_display-user_fee_info_temp.fee_display),1,base_fee.fee_display-user_fee_info_temp.fee_display,0.00),
             decode(sign(base_fee.fee_ext-user_fee_info_temp.fee_ext),1,base_fee.fee_ext-user_fee_info_temp.fee_ext,0.00),
             decode(sign(base_fee.fee_other-user_fee_info_temp.fee_other),1,base_fee.fee_other-user_fee_info_temp.fee_other,0.00),
             decode(sign(base_fee.fee_all-user_fee_info_temp.fee_all),1,base_fee.fee_all-user_fee_info_temp.fee_all,0.00),
             decode(sign(base_fee.fee_for_others-user_fee_info_temp.fee_for_others),1,base_fee.fee_for_others-user_fee_info_temp.fee_for_others,0.00),
             decode(sign(base_fee.fee_by_others-user_fee_info_temp.fee_by_others),1,base_fee.fee_by_others-user_fee_info_temp.fee_by_others,0.00) );

             insert into day_fee_info(id,fee_id,start_time) values(seq_day_fee_info.nextval,user_fee_info_nextval,sysdate-1);

             commit;
             exception when others then
               rollback;
               dbms_output.put_line('保存到user_fee_info,day_fee_info表出现了异常');
             end;
             ------------------(end)-------------------------------


             -------------------(begin)-----------------------------------------
             begin

             --保存到user_fee_info,month_fee_info表
             begin
               ----首先删除再更新原来的user_fee_info,month_fee_info

                select a.fee_id into v_fee_id  from month_fee_info a, user_fee_info b where a.fee_id = b.fee_id and b.user_id = base_fee.user_id   and to_char(a.start_time,'yyyy-mm') = to_char(add_months(sysdate,-1),'yyyy-mm') ;
                delete from user_fee_info x where x.fee_id = v_fee_id;
                delete from month_fee_info y where y.fee_id = fee_id;
                commit;


             exception when others then
               rollback;
             end;

             ----插入新的数据
             select seq_user_fee_info.nextval into user_fee_info_nextval from dual;
             insert into user_fee_info(fee_id,user_id,fee_base,fee_pkg_month,fee_keep,fee_oth_month,fee_vpn,
             fee_shift,fee_local,fee_long,fee_inprov,fee_inprov_long,fee_outprov,fee_outprov_long,fee_inter,fee_inter_long,
             fee_cmnet,fee_trust,fee_ptp_sms,fee_mms,fee_magazine,fee_gprs,fee_wap,fee_data_month,
             fee_data,fee_ring,fee_display,fee_ext,fee_other,fee_all,fee_for_others,fee_by_others)
             values( user_fee_info_nextval,base_fee.user_id, base_fee.fee_base, base_fee.fee_pkg_month,
             base_fee.fee_keep, base_fee.fee_oth_month, base_fee.fee_vpn,
             base_fee.fee_shift, base_fee.fee_local, base_fee.fee_long, base_fee.fee_inprov,
             base_fee.fee_inprov_long, base_fee.fee_outprov, base_fee.fee_outprov_long,
             base_fee.fee_inter, base_fee.fee_inter_long, base_fee.fee_cmnet,
             base_fee.fee_trust, base_fee.fee_ptp_sms,  base_fee.fee_mms,
             base_fee.fee_magazine,  base_fee.fee_gprs,  base_fee.fee_wap,
             base_fee.fee_data_month,  base_fee.fee_data,  base_fee.fee_ring,
             base_fee.fee_display, base_fee.fee_ext,  base_fee.fee_other,
             base_fee.fee_all,  base_fee.fee_for_others, base_fee.fee_by_others);

             insert into month_fee_info(id,fee_id,start_time) values(seq_month_fee_info.nextval,user_fee_info_nextval,sysdate-1);
             commit;
             exception when others then
               rollback;
               dbms_output.put_line('保存到user_fee_info,month_fee_info表出现了异常');
             end;
             ----------------------(end)-----------------------------------------------

           else --不是本月第一天
             ------------------------(begin)-------------------------------------
             begin
             --保存到user_fee_info,day_fee_info表

             begin
             --计算出本月的总和
               select  user_id,
               sum(fee_base),sum(fee_pkg_month),sum(fee_keep),sum(fee_oth_month),sum(fee_vpn),
               sum(fee_shift),sum(fee_local),sum(fee_long),sum(fee_inprov),sum(fee_inprov_long),
               sum(fee_outprov),sum(fee_outprov_long),sum(fee_inter),sum(fee_inter_long),
               sum(fee_cmnet),sum(fee_trust),sum(fee_ptp_sms),sum(fee_mms),sum(fee_magazine),
               sum(fee_gprs),sum(fee_wap),sum(fee_data_month),
               sum(fee_data),sum(fee_ring),sum(fee_display),sum(fee_ext),sum(fee_other),
               sum(fee_all),sum(fee_for_others),sum(fee_by_others)
               into user_fee_info_temp from user_fee_info
               where user_id = func_get_user_id(group_sims_info.sim)
               and fee_id in (select fee_id from day_fee_info where to_char(start_time,'yyyy-mm') = to_char(sysdate,'yyyy-mm'))
               group by user_id ;

             exception when others then
                  user_fee_info_temp.user_id := base_fee.user_id;

                  user_fee_info_temp.fee_base := 0.00;
                  user_fee_info_temp.fee_pkg_month := 0.00;
                  user_fee_info_temp.fee_keep := 0.00;
                  user_fee_info_temp.fee_oth_month := 0.00;
                  user_fee_info_temp.fee_vpn := 0.00;
                  user_fee_info_temp.fee_shift := 0.00;
                  user_fee_info_temp.fee_local := 0.00;
                  user_fee_info_temp.fee_long := 0.00;
                  user_fee_info_temp.fee_inprov := 0.00;
                  user_fee_info_temp.fee_inprov_long := 0.00;
                  user_fee_info_temp.fee_outprov := 0.00;
                  user_fee_info_temp.fee_outprov_long := 0.00;
                  user_fee_info_temp.fee_inter := 0.00;
                  user_fee_info_temp.fee_inter_long := 0.00;
                  user_fee_info_temp.fee_cmnet := 0.00;
                  user_fee_info_temp.fee_trust := 0.00;
                  user_fee_info_temp.fee_ptp_sms := 0.00;
                  user_fee_info_temp.fee_mms := 0.00;
                  user_fee_info_temp.fee_magazine := 0.00;
                  user_fee_info_temp.fee_gprs := 0.00;
                  user_fee_info_temp.fee_wap := 0.00;
                  user_fee_info_temp.fee_data_month := 0.00;
                  user_fee_info_temp.fee_data := 0.00;
                  user_fee_info_temp.fee_ring := 0.00;
                  user_fee_info_temp.fee_display := 0.00;
                  user_fee_info_temp.fee_ext := 0.00;
                  user_fee_info_temp.fee_other := 0.00;
                  user_fee_info_temp.fee_all := 0.00;
                  user_fee_info_temp.fee_for_others := 0.00;
                  user_fee_info_temp.fee_by_others := 0.00;
                end;

             --保存到user_fee_info,day_fee_info表
             select seq_user_fee_info.nextval into user_fee_info_nextval from dual;
             insert into user_fee_info(fee_id,user_id,fee_base,fee_pkg_month,fee_keep,fee_oth_month,fee_vpn,
             fee_shift,fee_local,fee_long,fee_inprov,fee_inprov_long,fee_outprov,fee_outprov_long,fee_inter,fee_inter_long,
             fee_cmnet,fee_trust,fee_ptp_sms,fee_mms,fee_magazine,fee_gprs,fee_wap,fee_data_month,
             fee_data,fee_ring,fee_display,fee_ext,fee_other,fee_all,fee_for_others,fee_by_others)
             values(
             user_fee_info_nextval,base_fee.user_id,
             decode(sign(base_fee.fee_base-user_fee_info_temp.fee_base),1,base_fee.fee_base-user_fee_info_temp.fee_base,0.00),
             decode(sign(base_fee.fee_pkg_month-user_fee_info_temp.fee_pkg_month),1,base_fee.fee_pkg_month-user_fee_info_temp.fee_pkg_month,0.00),
             decode(sign(base_fee.fee_keep-user_fee_info_temp.fee_keep),1,base_fee.fee_keep-user_fee_info_temp.fee_keep,0.00),
             decode(sign(base_fee.fee_oth_month-user_fee_info_temp.fee_oth_month),1,base_fee.fee_oth_month-user_fee_info_temp.fee_oth_month,0.00),
             decode(sign(base_fee.fee_vpn-user_fee_info_temp.fee_vpn),1,base_fee.fee_vpn-user_fee_info_temp.fee_vpn,0.00),
             decode(sign(base_fee.fee_shift-user_fee_info_temp.fee_shift),1,base_fee.fee_shift-user_fee_info_temp.fee_shift,0.00),
             decode(sign(base_fee.fee_local-user_fee_info_temp.fee_local),1,base_fee.fee_local-user_fee_info_temp.fee_local,0.00),
             decode(sign(base_fee.fee_long-user_fee_info_temp.fee_long),1,base_fee.fee_long-user_fee_info_temp.fee_long,0.00),
             decode(sign(base_fee.fee_inprov-user_fee_info_temp.fee_inprov),1,base_fee.fee_inprov-user_fee_info_temp.fee_inprov,0.00),
             decode(sign(base_fee.fee_inprov_long-user_fee_info_temp.fee_inprov_long),1,base_fee.fee_inprov_long-user_fee_info_temp.fee_inprov_long,0.00),
             decode(sign(base_fee.fee_outprov-user_fee_info_temp.fee_outprov),1,base_fee.fee_outprov-user_fee_info_temp.fee_outprov,0.00),
             decode(sign(base_fee.fee_outprov_long-user_fee_info_temp.fee_outprov_long),1,base_fee.fee_outprov_long-user_fee_info_temp.fee_outprov_long,0.00),
             decode(sign(base_fee.fee_inter-user_fee_info_temp.fee_inter),1,base_fee.fee_inter-user_fee_info_temp.fee_inter,0.00),
             decode(sign(base_fee.fee_inter_long-user_fee_info_temp.fee_inter_long),1,base_fee.fee_inter_long-user_fee_info_temp.fee_inter_long,0.00),
             decode(sign(base_fee.fee_cmnet-user_fee_info_temp.fee_cmnet),1,base_fee.fee_cmnet-user_fee_info_temp.fee_cmnet,0.00),
             decode(sign(base_fee.fee_trust-user_fee_info_temp.fee_trust),1,base_fee.fee_trust-user_fee_info_temp.fee_trust,0.00),
             decode(sign(base_fee.fee_ptp_sms-user_fee_info_temp.fee_ptp_sms),1,base_fee.fee_ptp_sms-user_fee_info_temp.fee_ptp_sms,0.00),
             decode(sign(base_fee.fee_mms-user_fee_info_temp.fee_mms),1,base_fee.fee_mms-user_fee_info_temp.fee_mms,0.00),
             decode(sign(base_fee.fee_magazine-user_fee_info_temp.fee_magazine),1,base_fee.fee_magazine-user_fee_info_temp.fee_magazine,0.00),
             decode(sign(base_fee.fee_gprs-user_fee_info_temp.fee_gprs),1,base_fee.fee_gprs-user_fee_info_temp.fee_gprs,0.00),
             decode(sign(base_fee.fee_wap-user_fee_info_temp.fee_wap),1,base_fee.fee_wap-user_fee_info_temp.fee_wap,0.00),
             decode(sign(base_fee.fee_data_month-user_fee_info_temp.fee_data_month),1,base_fee.fee_data_month-user_fee_info_temp.fee_data_month,0.00),
             decode(sign(base_fee.fee_data-user_fee_info_temp.fee_data),1,base_fee.fee_data-user_fee_info_temp.fee_data,0.00),
             decode(sign(base_fee.fee_ring-user_fee_info_temp.fee_ring),1,base_fee

原文：http://zhouyq.iteye.com/blog/240910