Oracle 19c中生成含动态列列表的JSON文档方法问询

1. 创建测试表并插入数据 #

CREATE TABLE tbl1 (
    id NUMBER,
    val1 VARCHAR2(50),
    val2 NUMBER,
    val3 DATE
);

INSERT INTO tbl1 VALUES (1, 'A', 100, SYSDATE);
INSERT INTO tbl1 VALUES (2, 'B', 200, SYSDATE-1);
COMMIT;

2. 静态SQL实现（列固定） #

静态查询可生成符合要求的JSON，但列名val1、val2、val3为硬编码，无法灵活调整：

SELECT JSON_OBJECT(
    'id' VALUE id,
    'val1' VALUE val1,
    'val2' VALUE val2,
    'val3' VALUE val3 FORMAT JSON
) AS json_doc
FROM tbl1;

静态查询输出示例： #

{"id":1,"val1":"A","val2":100,"val3":"2024-05-20T10:30:00"}
{"id":2,"val1":"B","val2":200,"val3":"2024-05-19T10:30:00"}

3. 常见动态查询错误示例 #

若直接拼接列名字符串，会导致JSON键被额外引号包裹、值未正确关联列的问题：

-- 错误示例：键被当作字符串值，未关联列数据
DECLARE
    cols VARCHAR2(100) := '''val1'',''val2'',''val3''';
    sql_stmt VARCHAR2(1000);
BEGIN
    sql_stmt := 'SELECT JSON_OBJECT(' || cols || ') FROM tbl1';
    EXECUTE IMMEDIATE sql_stmt;
END;
/

执行后会生成类似{"val1":"val1"}的错误结果，无法正确映射列值。

4. 完全动态解决方案 #

方案1：指定动态列列表生成JSON #

通过数据字典拼接JSON_OBJECT的键值对，支持自定义列列表，适配任意表：

CREATE OR REPLACE PROCEDURE generate_dynamic_json(
    p_table_name VARCHAR2,
    p_cols VARCHAR2, -- 参数格式：'id,val1,val3'
    p_output OUT SYS_REFCURSOR
) AS
    sql_stmt VARCHAR2(2000);
    col_pairs VARCHAR2(1000);
BEGIN
    -- 将输入列列表转换为JSON_OBJECT要求的键值对格式
    SELECT LISTAGG('''' || column_name || ''' VALUE ' || column_name, ', ') WITHIN GROUP (ORDER BY column_id)
    INTO col_pairs
    FROM all_tab_columns
    WHERE table_name = UPPER(p_table_name)
      AND column_name IN (SELECT TRIM(column_value) FROM XMLTABLE(('"' || REPLACE(p_cols, ',', '","') || '"')));

    sql_stmt := 'SELECT JSON_OBJECT(' || col_pairs || ') AS json_doc FROM ' || p_table_name;
    OPEN p_output FOR sql_stmt;
END;
/

使用示例：

DECLARE
    v_cursor SYS_REFCURSOR;
    v_json CLOB;
BEGIN
    generate_dynamic_json('tbl1', 'id,val1,val3', v_cursor);
    LOOP
        FETCH v_cursor INTO v_json;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_json);
    END LOOP;
    CLOSE v_cursor;
END;
/

输出示例：

{"id":1,"val1":"A","val3":"2024-05-20T10:30:00"}
{"id":2,"val1":"B","val3":"2024-05-19T10:30:00"}

方案2：全列动态生成JSON（适配任意表） #

若需生成表中所有列的JSON，无需指定列列表：

CREATE OR REPLACE PROCEDURE generate_full_table_json(
    p_table_name VARCHAR2,
    p_output OUT SYS_REFCURSOR
) AS
    sql_stmt VARCHAR2(2000);
    col_pairs VARCHAR2(1000);
BEGIN
    SELECT LISTAGG('''' || column_name || ''' VALUE ' || column_name, ', ') WITHIN GROUP (ORDER BY column_id)
    INTO col_pairs
    FROM all_tab_columns
    WHERE table_name = UPPER(p_table_name);

    sql_stmt := 'SELECT JSON_OBJECT(' || col_pairs || ') AS json_doc FROM ' || p_table_name;
    OPEN p_output FOR sql_stmt;
END;
/

使用示例：

DECLARE
    v_cursor SYS_REFCURSOR;
    v_json CLOB;
BEGIN
    generate_full_table_json('tbl1', v_cursor);
    LOOP
        FETCH v_cursor INTO v_json;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_json);
    END LOOP;
    CLOSE v_cursor;
END;
/

关键说明 #

• 利用LISTAGG和all_tab_columns数据字典动态拼接JSON键值对，避免硬编码，适配任意表结构。
• 统一转换表名、列名为大写，匹配数据字典存储格式，避免大小写问题。
• 通过游标输出结果，支持批量获取JSON文档。

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