如何写一个shell脚本调用写好的oracle存储过程?
发布网友
发布时间：2022-04-08 10:02
共3个回答
懂视网
时间：2022-04-08 14:24
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 &
把ORACLE过程写入SHELL脚本
标签：
热心网友
时间：2022-04-08 11:32
sqlplus -S user/password <<EOF
exec procre();
exit;
EOF
热心网友
时间：2022-04-08 12:50
