Oracle存储过程中基于动态列JSON生成INSERT语句的实现

以下是基于APEX_JSON解析输入JSON、动态生成INSERT语句的Oracle存储过程实现：

CREATE OR REPLACE PROCEDURE gen_insert_from_json(p_json CLOB)
IS
    l_table_name   VARCHAR2(128);
    l_col_names    VARCHAR2(4000);
    l_col_values   VARCHAR2(4000);
    l_count        PLS_INTEGER;
    l_insert_sql   VARCHAR2(4000);
BEGIN
    -- 解析输入JSON
    APEX_JSON.parse(p_json);
    
    -- 获取目标表名
    l_table_name := APEX_JSON.get_varchar2(p_path => 'tableName');
    
    -- 获取列名数组的长度
    l_count := APEX_JSON.get_count(p_path => 'columName');
    
    -- 循环拼接列名和对应值
    FOR i IN 1..l_count LOOP
        -- 拼接列名（添加双引号处理含特殊字符的列名）
        IF i > 1 THEN
            l_col_names := l_col_names || ', ';
            l_col_values := l_col_values || ', ';
        END IF;
        l_col_names := l_col_names || '"' || APEX_JSON.get_varchar2(p_path => 'columName[' || i || ']') || '"';
        -- 拼接列值（字符串类型添加双引号，若需支持其他类型需额外判断处理）
        l_col_values := l_col_values || '"' || APEX_JSON.get_varchar2(p_path => 'columnValue[' || i || ']') || '"';
    END LOOP;
    
    -- 构建完整INSERT语句
    l_insert_sql := 'INSERT INTO ' || l_table_name || '(' || l_col_names || ') VALUES(' || l_col_values || ')';
    
    -- 可选：打印生成的SQL语句用于调试
    DBMS_OUTPUT.put_line(l_insert_sql);
    
    -- 执行INSERT语句
    EXECUTE IMMEDIATE l_insert_sql;
    
    -- 提交事务（根据业务需求决定是否添加）
    -- COMMIT;
EXCEPTION
    WHEN APEX_JSON.INVALID_PATH THEN
        RAISE_APPLICATION_ERROR(-20001, 'JSON格式错误：缺失必要字段或数组索引无效');
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20002, '执行失败：' || SQLERRM);
END;
/

关键逻辑说明 #

• JSON解析：通过APEX_JSON.parse加载输入的CLOB格式JSON，后续通过get_varchar2和get_count获取对应节点的值和数组长度。
• 动态拼接：循环遍历列名和值数组，逐个拼接成带双引号的字符串，确保列名和字符串值符合Oracle语法要求。
• SQL执行：使用EXECUTE IMMEDIATE执行动态生成的INSERT语句，同时添加异常处理捕获JSON解析错误和SQL执行错误。

注意事项 #

1. SQL注入风险：上述直接拼接字符串的方式存在SQL注入风险，若输入不可信，建议改用绑定变量的方式优化：

   -- 优化后的绑定变量方式示例
   l_insert_sql := 'INSERT INTO ' || l_table_name || '(' || l_col_names || ') VALUES(' || APEX_STRING.join(l_col_values, ', :', 1) || ')';
   -- 循环绑定变量值
   FOR i IN 1..l_count LOOP
       EXECUTE IMMEDIATE l_insert_sql USING APEX_JSON.get_varchar2(p_path => 'columnValue[' || i || ']');
   END LOOP;
   

2. 数据类型兼容：示例默认按字符串类型处理列值，若需支持数字、日期等类型，需根据列的实际类型判断后调整值的拼接方式（例如数字无需加双引号，日期需用TO_DATE转换）。
3. 长度限制：VARCHAR2(4000)适用于列数较少的场景，若列数较多（如27列全量传入），建议改用CLOB存储拼接后的SQL语句。

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