預存程序 (SQL Stored Procedure)

預存程序 (Stored Procedure) 是一組預先編譯並儲存在資料庫中的 SQL 語句集合。你可以將常用的 SQL 操作封裝成預存程序，之後只要呼叫程序名稱即可執行，不需要重複撰寫相同的 SQL 語句。

預存程序的優點

1. 提升效能: 預存程序經過預先編譯，執行速度較快
2. 減少網路流量: 只需傳遞程序名稱和參數，而非大量 SQL 語句
3. 程式碼重用: 將邏輯封裝起來，可被多個應用程式呼叫
4. 安全性: 可控制使用者只能透過預存程序存取資料

SQL Server 建立預存程序

CREATE PROCEDURE procedure_name
    @parameter1 datatype,
    @parameter2 datatype
AS
BEGIN
    -- SQL 語句
END;

基本範例

CREATE PROCEDURE GetAllCustomers
AS
BEGIN
    SELECT * FROM customers;
END;

執行預存程序：

EXEC GetAllCustomers;
-- 或
EXECUTE GetAllCustomers;

帶參數的預存程序

CREATE PROCEDURE GetCustomersByCity
    @city VARCHAR(50)
AS
BEGIN
    SELECT * FROM customers WHERE City = @city;
END;

執行：

EXEC GetCustomersByCity @city = '台北';
-- 或
EXEC GetCustomersByCity '台北';

有預設值的參數

CREATE PROCEDURE GetProducts
    @minPrice DECIMAL(10,2) = 0,
    @maxPrice DECIMAL(10,2) = 999999
AS
BEGIN
    SELECT * FROM products 
    WHERE Price BETWEEN @minPrice AND @maxPrice;
END;

輸出參數

CREATE PROCEDURE GetCustomerCount
    @city VARCHAR(50),
    @count INT OUTPUT
AS
BEGIN
    SELECT @count = COUNT(*) FROM customers WHERE City = @city;
END;

執行並取得輸出：

DECLARE @result INT;
EXEC GetCustomerCount @city = '台北', @count = @result OUTPUT;
SELECT @result;

MySQL 建立預存程序

DELIMITER //
CREATE PROCEDURE procedure_name(
    IN parameter1 datatype,
    OUT parameter2 datatype
)
BEGIN
    -- SQL 語句
END //
DELIMITER ;

MySQL 範例

DELIMITER //
CREATE PROCEDURE GetCustomersByCity(IN p_city VARCHAR(50))
BEGIN
    SELECT * FROM customers WHERE City = p_city;
END //
DELIMITER ;

執行：

CALL GetCustomersByCity('台北');

MySQL 輸出參數

DELIMITER //
CREATE PROCEDURE GetCustomerCount(
    IN p_city VARCHAR(50),
    OUT p_count INT
)
BEGIN
    SELECT COUNT(*) INTO p_count FROM customers WHERE City = p_city;
END //
DELIMITER ;

執行：

CALL GetCustomerCount('台北', @result);
SELECT @result;

修改預存程序

SQL Server:

ALTER PROCEDURE procedure_name
AS
BEGIN
    -- 修改後的 SQL 語句
END;

MySQL:

DROP PROCEDURE IF EXISTS procedure_name;
-- 然後重新 CREATE

刪除預存程序

DROP PROCEDURE procedure_name;
-- 或 (避免不存在時報錯)
DROP PROCEDURE IF EXISTS procedure_name;

預存程序 vs 函數