bash 如何将 sqlplus 中受影响的行数返回给 shell 脚本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10838070/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I return the number of rows affected in sqlplus to a shell script?
提问by torjinx
Here is my shell script:
这是我的shell脚本：
# Deletes data from the 'sample' table starting August 30, 2011.
# This is done in stages with a 7 second break every
# 2 seconds or so to free up the database for other users.
# The message "Done." will be printed when there are
# no database entries left to delete.
user="*****"
pass="*****"
while(true); do
starttime=`date +%s`
while [[ $((`date +%s` - $starttime)) -lt 2 ]]; do
sqlplus $user/$pass@//blabla <<EOF
whenever sqlerror exit 1
delete from
sample
where
sampletime >= to_date('08-30-2011','mm-dd-yyyy') and
rownum <= 2;
commit;
EOF
rows = ???
if [ $rows -eq 0 ] ; then
echo "Done."
exit 0;
fi
done
sleep 7
done
If there is no way to get the number of rows, maybe I can use an error code returned by sqlplus to figure out when to end the script? Any thoughts?
如果没有办法获取行数，也许我可以使用sqlplus返回的错误代码来确定何时结束脚本？有什么想法吗？
Thank you!
谢谢！
回答by René Nyffenegger
I'd use sql%rowcountwhich tells you how many rows were affected by the last DML statement.
我会用sql%rowcountwhich 告诉你有多少行受到最后一个 DML 语句的影响。
delete from
sample
where
sampletime >= to_date('08-30-2011','mm-dd-yyyy') and
rownum <= 2;
if sql%rowcount = 0 then
dbms_output.put_line('Free');
end if;
commit;
回答by user2845344
Please try this:
请试试这个：
rows=`sqlplus -s ${DB_USER}/${DB_PASSWD}@${DB_SID} <<EOF
delete from sample where sampletime >= to_date('08-30-2011','mm-dd-yyyy') and rownum <= 2;
commit;
exit;
EOF` fi
EOF` fi
echo rows = $rows
回答by psaraj12
Kindly use the below Note:-Not tested
请使用以下注意：-未测试
user="*****"
pass="*****"
while(true);
do
starttime=`date +%s`
while [[ $((`date +%s` - $starttime)) -lt 2 ]];
do
rows=`sqlplus -silent $user/$pass@//blabla << EOF
whenever sqlerror exit 1
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
select count(1) from
sample
where
sampletime >= to_date('08-30-2011','mm-dd-yyyy') and
rownum <= 2;
EOF`
if [ $rows -neq 0] ;
then
sqlplus $user/$pass@//blabla << EOF1
delete from
sample
where
sampletime >= to_date('08-30-2011','mm-dd-yyyy') and
rownum <= 2;
commit;
EOF1
else
echo "Done."
exit 0;
fi
done
sleep 7
done
