Oracle PL/SQL：将JSON_ARRAY_T转换为XX_INPUT_TYPE对象表的方法

1. 先明确自定义类型定义（示例） #

假设你的自定义类型结构如下（需与实际定义匹配）：

-- 基础对象类型
CREATE OR REPLACE TYPE XX_OBJ_TYPE AS OBJECT (
    ID NUMBER,
    NAME VARCHAR2(100),
    AMOUNT NUMBER
);
/

-- 基于对象的表类型
CREATE OR REPLACE TYPE XX_INPUT_TYPE AS TABLE OF XX_OBJ_TYPE;
/

2. 两种转换实现方案 #

方案一：批量解析（性能更优） #

通过JSON_TABLE批量解析JSON数组，再用BULK COLLECT直接收集为自定义表类型：

DECLARE
    input_json JSON_ARRAY_T;
    input_type XX_INPUT_TYPE;
    json_str VARCHAR2(4000);
BEGIN
    -- 示例JSON数组数据（替换为你的实际JSON_ARRAY_T对象）
    input_json := JSON_ARRAY_T('[{"id":1,"name":"A","amount":100},{"id":2,"name":"B","amount":200}]');
    
    -- 将JSON_ARRAY_T转为字符串，供JSON_TABLE解析
    json_str := input_json.to_string();
    
    -- 解析并批量赋值
    SELECT XX_OBJ_TYPE(id, name, amount)
    BULK COLLECT INTO input_type
    FROM JSON_TABLE(
        json_str,
        '$[*]' COLUMNS (
            id NUMBER PATH '$.id',
            name VARCHAR2(100) PATH '$.name',
            amount NUMBER PATH '$.amount'
        )
    );
    
    -- 可选：验证结果
    FOR i IN 1..input_type.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('ID: ' || input_type(i).ID || ', Name: ' || input_type(i).NAME || ', Amount: ' || input_type(i).AMOUNT);
    END LOOP;
END;
/

方案二：逐个遍历构建 #

手动遍历JSON数组的每个元素，逐个创建对象并添加到表类型中：

DECLARE
    input_json JSON_ARRAY_T;
    input_type XX_INPUT_TYPE := XX_INPUT_TYPE(); -- 初始化表类型
    json_obj JSON_OBJECT_T;
    v_id NUMBER;
    v_name VARCHAR2(100);
    v_amount NUMBER;
BEGIN
    -- 示例JSON数组数据
    input_json := JSON_ARRAY_T('[{"id":1,"name":"A","amount":100},{"id":2,"name":"B","amount":200}]');
    
    -- 遍历JSON数组（注意JSON_ARRAY_T的索引从0开始）
    FOR i IN 0..input_json.get_size()-1 LOOP
        json_obj := input_json.get_object(i);
        
        -- 提取JSON字段值
        v_id := json_obj.get_number('id');
        v_name := json_obj.get_string('name');
        v_amount := json_obj.get_number('amount');
        
        -- 扩展表类型并添加新对象
        input_type.EXTEND();
        input_type(input_type.COUNT) := XX_OBJ_TYPE(v_id, v_name, v_amount);
    END LOOP;
    
    -- 可选：验证结果
    FOR i IN 1..input_type.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('ID: ' || input_type(i).ID || ', Name: ' || input_type(i).NAME || ', Amount: ' || input_type(i).AMOUNT);
    END LOOP;
END;
/

3. 关键注意事项 #

• 确保自定义对象的字段名、数据类型与JSON中的键名、数据类型完全匹配，否则会触发解析错误。
• 若JSON存在可选字段，可在JSON_TABLE中用DEFAULT设置默认值（例如id NUMBER PATH '$.id' DEFAULT 0），避免空值报错。
• 大数据量场景下，优先选择方案一的批量解析方式，性能远高于逐个遍历。

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