Oracle存储过程创建与执行报错求助：需实现多列结果返回

错误原因分析 #

1. SELECT语句语法错误：存储过程中SELECT子句里的test1_col和test2_col之间缺少逗号，属于基础语法错误，会导致存储过程编译失败。
2. 执行时未传递OUT参数：存储过程定义了两个OUT参数，但执行时仅传入输入参数，Oracle无法找到接收输出值的变量，触发ORA-06550错误。
3. 多行结果匹配问题：当输入var_in_test=2时，test_table中存在两行匹配数据，直接用INTO赋值会触发ORA-01422（单行子查询返回多行），因为INTO仅能处理单行结果。

解决方案 #

1. 修正存储过程基础语法（单行结果场景） #

先修复SELECT语句的逗号问题，同时统一参数类型避免隐式转换，再限制查询返回单行：

CREATE OR REPLACE PROCEDURE test_schema.test_name_procedure
(
  var_in_test IN INTEGER, -- 与test1_col类型统一，避免隐式转换
  var_out_test1 OUT NUMBER,
  var_out_test2 OUT VARCHAR2(7) -- 与表字段长度保持一致
) AS
BEGIN
  SELECT
    test1_col, -- 添加缺失的逗号
    test2_col
  INTO   
    var_out_test1,
    var_out_test2
  FROM test_schema.test_table
  WHERE test1_col = var_in_test
  AND ROWNUM = 1; -- 限制返回单行，或根据业务逻辑使用MAX/MIN等聚合函数
END;
/

正确执行方式

执行时必须声明变量接收OUT参数：

DECLARE
  v_test1 NUMBER;
  v_test2 VARCHAR2(7);
BEGIN
  test_schema.test_name_procedure(2, v_test1, v_test2);
  DBMS_OUTPUT.PUT_LINE('test1_col: ' || v_test1 || ', test2_col: ' || v_test2);
END;
/

2. 实现多行多列结果返回（核心需求） #

如果需要返回匹配条件的所有行，OUT参数无法直接承载多行数据，推荐使用REF CURSOR输出结果集：

步骤1：创建带REF CURSOR输出的存储过程

CREATE OR REPLACE PROCEDURE test_schema.test_name_procedure
(
  var_in_test IN INTEGER,
  var_out_result OUT SYS_REFCURSOR -- 使用系统预定义的REF CURSOR类型
) AS
BEGIN
  OPEN var_out_result FOR
    SELECT test1_col, test2_col
    FROM test_schema.test_table
    WHERE test1_col = var_in_test;
END;
/

步骤2：执行并获取结果

在PL/SQL块中调用并遍历结果：

DECLARE
  v_result SYS_REFCURSOR;
  v_test1 INTEGER;
  v_test2 VARCHAR2(7);
BEGIN
  test_schema.test_name_procedure(2, v_result);
  LOOP
    FETCH v_result INTO v_test1, v_test2;
    EXIT WHEN v_result%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('test1_col: ' || v_test1 || ', test2_col: ' || v_test2);
  END LOOP;
  CLOSE v_result;
END;
/

在SQL Developer等工具中也可以用简化方式查看结果：

VAR cur REFCURSOR;
EXEC test_schema.test_name_procedure(2, :cur);
PRINT cur;

关键说明 #

• 若业务逻辑要求返回单行，必须确保查询结果唯一（比如添加唯一键条件、使用聚合函数），否则会触发多行匹配错误。
• REF CURSOR是Oracle返回多行结果集的标准方案，比自定义集合类型更简洁通用，适合大多数多列多行返回场景。

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