MySQL 儲存程序完整教學：建立、呼叫、變數、流程控制與實戰範例

儲存程序基礎結構

CREATE PROCEDURE proc_name ([IN/OUT/INOUT] parameter type)
BEGIN
    -- 宣告區（必須在所有執行語句之前）
    DECLARE variable_name type [DEFAULT value];
    
    -- 程序本體
    IF condition THEN
        -- statements
    ELSE
        -- statements
    END IF;
    
    -- SQL 語句
    INSERT INTO tb (name) VALUES (variable);
END

關鍵語法規則

命名與參數

• 程序名不區分大小寫（PROCE1() = proce1()）
• 不可與內建函數同名
• 參數三要素：模式(IN/OUT/INOUT) + 名稱 + 型別
• IN（預設）= 輸入；OUT = 輸出；INOUT = 雙向
• 多參數以逗號分隔

分隔符變更（關鍵步驟）

程序內部以分號結束語句，定義前必須變更分隔符：

mysql> DELIMITER //
-- 建立程序...
CREATE PROCEDURE ...
BEGIN
    ...
END;
//
mysql> DELIMITER ;

刪除程序

DROP PROCEDURE [IF EXISTS] proc_name;

完整實戰流程

1. 開啟命令列終端機
2. 登入 MySQL：mysql -u用戶 -p密碼
3. 變更分隔符：DELIMITER //
4. 選擇資料庫：USE db_name
5. 建立程序
6. 呼叫測試：CALL proc_name(params);

範例一：條件查詢排序

-- 建表
USE test;
CREATE TABLE user(
    id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(15) NOT NULL DEFAULT '',
    pass CHAR(32) NOT NULL DEFAULT '',
    note TEXT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB CHARSET=utf8;

-- 程序
DELIMITER //
CREATE PROCEDURE proc_name (IN parameter INT)
BEGIN
    IF parameter = 0 THEN
        SELECT * FROM user ORDER BY id ASC;
    ELSE
        SELECT * FROM user ORDER BY id DESC;
    END IF;
END;
//
DELIMITER ;

-- 測試
CALL proc_name(1);
CALL proc_name(0);

範例二：變數宣告與指派

DROP PROCEDURE IF EXISTS proc_name;
DELIMITER //
CREATE PROCEDURE proc_name (IN parameter INT)
BEGIN
    DECLARE variable VARCHAR(20);
    IF parameter = 1 THEN
        SET variable = 'Windows';
    ELSE
        SET variable = 'Linux';
    END IF;
    SELECT parameter;
END;
//
DELIMITER ;

CALL proc_name(1);
CALL proc_name(0);

進階主題預覽

• 游標：逐列處理結果集
• 迴圈：WHILE、REPEAT、LOOP
• 例外處理：DECLARE HANDLER
• 巢狀呼叫：程序呼叫程序
• 交易控制：START TRANSACTION/COMMIT/ROLLBACK