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_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

posted @ 2009-10-23 21:59 QQ天堂阅读(15344) 评论(2) 收藏举报

刷新页面返回顶部

QQ天堂

QQ天堂的BLOG(每天灌水一点点，长此以往，大海将会出现！)

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

公告