存儲過程優勢

• 預編譯：減少 SQL 解析開銷，降低網路傳輸
• 業務封裝：複雜邏輯下沉資料庫層，應用層簡化
• 事務控制：原子性操作更容易管理 (電商下單、扣款)
• 權限隔離：應用只需 EXECUTE 權限，無需直連表
• 維護分離：修改邏輯不需改應用代碼、重新部署

基本語法

DELIMITER //

CREATE PROCEDURE proc_name (IN param1 INT, OUT param2 VARCHAR(100))
BEGIN
    -- 宣告區
    DECLARE var1 INT DEFAULT 0;
    DECLARE var2 VARCHAR(50);
    
    -- 游標宣告
    DECLARE cur CURSOR FOR SELECT col1, col2 FROM tbl WHERE condition;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET var1 = 1;
    
    -- 主體邏輯
    OPEN cur;
    FETCH cur INTO var1, var2;
    WHILE var1 = 0 DO
        -- 處理邏輯
        FETCH cur INTO var1, var2;
    END WHILE;
    CLOSE cur;
    
    SET param2 = var2;
END //

DELIMITER ;

實戰案例一：遞歸查詢樹形結構 (分類/組織架構)

-- 建立臨時表存儲結果
DROP TEMPORARY TABLE IF EXISTS tmp_entp_type_info;
CREATE TEMPORARY TABLE tmp_entp_type_info (
    id INT(20), type_name VARCHAR(255), fid INT(11), lvl INT
);

-- 存儲過程
DROP PROCEDURE IF EXISTS proEntpTypeInfo;
DELIMITER //
CREATE PROCEDURE proEntpTypeInfo(IN iid INT, IN lvl INT)
BEGIN
    DECLARE tid INT DEFAULT -1;
    DECLARE ttype_name VARCHAR(255) DEFAULT '';
    DECLARE tptype_id INT DEFAULT -1;
    
    DECLARE cur1 CURSOR FOR 
        SELECT id, type_name, ptype_id 
        FROM entp_type_info 
        WHERE (ptype_id = iid OR id = iid) AND type = 20 AND is_del = 0;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET tid = NULL;
    
    SET @@max_sp_recursion_depth = 13;  -- 允許遞歸深度
    
    OPEN cur1;
    FETCH cur1 INTO tid, ttype_name, tptype_id;
    
    WHILE tid IS NOT NULL DO
        INSERT INTO tmp_entp_type_info VALUES (tid, ttype_name, tptype_id, lvl);
        CALL proEntpTypeInfo(tid, lvl + 1);  -- 遞歸調用
        FETCH cur1 INTO tid, ttype_name, tptype_id;
    END WHILE;
    
    CLOSE cur1;
END //
DELIMITER ;

-- 調用
CALL proEntpTypeInfo(7, 0);
SELECT * FROM tmp_entp_type_info ORDER BY lvl, id;

實戰案例二：批量更新關聯表 (部門別名同步)

-- 表結構
CREATE TABLE dept (name CHAR(255) PRIMARY KEY, alias CHAR(255)) ENGINE=MyISAM;
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, dept CHAR(255), dept_alias CHAR(255), KEY idx_dept(dept)) ENGINE=MyISAM;

-- 存儲過程：游標逐行處理
DROP PROCEDURE IF EXISTS testProcedure;
DELIMITER //
CREATE PROCEDURE testProcedure()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE tID INT;
    DECLARE tDept CHAR(255);
    DECLARE tAlias CHAR(255);
    
    DECLARE cur CURSOR FOR SELECT id, dept FROM users;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur;
    FETCH cur INTO tID, tDept;
    WHILE done = 0 DO
        SELECT alias INTO tAlias FROM dept WHERE name = tDept;
        UPDATE users SET dept_alias = tAlias WHERE id = tID;
        FETCH cur INTO tID, tDept;
    END WHILE;
    CLOSE cur;
END //
DELIMITER ;

效能對比 (20 萬用戶、1000 部門)

方法	耗時	備註
單條 UPDATE 子查詢	4.25 秒	UPDATE users u SET u.dept_alias=(SELECT alias FROM dept WHERE name=u.dept); 最優
存儲過程游標	17.67 秒	逐行往返、上下文切換開銷大
PHP 循環單條 UPDATE	極慢	網路往返殺手

關鍵結論

• **簡單集合操作用單條 SQL (JOIN/子查詢)，效能遠超存儲過程**
• 存儲過程適合：**複雜流程控制、多步事務、動態 SQL、權限隔離**
• 避免在存儲過程中大量逐行游標處理，改用集合思維
• MySQL 存儲過程除錯困難，建議配合 SELECT ... INTO @var 打印調試

常用管理命令

SHOW PROCEDURE STATUS WHERE Db = 'mydb';
SHOW CREATE PROCEDURE proc_name;
DROP PROCEDURE IF EXISTS proc_name;