一个账单存储过程

------------------------------------------------------
-- Export file for user PRM --
-- Created by Administrator on 2008-12-17, 15:30:58 --
------------------------------------------------------

spool mb5_to_bill_item.log

prompt
prompt Creating procedure MB5_TO_BILL_ITEM
prompt ===================================
prompt
create or replace procedure mb5_to_bill_item(i integer) as
table_name varchar(200);index_name varchar(200);
sSql varchar(2000);
table_month varchar(20);col_name varchar(20);flag_error integer;
v_bill_item_id varchar(40);v_item_name varchar(80);
v_count number;

CURSOR SV_BILL IS
select BILL_ITEM_ID,ITEM_NAME from SV_BILL_ITEM;

---BOSS1。5每月帐单，转换格式（列变行）
--zG.ACC_DEF_FEE_ITEM@aitest 帐务明细表项目配置表
--zg.ACC_BILL_DTL_0791200601 每月帐务明细表
begin
--求前一个月的话费
table_month:=to_char(add_months(sysdate(),-i),'yyyymm');
--首先判断该月的帐目表是否生成了
/*
table_name:='zg.ACC_BILL_DTL_0791'||table_month;
if have_table(table_name)>0 then
*/
---SELECT * FROM zg.ACC_BILL_0791200601@JXCS1_CLONE 总帐单
---SELECT * FROM zg.ACC_BILL_DTL_0791200603@JXCS1_CLONE 详细帐单
--新建临时月帐单(获取有话费信息的号码,南昌全网数据)获取每个用户的BILL_ID
----如果本地SALES.CM_USER 不存在，或者表中的记录为0则自动从jxcs1_clone上获取
  begin
  flag_error:=0;
  table_name:='MB5_BILL_TEMP';
  exist_table(table_name);
  sSql:='create table MB5_BILL_TEMP as select a.* from zg.ACC_BILL_0791'||table_month||'@JXCS1_CLONE a,CM_USER b where a.serv_id=b.serv_id and a.acct_id=b.acct_id and b.user_sts=1 ';
  EXECUTE IMMEDIATE sSql;
  exception
  when others then
  flag_error:=1;
  return;
  end;
  if flag_error=0 then
  sSql:='alter table MB5_BILL_TEMP add primary key(bill_id) using index ';
  EXECUTE IMMEDIATE sSql;
  end if;
-------------
  begin
  flag_error:=0;
  table_name:='MB5_BILL_ITEM_TEMP';
  exist_table(table_name);
  sSql:='create table MB5_BILL_ITEM_TEMP as select a.* from zg.ACC_BILL_DTL_0791'||table_month||'@JXCS1_CLONE a where exists (select * from MB5_BILL_TEMP b where a.bill_id=b.bill_id )';
  EXECUTE IMMEDIATE sSql;
  exception
  when others then
  flag_error:=1;
  return;
  end;
  if flag_error=0 then
  sSql:='create index MB5_BILL_ITEM_TEMPid on MB5_BILL_ITEM_TEMP(bill_id) ';
  EXECUTE IMMEDIATE sSql;
  end if;
----------------------------------------------每月话费结构表MB5_BILL_ITEM_'||table_month||'
table_name:='MB5_HFJG_TEMP0';
exist_table(table_name);
sSql:='create table MB5_HFJG_TEMP0 as select bill_id,phone_id,'''||table_month||''' 月份, (unpay_fee+ppy_fee)/100 月话费总额 from MB5_BILL_TEMP ';
EXECUTE IMMEDIATE sSql;
sSql:='alter table MB5_HFJG_TEMP0 add primary key(bill_id) using index ';
EXECUTE IMMEDIATE sSql;

----循环游标(遍历费用配置表)分别求每个帐号的该明细项目的费用
OPEN sv_bill;
loop
FETCH sv_bill INTO v_bill_item_id,v_item_name;
EXIT WHEN sv_bill%NOTFOUND;
----------------------剔除字段名中不合法的符号
v_item_name:=replace(v_item_name,'(','');
v_item_name:=replace(v_item_name,')','');
v_item_name:=replace(v_item_name,' ','');
----------------------
table_name:='MB5_SV_HFJG_TEMP';
exist_table(table_name);
sSql:='create table MB5_SV_HFJG_TEMP AS select FEE_ITEM_ID from SV_FEE_BILL_ITEM where bill_item_id='''||v_bill_item_id||'''';
EXECUTE IMMEDIATE sSql;
sSql:='ALTER TABLE MB5_SV_HFJG_TEMP add primary key(FEE_ITEM_ID) using index ';
EXECUTE IMMEDIATE sSql;
table_name:='MB5_HFJG_TEMP1';
exist_table(table_name);
sSql:='create table MB5_HFJG_TEMP1 AS select a.* from MB5_BILL_ITEM_TEMP a,MB5_SV_HFJG_TEMP b where a.fee_item_id=b.fee_item_id';
EXECUTE IMMEDIATE sSql;
table_name:='MB5_HFJG_TEMP2';
exist_table(table_name);
sSql:='create table MB5_HFJG_TEMP2 AS select bill_id,sum(unpay_fee+ppy_fee)/100 total_fee from MB5_HFJG_TEMP1 group by bill_id';
EXECUTE IMMEDIATE sSql;
sSql:='ALTER TABLE MB5_HFJG_TEMP2 add primary key(bill_id) using index ';
EXECUTE IMMEDIATE sSql;
table_name:='MB5_HFJG_TEMP3';
exist_table(table_name);
sSql:='create table MB5_HFJG_TEMP3 AS select a.*,nvl(b.total_fee,0) '||v_item_name||' from MB5_HFJG_TEMP0 a,MB5_HFJG_TEMP2 b where a.bill_id=b.bill_id(+)';
EXECUTE IMMEDIATE sSql;
sSql:='ALTER TABLE MB5_HFJG_TEMP3 add primary key(bill_id) using index ';
EXECUTE IMMEDIATE sSql;
table_name:='MB5_HFJG_TEMP0';
exist_table(table_name);
sSql:='rename MB5_HFJG_TEMP3 to MB5_HFJG_TEMP0';
EXECUTE IMMEDIATE sSql;



END LOOP;
close sv_bill;
table_name:='MB5_HFJG_TEMP1';
exist_table(table_name);
sSql:='CREATE TABLE MB5_HFJG_TEMP1 as select b.msisdn,b.empid,b.area,b.cust_class,b.qz_class,b.sts,a.* from mb_nc_maincust b,MB5_HFJG_TEMP0 a where a.phone_id(+)=b.msisdn ';
EXECUTE IMMEDIATE sSql;
table_name:='MB5_BILL_ITEM_'||table_month||'';
exist_table(table_name);
sSql:='rename MB5_HFJG_TEMP1 to MB5_BILL_ITEM_'||table_month;
EXECUTE IMMEDIATE sSql;
----------------------删除临时表
table_name:='MB5_SV_HFJG_TEMP';
exist_table(table_name);
table_name:='MB5_HFJG_TEMP0';
exist_table(table_name);
table_name:='MB5_HFJG_TEMP1';
exist_table(table_name);
table_name:='MB5_HFJG_TEMP2';
exist_table(table_name);
table_name:='MB5_HFJG_TEMP3';
exist_table(table_name);
table_name:='MB5_BILL_ITEM_TEMP';
exist_table(table_name);
table_name:='MB5_BILL_TEMP';
exist_table(table_name);
end mb5_to_bill_item;
/


spool off