yue.txt

电信计费帐务系统话费查询营业厅触摸屏界面

CREATE PROCEDURE hrb_pc_cf_bal(
    ch_phone_nbr             IN      varchar2,/*电话号码*/
    df_depo_balance          OUT     number,/*预存款余额*/
    df_amount                OUT     number,/*欠费表的余额*/
    df_cachg                 OUT     number,/*长途费用*/
    df_balance               OUT     number,/*余额*/
    df_return_code           OUT     number)
is
ch_acct_nbr varchar2(20);
cursor cu_rec0 is
       select acct_nbr from hrb_vw_subscr_lk@oraR
        where phone_nbr = ch_phone_nbr
          and svc_status != 'IDLE';
lr_rec0 cu_rec0%rowtype;
cursor cu_rec1 is
	select sum(balance) balance
	  from hrb_tb_jf_acct_depo@oraR
         where acct_nbr=ch_acct_nbr
           and end_date is null;
lr_rec1 cu_rec1%rowtype;

cursor cu_rec2 is
      select sum(call_chg+call_tax) call_chg
        from hrb_tb_jf_subscr_cachg
       where phone_nbr=ch_phone_nbr
         and call_type='LDC';
lr_rec2 cu_rec2%rowtype;

cursor cu_rec3 is
      select sum(amount) amount
        from hrb_tb_jf_subscr_bal@oraR
       where pay_business in ('PH','IS')
         and pay_phone=ch_phone_nbr
         and pay_acct=ch_acct_nbr
         and seq_order_nbr is null
         and bs='N';
lr_rec3 cu_rec3%rowtype;

begin
df_depo_balance:=0;
df_amount:=0;
df_cachg:=0;
df_balance:=0;
df_return_code:=1; /*初始值为1,默认为找不到*/
open cu_rec0;
 fetch cu_rec0 into lr_rec0;
   if cu_rec0%found then
         ch_acct_nbr := lr_rec0.acct_nbr;
         df_return_code:=0; /*如找到将该变量赋为0 */
   end if;
close cu_rec0;
open cu_rec1;
fetch cu_rec1 into lr_rec1;
  if cu_rec1%found then
     df_depo_balance:=lr_rec1.balance;/*预存款余额*/
  end if;
close cu_rec1;
open cu_rec2;
 fetch cu_rec2 into lr_rec2;
   if cu_rec2%found then
      df_cachg:=lr_rec2.call_chg;/*长途*/
   end if;
close cu_rec2;
open cu_rec3;
 fetch cu_rec3 into lr_rec3;
   if cu_rec3%found then
      df_amount:=lr_rec3.amount;/*BAL表的欠费*/
   end if;
close cu_rec3;

if df_depo_balance is null then
   df_depo_balance:=0;
end if;
if df_cachg is null then
   df_cachg:=0;
end if;
if df_amount is null then
   df_amount:=0;
end if;
df_balance:=df_depo_balance-df_cachg-df_amount;
exception
  when others then rollback;
end hrb_pc_cf_bal;