建立存儲過程基本語法

CREATE PROCEDURE sp_name ([IN|OUT|INOUT] param_name type, ...)
[CHARACTERISTICS ...]
BEGIN
  -- SQL 語句
END;

• IN：輸入參數（預設），調用者傳入。
• OUT：輸出參數，過程內賦值，調用者接收。
• INOUT：雙向參數。
• DELIMITER $$：臨時修改語句結束符，避免過程體內 ; 過早結束。

完整範例：帶輸入輸出參數

DELIMITER $$

DROP PROCEDURE IF EXISTS `get_user_title`$$

CREATE PROCEDURE `get_user_title`(
  IN p_table VARCHAR(30),
  IN p_id INT,
  OUT p_title VARCHAR(100)
)
BEGIN
  SET @sql = CONCAT('SELECT title FROM ', p_table, ' WHERE id = ?');
  PREPARE stmt FROM @sql;
  SET @id = p_id;
  EXECUTE stmt USING @id;
  DEALLOCATE PREPARE stmt;
  -- 假設查詢結果單行，需用 SELECT ... INTO 給 OUT 參數賦值
  -- 這裡為演示簡化，實際需用動態 SQL 或游標
END$$

DELIMITER ;

調用存儲過程

CALL get_user_title('users', 1, @title);
SELECT @title;  -- 查看 OUT 參數值

變量聲明與賦值

DECLARE v_cnt INT DEFAULT 0;
SET v_cnt = 10;
SELECT COUNT(*) INTO v_cnt FROM users;  -- 查詢結果賦值給變量

流程控制

IF ... ELSE

IF v_cnt > 0 THEN
  SELECT 'has data';
ELSE
  SELECT 'empty';
END IF;

CASE

CASE v_type
  WHEN 1 THEN SELECT 'type1';
  WHEN 2 THEN SELECT 'type2';
  ELSE SELECT 'other';
END CASE;

LOOP / WHILE / REPEAT

WHILE v_cnt < 10 DO
  SET v_cnt = v_cnt + 1;
END WHILE;

游標

DECLARE cur CURSOR FOR SELECT id FROM users WHERE status=1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;
read_loop: LOOP
  FETCH cur INTO v_id;
  IF done THEN LEAVE read_loop; END IF;
  -- 處理 v_id
END LOOP;
CLOSE cur;

字元集編碼坑（中文參數亂碼）

錯誤：Illegal mix of collations (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE) for operation 'concat'

解決：建立過程時指定字元集，或連線執行 SET NAMES utf8mb4。

CREATE PROCEDURE sp_test()
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
BEGIN ... END;

優缺點與使用建議

優點	缺點
減少網路往返（批次業務邏輯）	可移植性差（方言差異大）
編譯後執行快、可快取計畫	除錯困難、無 IDE 斷點支援
權限控制精細（EXECUTE 權限）	業務邏輯分散，維護成本高
防 SQL 注入（參數化）	版本控制、Code Review 不便

建議： 僅將高頻、複雜、需原子性的核心邏輯（如庫存扣減、轉賬）封裝為存儲過程；一般 CRUD 交由應用層 ORM 處理。