資料函式庫檢視表與函式觸發器應用

– 9 分鐘閱讀

在資料函式庫開發過程中，經常需要處理複雜的查詢和重複的程式碼。為提升效率並遵循 DRY 原則，可運用檢視表、函式和觸發器等資料函式庫物件。檢視表能簡化複雜查詢，隱藏底層資料表結構，提升資料安全性。函式則可將特定邏輯封裝，方便重複呼叫，而觸發器可在資料變更時自動執行預設操作，確保資料完整性。透過結合這些工具，能有效簡化資料函式庫操作，減少程式碼冗餘，並提升整體效能。

使用檢視表、函式和觸發器節省時間

在程式設計中，一個重要的原則是避免重複自己（Don’t Repeat Yourself, DRY）。本章將探討如何將重複的查詢或步驟轉化為可重複使用的資料函式庫物件，從而簡化工作流程並確保資料的完整性。

儲存查詢為檢視表

檢視表（View）是一種虛擬表，其內容由查詢定義。與實際的表不同，檢視表不儲存資料，而是根據查詢結果動態生成資料。檢視表可以用於簡化複雜的查詢、隱藏敏感資料或提供資料的另一種視角。

建立檢視表

假設我們有一個包含電影資料的 films 表，並且想要建立一個檢視表來顯示每部電影的角色資訊。我們可以使用以下 SQL 陳述式：

CREATE VIEW film_characters AS
WITH characters (id, json) AS (
  SELECT id, jsonb_array_elements(film -> 'characters')
  FROM films
)
SELECT id,
       json ->> 'name' AS name,
       json ->> 'actor' AS actor
FROM characters
ORDER BY id;

這個檢視表 film_characters 使用了公用表運算式（CTE）和 jsonb_array_elements 函式來解析 films 表中的 characters JSON 陣列，並將角色名稱和演員名稱提取出來。

使用檢視表

建立檢視表後，我們可以像查詢普通表一樣查詢它：

SELECT * FROM film_characters;

內容解密：

  1. CREATE VIEW film_characters AS：建立一個名為 film_characters 的檢視表。
  2. WITH characters (id, json) AS (...)：定義一個公用表運算式（CTE）來解析 JSON 陣列。
  3. SELECT id, jsonb_array_elements(film -> 'characters')：使用 jsonb_array_elements 函式展開 JSON 陣列。
  4. SELECT id, json ->> 'name' AS name, json ->> 'actor' AS actor：提取角色名稱和演員名稱。

建立函式

函式是另一種可重複使用的資料函式庫物件，它們可以接受引數並傳回結果。函式可以用於封裝複雜的邏輯或運算。

建立函式

以下是一個簡單的範例，展示如何建立一個函式來計算兩個數字的總和：

CREATE OR REPLACE FUNCTION add_numbers(a numeric, b numeric)
RETURNS numeric AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

使用函式

建立函式後，我們可以像使用內建函式一樣使用它：

SELECT add_numbers(2, 3);

內容解密：

  1. CREATE OR REPLACE FUNCTION add_numbers(a numeric, b numeric)：建立或替換一個名為 add_numbers 的函式，它接受兩個 numeric 型別的引數。
  2. RETURNS numeric AS $$：指定函式傳回 numeric 型別，並開始定義函式體。
  3. RETURN a + b;：計算並傳回兩個引數的總和。
  4. $$ LANGUAGE plpgsql;：結束函式定義，並指定函式使用 PL/pgSQL 語言。

設定觸發器

觸發器是一種特殊的函式，它會在特定的事件發生時自動執行，例如插入、更新或刪除資料。

建立觸發器

以下是一個範例，展示如何建立一個觸發器來自動更新表的最後修改時間：

CREATE OR REPLACE FUNCTION update_modified_time()
RETURNS TRIGGER AS $$
BEGIN
  NEW.modified_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_modified_time_trigger
BEFORE UPDATE ON films
FOR EACH ROW
EXECUTE FUNCTION update_modified_time();

內容解密：

  1. CREATE OR REPLACE FUNCTION update_modified_time()：建立或替換一個名為 update_modified_time 的觸發器函式。
  2. NEW.modified_at = NOW();：更新 modified_at 欄位為當前時間。
  3. CREATE TRIGGER update_modified_time_trigger：建立一個名為 update_modified_time_trigger 的觸發器。
  4. BEFORE UPDATE ON films：指定觸發器在更新 films 表之前執行。
  5. FOR EACH ROW EXECUTE FUNCTION update_modified_time();：指定觸發器對每一行執行 update_modified_time 函式。

