把ORACLE过程写入SHELL脚本
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了把ORACLE过程写入SHELL脚本，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4195字，纯文字阅读大概需要6分钟。
内容图文
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 tmp_tbcsa.$1@TMP_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 tbcs.subscriber@TBCSA_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 tbcs.subscriber@TBCSA_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 tbcs.subscriber@TBCSA_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 tbcs.subs_spservice@TBCSA_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 tmp_tbcsa.$1@TMP_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 tmp_tbcsa.$1@TMP_HS_SJYZX.HEBEI.MOBILE.COM t
set t.modflag = ‘2‘
where t.rowid = cc.rowid;
WHEN e_nobiztype_Exception THEN
update tmp_tbcsa.$1@TMP_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 &
原文：http://www.cnblogs.com/dosttyy/p/4810057.html
内容总结
以上是互联网集市为您收集整理的把ORACLE过程写入SHELL脚本全部内容，希望文章能够帮你解决把ORACLE过程写入SHELL脚本所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。