功能单元

介绍

关于在数据库中执行计算，有两种截然不同的观点：认为它很棒的人和认为它错误的人。这并不是说函数、存储过程、生成或计算列以及触发器的世界都是阳光明媚的！这些工具远非万无一失，考虑不周的实现可能会导致性能低下、让维护人员感到痛苦等等，这在一定程度上解释了争议的存在。

但是，从定义上讲，数据库非常擅长处理和操作信息，而且大多数数据库将其相同的控制权和能力提供给其用户（SQLite 和 MS Access 在一定程度上除外）。外部数据处理程序需要先从数据库中提取信息（通常通过网络），才能进行任何操作，因此它们处于劣势。而数据库程序可以充分利用原生集合操作、索引、临时表以及半个世纪数据库演进的成果，而任何复杂程度的外部程序往往都涉及一定程度的重复造轮子。那么，为什么不利用数据库来完成工作呢？

以下是你可能不想为数据库编程的原因！

• 数据库功能往往变得不可见——尤其是触发器。这种弱点与与数据库交互的团队或应用程序的大小成正比，因为很少有人记得或意识到数据库中的编程。文档有所帮助，但帮助有限。
• SQL 是一种专门用于操作数据集的语言。它不擅长处理非数据集操作，而且这些操作越复杂，它就越不擅长。
• RDBMS 功能和 SQL 方言有所不同。简单的生成列得到了广泛支持，但将更复杂的数据库逻辑移植到其他存储库至少需要时间和精力。
• 数据库模式升级通常比应用程序升级更加困难。快速变化的逻辑最好在其他地方维护，尽管一旦情况稳定下来，就可以重新考虑。
• 管理数据库程序并不像人们希望的那样简单。许多模式迁移工具对组织功能很少甚至没有，导致代码差异巨大，代码审查工作量繁重（sqitch 的依赖关系图和对单个对象的重构使它成为一个值得注意的例外，而 migra 则试图完全避开这个问题）。在测试方面，像 pgTAP 和 utPLSQL 这样的框架改进了黑盒集成测试，但也代表了额外的支持和维护承诺。
• 对于已建立的外部代码库，任何结构性更改往往既费力又危险。

另一方面，对于适合 SQL 的任务，SQL 提供了速度、简洁性、持久性和将自动化工作流程“规范化”的机会。数据建模不仅仅是像将昆虫钉在硬纸板上一样固定实体，数据流动和数据静止之间的区别是一个棘手的问题。静止实际上是更细颗粒度的慢速运动；信息总是在从这里流向那里，而数据库可编程性是管理和引导这些流动的强大工具。

一些数据库引擎通过同时容纳其他编程语言来平衡 SQL 和其他编程语言之间的差异。SQL Server 支持用 .NET Framework 的任何语言 编写的函数；Oracle 有 Java 存储过程；PostgreSQL 允许 使用 C 进行扩展，并且可以用 Python、Perl 和 Tcl 进行用户编程，插件 添加了 shell 脚本、R、JavaScript 等。总结一下常见的几种数据库：对于 MySQL 和 MariaDB，只能使用 SQL 或不使用 SQL；MS Access 只能用 VBA 进行编程；SQLite 根本不能由用户编程。

如果 SQL 不足以完成某些任务，或者您想重用其他代码，可以使用非 SQL 语言，但这并不能解决使数据库编程成为一把双刃剑的其他问题。实际上，使用这些语言会进一步使部署和互操作性变得复杂。编者须知：让编写者注意！

函数与过程

默认值和生成

即使是简单的计算，如果执行次数足够多，或者存在多个相互竞争的实现，也会造成麻烦。 对单行中值的运算（例如在公制和英制之间转换、将费率乘以工作时数以计算发票小计、计算地理多边形的面积）可以在表定义中声明，以解决其中一个或两个问题。

CREATE TABLE pythag (
  a INT NOT NULL,
  b INT NOT NULL,
  c DOUBLE PRECISION NOT NULL
    GENERATED ALWAYS AS (sqrt(pow(a, 2) + pow(b, 2)))
    STORED
);

大多数 RDBMS 提供了“存储”和“虚拟”生成列之间的选择。 在前一种情况下，值在插入或更新行时计算并存储。 这是 PostgreSQL（截至 12 版）和 MS Access 的唯一选项。 虚拟生成列在查询时计算，就像在视图中一样，因此它们不占用空间，但会更频繁地重新计算。 这两种类型都受到严格限制：值不能依赖于它们所属行之外的信息，不能更新，并且各个 RDBMS 可能还有更具体的限制。 例如，PostgreSQL 禁止在生成列上对表进行分区。

