使用嵌套表执行MERGE时遇ORA-00902错误的解决方法

问题根源 #

你当前使用的nested_type是INDEX BY INTEGER的关联数组（索引表），这种类型属于PL/SQL专属类型，SQL引擎无法识别它，因此在MERGE语句中调用TABLE(nested_table)时会抛出"invalid datatype"错误。另外，你定义的row_type记录类型只包含a/b/c列，但MERGE语句中用到了d列，这也会导致后续列不存在的错误。

解决方案：使用SQL可见的集合类型 #

要让嵌套表能在SQL的MERGE语句中使用，必须改用数据库级别的对象类型+嵌套表类型（或包级别的非INDEX BY嵌套表类型，数据库级别更通用），具体步骤如下：

1. 创建数据库级别的对象类型和嵌套表类型 #

首先在数据库中创建SQL能识别的对象类型（替代PL/SQL的RECORD），再基于该对象创建嵌套表类型：

-- 创建对象类型，包含MERGE需要的a/b/c/d列
CREATE OR REPLACE TYPE row_obj_type AS OBJECT (
    a db_table.a%TYPE,
    b db_table.b%TYPE,
    c db_table.c%TYPE,
    d db_table.d%TYPE
);
/

-- 创建基于对象类型的嵌套表类型
CREATE OR REPLACE TYPE nested_obj_type AS TABLE OF row_obj_type;
/

2. 在PL/SQL存储过程中声明并填充嵌套表 #

将原来的关联数组变量替换为新的嵌套表类型，注意初始化并扩展集合：

DECLARE
    nested_table nested_obj_type := nested_obj_type(); -- 初始化空集合
BEGIN
    -- 示例：动态SQL批量填充嵌套表（根据你的实际逻辑调整）
    EXECUTE IMMEDIATE 'SELECT a, b, c, d FROM source_table'
    BULK COLLECT INTO nested_table;

    -- 执行MERGE语句
    MERGE INTO db_table t
    USING TABLE(nested_table) nt ON (t.a = nt.a AND t.b = nt.b)
    WHEN MATCHED THEN
        UPDATE SET c = nt.c, d = nt.d
    WHEN NOT MATCHED THEN
        INSERT (a, b, c, d) VALUES (nt.a, nt.b, nt.c, nt.d);
    
    COMMIT;
END;
/

补充说明 #

• 如果不想创建数据库级别的类型，也可以将对象类型和嵌套表类型声明在PL/SQL包中，但需要确保包的权限足够，且SQL中引用时要带上包名（如TABLE(package_name.nested_table)）。
• 若需要循环单条填充，可使用EXTEND方法扩展集合后，用对象构造函数赋值：

  FOR rec IN (SELECT a, b, c, d FROM source_table) LOOP
      nested_table.EXTEND;
      nested_table(nested_table.COUNT) := row_obj_type(rec.a, rec.b, rec.c, rec.d);
  END LOOP;
  

结论 #

完全可以从嵌套表MERGE到目标表，核心是确保嵌套表使用SQL引擎可见的类型（而非PL/SQL专属的关联数组），同时保证集合类型包含MERGE语句中需要的所有列。

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