MySQL 5.0 存儲過程開發入門：語法、分隔符、變量、流程控制、動態 SQL 全攻略

從 MS SQL Server 遷移到 MySQL 存儲過程的關鍵差異筆記

MySQL 5.0 引入存儲過程，語法與 T-SQL 有顯著差異。本文記錄從「Hello World」到動態 SQL 的完整遷移路徑。

核心語法骨架

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

proc_parameter: [ IN | OUT | INOUT ] param_name type
characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
                | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'
routine_body: Valid SQL procedure statement

關鍵差異 1：分隔符

MySQL 預設分隔符 ;，但過程體內部含多條語句需 ; 結束，故必須臨時修改分隔符：

DELIMITER //

CREATE PROCEDURE p_test()
BEGIN
  SELECT 'Hello World!' AS msg;
END //

DELIMITER ;

phpMyAdmin 執行時，在「分隔符」輸入框填 // 即可。

關鍵差異 2：變量聲明與賦值

-- 聲明 (必須在 BEGIN 後、其他語句前)
DECLARE v_uid MEDIUMINT(8);
DECLARE v_pwd CHAR(32);

-- 賦值：SELECT ... INTO (單行多列)
SELECT u.uid, u.password, f.nickname
INTO v_uid, v_pwd, v_nickname
FROM cdb_members u
JOIN cdb_memberfields f ON f.uid = u.uid
WHERE u.username = p_username;

關鍵差異 3：流程控制

IF condition THEN
  -- statements
ELSEIF condition THEN
  -- statements
ELSE
  -- statements
END IF;

-- 循環
WHILE condition DO
  -- statements
END WHILE;

REPEAT
  -- statements
UNTIL condition
END REPEAT;

LOOP
  -- statements
  IF condition THEN LEAVE label; END IF;
END LOOP;

關鍵差異 4：提前退出 — LEAVE 標籤替代 RETURN

CREATE PROCEDURE sp_login(...)
LABEL_PROC: BEGIN
  -- ...
  IF v_uid IS NULL THEN
    SELECT 2 AS ErrorCode;
    LEAVE LABEL_PROC;  -- 相當於 RETURN
  END IF;
  -- ...
END LABEL_PROC //

關鍵差異 5：動態 SQL — PREPARE/EXECUTE/DEALLOCATE

CREATE PROCEDURE sp_dynamic(IN p_hash CHAR(40))
BEGIN
  DECLARE v_sql VARCHAR(200);
  SET v_sql = CONCAT('SELECT filedir FROM resources WHERE hashcode = ''', p_hash, ''' LIMIT 1');
  SET @sql = v_sql;          -- 用戶變量 (@) 傳給 PREPARE
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //

注意：PREPARE 只接受用戶變量 @var，不能直接用局部變量 v_var。

完整實戰範例：分頁存儲過程

DELIMITER $$
CREATE PROCEDURE `MyPage`(
  IN tableName VARCHAR(100),
  IN fieldsName VARCHAR(100),
  IN pageIndex INT,
  IN pageSize INT,
  IN sortName VARCHAR(500),
  IN strWhere VARCHAR(500)
)
BEGIN
  DECLARE fieldlist VARCHAR(200);
  SET fieldlist = IF(fieldsName='' OR fieldsName IS NULL, '*', fieldsName);

  SET @strSQL = CONCAT('SELECT ', fieldlist, ' FROM ', tableName);
  IF strWhere != '' AND strWhere IS NOT NULL THEN
    SET @strSQL = CONCAT(@strSQL, ' WHERE ', strWhere);
  END IF;
  IF sortName != '' AND sortName IS NOT NULL THEN
    SET @strSQL = CONCAT(@strSQL, ' ORDER BY ', sortName);
  END IF;
  SET @strSQL = CONCAT(@strSQL, ' LIMIT ', (pageIndex-1)*pageSize, ',', pageSize);

  PREPARE stmt FROM @strSQL;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;

遷移檢查清單

• 分隔符處理
• DECLARE 置頂、SELECT ... INTO 賦值
• IF/ELSEIF/ELSE/END IF 結構
• LEAVE label 替代 RETURN
• 動態 SQL 用 @user_var + PREPARE/EXECUTE
• 錯誤處理：DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ...