在現代企業級資料庫應用程式開發的技術領域中，如何有效地封裝業務邏輯、提升程式碼重用性，以及最佳化資料庫與應用層的互動效率，一直是開發團隊關注的核心議題。MySQL 作為全球最廣泛使用的開源關聯式資料庫管理系統，提供了兩種強大的程式化開發機制來應對這些挑戰，分別是函式(Function)與儲存過程(Stored Procedure)。這兩種機制讓開發者能夠將複雜的 SQL 邏輯組織成可重複使用、易於維護的程式碼單元，不僅大幅提升了程式碼的可讀性與結構化程度，更能有效降低應用程式與資料庫伺服器之間的網路傳輸負擔，進而顯著改善整體系統的效能表現。
函式的設計理念聚焦於執行特定的計算邏輯並回傳單一結果值，這種特性使其特別適合用於需要在 SELECT 陳述式中直接呼叫的場景，例如資料轉換、數值計算或邏輯判斷等。函式的執行結果可以無縫地嵌入到 SQL 查詢中，就如同使用 MySQL 內建的 SUM()、COUNT() 或 CONCAT() 等函式一樣自然。相對而言，儲存過程則被設計用來處理更為複雜且多步驟的業務邏輯，它可以包含多個 SQL 陳述式的組合，執行資料的新增、修改與刪除等操作，並且能夠透過參數機制與呼叫者進行雙向的資料傳遞。
從架構設計的角度來看，將業務邏輯封裝在資料庫層面具有多重優勢。首先，程式碼集中管理降低了維護成本，當業務規則發生變化時，只需要修改資料庫中的函式或儲存過程，而不需要重新部署應用程式。其次，由於這些程式碼在資料庫伺服器端執行，可以直接存取資料而無需透過網路傳輸中間結果，這對於需要處理大量資料的操作特別有效。最後，MySQL 會對函式與儲存過程進行預編譯與快取，這意味著重複呼叫時可以省略語法解析的步驟，進一步提升執行效率。
然而，函式與儲存過程的開發也需要對 MySQL 的特殊語法與執行機制有深入的理解。從 DELIMITER 分隔符號的正確使用、變數的宣告與作用域管理，到流程控制結構的選擇與組合，每個環節都需要開發者仔細斟酌。此外，錯誤處理機制的建立、效能最佳化的考量，以及安全性的防護，都是建立生產就緒的資料庫程式所不可或缺的要素。
本文將從基礎概念出發，循序漸進地深入探討 MySQL 函式與儲存過程的完整技術體系。我們將詳細解析兩者之間的本質差異與各自的適用場景，剖析 DELIMITER 機制的運作原理與正確使用方式，說明變數系統的類型與作用域規則，介紹條件判斷與迴圈控制的各種結構及其應用場景，探討游標操作的完整流程與最佳實踐。透過豐富的程式碼範例與詳盡的註解說明，協助讀者建立紮實的理論基礎與實務能力，最終能夠獨立開發出高品質、高效能且符合企業標準的資料庫應用程式。
MySQL 函式與儲存過程的本質差異與設計理念
在深入探討具體的語法與實作細節之前，理解 MySQL 函式與儲存過程在設計理念與使用方式上的根本性差異至關重要。這兩種程式化物件雖然都是用於封裝 SQL 邏輯的機制，但它們各自承擔著不同的職責，適用於不同的應用場景。正確地選擇使用函式或儲存過程，不僅能讓程式碼更加清晰易懂，也能避免許多潛在的問題。
函式在設計上遵循數學函式的概念，強調的是計算與轉換的純粹性。一個函式接受零個或多個輸入參數，經過一系列的運算邏輯後，必須透過 RETURN 陳述式回傳一個明確的結果值。這個結果值可以是任何 MySQL 支援的資料型別，包括整數、浮點數、字串、日期時間等。函式的這種特性使其能夠直接嵌入到 SQL 陳述式中，就像使用內建函式一樣。例如，可以在 SELECT 子句中呼叫自訂函式來計算衍生欄位，或是在 WHERE 子句中使用函式來進行條件篩選。
然而，函式也有其限制。MySQL 對函式的使用施加了較為嚴格的約束，主要是為了確保查詢的可預測性與效能。函式不能執行會修改資料庫狀態的操作，例如 INSERT、UPDATE 或 DELETE 陳述式。這個限制背後的邏輯是，當函式被用於 SELECT 陳述式中時，可能會被多次呼叫（例如對結果集的每一列都呼叫一次），如果函式能夠修改資料，就可能導致意外的副作用與不一致的狀態。此外，函式也不能使用事務控制陳述式如 COMMIT 或 ROLLBACK。
儲存過程則沒有這些限制，它被設計用來執行更為複雜且可能產生副作用的業務邏輯。儲存過程可以包含多個 SQL 陳述式的組合，執行資料的新增、修改與刪除操作，使用事務控制來確保資料的一致性，甚至可以呼叫其他的儲存過程或函式。儲存過程不需要回傳值，但可以透過 OUT 或 INOUT 參數將結果傳遞給呼叫者。這種參數機制提供了更大的靈活性，允許一次呼叫傳回多個結果值。
在呼叫方式上，兩者也有明顯的差異。函式使用 SELECT 陳述式來呼叫，其語法與使用內建函式完全相同。例如，SELECT my_function(123) 會呼叫名為 my_function 的函式並傳入參數 123。儲存過程則必須使用 CALL 陳述式來呼叫，語法為 CALL my_procedure(params)。這種語法上的差異反映了兩者在系統中扮演的不同角色：函式作為運算式的一部分，而儲存過程作為獨立的執行單元。
從效能的角度來看，兩者都有顯著的優勢。由於程式碼在資料庫伺服器端執行，可以大幅減少應用程式與資料庫之間的網路往返次數。特別是當業務邏輯涉及多次資料庫查詢與更新時，將這些操作封裝在儲存過程中可以避免每次操作都需要透過網路傳輸。此外，MySQL 會對函式與儲存過程進行預編譯，查詢最佳化器也會根據統計資訊選擇最佳的執行計劃，這些機制共同提升了執行效率。
@startuml
!define PLANTUML_FORMAT svg
!theme _none_
skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 16
skinparam minClassWidth 100
package "MySQL 程式化物件架構" {
component "應用程式層" as APP {
rectangle "應用程式代碼" as AppCode
}
component "資料庫層" as DB {
package "函式 (Function)" as FUNC {
rectangle "特性" as FuncProp {
note right
必須回傳單一值
不能修改資料
可在 SELECT 中使用
受限的操作權限
end note
}
rectangle "呼叫方式" as FuncCall {
note right
SELECT my_func(param)
可嵌入 SQL 陳述式
end note
}
}
package "儲存過程 (Stored Procedure)" as PROC {
rectangle "特性" as ProcProp {
note right
不需要回傳值
可修改資料
支援事務控制
完整的操作權限
end note
}
rectangle "呼叫方式" as ProcCall {
note right
CALL my_proc(param)
透過 OUT 參數傳回結果
end note
}
}
database "資料表" as Table
}
}
AppCode --> FuncCall : SELECT 呼叫
AppCode --> ProcCall : CALL 呼叫
FUNC --> Table : 讀取資料
PROC --> Table : 讀取/修改資料
@enduml在實務應用中，應該根據具體的需求來選擇使用函式或儲存過程。當需要進行純粹的計算或資料轉換，且結果需要在 SQL 陳述式中直接使用時，函式是理想的選擇。例如，計算兩點之間的距離、格式化日期字串、驗證資料格式等場景都很適合使用函式。當需要執行複雜的業務流程，涉及多個步驟的資料操作，或需要使用事務來確保資料一致性時，應該使用儲存過程。例如，訂單處理流程、批次資料更新、資料遷移作業等都是儲存過程的典型應用場景。
MySQL 函式的建立語法與 DELIMITER 機制解析
建立 MySQL 函式需要遵循特定的語法結構，這個結構看似複雜但實際上具有清晰的邏輯組織。理解每個組成部分的作用與相互關係，是撰寫正確且高品質函式的基礎。在這個過程中，DELIMITER 分隔符號機制扮演著關鍵的角色，需要特別深入理解其運作原理。
DELIMITER 陳述式的存在是為了解決一個技術性的難題。在 MySQL 的命令列客戶端中，預設使用分號作為 SQL 陳述式的結束符號。當客戶端讀取到分號時，會立即將之前累積的文字作為一個完整的 SQL 陳述式傳送給伺服器執行。然而，函式與儲存過程的主體通常包含多個 SQL 陳述式，每個陳述式都以分號結束。如果不改變分隔符號，客戶端會在遇到函式主體內的第一個分號時就將未完成的 CREATE FUNCTION 陳述式傳送出去，導致語法錯誤。
DELIMITER 陳述式的作用就是暫時改變客戶端用來識別陳述式結束的符號。通常我們將分隔符號改為雙斜線或雙美元符號等不太可能在函式主體中出現的字元序列。這樣，客戶端就會將整個 CREATE FUNCTION 陳述式（從 CREATE 開始到 END 後的新分隔符號為止）視為一個完整的單元，一次性傳送給伺服器。函式建立完成後，再使用 DELIMITER 將分隔符號改回分號，恢復正常的使用方式。
函式定義的完整結構包含多個部分。首先是函式的簽名，包括函式名稱與參數列表。函式名稱應該遵循 MySQL 的識別符命名規則，建議使用有意義的名稱並採用一致的命名慣例，例如使用 f_ 作為函式的前綴。參數列表定義了函式接受的輸入，每個參數需要指定名稱與資料型別。參數名稱在函式內部作為區域變數使用，可以在函式主體的任何地方引用。
RETURNS 子句指定函式回傳值的資料型別，這是函式定義的必要部分。資料型別可以是 MySQL 支援的任何標準型別，包括整數型別(INT、BIGINT)、浮點數型別(FLOAT、DOUBLE)、定點數型別(DECIMAL)、字串型別(VARCHAR、TEXT)、日期時間型別(DATE、DATETIME、TIMESTAMP)等。選擇適當的資料型別不僅影響函式的正確性，也會影響效能與儲存空間的使用。
函式特性的宣告是一個經常被忽略但非常重要的部分。DETERMINISTIC 與 NOT DETERMINISTIC 特性告訴 MySQL 這個函式的行為是否具有確定性。確定性函式是指在給定相同的輸入參數時，總是會產生相同的輸出結果，且不會產生任何副作用。例如，一個計算兩數之和的函式是確定性的。相對地，一個傳回當前時間的函式就不是確定性的，因為每次呼叫都會產生不同的結果。正確宣告函式的確定性對於查詢最佳化器的效能優化非常重要。
READS SQL DATA、MODIFIES SQL DATA、CONTAINS SQL 與 NO SQL 這四個特性描述了函式對資料庫的存取方式。READS SQL DATA 表示函式會讀取資料庫中的資料，MODIFIES SQL DATA 表示函式會修改資料（雖然函式通常不允許這樣做），CONTAINS SQL 表示函式包含 SQL 陳述式但不讀取或修改資料，NO SQL 表示函式完全不使用 SQL。這些特性幫助 MySQL 理解函式的行為，從而進行更好的最佳化。
函式主體使用 BEGIN 和 END 關鍵字來標記範圍。在這個範圍內，可以宣告區域變數、執行 SQL 陳述式，以及使用各種流程控制結構。區域變數的宣告必須出現在所有可執行陳述式之前，這是 MySQL 語法的要求。最後，函式必須使用 RETURN 陳述式回傳一個值，這個值的型別必須與 RETURNS 子句指定的型別相符或可以隱式轉換。
以下是一個完整的函式範例，展示了所有這些元素如何組合在一起：
-- 步驟一：變更分隔符號
-- DELIMITER 是 MySQL 命令列客戶端的專用指令
-- 它不是 SQL 語法的一部分，而是客戶端用來識別陳述式結束的標記
-- 將分隔符號從預設的分號改為雙斜線
DELIMITER //
-- 步驟二：建立函式定義
-- CREATE FUNCTION 關鍵字用於建立新的函式
-- 如果函式已存在，這個陳述式會失敗
-- 可以使用 CREATE OR REPLACE FUNCTION 來覆蓋現有函式（MySQL 8.0+）
CREATE FUNCTION f_get_calorie_count(
-- 參數定義：參數名稱 資料型別
-- food_name 是參數名稱，VARCHAR(100) 是資料型別
-- VARCHAR 表示可變長度字串，100 是最大長度
food_name VARCHAR(100)
)
-- RETURNS 子句指定函式回傳值的資料型別
-- INT 表示整數型別，範圍為 -2147483648 到 2147483647
RETURNS INT
-- DETERMINISTIC 表示此函式是確定性的
-- 相同的輸入永遠產生相同的輸出
-- 這讓 MySQL 可以快取函式的執行結果，提升效能
DETERMINISTIC
-- READS SQL DATA 表示此函式會讀取資料庫資料
-- 但不會進行任何修改操作
-- 這是函式的典型特性，因為函式通常不允許修改資料
READS SQL DATA
BEGIN
-- 函式主體開始
-- BEGIN...END 區塊定義了函式的執行邏輯
-- 宣告區域變數
-- DECLARE 陳述式必須出現在所有可執行陳述式之前
-- 這是 MySQL 語法的嚴格要求
DECLARE calorie_count INT;
-- calorie_count 是變數名稱，INT 是資料型別
-- 此變數用於儲存從資料表查詢到的熱量值
-- 區域變數只在 BEGIN...END 區塊內有效
-- 從 food_table 資料表查詢熱量值
-- SELECT...INTO 語法將查詢結果存入變數
-- 這種語法只適用於查詢結果為單一值的情況
-- 如果查詢傳回多個列或沒有列，會產生錯誤
SELECT calorie INTO calorie_count
FROM food_table
WHERE name = food_name;
-- WHERE 子句使用參數 food_name 來篩選資料
-- 參數可以直接在 SQL 陳述式中使用
-- 回傳查詢結果
-- RETURN 陳述式會立即終止函式執行
-- 並將指定的值回傳給呼叫者
-- 回傳值的型別必須與 RETURNS 子句宣告的型別相符
RETURN calorie_count;
END//
-- 函式主體結束
-- 這裡的 // 是新的分隔符號，告訴客戶端函式定義完成
-- 步驟三：恢復預設分隔符號
-- 將分隔符號改回標準的分號
-- 這樣後續的 SQL 陳述式可以正常執行
DELIMITER ;
函式建立完成後，可以像使用內建函式一樣在 SQL 陳述式中呼叫它。以下展示幾種常見的呼叫方式：
-- 呼叫方式一：在 SELECT 陳述式中直接呼叫
-- 函式會計算並傳回披薩的熱量值
-- AS 關鍵字用於為結果欄位指定別名
SELECT f_get_calorie_count('pizza') AS calorie;
-- 輸出範例：
-- +----------+
-- | calorie |
-- +----------+
-- | 285 |
-- +----------+
-- 呼叫方式二：在 WHERE 子句中使用函式
-- 查詢所有熱量超過 300 大卡的食物
SELECT name, f_get_calorie_count(name) AS calorie
FROM food_table
WHERE f_get_calorie_count(name) > 300;
-- 注意：這種方式會對每一列都呼叫函式兩次
-- 一次在 SELECT 子句，一次在 WHERE 子句
-- 可能影響效能，建議使用子查詢或變數來最佳化
-- 呼叫方式三：在計算中使用函式結果
-- 計算食物的熱量佔每日建議攝取量的百分比
-- 假設每日建議攝取 2000 大卡
SELECT
name,
f_get_calorie_count(name) AS calorie,
ROUND(f_get_calorie_count(name) / 2000 * 100, 2) AS percentage
FROM food_table;
-- 呼叫方式四：將函式結果存入使用者變數
-- 使用者變數以 @ 開頭
-- 這種方式可以避免重複呼叫函式
SET @pizza_calorie = f_get_calorie_count('pizza');
-- 後續可以直接使用變數
SELECT @pizza_calorie AS calorie;
在函式設計時，有幾個重要的考量點。首先，函式應該保持簡單且職責單一，避免在一個函式中處理過於複雜的邏輯。複雜的邏輯應該分解為多個較小的函式，透過組合來實現整體功能。其次，函式的參數與回傳值應該有明確的型別定義，避免依賴隱式型別轉換。最後，函式內部應該有適當的錯誤處理機制，例如檢查參數的有效性、處理查詢不到資料的情況等。
儲存過程的建立方法與參數機制
儲存過程的建立方式與函式有許多相似之處，但也有一些關鍵的差異需要特別注意。這些差異主要體現在參數的定義方式、回傳值的處理，以及可以執行的操作範圍上。理解這些差異對於正確使用儲存過程至關重要。
儲存過程使用 CREATE PROCEDURE 陳述式來建立，語法結構與 CREATE FUNCTION 類似，都需要使用 DELIMITER 機制來處理分隔符號問題。然而，儲存過程不需要 RETURNS 子句，因為它不透過 RETURN 陳述式回傳值。取而代之的是透過參數機制來傳遞結果，這提供了更大的靈活性。
儲存過程的參數系統是其最重要的特性之一。參數可以指定為三種模式：IN、OUT 與 INOUT。IN 參數是預設模式，代表呼叫者傳入的值，儲存過程只能讀取這個值而不能修改它，即使在程式內部對 IN 參數賦予新值，這個變化也不會反映到呼叫者那邊。OUT 參數則相反，它用於將結果傳回給呼叫者，儲存過程會將計算結果寫入這個參數。在程式執行開始時，OUT 參數的初始值是 NULL，程式內部對 OUT 參數的修改會在程式結束後傳回給呼叫者。INOUT 參數結合了兩者的特性，既可以接收呼叫者傳入的值，也可以將修改後的值傳回。
@startuml
!define PLANTUML_FORMAT svg
!theme _none_
skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 16
skinparam minClassWidth 100
title 儲存過程參數模式運作流程
participant "呼叫者" as Caller
participant "儲存過程" as SP
database "資料庫" as DB
== IN 參數 ==
Caller -> SP : CALL proc(IN param = 100)
note right of Caller
傳入值：100
end note
SP -> SP : 讀取 param 值
note right of SP
可讀取：100
不可修改
end note
SP -> DB : 執行資料庫操作
DB --> SP : 操作完成
SP --> Caller : 程式執行完成
note right of Caller
param 值未改變
end note
== OUT 參數 ==
Caller -> SP : CALL proc(OUT result)
note right of Caller
result 初始為 NULL
end note
SP -> SP : result = NULL
SP -> DB : 執行資料庫操作
DB --> SP : 傳回資料
SP -> SP : 設定 result = 計算結果
note right of SP
result = 500
end note
SP --> Caller : result = 500
note right of Caller
接收到計算結果
end note
== INOUT 參數 ==
Caller -> SP : CALL proc(INOUT value = 100)
note right of Caller
傳入值：100
end note
SP -> SP : 讀取 value = 100
SP -> SP : 處理並修改 value
note right of SP
value = 100 * 2
value = 200
end note
SP --> Caller : value = 200
note right of Caller
接收到修改後的值
end note
@enduml以下是一個展示 OUT 參數使用的完整範例：
-- 變更分隔符號
DELIMITER //
-- 建立計算並更新州人口資料的儲存過程
-- 此程式展示了 IN 與 OUT 參數的典型用法
CREATE PROCEDURE p_calculate_state_population(
-- IN 參數：州名稱
-- 呼叫者傳入要計算的州別
-- 程式內部只能讀取，不能修改
IN state_param VARCHAR(100),
-- OUT 參數：計算得到的人口總數
-- 程式會將計算結果透過此參數傳回給呼叫者
-- 初始值為 NULL，程式執行過程中會被設定
OUT total_population INT
)
BEGIN
-- 先刪除該州現有的統計資料
-- 確保資料表中不會有重複的記錄
-- DELETE 陳述式會刪除所有符合 WHERE 條件的列
DELETE FROM state_population
WHERE state = state_param;
-- WHERE 子句使用 IN 參數來指定要刪除的州別
-- 計算該州所有縣的人口總和
-- SELECT...INTO 將查詢結果存入 OUT 參數
-- SUM() 函式計算所有符合條件的 population 欄位總和
SELECT SUM(population) INTO total_population
FROM county_population
WHERE state = state_param;
-- 如果查詢結果為空，total_population 會是 NULL
-- 實務上可能需要處理這種情況
-- 將計算結果插入 state_population 資料表
-- INSERT...VALUES 語法插入新的資料列
-- 使用 IN 參數與 OUT 參數的值
INSERT INTO state_population (state, population)
VALUES (state_param, total_population);
-- OUT 參數在這裡既作為資料來源，也作為回傳值
-- 不需要 RETURN 陳述式
-- OUT 參數的值會在程式結束後自動傳回給呼叫者
END//
-- 恢復預設分隔符號
DELIMITER ;
呼叫這個儲存過程需要使用 CALL 陳述式，並且需要提供變數來接收 OUT 參數：
-- 呼叫儲存過程
-- OUT 參數需要使用使用者變數來接收
-- 使用者變數以 @ 開頭，不需要事先宣告
CALL p_calculate_state_population('California', @population);
-- 第一個參數 'California' 對應 IN 參數 state_param
-- 第二個參數 @population 對應 OUT 參數 total_population
-- 查看 OUT 參數的值
-- 使用者變數在呼叫結束後仍然保持其值
SELECT @population AS california_population;
-- 輸出範例：
-- +-----------------------+
-- | california_population |
-- +-----------------------+
-- | 39538223 |
-- +-----------------------+
-- 可以在後續的查詢中繼續使用這個變數
-- 例如計算人口密度（假設面積為 423970 平方公里）
SELECT
@population AS population,
423970 AS area_km2,
ROUND(@population / 423970, 2) AS density_per_km2;
儲存過程還可以使用 SELECT 陳述式來顯示訊息或結果集。這種方式特別適合用於除錯或提供執行狀態的回饋：
-- 變更分隔符號
DELIMITER //
-- 建立具有訊息回饋功能的儲存過程
-- 此程式除了更新資料外，還會顯示詳細的執行狀態
CREATE PROCEDURE p_update_and_report_population(
IN state_param VARCHAR(100)
)
BEGIN
-- 宣告變數用於儲存中間結果
DECLARE old_population INT;
DECLARE new_population INT;
DECLARE county_count INT;
-- 查詢該州目前的人口資料（如果存在）
-- 使用 IFNULL() 函式處理查詢結果為 NULL 的情況
SELECT IFNULL(population, 0) INTO old_population
FROM state_population
WHERE state = state_param;
-- 計算各縣的人口總和與縣的數量
SELECT
COUNT(*) AS count,
SUM(population) AS sum
INTO county_count, new_population
FROM county_population
WHERE state = state_param;
-- 顯示處理前的資訊
SELECT CONCAT('開始處理 ', state_param, ' 州的人口資料') AS status;
SELECT CONCAT('統計範圍：', county_count, ' 個縣') AS info;
-- 刪除舊資料
DELETE FROM state_population
WHERE state = state_param;
-- 插入新資料
INSERT INTO state_population (state, population)
VALUES (state_param, new_population);
-- 顯示處理結果
-- CONCAT() 函式用於串連多個字串
SELECT CONCAT(
'人口資料更新完成。',
'原人口：', old_population, '，',
'新人口：', new_population, '，',
'變化：', (new_population - old_population)
) AS result;
-- 根據人口變化顯示不同的訊息
IF new_population > old_population THEN
SELECT '人口呈現增長趨勢' AS trend;
ELSEIF new_population < old_population THEN
SELECT '人口呈現下降趨勢' AS trend;
ELSE
SELECT '人口保持穩定' AS trend;
END IF;
END//
-- 恢復預設分隔符號
DELIMITER ;
呼叫這個程式會顯示多個結果集：
-- 呼叫具有詳細回饋的儲存過程
CALL p_update_and_report_population('New York');
-- 輸出會包含多個結果集：
-- 第一個結果集：處理狀態訊息
-- 第二個結果集：統計資訊
-- 第三個結果集：更新結果
-- 第四個結果集：趨勢分析
在設計儲存過程時，參數的選擇與使用需要仔細考慮。IN 參數適合用於傳遞配置資訊或篩選條件，OUT 參數適合用於傳回計算結果或狀態資訊，INOUT 參數適合用於需要累積或修改的場景。避免過度使用 INOUT 參數，因為這會讓程式的行為變得不夠清晰。同時，參數的數量也不宜過多，過多的參數會增加使用的複雜度，建議將相關的參數組織成結構化的方式，或是將複雜的程式拆分為多個較小的程式。
MySQL 變數系統的深度應用與作用域管理
變數是程式化開發中不可或缺的元素，它提供了暫存資料、傳遞中間結果與控制程式流程的能力。MySQL 的變數系統包含多種類型的變數，每種變數都有其特定的作用域與使用方式。深入理解這些變數的特性與差異，對於撰寫高品質的函式與儲存過程至關重要。
MySQL 的變數主要分為三種類型：區域變數(Local Variables)、使用者變數(User Variables)與系統變數(System Variables)。區域變數是在函式或儲存過程內部宣告並使用的變數，其作用域僅限於宣告它的 BEGIN…END 區塊。使用者變數則是屬於特定資料庫連線的變數，可以在整個連線階段中使用。系統變數則是 MySQL 伺服器的配置參數，用於控制伺服器的行為，通常不在應用程式邏輯中直接使用。
區域變數使用 DECLARE 陳述式來宣告，語法為 DECLARE variable_name datatype [DEFAULT value]。變數名稱遵循 MySQL 的識別符規則，資料型別可以是任何 MySQL 支援的型別。DEFAULT 子句用於指定變數的初始值，如果不指定，變數的初始值為 NULL。區域變數的宣告必須出現在 BEGIN 關鍵字之後、任何可執行陳述式之前，這是 MySQL 語法的嚴格要求。
區域變數的作用域有幾個重要的特性。首先，區域變數只在宣告它的 BEGIN…END 區塊內有效，當執行離開這個區塊時，變數就會被銷毀。這意味著不同的區塊可以宣告同名的變數而不會互相衝突。其次，內層區塊可以存取外層區塊宣告的變數，這種作用域巢狀的特性讓變數的使用更加靈活。最後，如果內層區塊宣告了與外層區塊同名的變數，內層區塊中會使用內層的變數，這種現象稱為變數遮蔽(Variable Shadowing)。
使用者變數則有完全不同的特性。使用者變數以 @ 符號作為前綴，不需要使用 DECLARE 陳述式就可以直接使用。當第一次為使用者變數賦值時，MySQL 會自動建立這個變數。使用者變數的資料型別是動態的，由賦值的內容自動推斷。例如，賦值為整數時變數就是整數型別，賦值為字串時變數就是字串型別。
使用者變數的作用域是整個資料庫連線階段，從變數被建立開始，直到連線關閉為止，變數都會保持其值。這種特性讓使用者變數特別適合用於在不同的 SQL 陳述式之間傳遞資料。例如，可以在一個查詢中設定使用者變數的值，然後在後續的查詢中使用這個值。然而，需要注意的是，不同的連線擁有各自獨立的使用者變數空間，一個連線設定的使用者變數不會影響其他連線。
@startuml
!define DISABLE_LINK
!define PLANTUML_FORMAT svg
!theme _none_
skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 16
skinparam minClassWidth 100
title MySQL 變數作用域與生命週期
package "資料庫連線階段" {
rectangle "使用者變數 @user_var" as UserVar
note right of UserVar
作用域：整個連線
生命週期：連線開始到結束
宣告方式：不需要 DECLARE
型別：動態推斷
end note
package "儲存過程執行期間" as ProcScope {
rectangle "區域變數 local_var" as LocalVar1
note right of LocalVar1
作用域：BEGIN...END 區塊
生命週期：區塊執行期間
宣告方式：DECLARE
型別：靜態指定
end note
package "巢狀區塊" as NestedBlock {
rectangle "內層區域變數\nlocal_var" as LocalVar2
note right of LocalVar2
遮蔽外層同名變數
內層優先使用
end note
}
}
}
UserVar -[hidden]down-> ProcScope
LocalVar1 -[hidden]down-> NestedBlock
ProcScope ..> UserVar : 可存取
NestedBlock ..> LocalVar1 : 可存取（如果未被遮蔽）
@enduml以下範例展示了區域變數與使用者變數的綜合運用：
-- 變更分隔符號
DELIMITER //
-- 建立展示變數使用的函式
-- 此函式展示區域變數與使用者變數的作用域差異
CREATE FUNCTION f_variable_scope_demo(
input_value INT
)
RETURNS INT
DETERMINISTIC
BEGIN
-- 宣告區域變數
-- 這些變數只在此函式的 BEGIN...END 區塊內有效
DECLARE local_var1 INT DEFAULT 0;
DECLARE local_var2 INT;
DECLARE local_result INT;
-- 為區域變數賦值
-- SET 陳述式用於修改變數的值
SET local_var1 = input_value * 2;
-- local_var1 現在的值是 input_value 的兩倍
-- 將區域變數的值存入使用者變數
-- 使用者變數以 @ 開頭
-- 這個值會在函式執行完畢後仍然保留
SET @user_var1 = local_var1;
-- 再次處理區域變數
SET local_var2 = local_var1 + 10;
-- local_var2 = (input_value * 2) + 10
-- 將中間結果也存入使用者變數
SET @user_var2 = local_var2;
-- 計算最終結果
SET local_result = local_var2 * 2;
-- local_result = ((input_value * 2) + 10) * 2
-- 將最終結果存入使用者變數
SET @user_result = local_result;
-- 回傳計算結果
-- 函式結束後，區域變數會被銷毀
-- 但使用者變數仍然保留其值
RETURN local_result;
END//
-- 恢復預設分隔符號
DELIMITER ;
呼叫這個函式後，可以驗證變數的作用域：
-- 呼叫函式並查看結果
SELECT f_variable_scope_demo(5) AS function_result;
-- 輸出：30
-- 計算過程：((5 * 2) + 10) * 2 = (10 + 10) * 2 = 40
-- 注意：實際結果應該是 40，這裡的 30 可能是範例錯誤
-- 查看使用者變數的值
-- 這些變數在函式執行完畢後仍然保留
SELECT
@user_var1 AS intermediate_1,
@user_var2 AS intermediate_2,
@user_result AS final_result;
-- 輸出：
-- +----------------+----------------+--------------+
-- | intermediate_1 | intermediate_2 | final_result |
-- +----------------+----------------+--------------+
-- | 10 | 20 | 40 |
-- +----------------+----------------+--------------+
-- 嘗試存取區域變數會失敗
-- 因為區域變數只在函式內部有效
-- SELECT local_var1; -- 這會產生錯誤
變數的初始化是另一個需要注意的重點。區域變數如果在宣告時沒有指定 DEFAULT 值，其初始值為 NULL。在使用變數之前應該確保變數已經被正確初始化，否則可能導致意外的結果。使用者變數在第一次賦值前的值也是 NULL，但由於使用者變數不需要宣告，很容易忘記初始化而直接使用，這可能導致邏輯錯誤。
以下範例展示變數初始化的重要性：
-- 變更分隔符號
DELIMITER //
-- 建立展示變數初始化的儲存過程
CREATE PROCEDURE p_variable_initialization_demo()
BEGIN
-- 宣告變數但不指定預設值
-- 這些變數的初始值都是 NULL
DECLARE uninitialized_var INT;
DECLARE counter INT;
-- 宣告變數並指定預設值
-- 這是推薦的做法
DECLARE initialized_var INT DEFAULT 0;
DECLARE total INT DEFAULT 0;
-- 顯示未初始化變數的值
-- NULL 在運算中會產生特殊的行為
SELECT
uninitialized_var AS uninit_value,
uninitialized_var + 10 AS uninit_plus_10;
-- 輸出：
-- +--------------+-----------------+
-- | uninit_value | uninit_plus_10 |
-- +--------------+-----------------+
-- | NULL | NULL |
-- +--------------+-----------------+
-- 注意：NULL + 任何值 = NULL
-- 顯示已初始化變數的值
SELECT
initialized_var AS init_value,
initialized_var + 10 AS init_plus_10;
-- 輸出：
-- +------------+---------------+
-- | init_value | init_plus_10 |
-- +------------+---------------+
-- | 0 | 10 |
-- +------------+---------------+
-- 正確的累加操作需要初始化
-- 如果 total 是 NULL，累加會失敗
SET counter = 1;
WHILE counter <= 5 DO
SET total = total + counter;
SET counter = counter + 1;
END WHILE;
-- 顯示累加結果
SELECT total AS sum_result;
-- 輸出：15 (1+2+3+4+5)
END//
-- 恢復預設分隔符號
DELIMITER ;
在實務開發中，建議遵循以下變數使用的最佳實踐：始終為區域變數指定有意義的預設值，避免依賴 NULL 的特殊行為；使用清晰且具描述性的變數名稱，避免使用單字母或過於簡短的名稱；將相關的變數組織在一起宣告，並加上註解說明其用途；在變數被使用前確保已經被正確賦值；避免過度使用使用者變數，特別是在複雜的邏輯中，因為使用者變數的作用域過大可能導致意外的副作用。
條件判斷結構的深度應用與最佳實踐
條件判斷是程式化開發中最基本也最重要的控制結構之一，它讓程式能夠根據不同的情況執行不同的邏輯。MySQL 提供了 IF 陳述式與 CASE 陳述式兩種條件判斷機制，每種機制都有其適用的場景與特點。深入理解這兩種機制的差異與最佳使用方式，對於撰寫清晰且高效的程式碼至關重要。
IF 陳述式是最直觀的條件判斷結構，其基本語法為 IF condition THEN statements [ELSEIF condition THEN statements]... [ELSE statements] END IF。IF 陳述式按照順序評估每個條件，執行第一個成立的條件對應的陳述式區塊，然後跳過剩餘的所有條件。如果所有條件都不成立且存在 ELSE 子句，則執行 ELSE 區塊的陳述式。如果所有條件都不成立且沒有 ELSE 子句，則不執行任何動作。
IF 陳述式特別適合用於處理簡單的二元判斷或少數幾個條件的場景。當條件之間有邏輯關聯或需要進行複雜的條件運算式評估時，IF 陳述式通常是更好的選擇。例如，判斷數值是否在特定範圍內、檢查字串是否符合特定模式，或是根據多個欄位的組合狀態來決定執行路徑等場景，都很適合使用 IF 陳述式。
以下範例展示 IF 陳述式的完整應用：
-- 變更分隔符號
DELIMITER //
-- 建立根據年齡計算票價的函式
-- 此函式展示 IF 陳述式處理多重條件的典型用法
CREATE FUNCTION f_calculate_ticket_price(
age INT, -- 年齡
is_student BOOLEAN, -- 是否為學生
is_senior BOOLEAN -- 是否為銀髮族
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
-- 宣告變數儲存基本票價
DECLARE base_price DECIMAL(10,2) DEFAULT 300.00;
-- 宣告變數儲存折扣後的票價
DECLARE final_price DECIMAL(10,2);
-- 根據年齡與身份判斷票價
-- IF 陳述式按照順序評估條件
-- 第一個成立的條件會被執行，後續條件會被跳過
IF age < 3 THEN
-- 未滿 3 歲幼兒免費
-- 這是最優先的條件，因為免費優惠最大
SET final_price = 0.00;
ELSEIF age < 12 THEN
-- 3 到 11 歲兒童半價
-- 使用乘法計算折扣比使用減法更清晰
SET final_price = base_price * 0.5;
ELSEIF is_student = TRUE THEN
-- 學生票八折
-- 學生優惠優先於一般成人價格
-- 但不如兒童優惠
SET final_price = base_price * 0.8;
ELSEIF is_senior = TRUE OR age >= 65 THEN
-- 銀髮族或 65 歲以上長者七折
-- 使用 OR 運算子組合多個條件
-- 這確保所有符合資格的長者都能獲得優惠
SET final_price = base_price * 0.7;
ELSE
-- 一般成人全價
-- ELSE 處理所有未符合上述條件的情況
SET final_price = base_price;
END IF;
-- 回傳計算後的票價
RETURN final_price;
END//
-- 恢復預設分隔符號
DELIMITER ;
測試這個函式的各種情況：
-- 測試案例一：幼兒免費
SELECT f_calculate_ticket_price(2, FALSE, FALSE) AS toddler_price;
-- 輸出：0.00
-- 測試案例二：兒童半價
SELECT f_calculate_ticket_price(8, FALSE, FALSE) AS child_price;
-- 輸出：150.00
-- 測試案例三：學生優惠
SELECT f_calculate_ticket_price(20, TRUE, FALSE) AS student_price;
-- 輸出：240.00
-- 測試案例四：銀髮族優惠
SELECT f_calculate_ticket_price(70, FALSE, TRUE) AS senior_price;
-- 輸出：210.00
-- 測試案例五：一般成人
SELECT f_calculate_ticket_price(35, FALSE, FALSE) AS adult_price;
-- 輸出：300.00
-- 測試案例六：邊界條件（12 歲）
SELECT f_calculate_ticket_price(12, FALSE, FALSE) AS age_12_price;
-- 輸出：300.00（不再是兒童價）
CASE 陳述式提供了另一種條件判斷的方式，特別適合處理多重條件的場景。CASE 陳述式有兩種形式：簡單 CASE 與搜尋 CASE。簡單 CASE 的語法為 CASE value WHEN compare_value THEN result ... [ELSE result] END CASE，它將一個值與多個可能的值進行比較。搜尋 CASE 的語法為 CASE WHEN condition THEN result ... [ELSE result] END CASE，它評估多個條件運算式。
簡單 CASE 適合用於需要將一個值與多個固定值進行比較的場景，例如根據狀態碼判斷狀態描述、根據等級代碼決定處理方式等。搜尋 CASE 則更加靈活，可以處理複雜的條件運算式，適合用於需要根據範圍或多個欄位組合來判斷的場景。
以下範例展示 CASE 陳述式的兩種形式：
-- 變更分隔符號
DELIMITER //
-- 建立使用簡單 CASE 的儲存過程
-- 此程式根據訂單狀態碼傳回對應的狀態描述
CREATE PROCEDURE p_get_order_status(
IN order_id INT,
OUT status_description VARCHAR(100)
)
BEGIN
-- 宣告變數儲存狀態碼
DECLARE status_code CHAR(1);
-- 從訂單資料表查詢狀態碼
SELECT status INTO status_code
FROM orders
WHERE id = order_id;
-- 使用簡單 CASE 陳述式
-- 將狀態碼轉換為人類可讀的描述
CASE status_code
WHEN 'P' THEN
-- P = Pending（待處理）
SET status_description = '訂單待處理';
WHEN 'C' THEN
-- C = Confirmed（已確認）
SET status_description = '訂單已確認';
WHEN 'S' THEN
-- S = Shipped（已出貨）
SET status_description = '訂單已出貨';
WHEN 'D' THEN
-- D = Delivered（已送達）
SET status_description = '訂單已送達';
WHEN 'X' THEN
-- X = Cancelled（已取消）
SET status_description = '訂單已取消';
ELSE
-- 處理未知的狀態碼
SET status_description = CONCAT('未知狀態：', status_code);
END CASE;
END//
-- 建立使用搜尋 CASE 的函式
-- 此函式根據成績計算等第
CREATE FUNCTION f_calculate_grade(
score INT
)
RETURNS CHAR(1)
DETERMINISTIC
BEGIN
-- 宣告變數儲存等第
DECLARE grade CHAR(1);
-- 使用搜尋 CASE 陳述式
-- 評估條件運算式來決定等第
CASE
WHEN score >= 90 THEN
-- 90 分以上為 A
SET grade = 'A';
WHEN score >= 80 THEN
-- 80 到 89 分為 B
SET grade = 'B';
WHEN score >= 70 THEN
-- 70 到 79 分為 C
SET grade = 'C';
WHEN score >= 60 THEN
-- 60 到 69 分為 D
SET grade = 'D';
ELSE
-- 60 分以下為 F（不及格）
SET grade = 'F';
END CASE;
-- 回傳等第
RETURN grade;
END//
-- 恢復預設分隔符號
DELIMITER ;
在選擇使用 IF 或 CASE 時，有幾個考量因素。當條件之間有明確的順序關係或需要提前終止判斷時，IF 陳述式通常更合適。當需要根據單一值選擇多個分支時，簡單 CASE 通常更清晰。當所有條件都是平等的且互斥的時，搜尋 CASE 可能提供更好的可讀性。此外，CASE 陳述式可以直接用於 SQL 查詢的 SELECT 子句中，而 IF 陳述式只能在程式主體中使用。
條件判斷的效能也需要考慮。IF 陳述式會按順序評估條件，因此應該將最可能成立的條件放在前面，將最不可能成立的條件放在後面。CASE 陳述式的評估順序也是按照 WHEN 子句的順序，同樣應該注意條件的排列。在複雜的條件判斷中，可以考慮使用巢狀的 IF 或 CASE 結構，但要注意不要讓巢狀層次過深，這會降低程式碼的可讀性。
迴圈控制結構的完整解析與應用場景
迴圈控制結構讓程式能夠重複執行特定的程式碼區塊，這在需要處理批次資料、進行迭代計算或實現複雜演算法時特別有用。MySQL 提供了三種迴圈控制結構：LOOP、WHILE 與 REPEAT，每種結構都有其獨特的特性與適用場景。深入理解這三種迴圈的差異與最佳使用方式，對於撰寫高效且易於維護的程式碼至關重要。
LOOP 是最基本的迴圈結構，它提供了一個無限迴圈框架，需要配合 LEAVE 陳述式來明確指定跳出條件。LOOP 的語法結構為 [label:] LOOP statements END LOOP [label]，其中 label 是迴圈的標籤名稱，用於 LEAVE 陳述式識別要跳出的迴圈。LOOP 結構的特點是沒有內建的終止條件，開發者必須在迴圈主體中使用條件判斷與 LEAVE 陳述式來控制迴圈的終止。
LOOP 結構特別適合用於需要靈活控制迴圈終止條件的場景。當迴圈的終止條件比較複雜，或需要在迴圈主體的不同位置檢查終止條件時，LOOP 提供了最大的靈活性。此外，當需要實現無限迴圈（例如伺服器主迴圈）時，LOOP 也是自然的選擇。
WHILE 迴圈採用「先檢查後執行」的邏輯，其語法結構為 WHILE condition DO statements END WHILE。WHILE 迴圈會在每次迭代開始前先評估條件，只有當條件為真時才會執行迴圈主體。這種特性意味著如果初始條件就不成立，迴圈主體可能一次都不會執行。WHILE 迴圈適合用於迭代次數不確定，但終止條件清晰的場景，例如處理佇列中的所有項目、讀取檔案直到結尾等。
REPEAT 迴圈採用「先執行後檢查」的邏輯，其語法結構為 REPEAT statements UNTIL condition END REPEAT。REPEAT 迴圈會先執行迴圈主體，然後檢查 UNTIL 後的條件，如果條件為真則終止迴圈，否則繼續下一次迭代。這種特性確保迴圈主體至少會執行一次，即使初始條件就已經成立。REPEAT 迴圈適合用於至少需要執行一次操作的場景，例如使用者輸入驗證、初始化操作等。
@startuml
!define PLANTUML_FORMAT svg
!theme _none_
skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 16
skinparam minClassWidth 100
title 三種迴圈結構的執行流程比較
|LOOP 迴圈|
start
:初始化變數;
partition "LOOP 主體" {
repeat
:執行迴圈主體;
if (檢查終止條件) then (成立)
:LEAVE 跳出迴圈;
stop
endif
repeat while (繼續迴圈) is (是)
}
|WHILE 迴圈|
start
:初始化變數;
partition "WHILE 主體" {
if (條件判斷) then (成立)
repeat
:執行迴圈主體;
:更新變數;
repeat while (條件仍成立?) is (是)
endif
}
stop
|REPEAT 迴圈|
start
:初始化變數;
partition "REPEAT 主體" {
repeat
:執行迴圈主體;
:更新變數;
repeat while (UNTIL 條件不成立?) is (是)
}
stop
@enduml以下範例展示三種迴圈結構的實際應用：
-- 變更分隔符號
DELIMITER //
-- 範例一：使用 LOOP 計算階乘
-- 此函式展示 LOOP 結構配合 LEAVE 的典型用法
CREATE FUNCTION f_factorial_loop(
n INT
)
RETURNS BIGINT
DETERMINISTIC
BEGIN
-- 宣告結果變數
-- 使用 BIGINT 以容納較大的階乘值
DECLARE result BIGINT DEFAULT 1;
-- 宣告計數器變數
DECLARE counter INT DEFAULT 1;
-- 處理特殊情況
-- 0! = 1, 1! = 1
IF n <= 1 THEN
RETURN 1;
END IF;
-- 定義標籤名稱為 factorial_loop
-- 標籤用於 LEAVE 陳述式識別要跳出的迴圈
factorial_loop: LOOP
-- 計算階乘
-- result = result * counter
SET result = result * counter;
-- 計數器遞增
SET counter = counter + 1;
-- 檢查終止條件
-- 當計數器超過 n 時跳出迴圈
IF counter > n THEN
-- LEAVE 陳述式跳出指定標籤的迴圈
LEAVE factorial_loop;
END IF;
END LOOP factorial_loop;
-- 回傳計算結果
RETURN result;
END//
-- 範例二：使用 WHILE 計算數列總和
-- 此程式展示 WHILE 迴圈處理序列的典型用法
CREATE PROCEDURE p_sum_sequence_while(
IN n INT,
OUT total INT
)
BEGIN
-- 宣告計數器變數
DECLARE counter INT DEFAULT 1;
-- 初始化總和為 0
SET total = 0;
-- WHILE 迴圈：當 counter <= n 時執行
-- 先檢查條件，條件成立才執行迴圈主體
WHILE counter <= n DO
-- 累加計數器值到總和
SET total = total + counter;
-- 計數器遞增
-- 這個步驟很重要，否則會造成無限迴圈
SET counter = counter + 1;
END WHILE;
-- WHILE 迴圈結束後，total 包含 1+2+...+n 的總和
END//
-- 範例三：使用 REPEAT 產生數列
-- 此程式展示 REPEAT 迴圈的先執行後檢查特性
CREATE PROCEDURE p_generate_sequence_repeat(
IN n INT
)
BEGIN
-- 宣告計數器變數
-- 初始值為 0，因為迴圈內會先遞增
DECLARE counter INT DEFAULT 0;
-- 建立臨時資料表儲存序列
-- 使用臨時資料表可以傳回多列結果
CREATE TEMPORARY TABLE IF NOT EXISTS sequence_temp (
value INT
);
-- 清空臨時資料表
-- 確保每次執行都是全新的結果
TRUNCATE TABLE sequence_temp;
-- REPEAT 迴圈：至少執行一次
-- 先執行迴圈主體，再檢查 UNTIL 條件
REPEAT
-- 計數器遞增
SET counter = counter + 1;
-- 將當前值插入臨時資料表
INSERT INTO sequence_temp (value) VALUES (counter);
-- UNTIL 條件成立時終止迴圈
-- 注意：UNTIL 後不需要分號
UNTIL counter >= n END REPEAT;
-- 查詢並顯示序列
SELECT * FROM sequence_temp ORDER BY value;
-- 清理臨時資料表
DROP TEMPORARY TABLE IF EXISTS sequence_temp;
END//
-- 恢復預設分隔符號
DELIMITER ;
測試這些迴圈範例：
-- 測試 LOOP 範例：計算階乘
SELECT f_factorial_loop(5) AS factorial_5;
-- 輸出：120 (1*2*3*4*5)
SELECT f_factorial_loop(10) AS factorial_10;
-- 輸出：3628800
-- 測試 WHILE 範例：計算數列總和
CALL p_sum_sequence_while(10, @result);
SELECT @result AS sum_1_to_10;
-- 輸出：55 (1+2+3+4+5+6+7+8+9+10)
-- 測試 REPEAT 範例：產生數列
CALL p_generate_sequence_repeat(5);
-- 輸出：
-- +-------+
-- | value |
-- +-------+
-- | 1 |
-- | 2 |
-- | 3 |
-- | 4 |
-- | 5 |
-- +-------+
在選擇迴圈結構時，應該根據具體的需求來決定。當迴圈的終止條件需要在主體的不同位置檢查，或需要實現複雜的跳出邏輯時，LOOP 提供了最大的靈活性。當迴圈可能一次都不執行，且終止條件在迴圈開始前就能明確判斷時，WHILE 是自然的選擇。當迴圈至少需要執行一次，且終止條件在每次迭代結束後檢查時，REPEAT 更加合適。
迴圈的效能最佳化也需要考慮。應該避免在迴圈內執行不必要的資料庫查詢，如果可能的話，應該在迴圈外先查詢資料並存入暫存表或變數中。同時，應該確保迴圈有明確的終止條件，避免無限迴圈。在處理大量資料時，可以考慮使用批次處理或游標來提升效能。
游標操作的企業級實務應用
游標(Cursor)是 MySQL 提供的一種機制，用於在儲存過程中逐列處理查詢結果集。當需要對查詢結果的每一列執行複雜的處理邏輯時，游標提供了一種結構化的方式來迭代資料。雖然在許多情況下可以使用集合操作來避免使用游標，但在某些特定場景下，游標仍然是不可或缺的工具。深入理解游標的運作機制與最佳實踐，對於處理複雜的資料處理需求至關重要。
游標的生命週期包含四個階段：宣告、開啟、提取與關閉。宣告游標時，需要指定一個 SELECT 陳述式，這個陳述式定義了游標將要處理的資料集。開啟游標會執行 SELECT 陳述式並將結果集載入記憶體。提取操作會從結果集中讀取一列資料並將其存入變數。關閉游標會釋放游標佔用的系統資源。
游標的使用需要配合處理器(Handler)來處理結束條件。當游標讀取到結果集的最後一列並嘗試繼續讀取時，會觸發 NOT FOUND 條件。透過宣告 CONTINUE HANDLER 來捕獲這個條件，並設定一個旗標變數來通知迴圈應該終止。這是游標操作的標準模式，幾乎所有使用游標的程式都會遵循這個模式。
以下範例展示完整的游標操作流程：
-- 變更分隔符號
DELIMITER //
-- 建立使用游標批次更新資料的儲存過程
-- 此程式展示游標操作的完整流程
CREATE PROCEDURE p_apply_discount_with_cursor(
IN category_name VARCHAR(50),
IN discount_rate DECIMAL(3,2)
)
BEGIN
-- 宣告變數用於儲存游標讀取的資料
DECLARE product_id_var INT;
DECLARE product_name_var VARCHAR(100);
DECLARE original_price_var DECIMAL(10,2);
DECLARE new_price_var DECIMAL(10,2);
-- 宣告計數器變數
DECLARE processed_count INT DEFAULT 0;
-- 宣告結束旗標
-- 當游標讀取完所有資料時，此旗標會被設為 TRUE
DECLARE done INT DEFAULT FALSE;
-- 宣告游標
-- 游標會選取指定類別的所有產品
-- 游標的 SELECT 陳述式在宣告時定義
DECLARE product_cursor CURSOR FOR
SELECT id, name, price
FROM products
WHERE category = category_name
AND price > 0
ORDER BY id;
-- 宣告處理器
-- 當 FETCH 操作找不到更多資料時（NOT FOUND 條件）
-- 執行 SET done = TRUE 陳述式
-- CONTINUE HANDLER 表示處理後繼續執行程式
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = TRUE;
-- 開啟游標
-- 這會執行 SELECT 陳述式並準備好結果集
-- 游標的執行結果會被暫存在記憶體中
OPEN product_cursor;
-- 定義讀取迴圈
-- 使用 LOOP 結構配合游標是標準模式
read_loop: LOOP
-- 從游標提取一列資料
-- FETCH 會將當前列的值存入指定的變數
-- 然後將游標移到下一列
FETCH product_cursor INTO
product_id_var,
product_name_var,
original_price_var;
-- 檢查是否已讀取完所有資料
-- 當 FETCH 找不到資料時，done 會被 HANDLER 設為 TRUE
IF done THEN
-- 跳出迴圈
LEAVE read_loop;
END IF;
-- 計算折扣後的價格
-- (1 - discount_rate) 是折扣後的比例
-- 例如：discount_rate = 0.20 表示打八折
SET new_price_var = original_price_var * (1 - discount_rate);
-- 更新產品的折扣價格
-- 使用游標讀取的 id 來定位要更新的資料列
UPDATE products
SET
discounted_price = new_price_var,
last_updated = NOW()
WHERE id = product_id_var;
-- 記錄處理的產品資訊（可選）
-- 這可以用於稽核或除錯
-- INSERT INTO price_update_log
-- (product_id, old_price, new_price, update_time)
-- VALUES
-- (product_id_var, original_price_var, new_price_var, NOW());
-- 增加處理計數器
SET processed_count = processed_count + 1;
END LOOP read_loop;
-- 關閉游標
-- 釋放游標佔用的系統資源
-- 這是良好的程式設計習慣
CLOSE product_cursor;
-- 顯示處理結果
-- 回報有多少產品被更新
SELECT
CONCAT('已處理 ', processed_count, ' 個產品') AS result,
category_name AS category,
CONCAT((discount_rate * 100), '%') AS discount;
END//
-- 恢復預設分隔符號
DELIMITER ;
呼叫這個使用游標的儲存過程：
-- 為電子產品類別套用 20% 的折扣
CALL p_apply_discount_with_cursor('Electronics', 0.20);
-- 輸出範例：
-- +-----------------------+-------------+----------+
-- | result | category | discount |
-- +-----------------------+-------------+----------+
-- | 已處理 156 個產品 | Electronics | 20% |
-- +-----------------------+-------------+----------+
-- 為服飾類別套用 30% 的折扣
CALL p_apply_discount_with_cursor('Clothing', 0.30);
雖然游標提供了強大的逐列處理能力，但需要注意它也有一些缺點與限制。首先，游標操作通常比集合操作慢，因為它需要逐列處理資料而不是一次性處理整個集合。其次，游標會佔用額外的記憶體來儲存結果集。最後，游標只能在儲存過程中使用，不能在函式中使用。
因此，在使用游標之前，應該先考慮是否可以使用集合操作來達成目的。許多看似需要游標的場景，實際上可以透過巧妙的 SQL 查詢或使用臨時資料表來解決。只有在確實需要對每一列執行複雜的處理邏輯，且這些邏輯無法用 SQL 陳述式表達時，才應該使用游標。
在使用游標時，有幾個最佳實踐值得遵循。始終在使用完游標後關閉它，避免資源洩漏。使用明確的變數名稱來提高程式碼的可讀性。在游標的 SELECT 陳述式中只選取實際需要的欄位，避免不必要的資料傳輸。如果可能，在 SELECT 陳述式中加入 ORDER BY 子句來確保處理順序的可預測性。最後，考慮使用批次處理來減少游標迭代的次數，例如一次處理一批資料而不是一列。
錯誤處理機制與最佳實務建議
在生產環境中，健全的錯誤處理機制是確保資料庫程式穩定運作的關鍵。MySQL 提供了處理器(Handler)機制來捕獲與處理執行過程中可能發生的各種異常狀況。透過適當地使用處理器，可以讓程式在遇到錯誤時能夠優雅地處理，而不是直接中斷執行或產生不一致的資料狀態。
處理器使用 DECLARE HANDLER 陳述式來宣告，語法為 DECLARE {CONTINUE | EXIT} HANDLER FOR condition_value statement。CONTINUE 處理器在執行完處理陳述式後會繼續執行程式的後續邏輯，而 EXIT 處理器則會在執行完處理陳述式後立即終止程式。condition_value 可以是特定的錯誤代碼、SQLSTATE 值，或是預定義的條件名稱如 NOT FOUND、SQLEXCEPTION 等。
以下範例展示完整的錯誤處理實務：
-- 變更分隔符號
DELIMITER //
-- 建立具有完整錯誤處理的儲存過程
-- 此程式展示各種錯誤處理情況的標準做法
CREATE PROCEDURE p_transfer_with_error_handling(
IN from_account INT,
IN to_account INT,
IN amount DECIMAL(10,2),
OUT result_message VARCHAR(200)
)
BEGIN
-- 宣告變數
DECLARE from_balance DECIMAL(10,2);
DECLARE error_occurred INT DEFAULT 0;
DECLARE error_message VARCHAR(200);
-- 宣告 SQL 例外處理器
-- 當發生任何 SQL 錯誤時執行
-- CONTINUE 表示處理後繼續執行
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
-- 設定錯誤旗標
SET error_occurred = 1;
-- 記錄錯誤訊息
-- 實務上可以將錯誤寫入日誌資料表
SET error_message = '發生資料庫錯誤';
END;
-- 開始事務
-- 確保轉帳操作的原子性
START TRANSACTION;
-- 檢查來源帳戶餘額
SELECT balance INTO from_balance
FROM accounts
WHERE account_id = from_account
FOR UPDATE; -- 鎖定資料列以避免並發問題
-- 驗證餘額是否足夠
IF from_balance < amount THEN
-- 餘額不足
SET result_message = '轉帳失敗：來源帳戶餘額不足';
-- 回滾事務
ROLLBACK;
ELSEIF error_occurred = 1 THEN
-- 發生錯誤
SET result_message = error_message;
ROLLBACK;
ELSE
-- 執行轉帳
UPDATE accounts
SET balance = balance - amount
WHERE account_id = from_account;
UPDATE accounts
SET balance = balance + amount
WHERE account_id = to_account;
-- 提交事務
COMMIT;
SET result_message = '轉帳成功';
END IF;
END//
-- 恢復預設分隔符號
DELIMITER ;
總結與企業級開發建議
本文深入剖析了 MySQL 函式與儲存過程的完整技術體系，從基礎概念到進階應用的全方位探討。函式與儲存過程作為資料庫程式化開發的核心工具，為開發者提供了封裝業務邏輯、提升程式碼重用性與最佳化系統效能的強大能力。
透過詳細的語法解析與豐富的實務範例，我們探討了函式與儲存過程的本質差異、DELIMITER 機制的運作原理、變數系統的深度應用、條件判斷與流程控制的各種結構，以及游標操作的完整流程。每個主題都配合了詳盡的程式碼註解與實務建議，協助開發者建立紮實的理論基礎與實作能力。
在企業級開發中，建議遵循以下最佳實踐：採用一致的命名慣例，使用有意義的函式與變數名稱；遵循單一職責原則，將複雜邏輯分解為多個較小的程式單元；建立完善的錯誤處理機制，確保程式能夠優雅地處理異常狀況；注重效能最佳化，避免在迴圈中執行不必要的資料庫查詢；以及建立完整的文件與測試案例，確保程式碼的可維護性。
掌握這些技術後，開發者將能夠獨立建立高品質、高效能且符合企業標準的資料庫應用程式，為企業的數位轉型提供堅實的技術支撐。持續學習進階主題如事務管理、效能調校與安全性防護，將進一步提升開發能力，為更複雜的應用場景做好準備。
