如何在Bash的Runsshlocal函数中为SQL脚本传参并输出到文件

问题背景 #

现有Bash函数Runsshlocal，通过SSH远程调用Oracle的sqlplus执行本地SQL脚本，并将结果输出到指定文件。需要扩展该函数，支持为SQL脚本传递参数，同时保留输出功能。

原函数代码：

function Runsshlocal
{
  HOSTS=$1
  SID=$2
  file_in=$3
  file_out=$4

  ssh -q ${HOSTS} "ORAENV_ASK=NO;ORACLE_SID=${SID}; . oraenv -s;sqlplus -s \"/ as sysdba \" " < ${file_in} > ${file_out}
}

错误尝试分析 #

之前的两种修改方式均无效，原因在于：

• 直接将参数放在重定向符号<后不符合Bash语法，<仅能指定单个输入源
• sqlplus不会自动将命令行参数绑定到脚本中的变量，需要显式定义参数映射

正确实现方式 #

方法核心思路 #

通过管道将参数定义命令和SQL脚本内容一并传递给远程的sqlplus，在脚本中使用&1、&2等占位符引用参数。

修改后的函数代码 #

function Runsshlocal
{
  # 提取基础参数
  HOSTS="$1"
  SID="$2"
  file_in="$3"
  file_out="$4"
  # 提取所有SQL脚本参数（第5个及以后的参数）
  shift 4
  SQL_PARAMS=("$@")

  # 构建sqlplus参数定义命令，将传入的参数绑定到&1、&2...变量
  DEFINE_CMD=""
  for idx in "${!SQL_PARAMS[@]}"; do
    param_num=$((idx + 1))
    DEFINE_CMD="${DEFINE_CMD}DEFINE &${param_num}='${SQL_PARAMS[$idx]}'; "
  done

  # 通过管道传递参数定义+脚本内容给远程sqlplus，结果输出到本地文件
  (echo "${DEFINE_CMD}"; cat "${file_in}") | ssh -q "${HOSTS}" "ORAENV_ASK=NO; ORACLE_SID=${SID}; . oraenv -s; sqlplus -s \"/ as sysdba\"" > "${file_out}"
}

SQL脚本编写示例 #

在SQL脚本中用&1、&2等占位符引用传递的参数：

-- example.sql
-- 引用第1个和第2个参数
SELECT '&1' AS input_param1, '&2' AS input_param2 FROM DUAL;
-- 执行完成后退出sqlplus
EXIT;

函数调用示例 #

# 调用格式：Runsshlocal 远程主机 OracleSID SQL脚本路径 输出文件路径 参数1 参数2 ...
Runsshlocal "remote-db-host" "ORCL" "./example.sql" "./result.txt" "test-value-1" "test-value-2"

替代方案（上传脚本到远程执行） #

如果允许将本地SQL脚本上传到远程主机，也可以采用以下方式：

function Runsshlocal
{
  HOSTS="$1"
  SID="$2"
  file_in="$3"
  file_out="$4"
  shift 4
  SQL_PARAMS="$@"

  # 将本地脚本上传到远程临时目录
  scp "${file_in}" "${HOSTS}:/tmp/$(basename ${file_in})"
  # 远程执行sqlplus，直接传递参数给脚本
  ssh -q "${HOSTS}" "ORAENV_ASK=NO; ORACLE_SID=${SID}; . oraenv -s; sqlplus -s \"/ as sysdba\" @/tmp/$(basename ${file_in}) ${SQL_PARAMS}" > "${file_out}"
  # 删除远程临时脚本
  ssh -q "${HOSTS}" "rm -f /tmp/$(basename ${file_in})"
}

此方法无需修改SQL脚本的参数引用方式，sqlplus会自动将命令行参数映射到&1、&2。

内容的提问来源于stack exchange，提问作者Shaz