Stored Procedure 預存程序

什麼是 Stored Procedure？

比喻：SP 就像自動販賣機 🎰

你投入硬幣（參數），按一個按鈕（呼叫）， 機器內部執行一連串步驟，最後吐出飲料（結果）。

Stored Procedure（SP）是存在資料庫裡的可重複執行的程式。

PostgreSQL 函數語法

PostgreSQL 使用 CREATE FUNCTION 而非 CREATE PROCEDURE（兩者都支持，但函數更常用）。

-- 建立一個函數：根據科系查詢學生
CREATE OR REPLACE FUNCTION get_students_by_dept(
    dept_name VARCHAR                    -- ← 輸入參數
)
RETURNS TABLE (                          -- ← 回傳表格類型
    student_name VARCHAR,
    student_age INT,
    student_score DECIMAL
)
LANGUAGE plpgsql                         -- ← 使用 PL/pgSQL 語言
AS $$
BEGIN
    RETURN QUERY                         -- ← 回傳查詢結果
    SELECT Name, Age, Score
    FROM Students
    WHERE Department = dept_name;
END;
$$;

呼叫函數

SELECT * FROM get_students_by_dept('資工系');

帶邏輯的函數

-- 註冊新學生（帶驗證邏輯）
CREATE OR REPLACE FUNCTION register_student(
    p_name VARCHAR,
    p_age INT,
    p_email VARCHAR
)
RETURNS INT                              -- 回傳新學生的 Id
LANGUAGE plpgsql
AS $$
DECLARE
    new_id INT;                          -- 宣告變數
BEGIN
    -- 驗證年齡
    IF p_age < 16 OR p_age > 60 THEN
        RAISE EXCEPTION '年齡必須在 16~60 之間';  -- 拋出錯誤
    END IF;

    -- 檢查 Email 是否已存在
    IF EXISTS (SELECT 1 FROM Students WHERE Email = p_email) THEN
        RAISE EXCEPTION 'Email 已被使用：%', p_email;
    END IF;

    -- 新增學生
    INSERT INTO Students (Name, Age, Email)
    VALUES (p_name, p_age, p_email)
    RETURNING Id INTO new_id;            -- 取得新 Id

    RETURN new_id;
END;
$$;

呼叫

SELECT register_student('小新', 19, 'new@test.com');
-- 成功 → 回傳 Id
-- 失敗 → 拋出錯誤訊息

變數與流程控制

CREATE OR REPLACE FUNCTION evaluate_student(p_id INT)
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
DECLARE
    v_score DECIMAL;                     -- 宣告變數
    v_result VARCHAR;
BEGIN
    -- 取得分數
    SELECT Score INTO v_score            -- 把查詢結果存入變數
    FROM Students
    WHERE Id = p_id;

    -- 判斷等級
    IF v_score >= 90 THEN
        v_result := '優秀';
    ELSIF v_score >= 80 THEN
        v_result := '良好';
    ELSIF v_score >= 60 THEN
        v_result := '及格';
    ELSE
        v_result := '不及格';
    END IF;

    RETURN v_result;
END;
$$;

SP / Function 的優缺點

✅ 優點

• 效能好：預先編譯，減少網路往返
• 安全性：可以透過函數控制存取權限
• 重用性：寫一次，到處呼叫
• 一致性：商業邏輯集中在資料庫

❌ 缺點

• 難版控：不像應用程式碼容易用 Git 管理
• 難除錯：PL/pgSQL 的除錯工具不如程式語言
• 可移植性差：每個資料庫的語法都不同
• 耦合：商業邏輯綁在資料庫，換資料庫很痛苦

💡 現代做法：簡單邏輯放 SP，複雜商業邏輯放應用程式碼。