Linux/Unix shell 参数传递到SQL脚本

3、通过定义变量的方式来传递参数

robin@SZDB:~/dba_scripts/custom/awr> more tmp3.sh

#!/bin/bash

# ----------------------------------------------

#  Set environment here

#  Author : Robinson Cheng

# ----------------------------------------------

if [ -f ~/.bash_profile ]; then

. ~/.bash_profile

fi

if [ -z "${1}" ] || [ -z "${2}" ] || [ -z "${3}" ] ;then

echo "Usage: "

echo "      `basename $0` <ORACLE_SID> <begin_dat> <end_date>"

read -p "please input begin ORACLE_SID:" ORACLE_SID

read -p "please input begin date and time(e.g. yyyymmddhh24):" begin_date

read -p "please input end date and time(e.g. yyyymmddhh24):" end_date

else

ORACLE_SID=${1}

begin_date=${2}

end_date=${3}

fi

export ORACLE_SID begin_date end_date

#Method 3: pass the parameter to global variable firstly.

sqlplus -S " / as sysdba" <<EOF

define begin_date=$begin_date        

define end_date=$end_date

prompt "variable value for begin_date is: &begin_date"

prompt "variable value for end_date id : &end_date"

@/users/robin/dba_scripts/custom/awr/tmp3.sql begin_date end_date

exit;

EOF

exit

robin@SZDB:~/dba_scripts/custom/awr> more tmp3.sql

SELECT snap_id, dbid, snap_level

 FROM dba_hist_snapshot

WHERE TO_CHAR (begin_interval_time, 'yyyymmddhh24') = '&begin_date'

AND TO_CHAR (end_interval_time, 'yyyymmddhh24') = '&end_date';

exit;

查看本栏目更多精彩内容：http://www.bianceng.cn/OS/unix/

（编辑：应用网_丽江站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!