You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

PL/SQL遍历动态JSON执行DML：多行编辑仅更新一行问题

avatar
AI 小北
2026-8-1
解决PL/SQL函数无法处理多行JSON数据DML操作的问题

问题背景

为支持可编辑表格的应用编写PL/SQL函数，处理包含行数据增删改操作的JSON请求负载时，发现仅第一行数据被处理，后续行无任何操作，无报错但未达预期。请求JSON中，修改行含"Changed":1标识，删除行含"Deleted":1标识。

示例JSON请求体 

{"Changes": [
    {"id": "AR46","Changed": 1,"SESSION_ID": "963","NAME": "IMAGE_LOGO","VALUE": "","DESCRIPTION": "v1","TYPE": "IMAGE","PARAM_GROUP": "J","BLOB_VALUE": "oracle.sql.BLOB@2ba32","EDIT": "Edit","DOWNLOAD": "Download","CLOB_VALUE": "oracle.sql.CLOB@7fd86843","XML_VALUE": "","CREATE_DATE": "11.03.2022 13:04:26","_DefaultSort": ""},
    {"id": "AR47","Changed": 1,"SESSION_ID": "963","NAME": "IMAGE_HPB_MEMO_FOOTER","VALUE": "","DESCRIPTION": "v2","TYPE": "IMAGE","PARAM_GROUP": "JASPER","BLOB_VALUE": "oracle.sql.BLOB@7621f9df","EDIT": "Edit","DOWNLOAD": "Download","CLOB_VALUE": "oracle.sql.CLOB@43e24152","XML_VALUE": "","CREATE_DATE": "11.03.2022 13:04:35","_DefaultSort": ""},
    {"id": "AR48","Changed": 1,"SESSION_ID": "963","NAME": "IMAGE_HPB_MEMO_INVCRED","VALUE": "","DESCRIPTION": "v3","TYPE": "IMAGE","PARAM_GROUP": "JASPER","BLOB_VALUE": "oracle.sql.BLOB@762074f6","EDIT": "Edit","DOWNLOAD": "Download","CLOB_VALUE": "oracle.sql.CLOB@4a068001","XML_VALUE": "","CREATE_DATE": "11.03.2022 13:04:46","_DefaultSort": ""}
]}

原PL/SQL函数（仅支持单行操作） 

create or replace function changesResources (p_data varchar2)
return varchar2
IS
    l_nullEx    exception;
    PRAGMA EXCEPTION_INIT(l_nullEx, -1400);
    p_rez     varchar2(100);
    p_session_id number;
    p_name varchar2(100);
    p_value VARCHAR2(500);  
    p_description VARCHAR2(1000); 
    p_type VARCHAR2(100);  
    p_param_group VARCHAR2(100);  
    p_blob_value VARCHAR2(1000);           
    p_clob_value VARCHAR2(1000);           
    p_xml_value VARCHAR2(1000);    
    p_create_date varchar2(50);
    
    l_json_obj JSON_OBJECT_T;
    l_json_arr JSON_ARRAY_T;
Begin

   l_json_obj := JSON_OBJECT_T.PARSE(p_data);

   l_json_arr := l_json_obj.get_array('Changes');
   
FOR i IN 0..l_json_arr.get_size()-1 LOOP
    
     p_session_id := JSON_VALUE(l_json_arr.get(i).to_string(), '$.SESSION_ID');
   p_name := JSON_VALUE(l_json_arr.get(i).to_string(), '$.NAME');
   p_value := JSON_VALUE(l_json_arr.get(i).to_string(), '$.VALUE');
   p_description := JSON_VALUE(l_json_arr.get(i).to_string(), '$.DESCRIPTION');
   p_type := JSON_VALUE(l_json_arr.get(i).to_string(), '$.TYPE');
   p_param_group := JSON_VALUE(l_json_arr.get(i).to_string(), '$.PARAM_GROUP');
   p_blob_value := JSON_VALUE(l_json_arr.get(i).to_string(), '$.BLOB_VALUE');
   p_clob_value := JSON_VALUE(l_json_arr.get(i).to_string(), '$.CLOB_VALUE');
   p_xml_value := JSON_VALUE(l_json_arr.get(i).to_string(), '$.XML_VALUE');
   p_create_date := JSON_VALUE(l_json_arr.get(i).to_string(), '$.CREATE_DATE');

    

IF JSON_VALUE(l_json_arr.get(i).to_string(), '$.Changed') = 1
THEN

    UPDATE BF_RESOURCES_CONF
        SET description = p_description,
        value=p_value,
        type = p_type,
        param_group = p_param_group,
        blob_value = utl_raw.cast_to_raw(p_blob_value),
        clob_value = TO_CLOB(p_clob_value),
        xml_value=p_xml_value,
        create_date = TO_DATE(p_create_date,'DD.MM.YYYY HH24:MI:SS')
        where session_id = p_session_id
        and name = p_name;
    p_rez := '1|success!';
    return p_rez;
ELSIF JSON_VALUE(l_json_arr.get(i).to_string(), '$.Deleted') = 1
THEN
DELETE FROM BF_RESOURCES_CONF
WHERE session_id = p_session_id
        and name = p_name;
        p_rez := '1|success!';
    return p_rez;
ELSE
INSERT INTO BF_RESOURCES_CONF (session_id,name, value,description, type,param_group,blob_value,clob_value,xml_value,create_date) VALUES (p_session_id, p_name, p_value, p_description, p_type, p_param_group, utl_raw.cast_to_raw(p_blob_value),TO_CLOB(p_clob_value),p_xml_value,TO_DATE(p_create_date,'DD.MM.YYYY HH24:MI:SS'));
        p_rez := '1|success!';
    return p_rez;
