如何将多记录JSON数组传入PL/SQL存储过程，求R12.2.10示例包

以下是适配Oracle EBS R12.2.10版本的JSON解析专用包及包体，基于Oracle 12c原生JSON函数实现，可直接在EBS环境中编译使用：

包定义 #

CREATE OR REPLACE PACKAGE json_parser_pkg AS
    -- 从JSON字符串中提取指定路径的字段值
    FUNCTION extract_json_field(p_json CLOB, p_field_path VARCHAR2) RETURN VARCHAR2;
    
    -- 解析JSON数组，返回游标形式的关系型结果集
    PROCEDURE parse_json_array(p_json CLOB, p_result_cur OUT SYS_REFCURSOR);
END json_parser_pkg;
/

包体实现 #

CREATE OR REPLACE PACKAGE BODY json_parser_pkg AS
    FUNCTION extract_json_field(p_json CLOB, p_field_path VARCHAR2) RETURN VARCHAR2 IS
        v_field_value VARCHAR2(4000);
    BEGIN
        SELECT JSON_VALUE(p_json, p_field_path) INTO v_field_value FROM DUAL;
        RETURN v_field_value;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN NULL;
        WHEN OTHERS THEN
            RETURN NULL;
    END extract_json_field;
    
    PROCEDURE parse_json_array(p_json CLOB, p_result_cur OUT SYS_REFCURSOR) IS
    BEGIN
        OPEN p_result_cur FOR
            SELECT jt.item_id, jt.item_name, jt.item_price
            FROM JSON_TABLE(
                p_json,
                '$[*]' COLUMNS (
                    item_id NUMBER PATH '$.id',
                    item_name VARCHAR2(150) PATH '$.name',
                    item_price NUMBER(10,2) PATH '$.price'
                )
            ) jt;
    EXCEPTION
        WHEN OTHERS THEN
            IF p_result_cur%ISOPEN THEN
                CLOSE p_result_cur;
            END IF;
            RAISE;
    END parse_json_array;
END json_parser_pkg;
/

使用示例 #

1. 提取单个JSON字段 #

DECLARE
    v_sample_json CLOB := '{"order": {"id": 2024, "customer": "ABC Corp", "total": 1500.50}}';
    v_customer_name VARCHAR2(150);
BEGIN
    v_customer_name := json_parser_pkg.extract_json_field(v_sample_json, '$.order.customer');
    DBMS_OUTPUT.PUT_LINE('客户名称: ' || v_customer_name);
END;
/

2. 解析JSON数组 #

DECLARE
    v_array_json CLOB := '[{"id":1,"name":"笔记本电脑","price":4999.00},{"id":2,"name":"无线鼠标","price":99.90},{"id":3,"name":"键盘","price":199.00}]';
    v_result_cur SYS_REFCURSOR;
    v_id NUMBER;
    v_name VARCHAR2(150);
    v_price NUMBER(10,2);
BEGIN
    json_parser_pkg.parse_json_array(v_array_json, v_result_cur);
    LOOP
        FETCH v_result_cur INTO v_id, v_name, v_price;
        EXIT WHEN v_result_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('商品ID: ' || v_id || ' | 商品名称: ' || v_name || ' | 价格: ' || v_price);
    END LOOP;
    CLOSE v_result_cur;
END;
/

注意事项 #

• R12.2.10配套的Oracle数据库版本需为12cR1及以上，确保原生JSON函数可用；
• 编译包的用户需具备CREATE PROCEDURE权限，以及访问JSON相关系统函数的权限；
• 若JSON数据长度超过4000字符，可根据需求调整函数返回值类型为CLOB。

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