如何在Oracle中创建运行时动态类型？适配批量收集需求

我手头有一张主表，里面存储着查找表、基础表、主表、主键列、基础列这些关键元数据信息。现在我要基于这些信息构建动态SQL语句，通过EXECUTE IMMEDIATE来执行；同时因为事务里要处理海量记录，所以打算用BULK COLLECT做批量数据收集。要实现这个批量收集的核心需求，就是得在Oracle运行时，根据主表里的列信息动态创建对应的类型。

以下是我目前的存储过程代码结构：

CREATE OR REPLACE PROCEDURE pro_name(code VARCHAR2, ...)
IS
    -- 这里需要定义动态类型相关的变量/逻辑
    TYPE dyn_rec_type IS RECORD(...); -- 但静态定义没法适配主表的动态列
    TYPE dyn_tab_type IS TABLE OF dyn_rec_type;
    v_dyn_data dyn_tab_type;
BEGIN
    -- 1. 从主表获取目标表的列信息
    -- 2. 动态创建适配列结构的记录类型和集合类型
    -- 3. 生成动态SELECT语句，用BULK COLLECT INTO批量收集数据
    EXECUTE IMMEDIATE 'SELECT ' || v_column_list || ' FROM ' || v_target_table
        BULK COLLECT INTO v_dyn_data;
    -- 后续的批量处理逻辑
END;
/

关键实现要点 #

• 首先得从主表中动态拼接出目标查询的列清单v_column_list，以及目标表名v_target_table
• 因为静态定义的RECORD类型无法适配动态变化的列结构，所以需要用Oracle的DBMS_SQL或者动态PL/SQL块来创建临时的自定义类型，或者利用SYS_REFCURSOR结合BULK COLLECT到SYS.ODCIVARCHAR2LIST这类通用集合，但如果是多列不同数据类型的情况，就得动态创建自定义的记录类型和集合类型
• 动态创建类型时，可以通过拼接CREATE TYPE语句在运行时生成，不过要注意权限问题，以及类型名的唯一性（可以用会话级的临时命名规则，比如结合当前会话ID）

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