Oracle 10g环境下如何设计支持部分字段更新的API函数

核心思路是利用PL/SQL存储过程的参数默认值特性，无需更新的字段可不传参，自动保留原值，完美适配仅更新少数字段的业务需求。

方案1：默认NULL参数实现动态字段更新（最推荐） #

和你现有查询封装的风格完全兼容，所有非主键字段设置默认值为NULL，更新逻辑中未传参的字段直接保留原值即可。

包定义示例 #

PACKAGE mypackage
IS
  -- 原有查询函数保留
  FUNCTION fnc_name (id_primary_key IN mytable.primary_key%TYPE) 
    RETURN mytable.name%TYPE;

  -- 新增更新存储过程，非主键字段均设默认值NULL
  PROCEDURE proc_update_mytable(
    id_primary_key IN mytable.primary_key%TYPE,
    p_name IN mytable.name%TYPE DEFAULT NULL,
    p_age IN mytable.age%TYPE DEFAULT NULL,
    p_email IN mytable.email%TYPE DEFAULT NULL
    -- 其余表字段依次添加，均设置DEFAULT NULL
  );
END mypackage;

包体实现示例 #

PACKAGE BODY mypackage
IS
  -- 原有fnc_name的实现逻辑保留

  PROCEDURE proc_update_mytable(
    id_primary_key IN mytable.primary_key%TYPE,
    p_name IN mytable.name%TYPE DEFAULT NULL,
    p_age IN mytable.age%TYPE DEFAULT NULL,
    p_email IN mytable.email%TYPE DEFAULT NULL
  )
  IS
  BEGIN
    UPDATE mytable
    SET 
      name = NVL(p_name, name),
      age = NVL(p_age, age),
      email = NVL(p_email, email)
      -- 其余字段同理，用NVL判断是否要保留原值
    WHERE primary_key = id_primary_key;
    
    -- 可选校验：主键不存在时抛出业务异常
    IF SQL%ROWCOUNT = 0 THEN
      RAISE_APPLICATION_ERROR(-20001, '指定主键的记录不存在');
    END IF;
    
    -- 可根据需求决定是否在此处提交事务，也可交由调用方控制
    COMMIT;
  END proc_update_mytable;
END mypackage;

调用示例 #

仅更新email字段时，只传主键和对应参数即可：

BEGIN 
  mypackage.proc_update_mytable(
    id_primary_key => 1001,
    p_email => 'new_email@test.com'
  );
END;

同时更新name和age字段：

BEGIN 
  mypackage.proc_update_mytable(
    id_primary_key => 1001,
    p_name => '张三',
    p_age => 28
  );
END;

特殊场景适配 #

如果业务需要主动将某个字段更新为NULL，可给对应字段增加布尔类型的更新标识参数，示例：

-- 包定义中调整参数
PROCEDURE proc_update_mytable(
  id_primary_key IN mytable.primary_key%TYPE,
  p_name IN mytable.name%TYPE DEFAULT NULL,
  p_name_set_null IN BOOLEAN DEFAULT FALSE,
  -- 其余参数不变
);

-- 更新逻辑调整为
name = CASE WHEN p_name_set_null THEN NULL ELSE NVL(p_name, name) END

需要将name设为NULL时，调用时传p_name_set_null => TRUE即可。

方案2：记录类型参数封装（适合多字段更新场景） #

如果经常需要更新半数以上字段，可使用表的%ROWTYPE作为入参，调用时仅给需要更新的字段赋值即可：

包定义示例 #

PACKAGE mypackage
IS
  PROCEDURE proc_update_mytable_by_row(
    p_row IN mytable%ROWTYPE
  );
END mypackage;

调用示例 #

DECLARE
  l_row mytable%ROWTYPE;
BEGIN
  l_row.primary_key := 1001;
  l_row.email := 'new_email@test.com';
  mypackage.proc_update_mytable_by_row(l_row);
END;

该方案缺点是每次调用需要先声明记录变量，灵活度低于第一种方案。

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