oracle存储过程例子（实战项目）
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_recordisuser_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;exceptionwhen others then v_success := 0;end;--其次，如果正常用户获取不到，再查离线用户信息if v_success < 1 thenbegin 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 numberisuser_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；beginbegin --如果本地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 isv_result number := 0; v_day varchar2(2);beginselect 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 ------单位：厦门新科技软件股份有限公司 ---------------------------------------------------------------------------------istype base_cursor is ref cursor;cf base_cursor;v_fee_id number(18);beginbegin open cf forselect 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 ------单位：厦门新科技软件股份有限公司 ---------------------------------------------------------------------------------istype base_cursor is ref cursor;cf base_cursor;v_fee_id number(18);beginbegin open cf forselect 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 ------单位：厦门新科技软件股份有限公司 ---------------------------------------------------------------------------------isgroup_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; --成功标志beginbegin --打开group_sims游标 open group_sims_ref for select * from group_sims where (groupsim isnull 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 ------单位：厦门新科技软件股份有限公司 ---------------------------------------------------------------------------------isgroup_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号码集合长度beginbegin --打开group_sims游标 open group_sims_ref for select * from group_sims where (groupsim isnull 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; ifgroup_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 ------单位：厦门新科技软件股份有限公司 ---------------------------------------------------------------------------------isgroup_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);--消费IDbeginbegin --打开group_sims游标 open group_sims_ref for select * from group_sims where (groupsim isnull 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 selectuser_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_idinto v_fee_idfrom 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 --计算出本月的总和 selectuser_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_idinto v_fee_idfrom 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 --计算出上个月的总和 selectuser_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_idfrom 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 --计算出本月的总和 selectuser_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
页:
[1]