生成列是一种专门的工具。 更常见的是，只需要一个默认值，以防在插入时没有提供值。 像 now() 这样的函数经常作为列默认值出现，但大多数数据库允许自定义函数和内置函数（除了 MySQL，其中只有 current_timestamp 可以作为默认值）。

让我们以批次号这个比较枯燥但简单的例子为例，它的格式为 YYYYXXX，其中前四位数字代表当前年份，后三位数字代表递增计数器：今年生产的第一个批次是 2020001，第二个是 2020002，依此类推。 没有默认类型或内置函数可以生成这样的值，但用户定义的函数可以对每个批次进行编号 在创建时

CREATE SEQUENCE lot_counter;

CREATE OR REPLACE FUNCTION next_lot_number () RETURNS TEXT AS $$
BEGIN
  RETURN date_part('year', now())::TEXT ||
    lpad(nextval('lot_counter'::REGCLASS)::TEXT, 2, '0');
END;
$$
LANGUAGE plpgsql;

CREATE TABLE lots (
  lot_number TEXT NOT NULL DEFAULT next_lot_number () PRIMARY KEY,
  current_quantity INT NOT NULL DEFAULT 0,
  target_quantity INT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  completed_at TIMESTAMPTZ,
  CHECK (target_quantity > 0)
);

在函数中引用数据

上面的序列方法有一个重要的弱点（和 至少一个不那么重要的 ）：到了新年，lot_counter 仍然会保持 12 月 31 日的值。 但是，有多种方法可以跟踪一年中创建的批次数量，通过查询 lots 本身，next_lot_number 函数可以保证在年份结束时得到正确的值。

CREATE OR REPLACE FUNCTION next_lot_number () RETURNS TEXT AS $$
BEGIN
  RETURN (
    SELECT date_part('year', now())::TEXT || lpad((count(*) + 1)::TEXT, 2, '0')
    FROM lots
    WHERE date_part('year', created_at) = date_part('year', now())
  );
END;
$$
LANGUAGE plpgsql;

ALTER TABLE lots
  ALTER COLUMN lot_number SET DEFAULT next_lot_number();

工作流

即使是单语句函数也比外部代码具有一个关键优势：执行永远不会离开数据库 ACID 保证的安全范围。 将上面的 next_lot_number 与客户端应用程序或甚至是手动流程的可能性进行比较，它们执行 一个 SQL 语句 来计算今年至今的批次数量，然后执行第二个语句来插入新的批次。 如果订单下达的速度足够快，那么在计数和插入之间，批次数量可能会发生变化。

多语句存储程序打开了巨大的可能性空间，因为 SQL 包含编写过程代码所需的所有工具，从异常处理到保存点（它甚至 使用窗口函数和公用表表达式是图灵完备的 ！）。 可以在数据库中执行整个数据处理工作流，最大限度地减少对系统其他区域的暴露，并消除数据库与其他域之间耗时的往返行程。

总的来说，软件架构的很大一部分是关于管理和隔离复杂性，防止它溢出子系统之间的边界。 如果一些或多或少复杂的流程涉及将数据拉入应用程序后端、脚本或 cron 作业，对其进行消化和添加，并将结果存储起来 - 那么现在是该问问究竟是什么需要走出数据库了。

如上所述，这是 RDBMS 版本和 SQL 方言之间差异显现的领域。 为一个数据库开发的函数或过程可能无法在另一个数据库上运行而无需更改，无论是将 SQL Server 的 TOP 替换为标准的 LIMIT 子句，还是在企业 Oracle 到 PostgreSQL 的迁移中完全重新构建临时状态的存储方式。 将您的工作流规范化为 SQL 也会比您做出的几乎任何其他选择都更彻底地将您绑定到当前平台和方言。

查询中的计算

到目前为止，我们已经研究了如何使用函数来存储和修改数据，无论是绑定到表定义还是管理多表工作流。 从某种意义上说，这是它们可以被赋予的最强大的用途，但函数在数据检索方面也占有一席之地。 您可能已经在查询中使用的许多工具都是作为函数实现的，从标准的内置函数（如 count）到扩展，如 Postgres 的 jsonb_build_object、PostGIS 的 ST_SnapToGrid 等。 当然，由于这些函数与数据库本身更加紧密地集成，因此它们大多是用 SQL 以外的语言编写的（例如，在 PostgreSQL 和 PostGIS 的情况下为 C）。

