ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用SHELL调度ORACLE存储过程

用SHELL调度ORACLE存储过程

原创 Linux操作系统 作者:tongwenbin 时间:2010-06-09 11:05:07 0 删除 编辑
用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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: PL/SQL包 详解
请登录后发表评论 登录
全部评论

注册时间:2008-07-03

  • 博文量
    30
  • 访问量
    41237