功能單元

簡介

關於在資料庫中執行計算，有兩種截然不同的觀點：一種人認為這很棒，另一種人則錯了。這並不是說函式、儲存過程、生成列或計算列以及觸發器的世界盡是陽光和玫瑰！這些工具遠非萬無一失，考慮不周的實現可能會導致效能低下，讓維護人員苦不堪言等等，這也解釋了爭議的存在。

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