Oracle 19.0下将指定JSON解析为表数据的PL/SQL过程需求

1. 目标表结构设计 #

需创建5张关联表，以product_key作为主关联字段：

1.1 产品基础信息表（product_general） #

CREATE TABLE product_general (
    product_key VARCHAR2(20) PRIMARY KEY,
    group_subtype_id NUMBER,
    group_subtype_name VARCHAR2(100),
    variant_id NUMBER,
    variant_name VARCHAR2(50),
    rb_customer_id NUMBER
);

1.2 零件编号表（product_partnumbers） #

CREATE TABLE product_partnumbers (
    partnumber VARCHAR2(50) PRIMARY KEY,
    product_key VARCHAR2(20) REFERENCES product_general(product_key),
    pn_type VARCHAR2(50),
    mat_status VARCHAR2(50)
);

1.3 零件属性表（product_properties） #

CREATE TABLE product_properties (
    property_id NUMBER,
    partnumber VARCHAR2(50) REFERENCES product_partnumbers(partnumber),
    property_name VARCHAR2(100),
    value_id NUMBER,
    value VARCHAR2(200),
    PRIMARY KEY (property_id, partnumber)
);

1.4 文档编号表（product_documents） #

CREATE TABLE product_documents (
    document_number VARCHAR2(20),
    document_version VARCHAR2(10),
    document_type VARCHAR2(50),
    product_key VARCHAR2(20) REFERENCES product_general(product_key),
    PRIMARY KEY (document_number, document_version, document_type)
);

1.5 目标市场表（product_target_markets） #

CREATE TABLE product_target_markets (
    country_name VARCHAR2(100),
    iso_code VARCHAR2(10),
    product_key VARCHAR2(20) REFERENCES product_general(product_key),
    PRIMARY KEY (country_name, iso_code)
);

2. PL/SQL存储过程实现 #

CREATE OR REPLACE PROCEDURE parse_product_json(p_json_data CLOB)
IS
    v_product_key VARCHAR2(20);
BEGIN
    -- 1. 解析并插入产品基础信息
    SELECT JSON_VALUE(p_json_data, '$.general.product_key')
    INTO v_product_key
    FROM DUAL;

    INSERT INTO product_general (
        product_key, group_subtype_id, group_subtype_name,
        variant_id, variant_name, rb_customer_id
    )
    SELECT
        JSON_VALUE(p_json_data, '$.general.product_key'),
        JSON_VALUE(p_json_data, '$.general.group_subtype_id'),
        JSON_VALUE(p_json_data, '$.general.group_subtype_name'),
        JSON_VALUE(p_json_data, '$.general.variant_id'),
        JSON_VALUE(p_json_data, '$.general.variant_name'),
        JSON_VALUE(p_json_data, '$.general.rb_customer_id')
    FROM DUAL;

    -- 2. 解析并插入零件编号及对应属性
    INSERT INTO product_partnumbers (
        partnumber, product_key, pn_type, mat_status
    )
    SELECT
        j.partnumber, v_product_key, j.pn_type, j.mat_status
    FROM JSON_TABLE(
        p_json_data, '$.partnumbers[*]'
        COLUMNS (
            partnumber VARCHAR2(50) PATH '$.partnumber',
            pn_type VARCHAR2(50) PATH '$.pn_type',
            mat_status VARCHAR2(50) PATH '$.mat_status'
        )
    ) j;

    INSERT INTO product_properties (
        property_id, partnumber, property_name, value_id, value
    )
    SELECT
        p.property_id, j.partnumber, p.property_name, p.value_id, p.value
    FROM JSON_TABLE(
        p_json_data, '$.partnumbers[*]'
        COLUMNS (
            partnumber VARCHAR2(50) PATH '$.partnumber',
            properties CLOB PATH '$.properties'
        )
    ) j
    CROSS JOIN JSON_TABLE(
        j.properties, '$[*]'
        COLUMNS (
            property_id NUMBER PATH '$.property_id',
            property_name VARCHAR2(100) PATH '$.property_name',
            value_id NUMBER PATH '$.value_id',
            value VARCHAR2(200) PATH '$.value'
        )
    ) p;

    -- 3. 解析并插入文档编号信息
    INSERT INTO product_documents (
        document_number, document_version, document_type, product_key
    )
    SELECT
        j.document_number, j.document_version, j.document_type, v_product_key
    FROM JSON_TABLE(
        p_json_data, '$.document_numbers[*]'
        COLUMNS (
            document_number VARCHAR2(20) PATH '$.document_number',
            document_version VARCHAR2(10) PATH '$.document_version',
            document_type VARCHAR2(50) PATH '$.document_type'
        )
    ) j;

    -- 4. 解析并插入目标市场信息
    INSERT INTO product_target_markets (
        country_name, iso_code, product_key
    )
    SELECT
        j.country_name, j.iso_code, v_product_key
    FROM JSON_TABLE(
        p_json_data, '$.target_markets[*]'
        COLUMNS (
            country_name VARCHAR2(100) PATH '$.country_name',
            iso_code VARCHAR2(10) PATH '$.iso_code'
        )
    ) j;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20001, 'JSON解析存储过程执行失败: ' || SQLERRM);
END parse_product_json;
/

3. 使用说明 #

调用存储过程时，传入JSON格式的CLOB参数即可：

DECLARE
    v_json CLOB;
BEGIN
    -- 替换为实际待解析的JSON数据
    v_json := '{
        "general":{
            "product_key":"501088",
            "group_subtype_id":1,
            "group_subtype_name":"Wheel Speed Sensor",
            "variant_id":6,
            "variant_name":"DF22",
            "rb_customer_id":287383
        },
        "partnumbers":[
            {
                "partnumber":"F04FD009BD",
                "pn_type":"Series OEM",
                "mat_status":"00 - planned",
                "properties":[
                    {"property_id":4,"property_name":"ASIC P/N","value_id":38,"value":"8905502648"},
                    {"property_id":5,"property_name":"ASIC type","value_id":56,"value":"TLE4942"}
                ]
            }
        ],
        "document_numbers":[
            {"document_number":"1234569871","document_version":"05","document_type":"TCD"}
        ],
        "target_markets":[
            {"country_name":"Belize","iso_code":"BZ"}
        ],
        "hierarchy_information":{"child_products":[]}
    }';
    parse_product_json(v_json);
END;
/

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