把ORACLE過程寫入SHELL指令碼

標籤：

qingli> cat ql_mon.sh
nohup $ORACLE_HOME/bin/sqlplus -s <<!! >ql_mon_$1.log
declare
  int_count number(10);
  n         number(10);
  n_tmp     number(10);
  n_subsid  number(18);
  n_region number(5);
  v_biztype varchar2(5);
  e_My_Exception EXCEPTION;
  e_nobiztype_Exception EXCEPTION;
  v_table varchar2(100);
begin
  int_count := 0;
  n_tmp := 0;
  v_biztype := null;
  select lpad(v_biztype,2,‘0‘) into v_biztype from dual;
  for cc in (select t.*, t.rowid
               from [email protected]_HS_SJYZX.HEBEI.MOBILE.COM t
              where flag =1 and  t.modflag is null
               /* and t.msisdn =‘13503355958‘*/
             /*and t.servnumber = ‘13463068105‘*/
             ) loop
    begin
    select count(*) into n_tmp from tbcs.iboss_spbizinfo where spid=cc.sp_code and bizcode = cc.oper_code;
    if n_tmp > 0 then
          select distinct t.biztype
        into v_biztype
        from tbcs.iboss_spbizinfo t
       where t.spid=cc.sp_code
         and t.bizcode = cc.oper_code;
    else
    RAISE e_nobiztype_Exception;
    end if;
    SELECT DISTINCT REGION
          INTO n_REGION
          FROM TBCS.REC_SERVNUMBER_REGION
         WHERE BEGINNUM <= cc.msisdn
           AND ENDNUM >= cc.msisdn;
      if n_region in (310, 312, 314, 316, 318) then
         select /*+ index(t,tbcs.IDX_SUBSCRIBER_SERVNUMBER) */
         count(*)
          into n_tmp
          from tbcs.subscriber t
         where t.servnumber = cc.msisdn
           and t.active = 1
           and t.region = n_region;
           if n_tmp >0 then
           select /*+ index(t,tbcs.IDX_SUBSCRIBER_SERVNUMBER) */
         t.subsid
          into n_subsid
          from tbcs.subscriber t
         where t.servnumber = cc.msisdn
           and t.active = 1
           and t.region = n_region;
          else
               n_subsid := null;
           end if;
      else
         select /*+ index(t,tbcs.IDX_SUBSCRIBER_SERVNUMBER) */
         count(*)
          into n_tmp
          from [email protected]_B.HEBEI.MOBILE.COM t
         where t.servnumber = cc.msisdn
           and t.active = 1
           and t.region = n_region;
        if n_tmp >0 then
        select /*+ index(t,tbcs.IDX_SUBSCRIBER_SERVNUMBER) */
         t.subsid
          into n_subsid
          from [email protected]_B.HEBEI.MOBILE.COM t
         where t.servnumber = cc.msisdn
           and t.active = 1
           and t.region = n_region;
         else
               n_subsid := null;
         end if;
      end if;
   
      /*select subsid
      into n_subsid
      from (select \*+ index(t,tbcs.IDX_SUBSCRIBER_SERVNUMBER) *\
             t.subsid
              from tbcs.subscriber t
             where t.servnumber = cc.servnumber
               and t.active = 1
               and t.region = n_region
            union
            select \*+ index(t,tbcs.IDX_SUBSCRIBER_SERVNUMBER) *\
             t.subsid
              from [email protected]_B.HEBEI.MOBILE.COM t
             where t.servnumber = cc.servnumber
               and t.region = n_region
               and t.active = 1);*/
      if (n_subsid is null) then
     
        RAISE e_My_Exception;
     
      end if;
      select count(*)
        into n
        from (select /*+ index(t,tbcs.IDX_SUBS_SPSERVICE_SUBSID)*/
               *
                from tbcs.subs_spservice t
               where t.subsid = n_subsid
                 and t.region = cc.msisdn
                 and t.spid = cc.sp_code
                 and t.spbizid = cc.oper_code
                 and (t.enddate is null or t.enddate >= sysdate)
              union
              select /*+ index(t,tbcs.IDX_SUBS_SPSERVICE_SUBSID) */
               *
                from [email protected]_B.HEBEI.MOBILE.COM t
               where t.subsid = n_subsid
                 and t.region = n_region
                 and t.spid = cc.sp_code
                 and t.spbizid = cc.oper_code
                 and (t.enddate is null or t.enddate >= sysdate));
   
      if n > 0 then
     
        RAISE e_My_Exception;
      end if;
        if v_biztype = ‘53‘ then
         d110601.pro_djp_to_mca_cmmb(
                                n_region,
                               cc.msisdn,
                               v_biztype,
                               cc.sp_code,
                               cc.oper_code,
                               ‘07‘,
                               cc.chrg_type+1);
        else   
        d110601.pro_djp_to_mca(n_region,
                               cc.msisdn,
                               v_biztype,
                               cc.sp_code,
                               cc.oper_code,
                               ‘07‘,
                               cc.chrg_type+1);
        end if;
      update [email protected]_HS_SJYZX.HEBEI.MOBILE.COM t
         set t.modflag = ‘1‘
       where t.rowid = cc.rowid;
   
      int_count := int_count + 1;
   
      if int_count = 1000 then
        int_count := 0;
        commit;
      end if;
   
    exception
      when e_My_Exception then
        update [email protected]_HS_SJYZX.HEBEI.MOBILE.COM t
           set t.modflag = ‘2‘
         where t.rowid = cc.rowid;
       WHEN  e_nobiztype_Exception THEN
        update [email protected]_HS_SJYZX.HEBEI.MOBILE.COM t
           set t.modflag = ‘3‘
         where t.rowid = cc.rowid;
     
    end;
  
  end loop;
  commit;
end;
/
exit;
 
 
這樣運行：
nohup ql_mon.sh TMP_12580_BOSSMINUS_20110915 &
nohup ql_mon.sh TMP_HBGJ_BOSSMINUS_20110915 &
nohup ql_mon.sh TMP_SJZQ_BOSSMINUS_20110915 &
nohup ql_mon.sh TMP_WXTYJLB_BOSSMINUS_20110915 &
nohup ql_mon.sh TMP_QTY_BOSSMINUS_20110915 &
nohup ql_mon.sh TMP_SJSJ_BOSSMINUS_20110915 &
nohup ql_mon.sh TMP_SJYL_BOSSMINUS_20110915 &
nohup ql_mon.sh TMP_KX_BOSSMINUS_20110915 &
nohup ql_mon.sh TMP_SJB_BOSSMINUS_20110915 &

把ORACLE過程寫入SHELL指令碼