PL/SQL中批量集合无法用于批量更新插入的问题及解决方法

一、批量更新失效的原因 #

你写的更新语句里where "InvoiceId" in (select invoiceId from invoice_data)无法工作，核心原因是PL/SQL自定义的RECORD类型集合不能直接在SQL语句中被当作表查询。SQL引擎只能识别SQL级别的类型（比如用CREATE TYPE定义的对象类型或集合类型），而你在PL/SQL块里声明的table_invoice记录类型和invoice_data_table集合类型是PL/SQL私有类型，SQL层无法解析。

二、批量更新的两种可行方案 #

方案1：改用FORALL语句（推荐，PL/SQL原生批量操作） #

不用在SQL里直接查询集合，而是用FORALL语句批量执行更新，效率比循环高很多：

FORALL i IN 1 .. invoice_data.COUNT
    UPDATE "Invoice"
    SET "Status" = 200,
        "LastModifiedDate" = SYSDATE
    WHERE "InvoiceId" = invoice_data(i).invoiceId;

注：SYSDATE可以直接用，不需要select sysdate from dual，PL/SQL和SQL都支持直接引用。

方案2：将集合类型改为SQL级别的对象类型 #

如果一定要在SQL中直接查询集合，需要先定义SQL级别的类型：

-- 先在SQL层创建对象类型（需要有创建类型权限）
CREATE TYPE invoice_obj AS OBJECT (
    invoiceId NUMBER(15),
    status NUMBER(10)
);
/

CREATE TYPE invoice_obj_table AS TABLE OF invoice_obj;
/

然后在PL/SQL块中使用这个SQL类型：

DECLARE
    invoice_data invoice_obj_table;
BEGIN
    SELECT invoice_obj(inv."InvoiceId", inv."Status")
    BULK COLLECT INTO invoice_data
    FROM "Invoice" inv
    JOIN "Company" c ON inv."CompanyId" = c."Id"
    WHERE c."CompanyType" = 1;

    -- 现在可以直接在SQL中查询集合
    UPDATE "Invoice"
    SET "Status" = 200,
        "LastModifiedDate" = SYSDATE
    WHERE "InvoiceId" IN (SELECT t.invoiceId FROM TABLE(invoice_data) t);
END;
/

三、批量插入的实现 #

同样可以用两种方式实现批量插入，避免逐条循环：

方案1：用FORALL结合查询语句 #

FORALL i IN 1 .. invoice_data.COUNT
    INSERT INTO "InvoiceStatusChange" ("Date", "NewStatus", "InvoiceId", "CompanyId")
    SELECT inv."InvoiceDate", inv."Status", inv."InvoiceId", inv."CompanyId"
    FROM "Invoice" inv
    WHERE inv."InvoiceId" = invoice_data(i).invoiceId;

方案2：直接关联集合与表的批量插入（需用SQL级别的集合类型） #

如果已经用了前面的invoice_obj_table类型，可以直接用一次INSERT完成：

INSERT INTO "InvoiceStatusChange" ("Date", "NewStatus", "InvoiceId", "CompanyId")
SELECT inv."InvoiceDate", inv."Status", inv."InvoiceId", inv."CompanyId"
FROM "Invoice" inv
JOIN TABLE(invoice_data) t ON inv."InvoiceId" = t.invoiceId;

这种方式不需要循环，直接通过集合和表关联完成批量插入，效率最高。

额外优化点 #

1. 你的初始查询里BULK COLLECT的位置写错了，正确的写法应该是：

SELECT inv."InvoiceId", inv."Status"
BULK COLLECT INTO invoice_data
FROM "Invoice" inv
JOIN "Company" c ON inv."CompanyId" = c."Id"
WHERE c."CompanyType" = 1;

原来的代码把BULK COLLECT放在列后面，语法错误，会导致编译失败。

2. 记录类型定义里最后一个字段后面多了逗号，需要去掉：

TYPE table_invoice IS RECORD
(
    invoiceId  number(15),
    status     number(10) -- 这里去掉逗号
);

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