功能单元

简介

关于在数据库中执行计算，有两种思想流派：认为它很棒的人，和错误的人。 这并不是说函数、存储过程、生成列或计算列以及触发器的世界都是阳光和玫瑰！ 这些工具远非万无一失，考虑不周的实现可能会表现不佳，给维护者带来创伤等等，这在一定程度上解释了争议的存在。

但是，顾名思义，数据库非常擅长处理和操作信息，并且它们中的大多数都将其相同的控制和能力提供给用户（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 中更有效地完成的事情。

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

FAQ

CREATE PROCEDURE procedure_name
AS
common_sql_statement;

EXEC procedute_name