功能单元

简介

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

但数据库，顾名思义，非常擅长处理和操作信息，并且它们中的大多数都将其相同的控制和能力提供给其用户（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：让作者当心。

函数 vs 过程

默认值和生成

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

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