通过POST请求用PL/SQL将JSON数据插入数据库

没问题，我来帮你搞定这个需求！首先咱们得先准备好存储数据的表结构，然后用PL/SQL解析收到的JSON，把顶层的Collection、Source、Timestamp和每个Inventory条目组合成独立的行插入到数据库中。

1. 先创建目标数据表 #

首先创建一个能容纳所有字段的表（假设表名为inventory_records，你可以根据实际需求修改）：

CREATE TABLE inventory_records (
    collection VARCHAR2(50),
    source VARCHAR2(50),
    record_timestamp TIMESTAMP WITH TIME ZONE,
    item_number VARCHAR2(20), -- 避免用关键字"NUMBER"作为字段名，也可以用双引号包裹原名称
    item_name VARCHAR2(50),
    item_status VARCHAR2(20)
);

2. PL/SQL插入实现方案 #

这里提供两种常用的实现方式，你可以根据自己的场景选择：

方案一：用SQL结合JSON_TABLE批量插入（高效推荐） #

这种方式直接通过SQL解析JSON并批量插入，性能比PL/SQL循环更好：

DECLARE
    -- 模拟POST请求收到的JSON数据，实际场景可替换为输入参数
    l_json CLOB := '{ 
        "Collection":"SA3", 
        "Source": "Test", 
        "Timestamp": "2013-02-20T11:13:57.7810751+01:00", 
        "Inventory": [ 
            { "NUMBER":"234A2", "NAME":"ONE", "STATUS":"OK" }, 
            { "NUMBER":"34A2", "NAME":"TWO", "STATUS":"NOTOKAY" }, 
            { "NUMBER":"9A3DA", "NAME":"THREE", "STATUS":"DONE" } 
        ] 
    }';
BEGIN
    INSERT INTO inventory_records (
        collection,
        source,
        record_timestamp,
        item_number,
        item_name,
        item_status
    )
    SELECT
        jt.collection,
        jt.source,
        TO_TIMESTAMP_TZ(jt.record_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM'),
        jt.item_number,
        jt.item_name,
        jt.item_status
    FROM JSON_TABLE(
        l_json,
        '$'
        COLUMNS (
            collection VARCHAR2(50) PATH '$.Collection',
            source VARCHAR2(50) PATH '$.Source',
            record_timestamp VARCHAR2(50) PATH '$.Timestamp',
            -- 嵌套解析Inventory数组的每一项
            NESTED PATH '$.Inventory[*]'
            COLUMNS (
                item_number VARCHAR2(20) PATH '$.NUMBER',
                item_name VARCHAR2(50) PATH '$.NAME',
                item_status VARCHAR2(20) PATH '$.STATUS'
            )
        )
    ) jt;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('数据插入完成，共插入' || SQL%ROWCOUNT || '条记录');
END;
/

方案二：PL/SQL循环遍历JSON数组 #

如果需要在插入前做自定义校验或逻辑处理，可以用Oracle的JSON对象API来遍历数组：

DECLARE
    l_json_obj JSON_OBJECT_T := JSON_OBJECT_T('{ 
        "Collection":"SA3", 
        "Source": "Test", 
        "Timestamp": "2013-02-20T11:13:57.7810751+01:00", 
        "Inventory": [ 
            { "NUMBER":"234A2", "NAME":"ONE", "STATUS":"OK" }, 
            { "NUMBER":"34A2", "NAME":"TWO", "STATUS":"NOTOKAY" }, 
            { "NUMBER":"9A3DA", "NAME":"THREE", "STATUS":"DONE" } 
        ] 
    }');
    l_inventory_arr JSON_ARRAY_T := l_json_obj.get_Array('Inventory');
    -- 先提取顶层公共字段
    l_collection VARCHAR2(50) := l_json_obj.get_String('Collection');
    l_source VARCHAR2(50) := l_json_obj.get_String('Source');
    l_timestamp TIMESTAMP WITH TIME ZONE := TO_TIMESTAMP_TZ(l_json_obj.get_String('Timestamp'), 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM');
    l_item_obj JSON_OBJECT_T;
BEGIN
    -- 遍历Inventory数组的每一项
    FOR i IN 0 .. l_inventory_arr.get_Size() - 1 LOOP
        l_item_obj := TREAT(l_inventory_arr.get(i) AS JSON_OBJECT_T);
        
        INSERT INTO inventory_records (
            collection,
            source,
            record_timestamp,
            item_number,
            item_name,
            item_status
        ) VALUES (
            l_collection,
            l_source,
            l_timestamp,
            l_item_obj.get_String('NUMBER'),
            l_item_obj.get_String('NAME'),
            l_item_obj.get_String('STATUS')
        );
    END LOOP;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('数据插入完成，共插入' || l_inventory_arr.get_Size() || '条记录');
END;
/

关键说明 #

• 我把字段名NUMBER改成了item_number，因为NUMBER是Oracle的关键字，直接使用会触发语法错误；如果你一定要保留原字段名，可以用双引号包裹（比如"NUMBER"），但不推荐这种写法。
• Timestamp字段转换成了TIMESTAMP WITH TIME ZONE类型，确保JSON中的时区信息不会丢失，转换格式'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM'能准确解析你的时间字符串格式。
• 如果是在存储过程中处理POST请求的JSON，只需要把示例中的l_json变量替换为实际的输入参数（比如CLOB类型的参数）即可。

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