MySQL存儲過程完整教程：變量、流程控制、參數傳遞與實戰案例

MySQL存儲過程核心語法與實戰指南

核心概念

• 存儲過程 = 預編譯SQL集合 + 流程控制（IF/CASE/WHILE/LOOP）
• 與函數區別：無返回值，通過OUT參數輸出結果
• 調用方式：CALL proc_name();

基礎管理命令

# 查看所有存儲過程
SHOW PROCEDURE STATUS;

# 刪除存儲過程
DROP PROCEDURE proc_name;

# 查看創建語句
SHOW CREATE PROCEDURE proc_name;

變量聲明與作用域

CREATE PROCEDURE demo_vars()
BEGIN
  -- 局部變量（僅在BEGIN...END塊內有效）
  DECLARE var1 INT DEFAULT 0;
  DECLARE var2 VARCHAR(20) DEFAULT 'test';
  
  -- 會話變量（整個連接可見，@開頭）
  SET @session_var = 100;
  
  -- 系統變量（@@開頭）
  SELECT @@max_connections;
END;

流程控制構造

條件判斷

-- IF ELSEIF ELSE
IF condition1 THEN
  -- statements
ELSEIF condition2 THEN
  -- statements
ELSE
  -- statements
END IF;

-- CASE WHEN
CASE 
  WHEN condition1 THEN statements
  WHEN condition2 THEN statements
  ELSE statements
END CASE;

-- CASE 值匹配
CASE expr
  WHEN value1 THEN statements
  WHEN value2 THEN statements
  ELSE statements
END CASE;

循環結構

-- WHILE 循環
WHILE condition DO
  -- statements
  ITERATE label_name;  -- 類似 continue
  LEAVE label_name;    -- 類似 break
END WHILE;

-- REPEAT 循環（至少執行一次）
REPEAT
  -- statements
UNTIL condition END REPEAT;

-- LOOP 循環（需配合LEAVE退出）
label_name: LOOP
  -- statements
  IF condition THEN LEAVE label_name; END IF;
END LOOP;

參數模式詳解

實戰案例：分頁歸檔半年前數據

CREATE PROCEDURE archive_old_accounts()
BEGIN
  DECLARE cnt INT DEFAULT 0;
  DECLARE file_suffix VARCHAR(20);
  
  SELECT COUNT(*) INTO cnt 
  FROM S_ACCOUNT 
  WHERE DATE_ADD(registerTime, INTERVAL 6 MONTH) <= NOW();
  
  IF cnt > 0 THEN
    SET file_suffix = UNIX_TIMESTAMP(NOW());
    
    -- 動態SQL導出數據
    SET @sql = CONCAT(
      "SELECT * FROM S_ACCOUNT ",
      "WHERE DATE_ADD(registerTime, INTERVAL 6 MONTH) <= NOW() ",
      "INTO OUTFILE 'C:/backup/", file_suffix, ".txt' ",
      "FIELDS TERMINATED BY ',' "
      "OPTIONALLY ENCLOSED BY '\"' "
      "LINES TERMINATED BY '\n'"
    );
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- 刪除歸檔數據
    DELETE FROM S_ACCOUNT 
    WHERE DATE_ADD(registerTime, INTERVAL 6 MONTH) <= NOW();
    
    COMMIT;
  END IF;
END;

定時任務（Event）配置

-- 創建每日執行的事件
CREATE EVENT daily_archive
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'
DO CALL archive_old_accounts();

-- 啟用事件調度器（永久生效需配置my.cnf）
SET GLOBAL event_scheduler = ON;
-- my.cnf添加：event_scheduler = 1

調試技巧

1. 用SELECT ... INTO @var;輸出中間結果
2. 在過程中插入INSERT INTO debug_log(msg) VALUES('step1');
3. 使用SHOW WARNINGS;捕獲隱式轉換警告