Oracle函数与存储过程

==========函数======================

create or replace function BILL_TABLE_NAME(userId in number)
return varchar2 is
  TABLE_NAME varchar2(20);
  TABLE_NAME1 varchar2(20);
  TABLE_NAME2 varchar2(20);
  MOD_NUM varchar2(20);
begin
TABLE_NAME1 := 'bill_billing_0';
TABLE_NAME2 := 'bill_billing_';
MOD_NUM := mod(userId,32);
if ( mod(userId,32) < 10 ) then
  TABLE_NAME := concat(TABLE_NAME1,MOD_NUM);
else
  TABLE_NAME := concat(TABLE_NAME2,MOD_NUM);
end if;
return(TABLE_NAME);
end BILL_TABLE_NAME;

=========存储过程====================

CREATE OR REPLACE PROCEDURE TMP_GJ_zk_BILL_REPAIR_DATA
(p_ali_pay_trade_id  in  VARCHAR2 )  is
  V_PROGRAME_NAME  VARCHAR2(100);
  v_error              VARCHAR2(4000);
  b_bill_id number;
  b_fee_type number;
  a_alipay_time date;
  a_notify_time date;
  a_taobao_alipay_id number;
  a_other_alipay_id number;
  a_other_alipay_email VARCHAR2(200);
  a_fee number;
  a_trade_no VARCHAR2(200);
  a_external_no VARCHAR2(200);
  a_user_id number;
  a_nick VARCHAR2(200);
  a_gmt_create date;
  a_bill_billing_id number;
  a_pay_tpye number;
  p_point_type number;
  countnum number;

BEGIN

for c in(
SELECT
bill_id,
ali_pay_time,
gmt_create,
ali_pay_trade_id,
point_type
FROM BILL_BILLING_POINT p
where p.ALI_PAY_TRADE_ID = p_ali_pay_trade_id
  ) loop
 
a_external_no := c.ali_pay_trade_id;

b_bill_id :=c.bill_id;

SELECT
notify_time,
taobao_alipay_id,
alipay_id,
alipay_email,
rel_receive_pay,
user_id,
nick,
fee_type,
  gmt_create
into
a_notify_time,
a_taobao_alipay_id,
a_other_alipay_id,
a_other_alipay_email,
a_fee,
a_user_id,
a_nick,
b_fee_type,
  a_gmt_create
FROM bill_billing 
  where id=b_bill_id;

if ( b_fee_type = 0) then
if( c.point_type = 3 )then
a_pay_tpye := 0;
else
a_pay_tpye := 1;
end if;
else
a_pay_tpye := 1;
end if;

  SELECT
  count(*)
  into
  countnum
  from bill_ali_pay
  where
  taobao_alipay_id='20880016748712110156' and
  external_no=c.ali_pay_trade_id and
  fee=a_fee and
  alipay_time=c.ali_pay_time and
  pay_tpye = a_pay_tpye;

if(countnum = 0)then
insert into bill_ali_pay(
id,
alipay_time,
notify_time,
taobao_alipay_id,
other_alipay_id,
other_alipay_email,
fee,
trade_no,
type,
status,
external_id,
external_no,
external_type,
descriptions,
user_id,
nick,
pay_tpye,
gmt_create,
bill_billing_id
) values(
seq_bill_ali_pay_id.nextval,--id
c.ali_pay_time,--alipay_time
a_notify_time,--notify_time
a_taobao_alipay_id,--taobao_alipay_id
a_other_alipay_id,--other_alipay_id
a_other_alipay_email,--other_alipay_email
a_fee,--fee
c.ali_pay_trade_id,--trade_no
null,--type
null,--status
0,--external_id
p_ali_pay_trade_id,--external_no
0,--external_type
'数据修正',--descriptions
a_user_id,
a_nick,
a_pay_tpye,
a_gmt_create,
c.bill_id
);
end if;

end loop;

end;

