在插入或更新记录存储过程中仅使用可更新表字段的子集?

Question

考虑简单的命令：

INSERT INTO TABLE table_name (fldX) VALUES (valueX)
UPDATE table_name SET fldX = valueX WHERE table_id = ? AND version_id = ?

更常见的是，我们只插入或更新一些表字段，但 INSERT/UPDATE 上的 CRUD 存储过程的所有示例都包含所有可更新的表字段，要使用此 SP，我们需要填充所有这些参数。

当以下情况下会出现此类 SP 的问题：- 用户
最初只想设置字段的子集 (1+(，因此他无法使用插入所有字段的 SP
- 用户并不总是知道所有表字段，因此他无法使用更新所有字段的 SP。我不想为可能的字段子集编写相同的 SP。
- 用户不应有权访问更新所有字段的 SP。
- 用户/用户操作并不总是有权更改所有表字段的值。
- 用户只想更新 1 个字段，但需要使用 CALL {table}_update( ... ( 中的所有表字段
在此示例中，用户确实有权访问记录的"主键"和"版本"(时间戳/数字(列。

---

可能的解决方案：

---

• 解决方案 0：继续使用 INSERT/UPDATE 语句

这种方法的优点：
- 它有效

这种方法的缺点：
- 允许用户直接访问表
时的安全问题 -删除记录时无法保存用户的数据

---

• 解决方案 1：将 DML 语句的一部分作为参数发送，SP 将使用它来创建动态 SQL 状态：

CALL table_update(p_id AS INT, p_changes CHAR(1000))
-- Parameters: p_changes = "fld1 = 1, fld2 = '01.01.2019', fld3 = 'abc'"
st1 = 'UPDATE table SET' || p_changes || 'WHERE id = p_id';
EXECUTE st1;

这种方法的缺点：
- 可能的 SQL 注入
- 动态 SQL
上没有可用的缓存和优化 - 无法检查输入字符串等...

---

• 解决方案 2：将未使用的值发送为NULL并添加字符串参数以及带有值的列列表以使用实际参数(即使它为 null(

PROCEDURE table_update(upd_fields VARCHAR(1000), fld1 CHR(30), fld2 CHAR(30))
-- If we want to update only fld1 we should execute
CALL table_update('fld1', value1, NULL)

这种方法的缺点：-
如果 SP 将来会更改参数的顺序，则此系统将中断
- 语句将再次动态准备，因此没有缓存。
- 创建"ins_fields"或"upd_fields"参数的复杂性

---

• 解决方案 3：使用 XML 字符串发送包含所有更改的更新。

PROCEDURE table_update(record_updates XML)
-- or even
PROCEDURE table_edit(table_changes XML) -- all INSERT/UPDATE/DELETE statemens together

这种方法的优点：
- 可用于 1 个 SP 调用
中的插入/更新/删除 - 只有 1 个参数列出所有更新的字段。

此方法的缺点：-
传输更多数据(因为 XML(-
由于需要在服务器上解析 XML 而降低性能
- 增加了存储过程的复杂性。
- 增加客户端代码的复杂性(以创建 XML 字符串(

---

那么我错过了什么解决方案？哪些被认为是主流/最佳/普遍的？

在现代ORM中如何解决这个问题？他们是否使用第一、第二或第三种方法？

就个人而言，我想使用第三个解决方案(带有XML参数(，但我需要示例：
1(此类XML参数的架构示例。
2( 分析 XML 参数的存储过程示例

当前使用的环境：从 Visual FoxPro 应用程序使用 SQL 直通 (ODBC( 执行直接插入/更新/删除语句。DBMS：DB2 for z/OS v10。

Answer 1

简单的答案;DB2 允许参数重载：

PROCEDURE update_widgets(p_id INTEGER, p_color VARCHAR(40) )
PROCEDURE update_widgets(p_id INTEGER, p_quantity INTEGER )
PROCEDURE update_widgets(p_id INTEGER, p_price DECIMAL(9,2) )
PROCEDURE update_widgets(p_id INTEGER, p_quantity INTEGER, p_price DECIMAL(9,2) )
PROCEDURE update_widgets(p_id INTEGER, p_color VARCHAR(40) , p_quantity INTEGER, p_price DECIMAL(9,2) )
. . .

只要你的论点不是模棱两可的，你可以有任意数量的变体

另一种解决方案是将所有可能的可更新行设置为可为空的参数，并使用 null 作为无更新检查：

UPDATE widgets SET price = :p_price WHERE id=:p_id AND :p_price IS NOT NULL;
UPDATE widgets SET color = :p_color WHERE id=:p_id AND :p_color IS NOT NULL;
. . .