Oracle 19c多条件JSON匹配关系库数据的PL/SQL实现

以下是满足需求的PL/SQL代码，包含直接运行的匿名块示例和可复用的存储过程两种形式：

匿名块测试示例 #

DECLARE
    v_input_json   CLOB := '{"EMPLOYEE_NUMBER": "E12345", "FIRST_NAME": "JOHN", "LAST_NAME": "DOE", "TAX_YEAR": 2024}';
    v_output_json  CLOB;
BEGIN
    -- 解析输入JSON提取过滤条件，关联EMPLOYEES表并构造输出JSON
    SELECT JSON_ARRAYAGG(
               JSON_OBJECT(
                   'BASE_SALARY' VALUE e.BASE_SALARY,
                   'BONUS' VALUE e.BONUS,
                   'STATUS' VALUE e.STATUS
                   FORMAT JSON NULL ON NULL
               )
           ) INTO v_output_json
    FROM EMPLOYEES e
    JOIN JSON_TABLE(
        v_input_json,
        '$' COLUMNS (
            EMPLOYEE_NUMBER VARCHAR2(50) PATH '$.EMPLOYEE_NUMBER',
            FIRST_NAME VARCHAR2(50) PATH '$.FIRST_NAME',
            LAST_NAME VARCHAR2(50) PATH '$.LAST_NAME',
            TAX_YEAR NUMBER PATH '$.TAX_YEAR'
        )
    ) jt ON e.EMPLOYEE_NUMBER = jt.EMPLOYEE_NUMBER
       AND e.FIRST_NAME = jt.FIRST_NAME
       AND e.LAST_NAME = jt.LAST_NAME
       AND e.TAX_YEAR = jt.TAX_YEAR;

    -- 输出结果（可按需替换为业务逻辑）
    DBMS_OUTPUT.PUT_LINE('输出JSON: ' || v_output_json);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('输出JSON: []');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END;
/

可复用存储过程 #

如果需要在业务中重复调用，可封装为存储过程：

CREATE OR REPLACE PROCEDURE GET_EMPLOYEE_SALARY_INFO(
    p_input_json   IN  CLOB,
    p_output_json  OUT CLOB
) AS
BEGIN
    SELECT JSON_ARRAYAGG(
               JSON_OBJECT(
                   'BASE_SALARY' VALUE e.BASE_SALARY,
                   'BONUS' VALUE e.BONUS,
                   'STATUS' VALUE e.STATUS
                   FORMAT JSON NULL ON NULL
               )
           ) INTO p_output_json
    FROM EMPLOYEES e
    JOIN JSON_TABLE(
        p_input_json,
        '$' COLUMNS (
            EMPLOYEE_NUMBER VARCHAR2(50) PATH '$.EMPLOYEE_NUMBER',
            FIRST_NAME VARCHAR2(50) PATH '$.FIRST_NAME',
            LAST_NAME VARCHAR2(50) PATH '$.LAST_NAME',
            TAX_YEAR NUMBER PATH '$.TAX_YEAR'
        )
    ) jt ON e.EMPLOYEE_NUMBER = jt.EMPLOYEE_NUMBER
       AND e.FIRST_NAME = jt.FIRST_NAME
       AND e.LAST_NAME = jt.LAST_NAME
       AND e.TAX_YEAR = jt.TAX_YEAR;

    -- 处理无匹配数据的情况
    IF p_output_json IS NULL THEN
        p_output_json := '[]';
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        p_output_json := '{"error": "' || REPLACE(SQLERRM, '"', '\\"') || '"}';
END;
/

关键逻辑说明 #

• JSON_TABLE解析输入：将输入的JSON对象转换为关系型行数据，提取四个过滤字段，这是多条件过滤的核心基础。
• 多条件关联过滤：通过JOIN子句将EMPLOYEES表与JSON_TABLE解析出的条件行关联，用AND连接四个匹配条件，实现精准过滤。
• 输出JSON构造：使用JSON_OBJECT将指定字段封装为JSON对象，再用JSON_ARRAYAGG将结果集合并为JSON数组（支持多条匹配数据返回）。
• NULL值处理：NULL ON NULL确保当字段值为NULL时，仍会在JSON中保留键名并显示为null，避免键缺失导致的格式问题。

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