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

MySQL 存儲過程完整指南：從入門到實戰應用

編輯：MySQL 綜合教學

MySQL 存儲過程完整指南：從入門到實戰應用

MySQL 存儲過程是資料庫開發中極具威力的功能，它允許將一組 SQL 語句封裝為可重複調用的程式單元，不僅能提升效能，還能增強安全性與維護性。本文將系統性地介紹存儲過程的核心概念、語法規則、最佳實踐以及常見應用場景。

什麼是存儲過程？

存儲過程是一組為了完成特定功能的 SQL 語句集合，經過編譯後存儲在資料庫中。用戶通過指定存儲過程的名稱並給出參數來調用執行。與單條 SQL 語句相比，存儲過程具有以下顯著優勢：

效能提升：減少網路傳輸開銷，經過預編譯優化執行計劃
安全性增強：通過權限控制限制直接訪問表，防止 SQL 注入
代碼復用：邏輯封裝在資料庫層，多應用共享
維護便利：修改業務邏輯只需更新存儲過程，無需重新部署應用

基本語法結構

CREATE PROCEDURE procedure_name ([IN|OUT|INOUT] parameter_name data_type, ...)
BEGIN
    -- SQL 語句塊
    DECLARE variable_name data_type [DEFAULT value];
    -- 邏輯控制：IF, CASE, LOOP, WHILE, REPEAT
    -- 錯誤處理：DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
END

參數模式詳解

模式	說明	用途
IN	輸入參數（預設）	傳遞值給存儲過程，內部修改不影響外部
OUT	輸出參數	返回值給調用者，初始為 NULL
INOUT	輸入輸出參數	既可傳入值，也可返回修改後的值

實戰範例：員工薪資調整存儲過程

DELIMITER //
CREATE PROCEDURE sp_adjust_salary(
    IN p_emp_id INT,
    IN p_percentage DECIMAL(5,2),
    OUT p_new_salary DECIMAL(10,2),
    OUT p_result VARCHAR(100)
)
BEGIN
    DECLARE v_current_salary DECIMAL(10,2);
    DECLARE v_dept_id INT;
    
    -- 獲取當前薪資與部門
    SELECT salary, department_id INTO v_current_salary, v_dept_id
    FROM employees WHERE employee_id = p_emp_id;
    
    IF v_current_salary IS NULL THEN
        SET p_result = '員工不存在';
        SET p_new_salary = 0;
    ELSE
        -- 計算新薪資
        SET p_new_salary = v_current_salary * (1 + p_percentage/100);
        
        -- 更新薪資
        UPDATE employees SET salary = p_new_salary WHERE employee_id = p_emp_id;
        
        -- 記錄調整日誌
        INSERT INTO salary_adjustments (employee_id, old_salary, new_salary, adjust_date, percentage)
        VALUES (p_emp_id, v_current_salary, p_new_salary, NOW(), p_percentage);
        
        SET p_result = '調整成功';
    END IF;
END //
DELIMITER ;

調用方式

-- 調用並獲取輸出參數
CALL sp_adjust_salary(1001, 10.00, @new_sal, @result);
SELECT @new_sal AS new_salary, @result AS result;

進階特性與最佳實踐

1. 條件處理與錯誤捕獲

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    SET p_result = '執行失敗：' + GET DIAGNOSTICS CONDITION 1 @msg = MESSAGE_TEXT;
END;

2. 動態 SQL 構建

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

3. 游標處理結果集

DECLARE cur CURSOR FOR SELECT id, name FROM users WHERE status = 'active';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
    FETCH cur INTO v_id, v_name;
    IF done THEN LEAVE read_loop; END IF;
    -- 處理每一行
END LOOP;
CLOSE cur;