END IF;
END LOOP;
   EXCEPTION
    WHEN l_nullEx THEN
           p_rez := '-1|Columns SESSION_ID, NAME I CREATE_DATE have to contain values!';
           RETURN p_rez;
        --WHEN OTHERS THEN
           -- p_rez := '-1|Error!';
           -- RETURN p_rez;
END changesResources ;

问题根源

原函数在循环处理每一行数据后立即执行return p_rez，导致循环仅执行一次就退出，后续行完全未被处理。

解决方案

1. 核心修改：移除循环内的return语句

将返回结果的逻辑移到循环结束后，确保所有行都被处理。

2. 优化JSON解析性能

避免每次将JSON元素转为字符串再用JSON_VALUE解析，直接使用JSON_OBJECT_T的方法获取字段值，提升效率。

3. 增加原子性与异常处理

加入保存点，任何行处理失败时回滚所有操作，保证数据一致性；同时返回详细的操作统计与错误信息。

以下是优化后的函数：

create or replace function changesResources (p_data varchar2)
return varchar2
IS
    l_nullEx    exception;
    PRAGMA EXCEPTION_INIT(l_nullEx, -1400);
    p_rez       varchar2(200);
    l_success_count number := 0;
    l_total_count   number := 0;
    
    l_json_obj JSON_OBJECT_T;
    l_json_arr JSON_ARRAY_T;
    l_row_obj  JSON_OBJECT_T;
    
    -- 字段变量
    p_session_id number;
    p_name varchar2(100);
    p_value VARCHAR2(500);  
    p_description VARCHAR2(1000); 
    p_type VARCHAR2(100);  
    p_param_group VARCHAR2(100);  
    p_blob_value VARCHAR2(1000);           
    p_clob_value VARCHAR2(1000);           
    p_xml_value VARCHAR2(1000);    
    p_create_date varchar2(50);
Begin
    l_json_obj := JSON_OBJECT_T.PARSE(p_data);
    l_json_arr := l_json_obj.get_array('Changes');
    l_total_count := l_json_arr.get_size();

    -- 开启保存点，保证操作原子性
    SAVEPOINT sp_before_changes;
    
    FOR i IN 0..l_json_arr.get_size()-1 LOOP
        l_row_obj := TREAT(l_json_arr.get(i) AS JSON_OBJECT_T);
        
        -- 解析当前行字段
        p_session_id := l_row_obj.get_number('SESSION_ID');
        p_name := l_row_obj.get_string('NAME');
        p_value := l_row_obj.get_string('VALUE');
        p_description := l_row_obj.get_string('DESCRIPTION');
        p_type := l_row_obj.get_string('TYPE');
        p_param_group := l_row_obj.get_string('PARAM_GROUP');
        p_blob_value := l_row_obj.get_string('BLOB_VALUE');
        p_clob_value := l_row_obj.get_string('CLOB_VALUE');
        p_xml_value := l_row_obj.get_string('XML_VALUE');
        p_create_date := l_row_obj.get_string('CREATE_DATE');

        IF l_row_obj.get_number('Changed') = 1 THEN
            UPDATE BF_RESOURCES_CONF
            SET description = p_description,
                value = p_value,
                type = p_type,
                param_group = p_param_group,
                blob_value = utl_raw.cast_to_raw(p_blob_value),
                clob_value = TO_CLOB(p_clob_value),
                xml_value = p_xml_value,
                create_date = TO_DATE(p_create_date,'DD.MM.YYYY HH24:MI:SS')
            WHERE session_id = p_session_id
              AND name = p_name;
            l_success_count := l_success_count + SQL%ROWCOUNT;
        ELSIF l_row_obj.get_number('Deleted') = 1 THEN
            DELETE FROM BF_RESOURCES_CONF
            WHERE session_id = p_session_id
              AND name = p_name;
            l_success_count := l_success_count + SQL%ROWCOUNT;
        ELSE
            INSERT INTO BF_RESOURCES_CONF 
                (session_id,name, value,description, type,param_group,blob_value,clob_value,xml_value,create_date) 
            VALUES 
                (p_session_id, p_name, p_value, p_description, p_type, p_param_group, 
                 utl_raw.cast_to_raw(p_blob_value),TO_CLOB(p_clob_value),p_xml_value,
                 TO_DATE(p_create_date,'DD.MM.YYYY HH24:MI:SS'));
            l_success_count := l_success_count + SQL%ROWCOUNT;
        END IF;
    END LOOP;
    
    -- 构建返回结果
    p_rez := '1|Success! Processed ' || l_success_count || '/' || l_total_count || ' rows.';
    RETURN p_rez;

EXCEPTION
    WHEN l_nullEx THEN
        ROLLBACK TO SAVEPOINT sp_before_changes;
        p_rez := '-1|Error: Columns SESSION_ID, NAME or CREATE_DATE cannot be null!';
        RETURN p_rez;
    WHEN OTHERS THEN
        ROLLBACK TO SAVEPOINT sp_before_changes;
        p_rez := '-1|Error: ' || SQLERRM;
        RETURN p_rez;
END changesResources ;

关键优化点说明

  • 移除循环内的return语句，确保所有行被遍历处理。
  • 使用TREAT将数组元素转为JSON_OBJECT_T，通过专属方法解析字段，避免字符串转换的性能损耗。
  • 增加操作统计，返回成功行数与总行数的对比信息。
  • 加入保存点，异常发生时回滚所有操作，保证数据一致性。
  • 捕获所有异常并返回具体错误信息，便于问题排查。

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.08.01 00:31:03