功能單元

簡介

關於在資料庫中執行計算，有兩種思想流派：認為這很棒的人，以及犯錯的人。 這並不是說函數、預存程序、產生或計算欄位和觸發器的世界都是陽光和玫瑰！ 這些工具遠非萬無一失，考慮不周的實作可能會表現不佳、讓維護者感到痛苦等等，這在某種程度上解釋了爭議的存在。

但根據定義，資料庫非常擅長處理和操作資訊，而且它們中的大多數都將相同的控制和能力提供給使用者（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