Oracle如何定义匹配MYTABLE的集合类型实现批量插入

原有实现的问题 #

你当前定义的MYTYPE_REC中DAY是嵌套集合类型，直接调用SELECT * FROM TABLE(myVarTable)返回的结果只有TS和嵌套表类型的DAY两列，和MYTABLE的32列（TS+31个DAY）结构不匹配，无法直接插入。

方案1：PL/SQL层面轻量实现（推荐，无需创建全局SQL对象） #

不需要额外创建Schema级别的类型对象，直接在PL/SQL块内定义即可，性能最优，完美适配循环赋值+批量插入的需求（要求Oracle 12c及以上版本支持动态属性访问）：

DECLARE
    -- 定义与MYTABLE行结构完全匹配的记录类型
    TYPE mytable_rec IS RECORD (
        ts DATE,
        day1 VARCHAR2(250),
        day2 VARCHAR2(250),
        -- 按结构补全到day31
        day31 VARCHAR2(250)
    );
    -- 定义记录类型的索引表集合
    TYPE mytable_t IS TABLE OF mytable_rec INDEX BY PLS_INTEGER;
    
    myVarTable mytable_t;
BEGIN
    -- 初始化100行集合数据
    FOR rec_idx IN 1 .. 100 LOOP
        myVarTable(rec_idx).ts := DATE '2021-01-01';
        -- 循环给day1~day31赋值
        FOR day_idx IN 1 .. 31 LOOP
            -- 动态访问对应DAY字段，适配你按下标赋值的需求
            myVarTable(rec_idx).('DAY'||day_idx) := TO_CHAR(day_idx);
        END LOOP;
    END LOOP;
    
    -- 批量插入，性能远高于逐行插入
    FORALL i IN 1 .. myVarTable.COUNT
        INSERT INTO mytable VALUES myVarTable(i);
    COMMIT;
END;
/

优势：

• 无全局Schema对象冗余，不需要额外的TYPE创建权限
• 语法简洁，动态属性访问完全适配你按序号循环给DAYi赋值的需求
• FORALL批量插入性能最优，适合大数据量插入场景

方案2：SQL级类型实现（适合跨模块复用场景） #

如果你的集合需要在多个存储过程、函数中复用，可以创建全局SQL级类型，适配SQL层面的TABLE()函数查询需求：

第一步：创建全局类型 #

-- 行对象，字段与MYTABLE完全对齐
CREATE OR REPLACE TYPE MYTYPE_REC AS OBJECT (
    TS DATE,
    DAY1 VARCHAR2(250),
    DAY2 VARCHAR2(250),
    -- 补全到DAY31
    DAY31 VARCHAR2(250)
);
/
-- 行对象集合
CREATE OR REPLACE TYPE MYTABLE_T AS TABLE OF MYTYPE_REC;
/

第二步：调用实现 #

DECLARE
    myVarTable MYTABLE_T := MYTABLE_T();
BEGIN
    -- 扩展100个元素空间
    myVarTable.EXTEND(100);
    FOR rec_idx IN 1 .. 100 LOOP
        -- 初始化行对象，占位符数量和DAY字段数量匹配即可
        myVarTable(rec_idx) := MYTYPE_REC(DATE '2021-01-01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                                          NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                                          NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                                          NULL);
        -- 循环给DAY1~DAY31赋值，11g及以下版本用动态SQL实现
        FOR day_idx IN 1 .. 31 LOOP
            EXECUTE IMMEDIATE 'BEGIN :1.DAY'||day_idx||' := :2; END;' 
                USING IN OUT myVarTable(rec_idx), TO_CHAR(day_idx);
        END LOOP;
    END LOOP;
    
    -- 直接批量插入
    INSERT INTO MYTABLE SELECT * FROM TABLE(myVarTable);
    COMMIT;
END;
/

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