功能单元

简介

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

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

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

• 数据库功能往往会变得隐形——尤其是触发器。这种弱点大致与团队和/或与数据库交互的应用程序的规模成比例，因为了解数据库内编程的人越来越少。文档有所帮助，但也仅限于此。
• SQL 是一种专门用于操作数据集的语言。它在操作数据集以外的事情上表现不佳，而且这些事情越复杂，它的表现就越差。
• RDBMS 的功能和 SQL 方言有所不同。简单的生成列得到广泛支持，但将更复杂的数据库逻辑移植到其他存储至少需要时间和精力。
• 数据库 Schema 升级通常比应用程序升级更麻烦。快速变化的逻辑最好在其他地方维护，尽管一旦情况稳定下来，可以再考虑。
• 管理数据库程序并不像人们希望的那么简单。许多 Schema 迁移工具对组织作用甚微或全无，导致蔓延的 diff 和繁重的代码审查（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 语言是一个选择，但这并不能解决使数据库编程成为一把双刃剑的其他问题。如果说有什么不同的话，那就是采用这些语言会进一步使部署和互操作性复杂化。写作者请注意：谨言慎行。

函数与存储过程

默认值和生成

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

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 job 中，对其进行消化和添加，然后存储结果——那么是时候问问，什么真正需要冒险离开数据库了。

如前所述，这是 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