您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 存儲過程與函數完整指南：從創建到變量、異常處理實戰

編輯：MySQL 綜合教學

MySQL 存儲過程與函數完整指南：從創建到變量、異常處理實戰

存儲過程與函數將業務邏輯下沉至資料庫層，可減少網絡交互、提升效能、封裝複雜邏輯。本文系統講解創建語法、參數模式、變量使用、流程控制、異常處理及最佳實踐。

一、存儲過程 (PROCEDURE)

1. 創建語法

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

-- 參數模式：IN (輸入)、OUT (輸出)、INOUT (雙向)
-- characteristic: LANGUAGE SQL, DETERMINISTIC, {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}, SQL SECURITY {DEFINER | INVOKER}, COMMENT '...'

-- 範例：統計部門員工數
DELIMITER $$
CREATE PROCEDURE sp_count_emp_by_dept(
  IN p_dept_id INT,
  OUT p_cnt INT
)
READS SQL DATA
BEGIN
  SELECT COUNT(*) INTO p_cnt FROM employee WHERE d_id = p_dept_id;
END$$
DELIMITER ;

2. 調用與查看

-- 調用
SET @cnt = 0;
CALL sp_count_emp_by_dept(1, @cnt);
SELECT @cnt;

-- 查看定義
SHOW CREATE PROCEDURE sp_count_emp_by_dept;
SHOW PROCEDURE STATUS WHERE Name = 'sp_count_emp_by_dept';

二、存儲函數 (FUNCTION)

1. 創建語法 (必須指定 RETURNS)

CREATE FUNCTION fn_name ([func_parameter[,...]])
  RETURNS type
  [characteristic ...] routine_body

-- 範例：根據員工編號查姓名
DELIMITER $$
CREATE FUNCTION fn_get_emp_name(p_emp_id INT)
RETURNS VARCHAR(20)
READS SQL DATA
BEGIN
  RETURN (SELECT name FROM employee WHERE num = p_emp_id);
END$$
DELIMITER ;

2. 調用方式 (像內建函數一樣)

SELECT fn_get_emp_name(1001);
SELECT id, fn_get_emp_name(id) FROM employee WHERE d_id = 1;

三、變量使用

類型	聲明/賦值	作用域	示例
局部變量	DECLARE var_name type [DEFAULT val]; SET var = expr; SELECT col INTO var FROM ...	BEGIN...END 區塊	DECLARE v_cnt INT DEFAULT 0;
會話變量	SET @var = expr; SELECT @var := expr	整個連線會話	SET @total = 0;
系統變量	@@global.var / @@session.var	全局/會話	@@sql_mode

四、流程控制語句

-- IF 分支
IF condition THEN ... ELSEIF ... ELSE ... END IF;

-- CASE 分支
CASE expr WHEN val1 THEN ... WHEN val2 THEN ... ELSE ... END CASE;
CASE WHEN condition THEN ... ELSE ... END CASE;

-- 迴圈
WHILE condition DO ... END WHILE;
REPEAT ... UNTIL condition END REPEAT;
LOOP ... END LOOP;  -- 需 LEAVE 跳出

-- 標籤與跳出
my_loop: LOOP
  IF condition THEN LEAVE my_loop; END IF;
END LOOP;

五、異常處理 (DECLARE HANDLER)

-- 捕獲重複鍵錯誤
DECLARE EXIT HANDLER FOR 1062
  SET @ret = -1;

-- 捕獲所有 SQL 異常並回滾
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  ROLLBACK;
  SET @ret = -2;
END;

-- 命名條件提升可讀性
DECLARE dup_key CONDITION FOR 1062;
DECLARE EXIT HANDLER FOR dup_key SET @ret = -1;

六、完整實戰範例：轉帳交易

DELIMITER $$
CREATE PROCEDURE sp_transfer(
  IN p_from_acc VARCHAR(30),
  IN p_to_acc VARCHAR(30),
  IN p_amt DECIMAL(12,2),
  OUT p_code INT,
  OUT p_msg VARCHAR(100)
)
MODIFIES SQL DATA
BEGIN
  DECLARE v_from_bal DECIMAL(12,2);
  DECLARE insufficient_funds CONDITION FOR SQLSTATE '45000';

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_code = -1;
    SET p_msg = '系統異常，轉帳失敗';
  END;

  DECLARE EXIT HANDLER FOR insufficient_funds
  BEGIN
    ROLLBACK;
    SET p_code = -2;
    SET p_msg = '餘額不足';
  END;

  START TRANSACTION;

  -- 查詢轉出帳戶餘額 (加鎖)
  SELECT balance INTO v_from_bal FROM accounts WHERE acc_no = p_from_acc FOR UPDATE;

  IF v_from_bal < p_amt THEN
    SIGNAL insufficient_funds;
  END IF;

  UPDATE accounts SET balance = balance - p_amt WHERE acc_no = p_from_acc;
  UPDATE accounts SET balance = balance + p_amt WHERE acc_no = p_to_acc;

  COMMIT;
  SET p_code = 1;
  SET p_msg = '轉帳成功';
END$$
DELIMITER ;