練習題

  1. 使用本章介紹的技術，建立一個檢視表來顯示每位老師的實驗室存取記錄。
  2. 建立一個函式來計算兩個日期之間的差值，並使用該函式查詢老師的實驗室存取記錄。
  3. 設定一個觸發器來自動更新老師的實驗室存取記錄的最後修改時間。

這些練習題可以幫助讀者更好地理解和掌握本章介紹的技術。

使用檢視簡化查詢

檢視本質上是一個儲存的查詢，具有名稱，可以像表一樣操作。例如，檢視可以儲存一個計算每個州總人口的查詢。與表一樣，您可以查詢檢視，將檢視與表（或其他檢視）聯接，並使用檢視更新或插入根據它的表的資料，儘管有一些注意事項。檢視中的儲存查詢可以很簡單，只參照一個表，也可以很複雜，具有多表聯接。

檢視在以下場景中特別有用：

  • 避免重複工作：它們允許您編寫一次複雜查詢，並在需要時存取結果。
  • 減少混亂：它們可以透過僅顯示與您的需求相關的列來減少需要處理的資訊量。
  • 提供安全性：檢視可以限制對表中某些列的存取。

在本文中，我們將探討兩種檢視。第一種是標準檢視，包含符合ANSI SQL標準的PostgreSQL語法，每次存取標準檢視時，儲存的查詢都會執行並生成臨時結果集。第二種是物化檢視，這是PostgreSQL、Oracle和其他有限數量的資料函式庫系統特有的。當您建立物化檢視時，其查詢傳回的資料像表一樣永久儲存在資料函式庫中；如果需要，您可以重新整理檢視以更新儲存的資料。

建立和查詢檢視

在本文中，我們將回到第5章匯入的us_counties_pop_est_2019表。清單17-1建立了一個標準檢視，傳回內華達州縣的人口。原始表有16列；檢視將傳回其中的4列。這對於快速存取內華達州人口普查資料的子集非常有用，尤其是在頻繁參照或在應用程式中使用這些資料時。

CREATE OR REPLACE VIEW nevada_counties_pop_2019 AS
SELECT county_name,
       state_fips,
       county_fips,
       pop_est_2019
FROM us_counties_pop_est_2019
WHERE state_name = 'Nevada';

內容解密：

  1. CREATE OR REPLACE VIEW 是用於建立或替換檢視的陳述式。這裡我們建立了一個名為 nevada_counties_pop_2019 的檢視。
  2. SELECT 陳述式選擇了 county_name、state_fips、county_fips 和 pop_est_2019 四列資料來自 us_counties_pop_est_2019 表。
  3. WHERE 子句過濾了資料，只保留 state_name 為 ‘Nevada’ 的行。

使用pgAdmin執行清單17-1中的程式碼。資料函式庫應該會以CREATE VIEW訊息回應。要找到新檢視，在pgAdmin的物件瀏覽器中，右鍵單擊分析資料函式庫並單擊重新整理。選擇Schemas▶public▶Views即可看到所有檢視。

查詢檢視

清單17-2傳回檢視中的所有列。與典型的SELECT查詢一樣，我們可以使用ORDER BY對結果進行排序，這裡使用了縣的FIPS程式碼。我們還增加了LIMIT子句以僅顯示五行。

SELECT *
FROM nevada_counties_pop_2019
ORDER BY county_fips
LIMIT 5;

內容解密：

  1. SELECT * 表示選擇檢視中的所有列。
  2. FROM nevada_counties_pop_2019 指定了要查詢的檢視。
  3. ORDER BY county_fips 根據 county_fips 列對結果進行排序。
  4. LIMIT 5 限制結果集只包含前五行。

這個簡單的例子在快速列出內華達州縣人口時很有用。現在，假設一個政治研究組織中的資料分析師可能會經常詢問：內華達州（或其他州）的每個縣從2010年到2019年的人口變化百分比是多少？

清單17-3建立了一個檢視，儲存了一個類別似於第7章中的查詢，用於計算每個縣的人口變化百分比。

CREATE OR REPLACE VIEW county_pop_change_2019_2010 AS
SELECT c2019.county_name,
       c2019.state_name,
       c2019.state_fips,
       c2019.county_fips,
       c2019.pop_est_2019 AS pop_2019,
       c2010.estimates_base_2010 AS pop_2010,
       round((c2019.pop_est_2019::numeric - c2010.estimates_base_2010) / c2010.estimates_base_2010 * 100, 1) AS percent_change
