如何编写含嵌套表的Oracle存储过程实现批量插入？

要实现同时插入任意数量城镇到嵌套表的需求，我们可以利用你已经定义好的嵌套表类型作为存储过程的输入参数，这样就能一次性接收多个城镇数据并完成插入。以下是完整的解决方案：

1. 修正类型定义的小问题 #

首先注意到你定义的town_objtyp里用了Oracle关键字desc作为字段名，这会导致语法错误，建议先把它改成非关键字，比如town_desc：

CREATE OR REPLACE TYPE town_objtyp AS OBJECT (
    townName VARCHAR,
    population NUMBER,
    town_desc VARCHAR(20) -- 替换原有的desc字段名
) /

其他类型定义保持不变即可。

2. 修改后的存储过程 #

我们给存储过程新增一个town_nestedtyp类型的参数，用来接收任意数量的城镇数据，并且设置默认值为NULL，这样用户可以选择只插入国家信息，或者同时插入城镇：

CREATE OR REPLACE PROCEDURE Add_Country_With_Towns (
    O_countryName IN CHAR,
    O_continent IN CHAR,
    O_towns IN town_nestedtyp DEFAULT NULL
) AS
BEGIN
    DBMS_OUTPUT.PUT_LINE ('Insert attempted');
    
    INSERT INTO Country_objtab(countryName, continent, town_nested)
    VALUES(
        O_countryName,
        O_continent,
        -- 如果没传城镇数据，插入空的嵌套表避免字段为NULL
        COALESCE(O_towns, town_nestedtyp())
    );
    
    DBMS_OUTPUT.PUT_LINE ('Insert succeeded');
END;
/

3. 存储过程调用示例 #

示例1：只插入国家信息 #

如果不需要插入城镇，直接调用时省略第三个参数即可：

BEGIN
    Add_Country_With_Towns('USA', 'North America');
END;
/

示例2：同时插入国家和多个城镇 #

通过town_nestedtyp构造多个town_objtyp对象，一次性传递所有城镇数据：

BEGIN
    Add_Country_With_Towns(
        'FRA',
        'Europe',
        town_nestedtyp(
            town_objtyp('Paris', 2161000, 'National capital'),
            town_objtyp('Lyon', 522000, 'Major industrial city'),
            town_objtyp('Marseille', 870000, 'Mediterranean port')
        )
    );
END;
/

额外说明 #

• 因为你给嵌套表定义了主键(NESTED_TABLE_ID, townName)，所以同一个国家下的城镇名称不能重复，插入时要避免重复值，否则会触发主键约束错误。
• 如果后续需要给已存在的国家添加城镇，可以用TABLE()函数更新嵌套表，示例如下：

INSERT INTO TABLE(SELECT town_nested FROM Country_objtab WHERE countryName = 'USA')
VALUES(town_objtyp('New York', 8336817, 'Big Apple'));

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