跳至主要內容
日沐 SunBathe

Appearance

預存程序與函數 ​

預存程序和函數讓你把業務邏輯封裝在資料庫內。本篇將介紹 PL/pgSQL 語法和常見應用場景。

一、 函數 vs 預存程序 ​

特性函數 (FUNCTION)預存程序 (PROCEDURE)
傳回值必須有可選
交易控制不能 COMMIT/ROLLBACK可以
呼叫方式SELECT func()CALL proc()
可用於觸發器是否

二、 建立函數 ​

基本語法 ​

sql
CREATE OR REPLACE FUNCTION 函數名稱(參數)
RETURNS 傳回類型
LANGUAGE plpgsql
AS $$
DECLARE
    -- 宣告變數
BEGIN
    -- 邏輯
    RETURN 結果;
END;
$$;

範例：計算總價 ​

sql
CREATE OR REPLACE FUNCTION calculate_total(price NUMERIC, quantity INTEGER, tax_rate NUMERIC DEFAULT 0.05)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN price * quantity * (1 + tax_rate);
END;
$$;

-- 使用
SELECT calculate_total(100, 5);        -- 525.00
SELECT calculate_total(100, 5, 0.1);   -- 550.00

範例：取得使用者資訊 ​

sql
CREATE OR REPLACE FUNCTION get_user_info(user_id INTEGER)
RETURNS TABLE(name TEXT, email TEXT, created_at TIMESTAMPTZ)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT u.name, u.email, u.created_at
    FROM users u
    WHERE u.id = user_id;
END;
$$;

-- 使用
SELECT * FROM get_user_info(1);

三、 PL/pgSQL 語法 ​

變數宣告 ​

sql
DECLARE
    -- 基本型別
    user_name TEXT;
    total_amount NUMERIC(10, 2) := 0;
    is_active BOOLEAN DEFAULT TRUE;

    -- 從表推斷型別
    user_row users%ROWTYPE;
    user_email users.email%TYPE;

    -- 常數
    tax_rate CONSTANT NUMERIC := 0.05;
BEGIN
    -- 使用變數
END;

條件判斷 ​

sql
IF condition THEN
    -- 操作
ELSIF other_condition THEN
    -- 操作
ELSE
    -- 操作
END IF;

-- CASE 表達式
result := CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    ELSE 'F'
END;

迴圈 ​

sql
-- FOR 迴圈
FOR i IN 1..10 LOOP
    RAISE NOTICE 'i = %', i;
END LOOP;

-- FOR 迴圈（步進）
FOR i IN REVERSE 10..1 BY 2 LOOP
    RAISE NOTICE 'i = %', i;
END LOOP;

-- FOR 迴圈（查詢結果）
FOR rec IN SELECT * FROM users WHERE is_active LOOP
    RAISE NOTICE 'User: %', rec.name;
END LOOP;

-- WHILE 迴圈
WHILE counter < 100 LOOP
    counter := counter + 1;
END LOOP;

-- LOOP + EXIT
LOOP
    -- 操作
    EXIT WHEN done;
END LOOP;

四、 例外處理 ​

sql
CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Division by zero, returning NULL';
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE NOTICE 'Error: %', SQLERRM;
        RETURN NULL;
END;
$$;

常見例外 ​

例外說明
division_by_zero除以零
unique_violation違反唯一約束
foreign_key_violation違反外鍵約束
null_value_not_allowed不允許 NULL
check_violation違反 CHECK 約束
OTHERS捕捉所有例外

拋出例外 ​

sql
CREATE OR REPLACE FUNCTION validate_age(age INTEGER)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    IF age < 0 THEN
        RAISE EXCEPTION 'Age cannot be negative: %', age
            USING HINT = 'Please provide a valid age',
                  ERRCODE = 'check_violation';
    END IF;

    IF age < 18 THEN
        RAISE WARNING 'User is underage: %', age;
    END IF;
END;
$$;

五、 傳回多個值 ​

RETURNS TABLE ​

sql
CREATE OR REPLACE FUNCTION get_orders_by_user(uid INTEGER)
RETURNS TABLE(order_id INTEGER, total NUMERIC, created_at TIMESTAMPTZ)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT o.id, o.total, o.created_at
    FROM orders o
    WHERE o.user_id = uid
    ORDER BY o.created_at DESC;
END;
$$;

-- 使用
SELECT * FROM get_orders_by_user(1);

RETURNS SETOF ​

