在 bash 脚本中循环 sql 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4859494/
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
loop sql query in a bash script
提问by Sanjan Grero
I need to loop a oracle sqlplus query using bash.
我需要使用 bash 循环一个 oracle sqlplus 查询。
my scenario is like this. I have a set of names in a text file and i need to find out details of that names using a sqlplus query.
我的场景是这样的。我在文本文件中有一组名称，我需要使用 sqlplus 查询找出这些名称的详细信息。
textfile.txt content:
textfile.txt 内容：
john
robert
samuel
chris
bash script
bash 脚本
#!/bin/bash
while read line
do
/opt/oracle/bin/sqlplus -s user@db/password @query.sql $line
done < /tmp/textfile.txt
sql query: query.sql
sql查询：query.sql
set verify off
set heading off
select customerid from customers where customername like '%&1%';
exit
problem is when I run the script I get errors like
问题是当我运行脚本时，我收到类似的错误
SP2-0734: unknown command beginning "robert..." - rest of line ignored.
SP2-0734：以“robert...”开头的未知命令 - 其余行被忽略。
can someone tell me how to solve this?
有人可以告诉我如何解决这个问题吗？
回答by Michael Ballent
The way I do this all the time is as follows:
我一直这样做的方式如下：
#!/bin/bash
cat textfile.txt |while read Name
do
sqlplus -s userid/password@db_name > output.log <<EOF
set verify off
set heading off
select customerid from customers where customername like '%${Name}%'
/
exit
EOF
Bash will auto magically expand ${Name} for each line and place it into the sql command before sending it into sqlplus
Bash 会自动神奇地为每一行扩展 ${Name} 并将其放入 sql 命令中，然后再将其发送到 sqlplus
回答by Benoit
Do you have set define on? Is your wildcard &? You could check glogin.sqlto know.
你有set define on吗？是你的通配符&吗？你可以查一下glogin.sql就知道了。
And yes, establishing nconnections to pass nqueries is probably not a good solution. Maybe it's faster for you to develop and you will do that one time, but if not, you should maybe think of crafting a procedure.
是的，建立n连接来传递n查询可能不是一个好的解决方案。也许你的开发速度更快，你会做一次，但如果不是，你可能应该考虑制定一个程序。
