oracle 从 sql 脚本返回值到 shell 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17710245/
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
Return value from sql script to shell script
提问by Angelina
I have shell script that calls the following sql script:
我有调用以下 sql 脚本的 shell 脚本：
INSERT INTO SEMANTIC.COUNT_STATISTICS (...);
UPDATE SEMANTIC.COUNT_STATISTICS
SET PRNCT_CHANGE = 1.1;
--want to store result of this bellow select statement in model_count variable
select PRNCT_CHANGE
FROM SEMANTIC.COUNT_STATISTICS
WHERE model = '&MY_MODEL'
AND NEW_DATE = (
select max(NEW_DATE)
from SEMANTIC.COUNT_STATISTICS
where MODEL = '&MY_MODEL'
);
Now, how do I return this PERCENTAGE_NUMBER variable back to my shell script?
现在，我如何将这个 PERCENTAGE_NUMBER 变量返回给我的 shell 脚本？
My shell script is as follows:
我的shell脚本如下：
#!/bin/bash
#
# setup oracle, java, and d2rq environment
. /etc/profile.d/oracle.sh
. /etc/profile.d/java.sh
. /etc/profile.d/d2rq.sh
cd /opt/D2RQ
model_count=$(sqlplus user/pass @count.sql 'MODEL')
if ["$model_count" > 0]; then
echo "percentage count is positive"
else
echo "its negative"
I would like for that last SELECT statement result to be stored into my model_count variable in shell script.
我希望将最后一个 SELECT 语句结果存储到 shell 脚本中的 model_count 变量中。
Anyone knows why is not working?
有谁知道为什么不工作？
采纳答案by Angelina
What I actually did is I separated those 2 queires and called them separatelly in my shell script:
我实际上做的是将这两个 queires 分开并在我的 shell 脚本中分别调用它们：
sqlplus -S user/pass << EOF
whenever sqlerror exit 1;
set echo on
@/opt/D2RQ/model_count.sql '$MODEL' <--model_count.sql still has those INSERT & UPDATE statements
exit;
EOF
model_count=`sqlplus -S user/pass << EOF
SELECT PRNCT_CHANGE
FROM COUNT_STATISTICS
WHERE model = '$MODEL'
AND NEW_DATE = (
select max(NEW_DATE)
from COUNT_STATISTICS
where MODEL = '$MODEL'
);
exit;
EOF`
if [ $model_count >= 0 ]; then
echo "$model_count"
else
echo "'$MODEL' is negative " | mail -s "scripts issues" -c [email protected]
fi
回答by Bjarte Brandt
A bash example with the use of a bash-function (note! database OS-authentication "/")
使用 bash 函数的 bash 示例（注意！数据库操作系统身份验证“/”）
#!/bin/bash
get_count () {
sqlplus -s / <<!
set heading off
set feedback off
set pages 0
select count(*) from all_objects where object_type = '';
!
}
count=$(get_count )
echo $count
if [ "$count" -gt 0 ]; then
echo "is greater than zero"
else
echo "is less or equal to zero"
fi
~/tmp/ $ ./count.sh INDEX
2922
is greater than zero
~/tmp/ $ ./count.sh TABLE
1911
is greater than zero
~/tmp/ $ ./count.sh FUNCTION
226
is greater than zero
~/tmp/ $ ./count.sh "SUPEROBJECT"
0
is less or equal to zero
