通过外壳脚本从文件读取并将值放在WHERE子句中 [英] To read from file and putting values in WHERE clause via shell script

问题描述

Shell脚本

#! /bin/bash
sqlplus -s <username>/<passwd>@dbname << EOF
set echo on
set pagesize 0
set verify off
set lines 32000
set trimspool on
set feedback off
SELECT *
  FROM <dbname>.<tablename1> tr
  LEFT JOIN <tablename2> t2 ON t2.id2 = tr.id1
  LEFT JOIN <tablename3> t3 ON t3.id2 = tr.id1
  LEFT JOIN <tablename4> t4 ON t4.id2 = tr.id1
 WHERE tr.TIMESTAMP > SYSDATE - 75 / 1440
   AND tr.TIMESTAMP <= SYSDATE - 15 / 1440
   AND t2.value in ( value1, value2, etc...)
 ORDER BY timestamp;

exit;  
EOF

现在，目的是在t2.value列中读取32000个值.这些值只是诸如1234、4567、1236之类的数字，我想我应该将这些数字放在单独的文件中，然后在t2.value中读取该文件.但是我希望只对SQL执行一次，而不是32000次.你能告诉我这怎么可能吗?如何获取t2.value中的值(用逗号分隔)(通过某个循环，可能读取行)?

Now, the purpose is to read 32000 values in t2.value column. These values are only numbers like 1234,4567,1236, etc. I guess i should put these numbers in a separate file and then reading that file in t2.value. But i want the SQL to be excuted only once not 32000 times. can you please advise how is that possible ? How can i get the values (separated by commas) in t2.value (by some loop, reading line probably) ?

推荐答案

您可以从文件中创建一个逗号分隔的列表，该列表包含每行一个数字，如下所示:

You can create a comma separated list from the file that contains all the numbers one per line as:

t2val=$(cat your_file_with_numbers | tr '\n' ',' | sed 's/,$//')

接下来，您可以将此变量 $ t2val 用作:

Next you can use this variable $t2val as:

....
and t2.value in ( "$t2val")

我们正在使用逗号替换两行之间的 \ n ，并删除最后一个逗号(由于后面没有数字，因为这会在Oracle中造成语法错误).

We are replacing the \n between the lines with a comma and deleting the last comma which as no following number as it will create a syntax error in Oracle.

这篇关于通过外壳脚本从文件读取并将值放在WHERE子句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！