如何在PL/SQL端点处理REST API POST发送的JSON批量更新员工薪资

需求说明 #

已实现单条员工薪资更新的Flutter调用与PL/SQL端点，但批量更新场景下，通过JSON传输员工ID与新薪资的方式更高效。现需编写PL/SQL代码，解析指定结构的JSON并完成批量薪资更新。

现有批量更新的Flutter调用代码 #

try {
  http.Response response = await http.post(
    Uri.parse(url),
    headers: <String, String>{
      'Content-Type': 'application/json; charset=UTF-8',
    },
    body: jsonEncode(requestData),
  );
}

示例JSON请求结构 #

{
  "employees": [
    {
      "id": 1,
      "new_salary": 50000
    },
    {
      "id": 2,
      "new_salary": 55000
    },
    {
      "id": 3,
      "new_salary": 60000
    },
    {
      "id": 4,
      "new_salary": 52000
    },
    {
      "id": 5,
      "new_salary": 58000
    }
  ]
}

PL/SQL实现方案 #

以下是解析JSON并批量更新的PL/SQL代码，采用JSON_TABLE解析JSON数组，结合MERGE语句高效完成批量更新：

存储过程实现（可用于REST端点） #

CREATE OR REPLACE PROCEDURE batch_update_employee_salary(p_json CLOB)
IS
BEGIN
  -- 解析JSON数组为关系数据集，批量更新员工薪资
  MERGE INTO employee e
  USING (
    SELECT 
      jt.id AS emp_id,
      jt.new_salary
    FROM JSON_TABLE(
      p_json,
      '$.employees[*]' COLUMNS (
        id NUMBER PATH '$.id',
        new_salary NUMBER PATH '$.new_salary'
      )
    ) jt
  ) upd
  ON (e.emp_id = upd.emp_id)
  WHEN MATCHED THEN
    UPDATE SET e.salary = upd.new_salary;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    -- 抛出自定义异常，便于REST端点捕获并返回错误信息
    RAISE_APPLICATION_ERROR(-20001, '批量更新失败: ' || SQLERRM);
END;
/

代码说明 #

• JSON_TABLE：将JSON数组转换为关系型表结构，提取每个员工的id和new_salary字段
• MERGE语句：匹配employee表中的emp_id与解析出的员工ID，匹配成功时更新薪资，相比循环单条更新效率更高
• 异常处理：更新失败时回滚事务，并抛出包含错误信息的自定义异常，方便前端处理错误

测试用匿名块 #

如果需要直接测试，可以使用以下匿名块：

DECLARE
  v_test_json CLOB := '{
  "employees": [
    {
      "id": 1,
      "new_salary": 50000
    },
    {
      "id": 2,
      "new_salary": 55000
    }
  ]
}';
BEGIN
  batch_update_employee_salary(v_test_json);
  DBMS_OUTPUT.PUT_LINE('批量更新完成');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END;
/

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