FROM us_counties_pop_est_2019 c2019
JOIN us_counties_pop_est_2010 c2010
ON c2019.state_fips = c2010.state_fips AND c2019.county_fips = c2010.county_fips;

內容解密：

  1. 這個檢視計算了從2010年到2019年每個縣的人口變化百分比。
  2. 它透過聯接 us_counties_pop_est_2019 和 us_counties_pop_est_2010 兩個表來取得相關資料。
  3. 使用了 round 函式來計算百分比變化並四捨五入到小數點後一位。

這個例子展示瞭如何使用檢視來簡化複雜查詢並使其可重用，從而提高工作效率並減少錯誤。

資料函式庫檢視的建立與應用

在資料函式倉管理中，檢視（View）是一種虛擬表，其內容由查詢定義。檢視可以用於簡化複雜的查詢、提供資料的安全性，以及提高資料的可讀性。在本章中，我們將介紹如何在 PostgreSQL 中建立和使用檢視。

建立檢視

建立檢視的語法如下：

CREATE OR REPLACE VIEW 檢視名稱 AS
SELECT 列名1, 列名2, ...
FROM 表名
WHERE 條件;

例如，我們可以建立一個檢視來顯示美國各縣市的人口變化：

CREATE OR REPLACE VIEW county_pop_change_2019_2010 AS
SELECT 
  c2019.county_name,
  c2019.state_name,
  c2019.pop_est_2019,
  c2010.estimates_base_2010,
  round((c2019.pop_est_2019 - c2010.estimates_base_2010) / c2010.estimates_base_2010 * 100, 1) AS pct_change_2019_2010
FROM us_counties_pop_est_2019 AS c2019
JOIN us_counties_pop_est_2010 AS c2010
ON c2019.state_fips = c2010.state_fips
AND c2019.county_fips = c2010.county_fips;

內容解密：

  1. CREATE OR REPLACE VIEW 陳述式用於建立或替換一個檢視。
  2. county_pop_change_2019_2010 是檢視的名稱。
  3. SELECT 陳述式定義了檢視的內容，包括了縣市名稱、州名、2019 年的人口估計、2010 年的人口基數，以及人口變化的百分比。
  4. JOIN 陳述式用於連線 us_counties_pop_est_2019 和 us_counties_pop_est_2010 兩個表，根據州和縣市的 FIPS 程式碼進行匹配。

查詢檢視

建立檢視後，我們可以像查詢普通表一樣查詢檢視。例如：

SELECT county_name, state_name, pop_2019, pct_change_2019_2010
FROM county_pop_change_2019_2010
WHERE state_name = 'Nevada'
ORDER BY county_fips
LIMIT 5;

內容解密：

  1. SELECT 陳述式指定了要檢索的列，包括縣市名稱、州名、2019 年的人口估計，以及人口變化的百分比。
  2. WHERE 子句用於過濾結果，只顯示州名為 ‘Nevada’ 的記錄。
  3. ORDER BY 子句用於對結果進行排序，根據縣市的 FIPS 程式碼進行排序。
  4. LIMIT 子句用於限制結果的數量，只顯示前 5 筆記錄。

建立實體化檢視

實體化檢視（Materialized View）是一種特殊的檢視，其查詢結果會被儲存到資料函式庫中。建立實體化檢視的語法如下：

CREATE MATERIALIZED VIEW 實體化檢視名稱 AS
SELECT 列名1, 列名2, ...
FROM 表名
WHERE 條件;

例如：

CREATE MATERIALIZED VIEW nevada_counties_pop_2019 AS
SELECT county_name, state_fips, county_fips, pop_est_2019
FROM us_counties_pop_est_2019
WHERE state_name = 'Nevada';

內容解密：

  1. CREATE MATERIALIZED VIEW 陳述式用於建立實體化檢視。
  2. nevada_counties_pop_2019 是實體化檢視的名稱。
  3. SELECT 陳述式定義了實體化檢視的內容，包括縣市名稱、州 FIPS 程式碼、縣市 FIPS 程式碼，以及 2019 年的人口估計。
  4. WHERE 子句用於過濾結果，只顯示州名為 ‘Nevada’ 的記錄。

更新實體化檢視

實體化檢視的資料可以透過 REFRESH MATERIALIZED VIEW 陳述式進行更新。例如：