sql
CREATE OR REPLACE FUNCTION active_users()
RETURNS SETOF users
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM users WHERE is_active = TRUE;
END;
$$;

OUT 參數 ​

sql
CREATE OR REPLACE FUNCTION get_stats(
    IN table_name TEXT,
    OUT row_count BIGINT,
    OUT total_size TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    EXECUTE format('SELECT COUNT(*) FROM %I', table_name) INTO row_count;
    SELECT pg_size_pretty(pg_total_relation_size(table_name)) INTO total_size;
END;
$$;

-- 使用
SELECT * FROM get_stats('users');
-- row_count | total_size
-- 12345     | 2048 kB

六、 動態 SQL ​

使用 EXECUTE 執行動態 SQL：

sql
CREATE OR REPLACE FUNCTION dynamic_query(table_name TEXT, filter_column TEXT, filter_value TEXT)
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY EXECUTE format(
        'SELECT * FROM %I WHERE %I = $1',
        table_name,
        filter_column
    ) USING filter_value;
END;
$$;

> **SQL Injection 風險**

使用 format() 的 %I（識別符）和 %L（字面值）來安全地插入變數。

sql
-- 安全的做法
EXECUTE format('SELECT * FROM %I WHERE name = %L', table_name, user_input);

-- 不安全！容易被注入
EXECUTE 'SELECT * FROM ' || table_name || ' WHERE name = ''' || user_input || '''';

七、 預存程序 ​

PostgreSQL 11+ 支援預存程序，可以控制交易。

sql
CREATE OR REPLACE PROCEDURE transfer_funds(
    from_account INTEGER,
    to_account INTEGER,
    amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 扣款
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;

    -- 確認餘額足夠（否則回滾）
    IF NOT FOUND OR (SELECT balance FROM accounts WHERE id = from_account) < 0 THEN
        ROLLBACK;
        RAISE EXCEPTION 'Insufficient funds';
    END IF;

    -- 入款
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;

    COMMIT;
END;
$$;

-- 呼叫
CALL transfer_funds(1, 2, 100.00);

八、 實用範例 ​

自動分配流水號 ​

sql
CREATE OR REPLACE FUNCTION generate_order_number()
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    seq_val BIGINT;
    order_number TEXT;
BEGIN
    SELECT nextval('order_number_seq') INTO seq_val;
    order_number := 'ORD-' || TO_CHAR(NOW(), 'YYYYMMDD') || '-' || LPAD(seq_val::TEXT, 6, '0');
    RETURN order_number;
END;
$$;

-- 使用
SELECT generate_order_number();  -- ORD-20240115-000001

軟刪除 ​

sql
CREATE OR REPLACE FUNCTION soft_delete()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.deleted_at := NOW();
    NEW.is_deleted := TRUE;
    RETURN NEW;
END;
$$;

審計日誌 ​

sql
CREATE OR REPLACE FUNCTION audit_log()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_logs (table_name, action, old_data, new_data, changed_at)
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD) ELSE NULL END,
        CASE WHEN TG_OP != 'DELETE' THEN row_to_json(NEW) ELSE NULL END,
        NOW()
    );

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$;

九、 函數管理 ​

查看函數 ​

sql
-- psql 內
\df                     -- 列出所有函數
\df+ function_name      -- 顯示函數詳情

-- SQL 查詢
SELECT proname, prosrc FROM pg_proc WHERE proname = 'my_function';

刪除函數 ​

sql
DROP FUNCTION IF EXISTS calculate_total(NUMERIC, INTEGER, NUMERIC);

授權 ​

sql
GRANT EXECUTE ON FUNCTION calculate_total TO app_user;
REVOKE EXECUTE ON FUNCTION calculate_total FROM PUBLIC;

總結 ​

概念說明
FUNCTION傳回值，可用於 SELECT
PROCEDURE可控制交易，用 CALL 呼叫
PL/pgSQLPostgreSQL 的程序語言
EXCEPTION例外處理
format()安全的動態 SQL
RETURNS TABLE傳回多列多欄

函數讓你把複雜邏輯封裝起來，提高程式碼復用性和維護性。

進階挑戰 ​

  1. 建立一個 calculate_order_total(order_id) 函數，計算訂單總金額（含折扣）
  2. 使用 RETURN TABLE 建立一個返回多列的函數，產生銷售報表
  3. 比較 LANGUAGE sql 和 LANGUAGE plpgsql 函數的效能差異

延伸閱讀與資源 ​

← 上一章：鎖機制與死結處理 | 返回專題首頁 | 下一章：觸發器實戰 →