demo.bash:
#!/bin/bash
#$1 RPT_RMTSpecialRates
T_NAME=$1 ###这个是传入变量, 我这里是表名
######${DB_ACCOUNT}/${XCS_DB_PASS}@${DB_NAME_DEFAULT} 用的oracleDB,这里是Dbuser/Dbpassword@SID
##SDate=`sqlplus -S 。。。。。。。 ` 会将sql查出来的值赋给 SDate, 返回值是拼接的一个字符串 'dateStr '||to_char((startDate),99999999)||' '||to_char((endDate),99999999) , 返回值如果是多条建议使用将结果输出到文件, 后面会有输出到文件的sample。
SDate=`sqlplus -S ${DB_ACCOUNT}/${XCS_DB_PASS}@${DB_NAME_DEFAULT}<<EOF
set heading off feedback off pagesize 0 verify off echo off numwidth 4
select 'dateStr '||to_char((startDate),99999999)||' '||to_char((endDate),99999999) from EXCLUSIVES.RunDateConfiguration where TableName = '$T_NAME' and ROWNUM <= 1 order by startDate desc ;
insert into EXCLUSIVES.RunDateConfiguration(startDate, endDate, TableName)
select max(endDate) ,case when '$T_NAME'='RPT_RMTSpecialRates' then to_number(to_char(to_date(max(endDate),'yyyymmdd'),'yyyymmdd')+15) else to_number(to_char(add_months(to_date(max(endDate),'yyyymmdd'),6),'yyyymmdd')) end, '$T_NAME' from EXCLUSIVES.RunDateConfiguration where TableName = '$T_NAME';
commit;
exit
EOF`
day1=`echo $SDate | awk '{print $2}' ` # 取$SDate的第二个值
day2=`echo $SDate | awk '{print $3}' ` # 取$SDate的第三个值
echo $day1
echo $day2
if [ -n "$SDate" ]; then
echo "The rows is $SDate"
else
echo "failed to get SDate"
fi
#返回多条数据, 输出到文件,每列数据之间以’,‘ 间隔
1) spool .....spool off
#!/bin/bash
T_NAME=RPT_RMTSpecialRates
sqlplus -s ${DB_ACCOUNT}/${XCS_DB_PASS}@${DB_NAME_DEFAULT} <<EOF
spool test.txt
set pages 0
set feed off
set heading off
set feedback off
set verify off
set linesize 1000
select to_char((startDate),99999999)||','||to_char((endDate),99999999) from EXCLUSIVES.RunDateConfiguration where TableName = '$T_NAME' order by startDate desc ;
spool off
EOF
result:
2) >/var/tmp/xcs/result.log
#!/bin/bash
#ident "%W%"
. /opt/foapps/sbl/commonProperties/SBL_Common_Properties.profile;
. /opt/foapps/sbl/commonProperties/script/cyberark/cyberArk.profile;
export DB_ACCOUNT='EXCLUSIVES';
export XCS_DB_PASS=`perl $GLOBAL_PROP_SCRIPT_DIR/cyberark/cyberArkUtil.pl ${DB_NAME_DEFAULT} ${DB_ACCOUNT}`;
sqlplus ${DB_ACCOUNT}/${XCS_DB_PASS}@${DB_NAME_DEFAULT} >/var/tmp/xcs/result.log <<EOF
set heading off feedback off pagesize 0 verify off echo off numwidth 4
select sysdate from dual;
exit
EOF
result:
最后有一个疑问: 如果返回的是一个number 类型的数据, $SDate 是空白, 没有返回结果值, 转成字符串就好了。