MySQL 21天學習計劃 - 第十三天：存儲過程與函數詳情 - 存儲過程,SQL,MySQL,AI寫作,AIGC 愛看C語言的BK 博客

今天我們將進入 MySQL 數據庫編程的核心部分——存儲過程與函數。如果你之前學過其他編程語言，你可以把存儲過程理解為數據庫中的"函數"或"子程序"，它們能幫你把複雜的 SQL 邏輯封裝起來，實現代碼複用和權限控制。想象一下，當你需要反覆執行一段包含十幾行 SQL 的業務邏輯時，存儲過程能讓你像調用函數一樣簡單地完成這一切！

存儲過程基礎：從創建到調用

存儲過程（Stored Procedure）是一組預編譯的 SQL 語句集合，它像一個"數據庫腳本"，可以接收參數、執行復雜邏輯並返回結果。與直接在客户端執行 SQL 相比，存儲過程有三個明顯優勢：執行速度更快（預編譯後存儲在服務器）、減少網絡傳輸（只需傳輸調用命令）、提高安全性（可控制執行權限而不暴露具體 SQL）。

創建存儲過程的基本語法如下：

複製

DELIMITER //  -- 修改語句結束符為//（避免與存儲過程中的分號衝突）
CREATE PROCEDURE 過程名(參數列表)
BEGIN
    -- SQL 語句集合
END //
DELIMITER ;  -- 恢復語句結束符為分號

讓我們通過一個實際案例來理解。假設我們需要創建一個存儲過程，用於查詢指定學生的成績並計算平均分。以下是完整的創建和調用過程：

MySQL 21天學習計劃 - 第十三天：存儲過程與函數_SQL

這個示例展示了帶 INOUT 參數的存儲過程創建過程。關鍵步驟解析：

使用 DELIMITER // 臨時修改結束符
通過 CREATE PROCEDURE 定義存儲過程，參數 p_inout 為 INOUT 類型
在 BEGIN...END 塊中編寫業務邏輯（這裏是修改參數值並查詢）
調用時需通過用户變量（以 @ 開頭）傳遞參數

調用存儲過程的語法非常簡單：CALL 過程名(參數值);。對於需要返回結果的存儲過程，可能需要配合 SELECT 語句或輸出參數使用。

參數類型詳解：IN、OUT與INOUT的區別

存儲過程的強大之處在於它能像函數一樣接收和返回參數。MySQL 支持三種參數類型，初學者常混淆它們的用法，這裏用"快遞"的例子幫你記憶：

IN 參數：最常用的參數類型，類似"寄給存儲過程的快遞"。調用時必須提供具體值，存儲過程內部可以讀取但不能修改它。例如：

複製

CREATE PROCEDURE GetStudentGrade(IN student_id INT)
BEGIN
    SELECT * FROM scores WHERE sid = student_id;
END;

OUT 參數：類似"存儲過程寄給你的快遞"。調用時需傳遞變量（不能是具體值），存儲過程會修改這個變量的值並返回。例如統計學生總數：

複製