REFRESH MATERIALIZED VIEW nevada_counties_pop_2019;

內容解密：

  1. REFRESH MATERIALIZED VIEW 陳述式用於更新實體化檢視的資料。
  2. nevada_counties_pop_2019 是要更新的實體化檢視名稱。

資料函式庫檢視表的應用：員工稅務部門資料管理

在資料函式倉管理中，檢視表（View）是一種虛擬表，其內容由查詢定義。檢視表可以簡化複雜的查詢操作，並提供資料安全性。本文將介紹如何使用檢視表來管理員工稅務部門的資料。

建立員工稅務部門檢視表

首先，我們需要建立一個檢視表來顯示稅務部門（dept_id = 1）的員薪水料。Listing 17-7 展示瞭如何建立這個檢視表。

CREATE OR REPLACE VIEW employees_tax_dept WITH (security_barrier) AS
SELECT emp_id, first_name, last_name, dept_id
FROM employees
WHERE dept_id = 1
WITH LOCAL CHECK OPTION;

這個檢視表的建立陳述式中包含了幾個重要的關鍵字：

  • WITH (security_barrier)：啟用資料函式庫安全機制，防止惡意使用者繞過檢視表的限制。
  • WHERE dept_id = 1：篩選出稅務部門的員薪水料。
  • WITH LOCAL CHECK OPTION：限制插入和更新操作，確保新資料符合檢視表的條件。

內容解密：

  1. CREATE OR REPLACE VIEW 陳述式用於建立或替換一個檢視表。
  2. WITH (security_barrier) 提供了額外的安全層，確保檢視表的資料不會被惡意操縱。
  3. SELECT 陳述式選擇了要顯示的欄位，包括員工ID、名字、姓氏和部門ID。
  4. WHERE 子句過濾了非稅務部門的員工。
  5. WITH LOCAL CHECK OPTION 確保了透過檢視表插入或更新的資料必須符合 dept_id = 1 的條件。

執行上述陳述式後，我們可以查詢 employees_tax_dept 檢視表來驗證其內容。

SELECT * FROM employees_tax_dept ORDER BY emp_id;

結果應該顯示稅務部門的員薪水料。

透過檢視表插入資料

我們可以使用 employees_tax_dept 檢視表來插入新的員薪水料。Listing 17-8 展示了兩個插入操作的例子。

INSERT INTO employees_tax_dept (emp_id, first_name, last_name, dept_id)
VALUES (5, 'Suzanne', 'Legere', 1);

INSERT INTO employees_tax_dept (emp_id, first_name, last_name, dept_id)
VALUES (6, 'Jamil', 'White', 2);

第一個插入操作成功，因為新資料符合檢視表的條件（dept_id = 1）。第二個插入操作失敗，因為 dept_id = 2 不符合檢視表的條件。

內容解密：

  1. 第一個 INSERT 陳述式成功插入了 Suzanne Legere 的資料，因為她的部門ID是1，符合檢視表的條件。
  2. 第二個 INSERT 陳述式失敗了，因為 Jamil White 的部門ID是2，不符合檢視表的條件，觸發了檢查選項的限制。

查詢 employees_tax_dept 和 employees 表，可以驗證 Suzanne Legere 的資料是否正確插入。

SELECT * FROM employees_tax_dept ORDER BY emp_id;
SELECT * FROM employees ORDER BY emp_id;

結果顯示 Suzanne Legere 的資料已成功插入到 employees 表中，但 salary 欄位為 NULL，因為檢視表中未包含該欄位。

透過檢視表更新資料

同樣地，我們可以使用 employees_tax_dept 檢視表來更新員薪水料。Listing 17-9 展示了一個更新操作的例子。

UPDATE employees_tax_dept
SET last_name = 'Le Gere'
WHERE emp_id = 5;

SELECT * FROM employees_tax_dept ORDER BY emp_id;

這個更新操作成功地將 Suzanne Legere 的姓氏更正為 Le Gere。

內容解密：

  1. UPDATE 陳述式更新了 Suzanne Legere 的姓氏。
  2. 由於 Suzanne Legere 的部門ID是1，符合檢視表的條件，因此更新操作成功。

嘗試更新非稅務部門的員薪水料或更新 salary 欄位將會失敗，因為這些操作不符合檢視表的定義或限制。

總之，使用檢視表可以有效地管理和保護資料函式庫中的敏感資料，同時簡化複雜的查詢操作。透過適當地定義檢視表，可以實作對資料的精細控制和安全管理。

玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。