Shell操作数据库流控脚本实例

#!/bin/bash

if [ "$(whoami)" != "root" ]; then
echo "Current username must be root!"
return 1
fi
sqlfile="./updatedetail.log"
m=$1
if [ -z "$m" ]; then
    m=2000
fi

tag=`. /home/oracle/.bash_profile; sqlplus -S dbo/passport@abc<<!
set head off
select max(id),min(id) from t_subscriber;
quit
!
`

count=`echo "${tag}"|awk '{print $1}'|xargs`
begin=`echo "${tag}"|awk '{print $2}'|xargs`



loop=$[($count-$begin+$m-1)/$m]
index=0


rm -rf $sqlfile
while [ "$index" -lt "$loop" ]; do
    ID1=$[$m*${index}+$begin]
    ID2=$[$m*(${index}+1)+$begin]
    let index=index+1
   
    cmd="UPDATE (SELECT SUBONLINESTATUS,CLUSTERPARTIONNAME FROM T_SUBSCRIBER WHERE CLUSTERPARTIONNAME IS NOT NULL AND ID>=${ID1} AND ID<=${ID2}) SET SUBONLINESTATUS = 2,CLUSTERPARTIONNAME=NULL;"
    echo "HOST echo 'NO. $index/$loop'">>$sqlfile
    echo "${cmd}" >>$sqlfile
   
    cmdline=". /home/oracle/.bash_profile
lmtruntime -500 sqlplus -S dbo/passport<<!
${cmd}
commit
quit
!"
    su - oracle -c "${cmdline}" > tmp.log
    result=`cat ./tmp.log`
        rm -f ./tmp.log
if [ "$(echo "${result}" | grep -c 'ORA-')" != "0" ]; then
"Update failed" >>$sqlfile
exit
else
  echo "COMMIT;" >>$sqlfile
fi 

    echo "HOST sleep $n" >>$sqlfile
done

echo "HOST echo 'Done!'" >>$sqlfile