Oracle ORDS中PL/SQL块适配JSON对象数组输入的问题

问题说明 #

我在Oracle ORDS中使用现有PL/SQL块处理请求，当前输入JSON为字符串数组格式，现需将输入改为JSON对象数组格式，需要调整代码适配新的输入结构。

当前输入JSON格式 #

['123','456']

目标输入JSON格式 #

[{
"vendorid":123
}]

调整后的PL/SQL代码 #

DECLARE
    -- JSON Object and Array to hold the final response and data
    L_JSON_OBJECT   JSON_OBJECT_T := JSON_OBJECT_T();
    L_JSON_ARRAY    JSON_ARRAY_T := JSON_ARRAY_T();
    L_SUPPLIER_DATA JSON_OBJECT_T;
    
    -- Variable to hold the parsed JSON array of vendor objects from the request body
    L_VENDOR_OBJS JSON_ARRAY_T;
    L_VENDOR_OBJ  JSON_OBJECT_T; -- 新增：存储数组中的单个JSON对象
    L_VENDOR_ID   NUMBER;

    -- Cursor to fetch matching records with non-null EXP_DATE and ensure uniqueness
    CURSOR C_GET_SUPPLIERS (P_VENDOR_ID NUMBER) IS
        SELECT DISTINCT 
            VENDOR_ID,
            EXP_DATE
        FROM 
            XXMIC_AP_SUPP_DETAILS_OUT_T
        WHERE 
            VENDOR_ID = P_VENDOR_ID
            AND EXP_DATE IS NOT NULL
            AND SYSDATE < EXP_DATE;  -- 仅获取过期日期在未来的记录

    -- Record to store each row fetched from the cursor
    L_SUPPLIER_ROW C_GET_SUPPLIERS%ROWTYPE;
    
    -- Status and description
    L_STATUS VARCHAR2(10);
    L_MESSAGE VARCHAR2(4000);
    
BEGIN
    -- Parse the JSON array from the request body
    L_VENDOR_OBJS := JSON_ARRAY_T.PARSE(:body);  -- :body为请求体中的输入JSON

    -- Iterate over the list of vendor objects
    FOR I IN 1 .. L_VENDOR_OBJS.GET_SIZE LOOP
        -- 从数组中取出单个JSON对象
        L_VENDOR_OBJ := JSON_OBJECT_T(L_VENDOR_OBJS.GET(I));
        -- 提取对象中的vendorid字段值
        L_VENDOR_ID := L_VENDOR_OBJ.GET_NUMBER('vendorid');

        -- Open the cursor to fetch supplier records
        OPEN C_GET_SUPPLIERS(L_VENDOR_ID);
        LOOP
            FETCH C_GET_SUPPLIERS INTO L_SUPPLIER_ROW;
            EXIT WHEN C_GET_SUPPLIERS%NOTFOUND;

            -- Create a new JSON object for each supplier record
            L_SUPPLIER_DATA := JSON_OBJECT_T();
            L_SUPPLIER_DATA.PUT('vendor_id', L_SUPPLIER_ROW.VENDOR_ID);
            L_SUPPLIER_DATA.PUT('exp_date', TO_CHAR(L_SUPPLIER_ROW.EXP_DATE, 'YYYY-MM-DD'));

            -- Append the JSON object to the JSON array
            L_JSON_ARRAY.APPEND(L_SUPPLIER_DATA);
        END LOOP;

        CLOSE C_GET_SUPPLIERS;

        -- 若当前vendor_id无匹配记录，设置错误状态
        IF L_JSON_ARRAY.GET_SIZE = 0 THEN
            L_STATUS := 'ERROR';
            L_MESSAGE := '未找到vendor_id: ' || L_VENDOR_ID || '对应的有效EXP_DATE记录';
        ELSE
            L_STATUS := 'SUCCESS';
            L_MESSAGE := '数据获取成功';
        END IF;
    END LOOP;

    -- Build the final JSON object
    L_JSON_OBJECT.PUT('status', L_STATUS);
    L_JSON_OBJECT.PUT('message', L_MESSAGE);
    L_JSON_OBJECT.PUT('data', L_JSON_ARRAY);

    -- Output the JSON response
    OWA_UTIL.MIME_HEADER('application/json', TRUE);
    HTP.P(L_JSON_OBJECT.TO_CLOB);

EXCEPTION
    WHEN OTHERS THEN
        -- Handle any errors and return an error JSON response
        L_JSON_OBJECT := JSON_OBJECT_T();
        L_JSON_OBJECT.PUT('status', 'ERROR');
        L_JSON_OBJECT.PUT('message', '发生错误: ' || SQLERRM);
        L_JSON_OBJECT.PUT('data', JSON_ARRAY_T());

        OWA_UTIL.MIME_HEADER('application/json', TRUE);
        HTP.P(L_JSON_OBJECT.TO_CLOB);
END;

代码修改说明 #

• 新增变量L_VENDOR_OBJ JSON_OBJECT_T，用于存储JSON数组中的单个对象元素
• 将原变量L_VENDOR_IDS重命名为L_VENDOR_OBJS，更贴合新输入的语义
• 修改遍历逻辑：从数组中取出每个JSON对象，通过GET_NUMBER('vendorid')提取字段值，替代原直接获取数组元素的方式
• 调整提示信息为中文表述，更易理解

表结构 #

SEQ_ID                  NOT NULL NUMBER        
VENDOR_ID               NOT NULL NUMBER        
PARTY_ID                         NUMBER        
VENDOR_NUMBER                    VARCHAR2(30)  
VENDOR_START_DATE                DATE          
VENDOR_END_DATE                  DATE          
VENDOR_NAME                      VARCHAR2(360) 
PARTY_SITE_ID                    NUMBER        
VENDOR_SITE_ID          NOT NULL NUMBER        
VENDOR_SITE_CODE                 VARCHAR2(50)  
SITE_START_DATE                  DATE          
SITE_END_DATE                    DATE          
BUSINESS_RELATION_SHIP           VARCHAR2(30)  
CREATION_DATE                    DATE          
LAST_UPDATE_DATE                 DATE          
FUSION_CREATION_DATE             DATE          
FUSION_LAST_UPDATE_DATE          DATE          
FUSION_CREATED_BY                VARCHAR2(64)  
FUSION_LAST_UPDATED_BY           VARCHAR2(64)  
BUSINESS_UNIT_ID                 NUMBER        
EXP_DATE                         DATE   

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