Oracle PL/SQL：解决非嵌套表报错并封装查询输出CLOB结果

报错原因 #

报错cannot access rows from a non-nested table item是因为你定义的type_aa是关联数组（INDEX BY pls_integer），这种类型仅支持PL/SQL环境，无法在SQL语句中通过TABLE()操作符展开查询。

问题1：能否在存储过程/函数中调用get_cash_flow.func_aa？ #

可以调用，但需要先调整类型定义，让返回的集合能被SQL引擎识别（将关联数组改为嵌套表）。

问题2：封装JSON数组生成逻辑为存储过程/函数 #

步骤1：修改包定义与包体（调整集合类型） #

将原包中的关联数组改为嵌套表（移除INDEX BY pls_integer），并调整包体中集合的赋值方式：

修改后的包定义

CREATE OR REPLACE PACKAGE get_cash_flow 
IS
  TYPE type_rec IS RECORD (
    asofdate VARCHAR2(30),
    cfValue VARCHAR2(30)
  );
  -- 改为嵌套表类型，支持SQL访问
  TYPE type_aa IS TABLE OF type_rec;
  FUNCTION func_aa RETURN type_aa;
END;
/

修改后的包体

CREATE OR REPLACE PACKAGE BODY get_cash_flow
IS
  FUNCTION func_aa RETURN type_aa
  IS
    l_aa_var1 get_cash_flow.type_aa;
  BEGIN
    FOR loop_aa IN (
      SELECT 
        asofdate, 
        -- 简化计算表达式（原表达式存在重复减项，可按需调整）
        cash_inflow + TRANSFERS + ASSET_INFLOW + INTEREST_INCOME_LONG + DIVIDEND_INCOME 
        - 2*CASH_OUTFLOW - 2*ASSET_OUTFLOW 
        + INTEREST_INCOME_SHORT + MANAGEMENT_FEE + OTHER_FEES + EXPENSES AS cfValue 
      FROM eod_value_account_mtbl 
      ORDER BY asofdate
      FETCH FIRST 500 rows only
    ) LOOP
      l_aa_var1.EXTEND; -- 扩展嵌套表容量
      l_aa_var1(l_aa_var1.LAST).asofdate := loop_aa.asofdate;
      l_aa_var1(l_aa_var1.LAST).cfValue := loop_aa.cfValue;
    END LOOP;
    RETURN l_aa_var1;
  END func_aa;
END get_cash_flow;
/

步骤2：封装生成JSON数组的函数 #

创建返回CLOB类型的函数，直接返回JSON数组：

CREATE OR REPLACE FUNCTION get_cash_flow_json RETURN CLOB
IS
  vcResult CLOB;
  l_aa_var1 get_cash_flow.type_aa;
BEGIN
  -- 调用原函数获取数据集合
  l_aa_var1 := get_cash_flow.func_aa;
  
  -- 转换为JSON数组CLOB
  SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
      KEY 'cfDate' VALUE asofdate,
      KEY 'cfValue' VALUE cfValue
    ) FORMAT JSON
    RETURNING CLOB
  ) INTO vcResult
  FROM TABLE(l_aa_var1);
  
  RETURN vcResult;
END;
/

步骤3：测试函数 #

执行以下代码验证结果：

DECLARE
  v_json_result CLOB;
BEGIN
  v_json_result := get_cash_flow_json;
  DBMS_OUTPUT.PUT_LINE(v_json_result);
END;
/

可选：封装为存储过程（带输出参数） #

如果需要用存储过程实现，可创建带OUT参数的版本：

CREATE OR REPLACE PROCEDURE get_cash_flow_json_proc(p_json_result OUT CLOB)
IS
  l_aa_var1 get_cash_flow.type_aa;
BEGIN
  l_aa_var1 := get_cash_flow.func_aa;
  
  SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
      KEY 'cfDate' VALUE asofdate,
      KEY 'cfValue' VALUE cfValue
    ) FORMAT JSON
    RETURNING CLOB
  ) INTO p_json_result
  FROM TABLE(l_aa_var1);
END;
/

测试存储过程

DECLARE
  v_json_result CLOB;
BEGIN
  get_cash_flow_json_proc(v_json_result);
  DBMS_OUTPUT.PUT_LINE(v_json_result);
END;
/

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