引用自：
create or replace function func_get_user_by_msisdn(msisdn in number) ------------------------------------------------------------------------------ ---功能描述：通过用户的电话号码,调用局方存储过程 获取用户的基本信息， --- --- 以遍更新card_user_info本地数据表。 --- ---参数： msisdn in number 代表用户的电话号码（SIM卡） --- ------------------------------------------------------------------------------ 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卡） --- ------------------------------------------------------------------------------ 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代表不是 --- ------------------------------------------------------------------------------ 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月份删除） --- ------------------------------------------------------------------------------ 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月份删除） --- ------------------------------------------------------------------------------ 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表 。 --- ------------------------------------------------------------------------------ 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表 。 --- ------------------------------------------------------------------------------ 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表 。 --- ------------------------------------------------------------------------------ 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