====================================
declare

  V_PROGRAME_NAME  VARCHAR2(100);
  v_error              VARCHAR2(4000);
  b_bill_id number;
  b_fee_type number;
  a_alipay_time date;
  a_notify_time date;
  a_taobao_alipay_id number;
  a_other_alipay_id number;
  a_other_alipay_email VARCHAR2(200);
  a_fee number;
  a_trade_no VARCHAR2(200);
  a_external_no VARCHAR2(200);
  a_user_id number;
  a_nick VARCHAR2(200);
  a_gmt_create date;
  a_bill_billing_id number;
  a_pay_tpye number;
  p_point_type number;
  countnum number;
  tabmod varchar2(2);
  usermod number;
 
 
  cursor alipay_cur is
  select  order_id  from bill_balance_result_dtl
  where biz_type=3 and gmt_create>to_date('20091111','yyyymmdd') and rel_dtl_id<0;
 
BEGIN

for alipay_rec  in  alipay_cur  loop
      for c in(
      SELECT
      bill_id,
      ali_pay_time,
      gmt_create,
      ali_pay_trade_id,
      point_type,
        user_id
      FROM BILL_BILLING_POINT p
      where p.ALI_PAY_TRADE_ID = alipay_rec.order_id
        ) loop
       
      a_external_no := c.ali_pay_trade_id;
     
      b_bill_id :=c.bill_id;
     
      /* SELECT
      notify_time,
      taobao_alipay_id,
      alipay_id,
      alipay_email,
      rel_receive_pay,
      user_id,
      nick,
      fee_type,
        gmt_create
      into
      a_notify_time,
      a_taobao_alipay_id,
      a_other_alipay_id,
      a_other_alipay_email,
      a_fee,
      a_user_id,
      a_nick,
      b_fee_type,
        a_gmt_create
      FROM bill_billing 
        where id=b_bill_id;*/
     
        usermod := mod(c.user_id,32);
        if(usermod<10) then
                       tabmod:='0'||usermod ;
        else tabmod:=''||usermod;
        end if;
       
        execute immediate '
        SELECT
      notify_time,
      taobao_alipay_id,
      alipay_id,
      alipay_email,
      rel_receive_pay,
      user_id,
      nick,
      fee_type,
        gmt_create  FROM bill_billing_'||tabmod||' where id= :b_bill_id'
        into
      a_notify_time,
      a_taobao_alipay_id,
      a_other_alipay_id,
      a_other_alipay_email,
      a_fee,
      a_user_id,
      a_nick,
      b_fee_type,
        a_gmt_create using b_bill_id;
     
     
     
     
      if ( b_fee_type = 0 and c.point_type = 3 )then
      a_pay_tpye := 0;
      else
          a_pay_tpye := 1;
      end if;
     
        SELECT
        count(*)
        into
        countnum
        from bill_ali_pay
        where
        taobao_alipay_id='20880016748712110156' and
        external_no=c.ali_pay_trade_id and
        fee=a_fee and
        alipay_time=c.ali_pay_time and
        pay_tpye = a_pay_tpye;
     
      if(countnum = 0)then
      insert into bill_ali_pay(
      id,
      alipay_time,
      notify_time,
      taobao_alipay_id,
      other_alipay_id,
      other_alipay_email,
      fee,
      trade_no,
      type,
      status,
      external_id,
      external_no,
      external_type,
      descriptions,
      user_id,
      nick,
      pay_tpye,
      gmt_create,
      bill_billing_id
      ) values(
      seq_bill_ali_pay_id.nextval,--id
      c.ali_pay_time,--alipay_time
      a_notify_time,--notify_time
      a_taobao_alipay_id,--taobao_alipay_id
      a_other_alipay_id,--other_alipay_id
      a_other_alipay_email,--other_alipay_email
      a_fee,--fee
      c.ali_pay_trade_id,--trade_no
      null,--type
      null,--status
      0,--external_id
      alipay_rec.order_id,--external_no
      0,--external_type
      '数据修正',--descriptions
      a_user_id,
      a_nick,
      a_pay_tpye,
      a_gmt_create,
      c.bill_id
      );
      end if;
     
      end loop;

end loop;
end;