如何在Shell脚本中获取Oracle存储过程的输出参数值（含多参数与字符串）

问题背景 #

现有Shell脚本通过exit :变量的方式获取Oracle存储过程的数值型输出参数，但该方法仅支持单个数值参数（exit返回值范围0-255，无法传递字符串或超出范围的数值）。需要实现：

1. 支持获取字符串类型的输出参数
2. 支持同时获取多个输出参数（混合数值、字符串类型）

现有测试代码：

现有Shell脚本 #

#!/bin/bash

sqlplus -s HOME/testhome#@SIR22457 << EOF

set serveroutput on;
variable out_shell number;
begin sam('$1',:out_shell); 
end;
/

exit :out_shell
EOF

echo "Value out_shell: $?"

单个输出参数的存储过程 #

create or replace procedure sam (var1 in varchar2, var2 out number)
as
begin
dbms_output.put_line('var1 is: '||var1); 
var2 := var1; 
end;
/

解决方案 #

一、处理字符串类型的输出参数 #

由于exit只能返回0-255的数值，无法传递字符串，因此改为在SQL*Plus中直接打印输出变量的值，再由Shell捕获并提取。

示例脚本：

#!/bin/bash

# 捕获SQL*Plus的所有输出
output=$(sqlplus -s HOME/testhome#@SIR22457 << EOF
set serveroutput on feedback off verify off heading off;
variable out_str varchar2(100);
-- 调用输出字符串参数的存储过程
begin sam_str('$1', :out_str);
end;
/
-- 打印变量值
print out_str
exit;
EOF
)

# 提取字符串参数值（跳过变量名行，取后续内容）
out_str=$(echo "$output" | grep -A1 "out_str" | tail -n1 | xargs)

echo "字符串输出参数: $out_str"

二、处理多个输出参数（混合类型） #

对于多参数场景，通过print命令逐个输出变量，再在Shell中分别提取对应值。

多输出参数的存储过程示例

create or replace procedure sam_multi (
    var1 in varchar2,
    out_num out number,
    out_str out varchar2
) as
begin
    dbms_output.put_line('输入参数: '||var1);
    out_num := length(var1); -- 数值输出：输入字符串长度
    out_str := '前缀_'||var1; -- 字符串输出
end;
/

对应的Shell脚本

#!/bin/bash

# 执行SQL并捕获输出
output=$(sqlplus -s HOME/testhome#@SIR22457 << EOF
set serveroutput on feedback off verify off heading off;
-- 声明两个输出变量
var out_num number;
var out_str varchar2(100);
begin
    sam_multi('$1', :out_num, :out_str);
end;
/
-- 打印所有输出变量
print out_num
print out_str
exit;
EOF
)

# 分别提取数值和字符串参数
out_num=$(echo "$output" | grep -A1 "out_num" | tail -n1 | xargs)
out_str=$(echo "$output" | grep -A1 "out_str" | tail -n1 | xargs)

echo "数值输出参数out_num: $out_num"
echo "字符串输出参数out_str: $out_str"

注意事项 #

• 使用feedback off verify off heading off可以屏蔽SQL*Plus的多余提示信息，避免干扰参数提取
• 如果输出参数包含特殊字符（如空格、换行），需要调整提取逻辑，例如使用awk按行匹配或更精确的正则表达式
• 若输入参数来自用户输入，建议使用绑定变量而非直接拼接字符串，避免SQL注入风险

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