功能单元

简介

关于在数据库中执行计算，有两种观点：一种认为这很棒，另一种则认为这种观点是错误的。这并不是说函数、存储过程、生成列或计算列以及触发器的世界都是阳光明媚、万事如意的！这些工具远非万无一失，考虑不周的实现可能会导致性能不佳，给维护人员带来痛苦，甚至更多，这在一定程度上解释了争议的存在。

但数据库，顾名思义，非常擅长处理和操作信息，而且大多数数据库都向用户提供了同样的控制和能力（SQLite 和 MS Access 的程度较低）。外部数据处理程序在起步阶段就处于劣势，必须先从数据库中（通常通过网络）提取信息，然后才能进行任何操作。而且，数据库程序可以充分利用原生集合操作、索引、临时表以及半个世纪以来数据库演进的其他成果，而任何复杂程度的外部程序都倾向于涉及某种程度的“重新发明轮子”。那么，为什么不让数据库来完成这项工作呢？

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

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

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

有些数据库引擎通过同时兼容 SQL 和其他编程语言来弥合两者之间的差异。SQL Server 支持用任何 .NET Framework 语言编写的函数；Oracle 有Java 存储过程；PostgreSQL 允许使用 C 扩展，并支持用户用Python、Perl 和Tcl 进行编程，通过插件还增加了 shell 脚本、R、JavaScript 等。至于常见的其他数据库，MySQL 和 MariaDB 只能使用 SQL，MS Access 只能用 VBA 编程，而 SQLite 则根本不支持用户编程。

如果 SQL 不足以完成某些任务，或者您想重用其他代码，那么使用非 SQL 语言是一个选择，但这并不能解决使数据库编程成为一把双刃剑的其他问题。如果说有什么不同的话，那就是采用这些语言会使部署和互操作性进一步复杂化。Caveat scriptor：撰写者需谨慎。

函数与存储过程

默认值和生成

即使是简单的计算，如果执行频率足够高，或者存在多个相互竞争的实现，也可能带来麻烦。对单行中的值进行操作——例如公制单位和英制单位之间的转换、将费率乘以工作小时数以计算发票小计、计算地理多边形的面积——都可以在表定义中声明，以解决其中一个或另一个问题。

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