CREATE PROCEDURE CountStudents(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM students;
END;

-- 調用方式
SET @num = 0;
CALL CountStudents(@num);
SELECT @num;  -- 輸出學生總數

INOUT 參數：雙向傳遞的"快遞箱"，既可以傳入值，也會返回修改後的值。前面的示例中已經展示過，它適合需要"輸入初始值並獲取計算結果"的場景，如累加計算、狀態切換等。

💡 注意：調用帶 OUT 或 INOUT 參數的存儲過程時，必須使用用户變量（以 @ 開頭）接收結果，不能直接傳遞數字或字符串。

自定義函數：與存儲過程的異同

除了存儲過程，MySQL 還允許創建自定義函數（User-Defined Function, UDF）。函數與存儲過程類似，但有兩個關鍵區別：函數必須返回單個值，且可以在 SQL 語句中直接使用（就像內置函數一樣）。

創建函數的基本語法：

複製

CREATE FUNCTION 函數名(參數列表) RETURNS 返回類型
BEGIN
    -- 函數體（必須包含 RETURN 語句）
END;

舉個實用的例子，創建一個計算學生平均分的函數：

複製

CREATE FUNCTION CalculateAvg(sid INT) RETURNS DECIMAL(5,2)
BEGIN
    DECLARE avg_score DECIMAL(5,2);
    SELECT AVG(score) INTO avg_score FROM scores WHERE student_id = sid;
    RETURN avg_score;
END;

-- 使用方式（可直接在 SQL 中調用）
SELECT name, CalculateAvg(id) AS average FROM students;

函數的參數類型比較簡單，默認都是 IN 類型（不支持 OUT 和 INOUT）。返回類型必須在 RETURNS 子句中明確指定，且函數體中必須有 RETURN 語句。

⚠️ 安全提示：創建函數可能需要特殊權限。如果出現 This function has none of DETERMINISTIC... 錯誤，需要先執行 SET GLOBAL log_bin_trust_function_creators = 1;（僅測試環境使用）。

流程控制語句：讓存儲過程"聰明"起來

沒有流程控制的存儲過程就像沒有靈魂的腳本。MySQL 提供了豐富的流程控制語句，讓你能在存儲過程中實現條件判斷、循環等複雜邏輯。

IF 條件判斷

最常用的條件語句，語法類似其他編程語言：

複製

IF 條件 THEN
    -- 滿足條件執行的語句
ELSEIF 條件 THEN
    -- 滿足第二個條件執行的語句
ELSE
    -- 所有條件都不滿足時執行
END IF;

MySQL 21天學習計劃 - 第十三天：存儲過程與函數_MySQL_02

這個流程圖展示了 IF 語句的基本邏輯：當條件為 true 時執行指定語句，否則進入循環判斷。實際使用時，我們可以結合學生成績評級的例子：

複製

CREATE PROCEDURE GetGradeLevel(IN score INT, OUT level VARCHAR(10))
BEGIN
    IF score >= 90 THEN
        SET level = '優秀';
    ELSEIF score >= 70 THEN
        SET level = '良好';
    ELSEIF score >= 60 THEN
        SET level = '及格';
    ELSE
        SET level = '不及格';
    END IF;
END;

循環結構

對於需要重複執行的邏輯，MySQL 提供了三種循環：

WHILE 循環（先判斷後執行）：

複製

DECLARE i INT DEFAULT 1;
WHILE i <= 10 DO
    INSERT INTO numbers VALUES(i);
    SET i = i + 1;
END WHILE;

REPEAT 循環（先執行後判斷，至少執行一次）：

複製

DECLARE i INT DEFAULT 1;
REPEAT
    INSERT INTO logs VALUES(NOW());
    SET i = i + 1;
UNTIL i > 5 END REPEAT;

LOOP 循環（無限循環，需配合 LEAVE 退出）：

複製

DECLARE i INT DEFAULT 1;
my_loop: LOOP  -- my_loop 是循環標籤
    INSERT INTO test VALUES(i);
    SET i = i + 1;
    IF i > 3 THEN
        LEAVE my_loop;  -- 退出循環
    END IF;
END LOOP;

存儲過程調用流程與權限管理

存儲過程的執行涉及到 MySQL 服務器內部的多個環節，瞭解其調用流程有助於排查問題和優化性能：

語法檢查：服務器首先驗證調用語句的語法正確性
權限驗證：檢查當前用户是否有執行該存儲過程的權限
參數傳遞：將參數值傳遞給存儲過程內部變量
執行計劃：如果存儲過程是首次執行，會生成執行計劃並緩存
結果返回：將執行結果通過 OUT 參數、返回值或結果集返回給客户端

🔒 權限控制：可以通過 GRANT EXECUTE ON PROCEDURE 過程名 TO '用户'@'主機'; 授予執行權限，這樣即使用户沒有表的查詢權限，也能通過存儲過程獲取數據，提高了數據安全性。

查看和刪除存儲過程/函數的常用命令：

複製

-- 查看所有存儲過程
SHOW PROCEDURE STATUS;

-- 查看函數定義
SHOW CREATE FUNCTION 函數名;

-- 刪除存儲過程
DROP PROCEDURE IF EXISTS 過程名;

-- 刪除函數
DROP FUNCTION IF EXISTS 函數名;

實戰任務：學生成績管理系統

現在讓我們通過一個綜合案例鞏固今天所學。假設我們有一個學生成績表 scores（包含 student_id, subject, score 字段），請完成以下任務：

基礎任務：

創建存儲過程 AddScore，接收學生 ID、科目和分數三個 IN 參數，向 scores 表插入新成績
創建函數 GetTotalScore，接收學生 ID，返回該學生的總分

進階任務：

創建帶 INOUT 參數的存儲過程 UpdateAndReturn，實現"更新指定學生的某科成績並返回更新前的分數"
使用流程控制語句創建存儲過程 GradeDistribution，統計各分數段（90+, 80-89, 70-79, 60-69, <60）的學生人數

💡 提示：進階任務 4 需要使用多個 OUT 參數分別返回各分數段人數，或通過臨時表返回結果集。

任務參考答案：

複製

-- 基礎任務1：添加成績的存儲過程
DELIMITER //
CREATE PROCEDURE AddScore(IN sid INT, IN subj VARCHAR(20), IN s INT)
BEGIN
    INSERT INTO scores(student_id, subject, score) VALUES(sid, subj, s);
END //
DELIMITER ;

-- 基礎任務2：計算總分的函數
CREATE FUNCTION GetTotalScore(sid INT) RETURNS INT
BEGIN
    DECLARE total INT;
    SELECT SUM(score) INTO total FROM scores WHERE student_id = sid;
    RETURN total;
END;

總結與行業應用場景

今天我們學習了存儲過程和函數的創建、參數傳遞、流程控制等核心知識點。這些工具在實際開發中有廣泛應用：

電商系統：訂單狀態流轉、庫存扣減等複雜業務邏輯
報表生成：定期統計銷售額、用户活躍度等數據
數據遷移：批量處理歷史數據，轉換格式或清洗髒數據
權限控制：在不暴露表結構的情況下，允許第三方系統訪問數據

但需要注意，存儲過程也有缺點：移植性差（不同數據庫語法差異大）、調試困難（不如應用程序代碼調試方便）。實際項目中，建議將簡單邏輯用存儲過程實現，複雜業務邏輯還是放在應用層處理。

明天我們將學習觸發器和事件調度器，繼續探索 MySQL 的自動化數據處理能力。思考一下：存儲過程、函數和觸發器三者有什麼區別？它們分別適合解決什麼問題？帶着這個問題開始明天的學習吧！

愛看C語言的BK 博客

愛看C語言的BK 博客

博客 / 詳情