MySQL 存儲過程編程指南：變量、參數、流程控制、游標、異常處理與數據庫交互全解

一、Hello World

DELIMITER $$
DROP PROCEDURE IF EXISTS `HelloWorld`$$
CREATE PROCEDURE `HelloWorld`()
BEGIN
  SELECT 'Hello World';
END$$
DELIMITER ;

二、變量聲明與賦值

DECLARE my_int INT DEFAULT 0;
DECLARE my_decimal NUMERIC(8,2) DEFAULT 0.00;
DECLARE my_str VARCHAR(30) DEFAULT 'Hello';
DECLARE my_date DATE DEFAULT '2023-01-01';
SET my_int = 20;
SET my_decimal = POW(my_int, 3);  -- 可用函數表達式

• DECLARE 僅能在 BEGIN ... END 塊開頭；
• 命名規則同列名，區分大小寫（取決於 lower_case_table_names）。

三、參數模式

默認 IN。函數**只能用 IN**。

示例：平方根輸出

CREATE PROCEDURE `my_sqrt`(IN input_number INT, OUT out_number FLOAT)
BEGIN
  SET out_number = SQRT(input_number);
END;
-- 調用
CALL my_sqrt(12, @result);
SELECT @result;  -- 3.464...

四、流程控制

4.1 IF-ELSEIF-ELSE

IF normal_price > 1000 THEN
  SET discount = 0.8;
ELSEIF normal_price > 500 THEN
  SET discount = 0.9;
ELSE
  SET discount = 1.0;
END IF;

4.2 三種循環

示例：計數到 10

CREATE PROCEDURE `simple_loop`()
BEGIN
  DECLARE counter INT DEFAULT 0;
  my_loop: LOOP
    SET counter = counter + 1;
    IF counter = 10 THEN LEAVE my_loop; END IF;
  END LOOP my_loop;
  SELECT 'I can count to 10';
END;

五、異常處理

5.1 NOT FOUND 處理（游標必備）

DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

防止 FETCH 空結果導致過程終止。

5.2 SQL 異常處理（如唯一鍵衝突）

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @msg = MESSAGE_TEXT;
  -- 記錄日志、設置標誌、繼續執行
END;

CONTINUE 繼續後續語句，EXIT 退出當前 BEGIN...END 塊。

六、與數據庫交互的四種模式

1. 單行結果賦值：SELECT col INTO var FROM t WHERE ...；
2. 游標迭代結果集：聲明 → 打開 → 循環 FETCH → 關閉；
3. 直接返回結果集給調用者：過程中直接 SELECT ...，無 INTO；
4. 執行無結果集 DML：INSERT/UPDATE/DELETE 直接寫在過程中。

6.1 SELECT INTO 示例

CREATE PROCEDURE `customer_sales`(IN in_customer_id INT)
READS SQL DATA
BEGIN
  DECLARE total_sales NUMERIC(8,2);
  SELECT SUM(sale_value) INTO total_sales FROM sales WHERE customer_id = in_customer_id;
  SELECT CONCAT('Total sales for ', in_customer_id, ' is ', total_sales);
END;

6.2 游標完整示例

CREATE PROCEDURE `cursor_example`()
READS SQL DATA
BEGIN
  DECLARE l_emp_id INT;
  DECLARE l_salary NUMERIC(8,2);
  DECLARE l_dept_id INT;
  DECLARE done INT DEFAULT 0;
  
  DECLARE cur CURSOR FOR SELECT employee_id, salary, department_id FROM employees;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  
  OPEN cur;
  emp_loop: LOOP
    FETCH cur INTO l_emp_id, l_salary, l_dept_id;
    IF done = 1 THEN LEAVE emp_loop; END IF;
    -- 處理每行邏輯
  END LOOP emp_loop;
  CLOSE cur;
END;

七、觸發器基礎

CREATE TRIGGER `t3_trigger` AFTER INSERT ON `t3` FOR EACH ROW
BEGIN
  INSERT INTO t4 SET id = NEW.id;
END;

• 觸發時機：BEFORE/AFTER；事件：INSERT/UPDATE/DELETE；
• NEW 代表新行，OLD 代表舊行（UPDATE/DELETE 可用）；
• 觸發器內**不能調用事務控制語句**，不能返回結果集。

八、最佳實踐清單

1. 盡量用 **單條 SQL / CTE / View** 替代過程，減少邏輯下沉；
2. 過程中**顯式事務**控制提交粒度，避免自動提交產生大量 Binlog；
3. 游標處理大結果集時，**分批 FETCH** 或改用應用層流式讀取；
4. 異常處理**記錄診斷信息**，不要靜默吞噬；
5. 命名規範：sp_業務_動作、fn_業務_計算、trg_表_時機_事件。