用SHELL调度ORACLE存储过程

最新推荐文章于 2023-11-23 20:59:09 发布

congge4433

最新推荐文章于 2023-11-23 20:59:09 发布

阅读量174

点赞数

文章标签： shell 数据库

用SHELL调度ORACLE存储过程

2009-12-20 00:23

1、带参数的调度
由两个文件组成：
cat shell.sh
sqlplus EDW1/EDW @/home/oracle/EDW1_PAR/exec.sql $data_date
cat exec.sql
WHENEVER SQLERROR exit failure ROLLBACK;
exec BATCH_DW_CTL_ALL('&data_date');
exit
2、更加完善
cat shell.sh
sqlplus EDW1/EDW @/home/oracle/EDW1_PAR/exec.sql $data_date
if [ $? != 0 ]
then echo "程序执行异常，请检查日志！"
       exit
else echo "程序执行正常!"
fi
cat exec.sql
WHENEVER SQLERROR exit failure ROLLBACK;
exec BATCH_DW_CTL_ALL('&data_date');
exit
3、带2个或2个以上的参数调度
cat shell_2.sh
sqlplus EDW1/EDW @/home/oracle/EDW1_PAR/exec_2.sql $curr_rate $first_lend_amt
cat exec_2.sql
WHENEVER SQLERROR exit failure ROLLBACK;
exec p_par_two('&curr_rate','&first_lend_amt');
exit
4、用游标取参数，以实现多轮调度
cat DW_BATCH.sh
while read p_etldate
do
sqlplus edw2/edw @ ./DW_BATCH.spl ${p_etldate}
if [ $? != 0 ]
then echo "${p_etldate}出错！"
       exit
else echo "${p_etldate}完成!"
fi
done < ./DW_BATCH.list
cat DW_BATCH.spl
WHENEVER SQLERROR exit failure ROLLBACK;
exec batch_dw_create_ods_table_2('&1');
exec batch_dw_ctl_2('&1');
exec batch_dw_sub_rpt_2('&1');
exit
cat DW_BATCH.list
20091125
20091126
20091127
5、外部输入参数，多存储过程的调度
cat DW_BATCH.sh
echo "please enter ETLDATE:\c"
read p_etldate
sqlplus edw2/edw@bdw @ ./DW_BATCH.spl ${p_etldate}
if [ $? != 0 ]
then echo "${p_etldate}出错！"
       exit
else echo "${p_etldate}完成!"
       exit
fi
cat DW_BATCH.spl
WHENEVER SQLERROR exit failure COMMIT;
prompt 开始清空EDW_CTL_LOG!
truncate table EDW_CTL_LOG;
prompt 开始执行ETL和SUB总控过程!
batch_dw_ctl_2(&1);
batch_dw_sub_rpt_2(&1);
exit

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9778796/viewspace-664781/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9778796/viewspace-664781/