将变量从 shell 脚本传递到 sql 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1926289/
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
Passing variable from shell script to sql statement
提问by Cez
I was trying to make cat1.txt as a variable ($2) so that it will be inserted in sql select statement. Is there a way to do this?
我试图将 cat1.txt 作为变量（$2），以便将其插入到 sql select 语句中。有没有办法做到这一点？
my cat1.txt
1111
2334
2234
3333
4444
....
....
etc.
my SQL Statement
我的 SQL 语句
set echo off linesize 280 pagesize 0 newpage 0 feedback 1 verify off head off trimspool on
alter session set sort_area_size=104857600;
define filename = &1
spool &&filename
prompt DATE TIME Service Name Amount
select trim(to_char(trans_time,'yyyymmdd')) || chr(9) ||
trim(to_char(trans_time,'hh24miss')) || chr(9) ||
trim(prod_name)|| chr(9) ||
(prod_price)
where m_date = 11 and day >= 23 and day <= 30
and (prod_bill) in ('1111','2334','2234','3333','4444')
/
spool off
exit
I want to try this,please see below. Instead of manually typing the prod_bill make it as variable like the filename. Anything I should add to my shell script? Thanks :-)
我想试试这个，请看下面。而不是手动输入 prod_bill 将其作为文件名之类的变量。我应该在我的 shell 脚本中添加什么？谢谢 ：-）
from prod_trans
where m_date = 11 and day >= 23 and day <= 30
and (prod_bill) in ()
MY SHELL SCRIPT
我的外壳脚本
#!/bin/sh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=prod
export ORACLE_HOME=/u01/app/oracle/product/9.2.0
export ORACLE_BASE ORACLE_HOME ORACLE_SID
export PATH=$PATH:/usr/local/bin:$ORACLE_HOME/bin:/usr/sbin:/opt/java1.5/bin
export USERNAME=prods
export PASSWORD=prods
export OUT_PATH=/home/prod/reports/adhoc/REp
export SCRIPT=$OUT_PATH/BICScripts/Translog
export OUTPUT=/home/prod/reports/adhoc/REp/Scripts/log/OUTPUT
export FILENM=$OUTPUT/log_`date +%y%m%d`.txt
sqlplus -s $USERNAME/$PASSWORD@$ORACLE_SID @$SCRIPT/log.sql $FILENM
回答by Venkataramesh Kommoju
#!/bin/sh
v_var=
sqlplus -S system/manager << EOF`enter code here`
SELECT username, account_status, expiry_date
FROM dba_users WHERE lower(username)=lower('$v_var');
exit;
EOF
hope it solves the problem and i answered correctly.
希望它解决了问题，我回答正确。
回答by Illotus
I've no idea whether there is any way to access Oracle db from shell script, but can you use for example Perl? I imagine this would be quite easy to do using DBIto access the db.
我不知道是否有任何方法可以从 shell 脚本访问 Oracle db，但是您可以使用例如 Perl 吗？我想这很容易使用DBI访问数据库。
Of course you could just parse the .sql -file using shell script to add the prod_bills from cat1.txt straight to it.
当然，您可以使用 shell 脚本解析 .sql 文件，将 cat1.txt 中的 prod_bills 直接添加到它。