如果您经常发现自己（或者认为自己可能会发现自己）需要检索数据，然后在数据真正准备好之前对每个记录执行一些操作，请考虑在数据从数据库中取出时对其进行转换！ 从日期中推算出一些工作日 ？ 生成两个 JSONB 字段之间的差异 ？ 实际上，任何仅依赖于您正在查询的信息的计算都可以用 SQL 完成。 并且在数据库中完成的任何操作（只要它被一致地访问）对于建立在数据库之上的任何东西来说都是规范的。

必须说：如果您使用的是应用程序后端，它的数据访问工具包可能会限制您可以从使用函数来增强查询结果中获得多少价值。 大多数此类库都可以执行任意的 SQL，但那些基于模型类生成常见 SQL 语句的库可能会允许或可能不允许自定义查询 SELECT 列表。 生成列或视图可以解决这个问题。

触发器及其后果

函数和过程在数据库设计人员和用户之间已经足够有争议了，但当涉及到触发器时，情况就变得 真的 火热起来了。 触发器定义一个自动操作，通常是一个过程（SQLite 只允许单个语句），在另一个操作之前、之后或代替另一个操作执行。

启动操作通常是对表的插入、更新或删除，并且通常可以将触发器过程设置为对每个记录执行，也可以对整个语句执行。 SQL Server 还允许对可更新视图进行触发，这主要是一种强制执行更详细的安全措施的方式； 并且它、PostgreSQL 和 Oracle 都提供了一些形式的事件或 DDL 触发器，这些触发器可以对数据库结构的变化做出反应。

触发器的一个常见的低风险用途是作为一种额外的强大约束，防止无效数据被存储。 在所有主要的关联数据库中，只有主键和外键以及 UNIQUE 约束可以评估候选记录之外的信息。 无法在表定义中声明例如，在一个月中只能创建两个批次——最简单的数据库和代码解决方案容易受到与上面的 lot_number 的计数然后设置方法类似的竞争条件的影响。 要强制执行任何其他涉及整个表或其他表的约束，您需要一个 触发器 来查看记录范围之外的内容。

CREATE FUNCTION enforce_monthly_lot_limit () RETURNS TRIGGER
AS $$
DECLARE current_count BIGINT;
BEGIN
  SELECT count(*) INTO current_count
  FROM lots
  WHERE date_trunc('month', created_at) = date_trunc('month', NEW.created_at);

  IF current_count >= 2 THEN
    RAISE EXCEPTION 'Two lots already created this month';
  END IF;

  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER monthly_lot_limit
BEFORE INSERT ON lots
FOR EACH ROW
EXECUTE PROCEDURE enforce_monthly_lot_limit();

一旦您开始执行 DML 并使用流程控制，那么离启动更复杂的一系列步骤只有一步之遥，并且范围会扩展到包括其他表。 也许将记录插入到 lots 本身可能是由插入到 orders 中的触发器启动的最后操作，并且没有人为用户或应用程序后端有权直接写入 lots。 或者，当向批次添加 items 时，那里的触发器可能会处理更新 current_quantity，并在达到 target_quantity 时启动其他过程。

触发器和函数可以在其定义者的访问级别运行（在 PostgreSQL 中，函数的 LANGUAGE 旁边有一个 SECURITY DEFINER 声明），这赋予了原本权限有限的用户启动更广泛过程的能力——也让验证和测试这些过程变得更加重要。

触发器-动作-触发器-动作调用栈可以变得任意长，虽然在某些平台上，以修改相同表或记录多次的形式进行真正的递归是非法的，并且在几乎所有情况下，这种方法都是一个糟糕的主意。触发器嵌套会迅速超出我们理解其范围和影响的能力。大量使用嵌套触发器的数据库开始从复杂领域向复杂领域转变，变得难以或无法分析、调试和预测。

实用编程能力

数据库中的计算不仅更快，表达更简洁：它们消除了歧义并设定了标准。上面的示例使数据库用户免于自己计算批次号，或免于担心意外创建超过处理能力的批次。特别是应用程序开发人员经常被训练成将数据库视为“哑存储”，只提供结构和持久性，因此他们可能会发现自己——或者更糟糕的是，没有意识到——笨拙地表达数据库之外的东西，而这些东西在 SQL 中可以更有效地完成。

可编程性是关系数据库中一个被不公平地忽视的功能。确实存在一些理由来避免使用它，以及更多理由来限制其使用，但函数、过程和触发器都是限制数据模型对嵌入它的系统带来的复杂性的强大工具。

常见问题解答

CREATE PROCEDURE procedure_name
AS
common_sql_statement;

EXEC procedute_name