SQL Macros

發表於 分類於 閱讀次數：
SQL Macros

SQL Macros 概述

SQL Macros 是 Oracle 21c 引入的一項強大功能，允許開發人員創建可重用的 SQL 代碼片段，這些片段可以在查詢中像函數一樣使用。SQL Macros 透過簡化和模組化 SQL 代碼，使得複雜查詢更具可讀性和可維護性。

SQL Macros 的類型

Oracle 21c 支援兩種類型的 SQL Macros：

  1. Scalar SQL Macros（標量 SQL 宏）

  2. Table SQL Macros（表 SQL 宏）

  3. Scalar SQL Macros（標量 SQL 宏）
    標量 SQL 宏生成一個標量表達式，並返回一個單一值。它類似於標量函數，但在 SQL 執行期間，宏會直接展開成相應的 SQL 代碼，從而避免了函數調用的性能開銷。

範例：

假設你有一個用於計算折扣價格的常用公式，你可以將其封裝到一個 SQL Macro 中。

1
2
3
4
5
6
7
8
CREATE OR REPLACE FUNCTION discount_price(price NUMBER, discount_pct NUMBER)
RETURN VARCHAR2 SQL_MACRO IS
BEGIN
  RETURN q'{
    price - (price * discount_pct / 100)
  }';
END discount_price;
/

現在，你可以在查詢中使用這個 SQL Macro 來計算折扣後的價格：

1
2
SELECT product_id, price, discount_price(price, 10) AS discounted_price
FROM products;

在執行期間，discount_price(price, 10) 將被替換為 price - (price * 10 / 100)。

  1. Table SQL Macros（表 SQL 宏）
    表 SQL 宏生成一個返回行集（表）的表達式，類似於視圖或表函數。在查詢執行時，宏會被展開成一個完整的 SQL 查詢語句。

範例：

你可以創建一個動態過濾器的 SQL Macro，它根據輸入條件返回特定的數據集。

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE FUNCTION filter_employees(dept_id NUMBER)
RETURN VARCHAR2 SQL_MACRO IS
BEGIN
  RETURN q'{
    SELECT employee_id, first_name, last_name, department_id
    FROM employees
    WHERE department_id = dept_id
  }';
END filter_employees;
/

然後可以在查詢中使用這個宏來獲取某個部門的員工列表：

1
2
SELECT *
FROM filter_employees(10);

這裡的 filter_employees(10) 在查詢執行時會被展開為：

1
2
3
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 10;

SQL Macros 的優勢

. 提高代碼可讀性和重用性: SQL Macros 允許開發者將複雜的 SQL 邏輯封裝成簡單的、可重用的片段，提升了 SQL 代碼的可讀性。

. 性能優化: 由於 SQL Macros 在查詢執行前被展開為原生 SQL 代碼，因此沒有函數調用的額外開銷，可以提升查詢性能。

. 簡化 SQL 代碼: SQL Macros 有助於減少代碼重複，使得 SQL 查詢更加簡潔和易於維護。

. 靈活的代碼模組化: 可以將複雜的邏輯提取到 SQL Macros 中，通過參數化實現靈活的代碼模組化，增強了 SQL 查詢的可擴展性。

總結

SQL Macros 是 Oracle 21c 中一項非常有用的新特性，特別適合用來簡化複雜的 SQL 查詢。透過模組化和重用代碼，提升了開發效率，同時優化了 SQL 執行的性能。