Oracle数据库中如何查询返回RECORD类型的函数返回值？

Oracle的RECORD类型是PL/SQL专属数据类型，SQL引擎无法识别该类型。当你在SQL查询中直接调用返回RECORD的函数并尝试访问其字段时，SQL解析器会因无法识别该类型抛出ORA-00902: invalid datatype错误。

另外注意你提供的函数代码存在笔误：两次赋值给v_row.l_1，应该是v_row.l_2 := p_par+1，以下解决方案已修正该问题。

解决方案1：用SQL对象类型替代PL/SQL RECORD #

将包中的RECORD类型替换为SQL级别的OBJECT类型，让SQL引擎能够识别并处理：

修改后的包定义 #

CREATE OR REPLACE TYPE lst_obj IS OBJECT(
    l_1 NUMBER,
    l_2 NUMBER
);
/

CREATE OR REPLACE PACKAGE pkg1 IS
  FUNCTION func1(p_par NUMBER) RETURN lst_obj;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg1 IS
FUNCTION func1(p_par NUMBER) RETURN lst_obj IS
    v_row lst_obj;
BEGIN
    v_row := lst_obj(p_par, p_par + 1);
    RETURN v_row;
END;          
END;
/

执行查询 #

SELECT pkg1.func1(10).l_1 FROM dual;

此语句可正常返回结果10。

解决方案2：新增返回单个字段的包装函数 #

若不想修改原有RECORD类型，可在包中新增函数专门返回RECORD指定字段：

修改后的包定义 #

CREATE OR REPLACE PACKAGE pkg1 IS
  TYPE LST_REC IS RECORD(
    L_1          NUMBER,
    L_2          NUMBER
  );
  FUNCTION func1(p_par NUMBER) RETURN LST_REC;
  FUNCTION func1_l1(p_par NUMBER) RETURN NUMBER; -- 新增包装函数
END;
/

CREATE OR REPLACE PACKAGE BODY pkg1 IS
FUNCTION func1(p_par NUMBER) RETURN LST_REC IS
    v_row LST_REC;
BEGIN
    v_row.l_1 := p_par;
    v_row.l_2 := p_par + 1;
    RETURN v_row;
END;          

FUNCTION func1_l1(p_par NUMBER) RETURN NUMBER IS
    v_rec LST_REC;
BEGIN
    v_rec := func1(p_par);
    RETURN v_rec.l_1;
END;
END;
/

执行查询 #

SELECT pkg1.func1_l1(10) FROM dual;

解决方案3：在PL/SQL块中调用函数 #

若仅需在PL/SQL环境中获取结果，可直接在块内调用并输出：

DECLARE
    v_result pkg1.LST_REC;
BEGIN
    v_result := pkg1.func1(10);
    DBMS_OUTPUT.PUT_LINE('L_1: ' || v_result.l_1);
END;
/

执行后通过DBMS输出窗口查看结果。

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