存儲過程結構

CREATE PROCEDURE `sp_name` (
  IN  param1 INT,           -- 輸入
  OUT param2 VARCHAR(100),  -- 輸出
  INOUT param3 DATETIME     -- 輸入輸出
)
BEGIN
  -- 宣告區
  DECLARE var1 INT DEFAULT 0;
  DECLARE var2 VARCHAR(50) DEFAULT (SELECT name FROM t WHERE id=1);
  
  -- 主體
  IF param1 > 0 THEN
    SET var1 = param1 * 2;
  ELSE
    SET var1 = 0;
  END IF;
  
  SELECT COUNT(*) INTO param2 FROM some_table;
  SET param3 = NOW();
END

參數模式詳解

模式	方向	呼叫端可見	預設值
IN	呼叫者 → 過程	否	可設 DEFAULT
OUT	過程 → 呼叫者	是 (初始為 NULL)	不可設 DEFAULT
INOUT	雙向	是	可設 DEFAULT

流程控制語句

-- IF-ELSEIF-ELSE
IF condition THEN stmt_list
[ELSEIF condition THEN stmt_list] ...
[ELSE stmt_list]
END IF;

-- CASE (兩種形式)
CASE case_value
  WHEN when_value THEN stmt_list
  [WHEN ...] [ELSE stmt_list]
END CASE;

CASE
  WHEN condition THEN stmt_list
  [WHEN ...] [ELSE stmt_list]
END CASE;

-- WHILE
WHILE condition DO stmt_list END WHILE;

-- REPEAT (至少執行一次)
REPEAT stmt_list UNTIL condition END REPEAT;

-- LOOP + LEAVE/ITERATE
label: LOOP
  IF condition THEN LEAVE label; END IF;
  stmt_list;
END LOOP label;

變量賦值

SET var = expr;
SET var := expr;
SELECT col INTO var FROM t WHERE ...;  -- 單行單列
SELECT col1, col2 INTO var1, var2 FROM t WHERE ...;  -- 多列

游標 (Cursor)

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

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

錯誤處理

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @msg = MESSAGE_TEXT;
  INSERT INTO error_log (sqlstate, msg) VALUES (@sqlstate, @msg);
  ROLLBACK;
END;

DECLARE EXIT HANDLER FOR SQLWARNING
BEGIN
  -- 警告處理
END;

動態 SQL (Prepared Statement)

SET @sql = CONCAT('SELECT * FROM ', @table_name, ' WHERE id = ?');
PREPARE stmt FROM @sql;
SET @id = 123;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;

函數 (Function) 差異

• CREATE FUNCTION 必須 RETURNS type 且包含 RETURN
• 函數內不能執行修改資料的語句 (INSERT/UPDATE/DELETE/DDL)，除非宣告 NOT DETERMINISTIC 或 MODIFIES SQL DATA (仍受限)
• 函數可在 SQL 表達式中呼叫：SELECT my_func(col) FROM t

呼叫與管理

-- 呼叫存儲過程
CALL sp_name(1, @out_var, @inout_var);
SELECT @out_var, @inout_var;

-- 查看定義
SHOW CREATE PROCEDURE sp_name\G
SHOW PROCEDURE STATUS WHERE Db = 'mydb';

-- 刪除
DROP PROCEDURE IF EXISTS sp_name;

效能與安全提醒

• 存儲過程編譯緩存 (存儲過程緩存)，但不緩存執行計劃 (每次執行重新優化)
• 避免在存儲過程中大量迴圈單行操作，改用集合操作 (INSERT ... SELECT)
• 權限：GRANT EXECUTE ON PROCEDURE db.sp TO user@host
• 避免業務邏輯過度下沉 DB，保持「薄資料庫、厚應用」