Day 13: SQL 函數和預存程序

今天我們將探討 SQL 函數和預存程序。這些都是一些進階的 SQL 主題，但是一旦你掌握了它們，你會發現它們在實際應用中是非常有用的。

SQL 函數

SQL 函數是用於進行特定操作的預定義方法。它們可以分為兩種：內建函數和用戶定義函數（UDFs）。

例子：內建函數

內建函數是 SQL 語言內部提供的，例如，COUNT()、SUM()、AVG()。我們可以用這些函數來進行一些基本的數學計算或者數據分析。

SELECT AVG(score) AS average_score FROM students;

這個查詢會返回 students 表中所有學生的平均分數。

用戶定義函數 (UDFs)

UDFs 允許你定義自己的函數，來滿足特定的業務需求。

語法

CREATE [OR REPLACE] FUNCTION function_name (parameter_list)
RETURNS return_datatype
LANGUAGE plpgsql
AS $$
DECLARE
    -- Variable declarations
BEGIN
    -- Function logic
    RETURN value;
END; $$

例子：

CREATE FUNCTION greet_world()
RETURNS TEXT 
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN 'Hello, World!';
END; $$

這個 UDF 名為 greet_world，當被調用時，它會返回一個簡單的字符串 'Hello, World!'。

預存程序

預存程序是一種在數據庫中存儲的預編譯的 SQL 代碼，可以被多次調用。預存程序可以接受參數，進行邏輯處理，並返回值。

語法：

CREATE [OR REPLACE] PROCEDURE procedure_name (parameter_list)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Procedure logic
END; $$

例子：預存程序

假設我們有一個名為 employees 的表，我們想要創建一個預存程序，來增加員工的薪水。

CREATE OR REPLACE PROCEDURE raise_salary(emp_id INT, amount FLOAT)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees SET salary = salary + amount WHERE id = emp_id;
END; $$

這個預存程序接受員工 ID 和加薪金額作為參數，然後在 employees 表中更新相應員工的薪水。

使用預存程序，我們可以這樣做：

CALL raise_salary(1, 500.0);

這會給 ID 為 1 的員工加薪 $500。

兩者比較

預存程序（Stored Procedure）和用戶定義函數（UDFs）都是可以在數據庫中存儲和多次重用的數據庫對象，但它們之間有一些核心的區別：

1. 返回值：

• UDFs： 必須有一個返回值。它可以是任何數據類型，包括標量、表等。
• 預存程序： 通常不返回值。它主要用於執行一個任務，比如修改數據庫中的數據，並且可以有輸出參數。

2. 呼叫方式：

• UDFs： 可以在 SQL 查詢中像函數一樣被呼叫。
• 預存程序： 不能直接在 SQL 查詢中呼叫，而是使用特定的語句（例如 CALL）來執行。

3. 用途：

• UDFs： 常用於計算並返回一個值，並且可以在 SELECT、WHERE 和 ORDER BY 等 SQL 語句中使用。
• 預存程序： 更適合進行複雜的業務邏輯操作和數據庫管理任務，例如插入、更新、刪除數據，或者其他涉及多步驟的任務。

4. 效能：

• 預存程序： 通常具有更好的效能，因為它們是預編譯的。
• UDFs： 每次呼叫時都需要重新編譯，因此可能會有較低的效能。

5. 事務管理：

• 預存程序： 可以有完整的事務管理，包括提交和回滾事務。
• UDFs： 通常不能控制事務。

例子：

UDFs：

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

預存程序：

CREATE OR REPLACE PROCEDURE insert_employee(emp_name VARCHAR, emp_salary FLOAT)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employees (name, salary) VALUES (emp_name, emp_salary);
END; $$

在這個預存程序的例子中，我們沒有返回值，而是執行了一個插入操作。而在 UDF 的例子中，我們計算並返回了兩個數字的和。