统一执行脚本

说明:在项目中经常会有临时需求,如临时调用一些存储过程,如果每次都需要写脚本,会比较麻烦,
     而且时间长了,也不知道具体做了什么,所以写一个脚本进行集中管理.
     
建表脚本
create sequence S_commid
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;

drop table t_command_list;
create table t_command_list
(inpudate                 date,   
 commid                   number primary key,
 commname                 varchar2(1000),
 status                   number,
 starttime                date,
 endtime                  date,
 usetime                  INTERVAL DAY(2) TO SECOND(6)
 );
comment on column t_command_list.status
  is '0 未执行，1正在执行，2执行完成，505执行错误';
 
初始化数据
delete from t_command_list;
commit;   
insert into t_command_list values(trunc(sysdate,'dd'),s_commid.NEXTVAL,'loopcalculate.sh',0,null,null,null);
commit; 

创建测试脚本 loopcalculate.sh
#!/bin/sh
#for i in `seq 1 3`
i=0
while [ $i -le 3 ]
do
i=`expr $i + 1`
#echo $i
done
echo $i
 
创建shell脚本 exeCommandFroTable.sh
#!/bin/sh
# creator: gardeni
# function: loop to execute command
# parameter
# usage:
dblogin=aos2008/aos2008
declare -a command
command=`sqlplus -s $dblogin<         set head off
         select 123||'%'||commid||'%'||commname
           from t_command_list
          where status=0
          order by commid asc;
          exit;
          eof`
#echo $command
for i in $command
do
commid=`echo $i|awk -F% '/^[0-9]*%/{print($2)}'`
commname=`echo $i|awk -F% '/^[0-9]*%/{print($3)}'`
echo $commid
echo $commname
#update the status to 1 which means the command is running before execute the command
runSql="update t_command_list set status=1, starttime=sysdate where commid=$commid;"
echo $runSql | sqlplus -s $dblogin
#execute command
echo '' > exeCommandFroTable.log
sh $commname > exeCommandFroTable.log
#check if there is error
errNum=`cat exeCommandFroTable.log | grep ORA | wc -l`
echo $errNum
if [ $errNum -gt 0 ]; then
  #update the status to 505 which means there is error
  errSql="update t_command_list set status=505, endtime=sysdate,usetime=numtodsinterval(sysdate-starttime,'day') where commid=$commid;"
  echo $errSql | sqlplus -s $dblogin
else
  #update the status to 2 which means the command has finished
  finSql="update t_command_list set status=2, endtime=sysdate,usetime=numtodsinterval(sysdate-starttime,'day') where commid=$commid;"
  echo $finSql | sqlplus -s $dblogin
fi
done