函式與程序之間的互動

之前幾個章節我們已經聊過 PostgreSQL 裡的 procedure 跟 function。現在該來深入一點了。

Function 跟 procedure 可以各自運作，但通常它們的互動才是整個系統成敗的關鍵。最方便的地方就是 function 可以互相呼叫，還能傳資料甚至拿到執行結果。

Function vs Procedure：差在哪？

來複習一下 PostgreSQL 裡 function 跟 procedure 有什麼不一樣：

• 函式（FUNCTION）：

  • 會回傳值。
  • 可以在 SELECT 裡用。
  • 常常拿來做計算或資料轉換。

• 程序（PROCEDURE）：

  • 不會直接回傳值。
  • 用來做像是 insert、update 或 delete 這種操作。
  • 要用 CALL 指令來呼叫。

函式之間的資料傳遞

來點實作，先看個基本範例，怎麼在 function 跟 procedure 之間傳資料。其實 function 之間傳資料就是靠參數跟回傳值啦。

這是怎麼在一個 function 裡呼叫另一個 function：

CREATE OR REPLACE FUNCTION get_student_name(student_id INT)
RETURNS TEXT AS $$
DECLARE
    student_name TEXT;
BEGIN
    -- 根據 ID 取出學生名字
    SELECT name INTO student_name FROM students WHERE id = student_id;

    -- 回傳名字
    RETURN student_name;
END;
$$ LANGUAGE plpgsql;

這個 function 可以在另一個 function 裡被呼叫：

CREATE OR REPLACE FUNCTION welcome_student(student_id INT)
RETURNS TEXT AS $$
DECLARE
    message TEXT;
BEGIN
    -- 用另一個 function 拿到學生名字
    message := 'Welcome, ' || get_student_name(student_id) || '!';

    -- 回傳歡迎訊息
    RETURN message;
END;
$$ LANGUAGE plpgsql;

1. Function get_student_name 會根據 student_id 回傳學生名字。
2. 另一個 function — welcome_student — 就用這個名字來組歡迎訊息。

注意： 用 SELECT INTO 可以把查詢結果存到 PL/pgSQL 的變數裡。

從 function 呼叫 procedure 的範例

現在來看怎麼在 function 裡呼叫 procedure。假設我們有個 procedure，會記錄學生登入系統的時間：

CREATE OR REPLACE PROCEDURE log_student_entry(student_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO log_entries(student_id, entry_time)
    VALUES (student_id, NOW());
END;
$$;

現在在 function 裡呼叫這個 procedure，讓它記錄登入，然後回傳訊息：

CREATE OR REPLACE FUNCTION student_login(student_id INT)
RETURNS TEXT AS $$
BEGIN
    -- 呼叫 procedure 來記錄
    CALL log_student_entry(student_id);

    -- 回傳訊息
    RETURN 'Student login logged successfully.';
END;
$$ LANGUAGE plpgsql;

互動的實用範例

範例 1：計算訂單總額並記錄

想像你在做線上訂單系統。要算訂單總額有個 function：

CREATE OR REPLACE FUNCTION calculate_order_total(order_id INT)
RETURNS NUMERIC AS $$
DECLARE
    total NUMERIC;
BEGIN
    -- 把所有訂單項目加總
    SELECT SUM(price * quantity) INTO total
    FROM order_items
    WHERE order_id = order_id;

    RETURN total;
END;
$$ LANGUAGE plpgsql;

要把訂單總額存下來就用 procedure：

CREATE OR REPLACE PROCEDURE log_order_total(order_id INT, total NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO order_totals(order_id, total)
    VALUES (order_id, total);
END;
$$;

現在把它們串起來：

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS TEXT AS $$
DECLARE
    total NUMERIC;
BEGIN
    -- 呼叫 function 算總額
    total := calculate_order_total(order_id);

    -- 用 procedure 記錄總額
    CALL log_order_total(order_id, total);

    RETURN 'Order processed successfully.';
END;
$$ LANGUAGE plpgsql;

範例 2：取得學生最高評分並更新個人資料

拿最高評分的 function：

CREATE OR REPLACE FUNCTION get_highest_rating(student_id INT)
RETURNS INT AS $$
DECLARE
    max_rating INT;
BEGIN
    -- 找出學生最高評分
    SELECT MAX(rating) INTO max_rating
    FROM ratings
    WHERE student_id = student_id;

    RETURN max_rating;
END;
$$ LANGUAGE plpgsql;

更新學生個人資料的 procedure：

CREATE OR REPLACE PROCEDURE update_student_profile(student_id INT, max_rating INT)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE students
    SET highest_rating = max_rating
    WHERE id = student_id;
END;
$$;

呼叫這些操作的 function：

CREATE OR REPLACE FUNCTION refresh_student_profile(student_id INT)
RETURNS TEXT AS $$
DECLARE
    max_rating INT;
BEGIN
    -- 取得最高評分
    max_rating := get_highest_rating(student_id);

    -- 更新學生個人資料
    CALL update_student_profile(student_id, max_rating);

    RETURN 'Profile updated successfully.';
END;
$$ LANGUAGE plpgsql;

互動時常見錯誤

最常見的錯誤之一就是 function 跟 procedure 之間資料型別不對。例如 procedure 需要 NUMERIC，你卻給 INTEGER，PostgreSQL 就會報型別不符。記得型別一定要對。

還有一種錯誤是 function 互相遞迴呼叫，像 function A 呼叫 function B，然後 B 又呼叫回 A，這樣會無限循環，系統就掛了。

實際意義

為什麼要這樣互動？現實中 function 跟 procedure 就像「積木」一樣，讓複雜系統可以拆成小塊，這樣 debug、重複利用、測試都方便。例如：

• 面試時可能會叫你寫個 function，裡面要呼叫 procedure 做複雜操作。會這招很加分。
• 做真實專案，像電商、log 系統或 CRM，會用 function 跟 procedure 互動來讓程式碼更簡單好維護。

想更深入學 function 跟 procedure 的互動，可以去看 PL/pgSQL 官方文件。