如何在 bash 脚本中迭代 sql 查询结果集

Question

我正在尝试编写一个bash脚本，该脚本将遍历以下sql查询的输出(我们正在使用oracle数据库(。根据模块列的值，我们需要将以下任一列的值传递给 curl 调用。列 - process_instance、parent_process_instance、root_process_instance。

SQL查询：

SELECT PROCESS_INSTANCE, PARENT_PROCESS_INSTANCE, ROOT_PROCESS_INSTANCE, STATUS, MODULE, START_TIME FROM PROCESS_INFO WHERE STATUS='pi_halted' ORDER BY START_TIME DESC;

到目前为止，我已经想出了下面的bash脚本。您能否帮助我迭代结果集，以便我可以根据模块列值传递所需的值。

#!/bin/bash
#Update below properties as per enviornment
export ENV=DEV
export DBHOST=localhost
export DBPORT=1737
export DBSID=TEST01
export DBUSER=TESTUSER
export DBPASS=TESTUSER
export LOGDIR=/usr/app/$USER/data/logs
#-------------------------------------------------------------------------
#----------- DO NOT EDIT AFTER THIS LINE ---------------------------------
#-------------------------------------------------------------------------
#create directory structure if not exists for storing log files
mkdir -p $LOGDIR/process_instance_data
mkdir -p $LOGDIR/process_instance_data/logs
mkdir -p $LOGDIR/process_instance_data/logs/halted
export halted_process_instance_logfile=$LOGDIR/process_instance_data/logs/halted/log_$(date "+%Y%m%d%H%M%S").log
#execute sql query
echo
echo "Enviornment : $ENV"
echo
echo "Connecting to - $DBUSER/$DBPASS@$DBHOST:$DBPORT/$DBSID"
echo "Getting halted process instance data logged before : $(date -d "1 days ago" +%d/%m/%Y) 00:01:00"
echo
sqlplus -s $DBUSER/$DBPASS@$DBHOST:$DBPORT/$DBSID << FIN_SQL > $halted_process_instance_logfile
set head off
set line 1024
set pages 9999
SET SERVEROUTPUT ON;
SELECT PROCESS_INSTANCE, PARENT_PROCESS_INSTANCE, ROOT_PROCESS_INSTANCE, STATUS, MODULE, START_TIME FROM PROCESS_INFO WHERE STATUS='pi_halted' ORDER BY START_TIME DESC;
SET SERVEROUTPUT OFF;
FIN_SQL
echo "Please check log file for details : $(readlink -f $halted_process_instance_logfile)"

Answer 1

基本上解析你的输出文件

cat $halted_process_instance_logfile |while read variablename;do myfoo ${variablename} ;echo foo done;done

• 在正常模式下，这将逐行工作(内部字段分隔符 IFS=''(，但您可以更改 IFS 或使用多行 sed

• 请注意，while()是一个子shell，因此无法写入其父级(您的脚本(中的变量，因此您也可以这样做

  for "$(cat $halted_process_instance_logfile )" in variablename;do myfoo ${variablename} ;echo foo done;fi