---

MySQL 存儲過程面試基礎知識總結

一、存儲過程基礎

存儲過程（Stored Procedure）是一組 SQL 語句的集合，存儲在數據庫中，通過指定名稱和參數調用執行。它主要用于提高性能、代碼復用和增強安全性。

（一）概述

1.優點

• 提高性能：存儲過程在數據庫服務器端執行，減少了網絡傳輸的次數。例如，當需要對大量數據進行復雜的查詢和更新操作時，存儲過程可以將這些操作封裝在一起，一次執行完成，大大減少了網絡通信開銷。而且存儲過程在第一次執行時會被編譯，之后的調用可以直接執行編譯后的代碼，提高了執行效率。
• 代碼復用：可以將常用的 SQL 操作封裝成存儲過程，方便在不同的應用程序和地方調用。比如，一個企業數據庫中，經常需要查詢員工的詳細信息，包括姓名、部門、工資等，將這些查詢操作封裝成存儲過程后，不同的部門在開發自己的應用程序時就可以直接調用這個存儲過程來獲取數據，避免重復編寫代碼。
• 安全性增強：可以通過存儲過程控制用戶對數據庫的訪問權限。例如，只允許用戶通過特定的存儲過程來修改數據，而不能直接對表進行修改操作，這樣可以防止用戶對數據庫進行不恰當的操作，提高數據庫的安全性。

2.缺點

• 可移植性差：不同數據庫管理系統（DBMS）的存儲過程語法有所不同。例如，SQL Server 使用 Transact - SQL（T - SQL）語言來編寫存儲過程，而 MySQL 使用自己的存儲過程語法。如果要將一個數據庫從 SQL Server 遷移到 MySQL，存儲過程可能需要重新編寫或修改。
• 調試困難：存儲過程的調試相對復雜，特別是在存儲過程比較復雜，包含多個分支和循環時。與在應用程序代碼中調試相比，數據庫端的調試工具通常沒有那么強大，很難像在高級語言（如 Java、Python）中那樣方便地設置斷點、查看變量值等。

（二）創建與調用

1.創建存儲過程

在 MySQL 中，創建存儲過程的基本語法如下：

DELIMITER $$CREATE PROCEDURE procedure_name( [ IN | OUT | INOUT ] parameter_name parameter_data_type, ... )
BEGIN-- SQL statements
END$$DELIMITER ;

• DELIMITER：MySQL 默認的語句分隔符是分號（;）。在存儲過程內部，可能會包含多個 SQL 語句，這些語句也需要用分號分隔。為了避免與存儲過程內部的分號沖突，需要將分隔符改為其他字符（如 $$）。在存儲過程定義完成后，再將分隔符改回默認的分號。
• IN、OUT、INOUT：用于指定參數的類型。IN 表示輸入參數，OUT 表示輸出參數，INOUT 表示輸入輸出參數。
• parameter_name 和 parameter_data_type：分別是參數的名稱和數據類型。例如，IN employee_id INT 表示一個名為 employee_id 的輸入參數，數據類型為整數。
• SQL statements：是存儲過程要執行的 SQL 語句，可以是查詢、插入、更新、刪除等操作。

例如，創建一個存儲過程，用于查詢員工的工資信息：

DELIMITER $$CREATE PROCEDURE GetEmployeeSalary(IN employee_id INT)
BEGINSELECT salaryFROM employeesWHERE employee_id = employee_id;
END$$DELIMITER ;

2.調用存儲過程

調用存儲過程的語法為：

CALL procedure_name(parameter1, parameter2, ...);

對于上面創建的 GetEmployeeSalary 存儲過程，調用它來查詢員工編號為 1001 的員工工資：

CALL GetEmployeeSalary(1001);

3.查看存儲過程

查看某個數據庫下面的存儲過程：

select name from mysql.proc where db='數據庫名';或者select routine_name from information_schema.routines where routine_schema='數據庫名';或者show procedure status where db='數據庫名';

在 MySQL 8.0 及更高版本中，存儲過程和函數的元數據存儲在 INFORMATION_SCHEMA.ROUTINES 表中，而不是 mysql.proc 表中。

詳細查看存儲過程：
1.SHOW CREATE PROCEDURE 語句

SHOW CREATE PROCEDURE procedure_name;

2.INFORMATION_SCHEMA 表

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'procedure_name';

4.修改存儲過程

刪除并重新創建

DROP PROCEDURE procedure_name;
CREATE PROCEDURE procedure_name ...

使用 ALTER PROCEDURE 語句（MySQL 8.0+）

ALTER PROCEDURE procedure_name ...

5.存儲過程權限管理

授予權限

GRANT EXECUTE ON procedure_name TO 'username'@'host';

撤銷權限

REVOKE EXECUTE ON procedure_name FROM 'username'@'host';

（三）參數

1.輸入參數

輸入參數是調用存儲過程時從外部傳入的參數，存儲過程內部根據這些參數來執行相應的操作。在前面的 GetEmployeeSalary 存儲過程中，employee_id 就是一個輸入參數。

2.輸出參數

輸出參數是存儲過程執行完成后返回給調用者的參數。在 MySQL 中，輸出參數需要在參數定義時加上 OUT 關鍵字。例如，創建一個存儲過程，計算兩個數的和，并將結果通過輸出參數返回：

DELIMITER $$CREATE PROCEDURE AddTwoNumbers(IN number1 INT, IN number2 INT, OUT sum INT)
BEGINSET sum = number1 + number2;
END$$DELIMITER ;

調用這個存儲過程并獲取輸出參數的值：

SET @result = 0;
CALL AddTwoNumbers(5, 3, @result);
SELECT @result AS Sum;

3.輸入輸出參數

輸入輸出參數既可以接收外部傳入的值，也可以在存儲過程執行完成后將修改后的值返回給調用者。在 MySQL 中，輸入輸出參數需要加上 INOUT 關鍵字。例如，創建一個存儲過程，將輸入的數字乘以 2，并將結果返回：

DELIMITER $$CREATE PROCEDURE DoubleNumber(INOUT number INT)
BEGINSET number = number * 2;
END$$DELIMITER ;

調用這個存儲過程：

SET @input_number = 10;
CALL DoubleNumber(@input_number);
SELECT @input_number AS Result;

（四）控制流語句

1.IF - ELSE 語句

用于條件判斷。例如，創建一個存儲過程，根據員工的工資等級（通過輸入參數傳入）來判斷是否發放獎金：

DELIMITER $$CREATE PROCEDURE CheckBonus(IN salary_grade INT)
BEGINIF salary_grade = 1 THENSELECT '發放獎金';ELSESELECT '不發放獎金';END IF;
END$$DELIMITER ;

2.WHILE 循環語句

用于循環操作。例如，創建一個存儲過程，將表中的員工工資依次增加 100，直到工資超過 10000：

DELIMITER $$CREATE PROCEDURE IncreaseSalary()
BEGINDECLARE employee_id INT DEFAULT 1;DECLARE current_salary INT;WHILE TRUE DOSELECT salary INTO current_salaryFROM employeesWHERE employee_id = employee_id;IF current_salary IS NULL THENLEAVE;END IF;IF current_salary < 10000 THENUPDATE employeesSET salary = salary + 100WHERE employee_id = employee_id;END IF;SET employee_id = employee_id + 1;END WHILE;
END$$DELIMITER ;

非常抱歉遺漏了 CASE 語句的介紹。CASE 語句在 MySQL 中用于條件判斷，類似于其他編程語言中的 switch 語句。它可以根據不同的條件執行不同的代碼塊。現在我將補充 CASE 語句的內容，并提供一個示例。

3.CASE 語句

CASE 語句用于根據不同的條件執行不同的代碼塊。它可以根據一個表達式的值選擇執行多個分支中的一個。

CASE 語句的基本語法如下：

CASE case_valueWHEN when_value1 THEN statement1;WHEN when_value2 THEN statement2;...ELSE statementN;
END CASE;

• case_value：要比較的表達式。
• when_value：與 case_value 比較的值。
• statement：當 case_value 等于 when_value 時執行的語句。
• ELSE：可選，當 case_value 不等于任何 when_value 時執行的語句。

以下是一個使用 CASE 語句的存儲過程示例，根據員工的工資等級（通過輸入參數傳入）來判斷是否發放獎金，并打印相應的消息：

DELIMITER $$CREATE PROCEDURE CheckBonus(IN salary_grade INT)
BEGINCASE salary_gradeWHEN 1 THENSELECT '發放獎金';WHEN 2 THENSELECT '發放小禮品';WHEN 3 THENSELECT '發放感謝信';ELSESELECT '不發放任何獎勵';END CASE;
END$$DELIMITER ;

在這個示例中：

• CASE salary_grade 表示根據 salary_grade 的值進行條件判斷。
• WHEN 1 THEN 表示如果 salary_grade 等于 1，則執行 SELECT '發放獎金';。
• WHEN 2 THEN 表示如果 salary_grade 等于 2，則執行 SELECT '發放小禮品';。
• WHEN 3 THEN 表示如果 salary_grade 等于 3，則執行 SELECT '發放感謝信';。
• ELSE 表示如果 salary_grade 不等于 1、2 或 3，則執行 SELECT '不發放任何獎勵';。

調用上述存儲過程，傳入不同的 salary_grade 值：

CALL CheckBonus(1); -- 輸出：發放獎金
CALL CheckBonus(2); -- 輸出：發放小禮品
CALL CheckBonus(3); -- 輸出：發放感謝信
CALL CheckBonus(4); -- 輸出：不發放任何獎勵

4.LOOP 循環

語法：

LOOP-- 循環體中的語句
END LOOP;

示例：
以下是一個使用 LOOP 循環的存儲過程示例，該存儲過程將表中的員工工資依次增加 100，直到工資超過 10000：

DELIMITER $$CREATE PROCEDURE IncreaseSalary()
BEGINDECLARE employee_id INT DEFAULT 1;DECLARE current_salary INT;my_loop: LOOPSELECT salary INTO current_salaryFROM employeesWHERE employee_id = employee_id;IF current_salary IS NULL THENLEAVE my_loop;END IF;IF current_salary < 10000 THENUPDATE employeesSET salary = salary + 100WHERE employee_id = employee_id;END IF;SET employee_id = employee_id + 1;END LOOP my_loop;
END$$DELIMITER ;

5.REPEAT 循環

語法：

REPEAT-- 循環體中的語句
UNTIL 條件
END REPEAT;

示例：
以下是一個使用 REPEAT 循環的存儲過程示例，該存儲過程將表中的員工工資依次增加 100，直到工資超過 10000：

DELIMITER $$CREATE PROCEDURE IncreaseSalary()
BEGINDECLARE employee_id INT DEFAULT 1;DECLARE current_salary INT;REPEATSELECT salary INTO current_salaryFROM employeesWHERE employee_id = employee_id;IF current_salary IS NOT NULL THENIF current_salary < 10000 THENUPDATE employeesSET salary = salary + 100WHERE employee_id = employee_id;END IF;ELSELEAVE;END IF;SET employee_id = employee_id + 1;UNTIL current_salary IS NULLEND REPEAT;
END$$DELIMITER ;

（五）練習

練習一：創建和調用存儲過程

創建一個存儲過程，用于查詢指定部門的員工數量。存儲過程名稱為 GetEmployeeCountByDepartment，參數為部門編號（@department_id）。
調用這個存儲過程，查詢部門編號為 10 的員工數量。
答案：

DELIMITER $$CREATE PROCEDURE GetEmployeeCountByDepartment(IN department_id INT)
BEGINSELECT COUNT(*) AS EmployeeCountFROM employeesWHERE department_id = department_id;
END$$DELIMITER ;CALL GetEmployeeCountByDepartment(10);

練習二：使用輸出參數

創建一個存儲過程，用于計算兩個數的乘積，并將結果通過輸出參數返回。存儲過程名稱為 MultiplyTwoNumbers，輸入參數為兩個數字（@number1 和 @number2），輸出參數為乘積（@product）。
調用這個存儲過程，計算 4 和 6 的乘積，并將結果存儲到一個變量中。
答案：

DELIMITER $$CREATE PROCEDURE MultiplyTwoNumbers(IN number1 INT, IN number2 INT, OUT product INT)
BEGINSET product = number1 * number2;
END$$DELIMITER ;SET @result = 0;
CALL MultiplyTwoNumbers(4, 6, @result);
SELECT @result AS Product;

練習三：控制流語句應用

創建一個存儲過程，用于判斷一個數字是否為偶數。如果是偶數，打印“偶數”，否則打印“奇數”。存儲過程名稱為 CheckEvenOdd，輸入參數為數字（@number）。
調用這個存儲過程，判斷數字 7 是否為偶數。
答案：

DELIMITER $$CREATE PROCEDURE CheckEvenOdd(IN number INT)
BEGINIF number % 2 = 0 THENSELECT '偶數';ELSESELECT '奇數';END IF;
END$$DELIMITER ;CALL CheckEvenOdd(7);

二、變量

（一）局部變量

1.定義

局部變量是在存儲過程、函數或語句塊中聲明的變量，其作用域僅限于聲明它的存儲過程、函數或語句塊內部。

2.聲明

使用 DECLARE 語句聲明局部變量：

DECLARE variable_name variable_data_type [DEFAULT default_value];

• variable_name：變量的名稱，用于在存儲過程中引用該變量。
• variable_data_type：變量的數據類型，如 INT、VARCHAR、DATE 等。
• DEFAULT default_value（可選）：為變量指定默認值。如果不指定默認值，則變量的初始值為 NULL。

3.賦值

可以使用 SET 語句或在 SELECT 語句中使用 INTO 子句為局部變量賦值：

SET variable_name = value;

或

SELECT column INTO variable_name
FROM table_name
WHERE condition;

示例：

DELIMITER $$CREATE PROCEDURE GetEmployeeSalary(IN employee_id INT)
BEGIN-- 聲明局部變量DECLARE employee_salary DECIMAL(10, 2);-- 使用 SELECT ... INTO ... 語句查詢員工的工資并賦值給局部變量SELECT salary INTO employee_salaryFROM employeesWHERE employee_id = employee_id;-- 輸出查詢結果SELECT employee_salary AS Salary;
END$$DELIMITER ;

4.作用域

局部變量的作用域僅限于聲明它的存儲過程、函數或語句塊內部，存儲過程或函數執行完畢后，局部變量被銷毀。

（二）全局變量

1.定義

全局變量是在整個數據庫會話中有效的變量，其值在會話期間保持不變，直到顯式地更改它。

2.聲明

全局變量不需要顯式聲明，它們是 MySQL 內置的系統變量，通常以 @@ 開頭。例如：

SELECT @@global.variable_name;

或

SET GLOBAL variable_name = value;

3.賦值

可以使用 SET GLOBAL 語句或 SET @@global.variable_name 語法為全局變量賦值：

SET GLOBAL variable_name = value;

或

SET @@global.variable_name = value;

4.作用域

全局變量的作用域是整個數據庫會話，所有用戶都可以訪問和修改全局變量的值，但修改后的值僅對當前會話有效，不會影響其他會話。

（三）示例

1.局部變量示例

以下是一個存儲過程示例，展示了如何聲明和使用局部變量：

DELIMITER $$CREATE PROCEDURE CalculateTotal(IN order_id INT, OUT total DECIMAL(10, 2))
BEGIN-- 聲明局部變量DECLARE item_price DECIMAL(10, 2);DECLARE item_quantity INT;DECLARE total_items INT DEFAULT 0;DECLARE total_amount DECIMAL(10, 2) DEFAULT 0.0;-- 查詢訂單中的商品數量SELECT COUNT(*)INTO total_itemsFROM order_itemsWHERE order_id = order_id;-- 遍歷訂單中的每個商品DECLARE item_cursor CURSOR FORSELECT price, quantityFROM order_itemsWHERE order_id = order_id;DECLARE CONTINUE HANDLER FOR NOT FOUNDSET total_items = 0;OPEN item_cursor;fetch_loop: LOOPFETCH item_cursor INTO item_price, item_quantity;IF total_items = 0 THENLEAVE fetch_loop;END IF;-- 計算每個商品的總價并累加到總金額SET total_amount = total_amount + (item_price * item_quantity);END LOOP;CLOSE item_cursor;-- 將總金額賦值給輸出參數SET total = total_amount;
END$$DELIMITER ;

在上述存儲過程中：

• 使用 DECLARE 聲明了多個局部變量，包括 item_price、item_quantity、total_items 和 total_amount。
• 這些變量在存儲過程的邏輯中用于存儲臨時數據，如商品價格、數量、訂單中商品的總數以及訂單的總金額。
• 變量 total_items 和 total_amount 被賦予了默認值，分別是 0 和 0.0。
• 使用 SET 語句為變量賦值，例如 SET total_amount = total_amount + (item_price * item_quantity);。

2.全局變量示例

以下是一個示例，展示了如何使用全局變量：

-- 查看全局變量的值
SELECT @@global.max_connections;-- 設置全局變量的值
SET GLOBAL max_connections = 1000;-- 或者
SET @@global.max_connections = 1000;

在上述示例中：

• 使用 SELECT @@global.max_connections; 查看了全局變量 max_connections 的值。
• 使用 SET GLOBAL max_connections = 1000; 或 SET @@global.max_connections = 1000; 設置了全局變量 max_connections 的值。

（四）面試常問問題

1.局部變量和全局變量的區別是什么？

• 作用域：局部變量的作用域僅限于聲明它的存儲過程、函數或語句塊內部；全局變量的作用域是整個數據庫會話。
• 聲明方式：局部變量使用 DECLARE 語句聲明；全局變量不需要顯式聲明，使用 @@global.variable_name 或 SET GLOBAL 語句。
• 賦值方式：局部變量使用 SET 語句或 SELECT ... INTO ... 語句賦值；全局變量使用 SET GLOBAL 語句或 SET @@global.variable_name 語法賦值。
• 生命周期：局部變量的生命周期與存儲過程或函數的執行周期相同；全局變量的生命周期是整個數據庫會話。

2.如何聲明和使用局部變量？

• 使用 DECLARE 語句聲明局部變量，例如 DECLARE variable_name variable_data_type [DEFAULT default_value];。
• 使用 SET 語句或 SELECT ... INTO ... 語句為局部變量賦值，例如 SET variable_name = value; 或 SELECT column INTO variable_name FROM table_name WHERE condition;。
• 局部變量的作用域僅限于聲明它的存儲過程、函數或語句塊內部，存儲過程或函數執行完畢后，局部變量被銷毀。

3.如何查看和設置全局變量的值？

• 使用 SELECT @@global.variable_name; 查看全局變量的值。
• 使用 SET GLOBAL variable_name = value; 或 SET @@global.variable_name = value; 設置全局變量的值。

4.在存儲過程中如何使用局部變量？

• 在存儲過程的開始部分使用 DECLARE 語句聲明局部變量。
• 使用 SET 語句或 SELECT ... INTO ... 語句為局部變量賦值。
• 在存儲過程的邏輯中通過變量名直接引用和操作局部變量。

5.全局變量和局部變量的命名沖突如何解決？

• 全局變量和局部變量的命名沖突通常不會發生，因為它們的作用域不同。全局變量以 @@global. 開頭，而局部變量在存儲過程內部聲明和使用。
• 如果需要在存儲過程中訪問全局變量，可以顯式地使用 @@global. 前綴來區分，例如 SELECT @@global.max_connections;。

附:【局部變量 用戶定義的變量（帶@的變量） 全局變量 】三者對比

特性	局部變量	用戶定義的變量（帶@的變量）	全局變量
作用域	僅限于聲明它的存儲過程、函數或語句塊內部	整個數據庫會話	整個數據庫服務器
聲明方式	使用DECLARE語句 示例：DECLARE variable_name variable_data_type [DEFAULT default_value];	使用SET語句 示例：SET @variable_name = value;	使用SET GLOBAL語句或SET @@global.語法 示例：SET GLOBAL variable_name = value; 或SET @@global.variable_name = value;
賦值方式	使用SET語句或SELECT ... INTO ...語句 示例：SET variable_name = value; 或SELECT column INTO variable_name FROM table_name WHERE condition;	使用SET語句 示例：SET @variable_name = value;	使用SET GLOBAL語句或SET @@global.語法 示例：SET GLOBAL variable_name = value; 或SET @@global.variable_name = value;
生命周期	存儲過程或函數執行完畢后銷毀	會話結束時銷毀	服務器重啟或顯式更改時更新
用途	存儲臨時數據，參與計算或作為邏輯控制的依據	存儲臨時值，傳遞數據，接收存儲過程的輸出參數	配置數據庫服務器的行為，影響所有會話

示例代碼

• 局部變量示例：

  DELIMITER $$
  CREATE PROCEDURE CalculateTotal(IN order_id INT, OUT total DECIMAL(10, 2))
  BEGINDECLARE item_price DECIMAL(10, 2);DECLARE item_quantity INT;DECLARE total_items INT DEFAULT 0;DECLARE total_amount DECIMAL(10, 2) DEFAULT 0.0;-- 其他邏輯...
  END$$
  DELIMITER ;
  

• 用戶定義的變量（帶@的變量）示例：

  SET @result = 0;
  CALL AddTwoNumbers(5, 3, @result);
  SELECT @result AS Sum;
  

• 全局變量示例：

  SELECT @@global.max_connections;
  SET GLOBAL max_connections = 1000;
  

詳細解釋

1. 作用域

   • 局部變量：僅限于聲明它的存儲過程、函數或語句塊內部。一旦存儲過程或函數執行完畢，局部變量將被銷毀。
   • 用戶定義的變量（帶 @ 的變量）：整個數據庫會話。在同一個會話中，這些變量可以跨多個 SQL 語句使用，直到會話結束。
   • 全局變量：整個數據庫服務器。所有會話都可以訪問和修改全局變量的值，修改后會影響所有當前和未來的會話。

2. 聲明方式

   • 局部變量：使用 DECLARE 語句在存儲過程或函數的開始部分聲明。
   • 用戶定義的變量（帶 @ 的變量）：使用 SET 語句在會話中聲明和初始化。
   • 全局變量：使用 SET GLOBAL 語句或 SET @@global. 語法聲明和賦值。

3. 賦值方式

   • 局部變量：使用 SET 語句或 SELECT ... INTO ... 語句賦值。
   • 用戶定義的變量（帶 @ 的變量）：使用 SET 語句賦值。
   • 全局變量：使用 SET GLOBAL 語句或 SET @@global. 語法賦值。

4. 生命周期

   • 局部變量：存儲過程或函數執行完畢后銷毀。
   • 用戶定義的變量（帶 @ 的變量）：會話結束時銷毀。
   • 全局變量：服務器重啟或顯式更改時更新。

5. 用途

   • 局部變量：用于存儲臨時數據，參與計算或作為邏輯控制的依據。
   • 用戶定義的變量（帶 @ 的變量）：用于存儲臨時值，傳遞數據，接收存儲過程的輸出參數。
   • 全局變量：用于配置數據庫服務器的行為，影響所有會話。

三、 游標（CURSOR）

定義

游標（Cursor）是數據庫中一個臨時的工作區，用于存儲和操作查詢結果集中的數據。

聲明

DECLARE cursor_name CURSOR FOR select_statement;

打開游標

OPEN cursor_name;

獲取數據

FETCH cursor_name INTO variable_name [, variable_name ...];

關閉游標

CLOSE cursor_name;

示例

以下是一個使用游標的存儲過程示例，該存儲過程將表中的員工工資依次增加 100，直到工資超過 10000：

DELIMITER $$CREATE PROCEDURE IncreaseSalary()
BEGINDECLARE employee_id INT;DECLARE current_salary INT;DECLARE done INT DEFAULT 0;DECLARE cursor_name CURSOR FORSELECT employee_id, salaryFROM employees;DECLARE CONTINUE HANDLER FOR NOT FOUNDSET done = 1;OPEN cursor_name;my_loop: LOOPFETCH cursor_name INTO employee_id, current_salary;IF done = 1 THENLEAVE my_loop;END IF;IF current_salary < 10000 THENUPDATE employeesSET salary = salary + 100WHERE employee_id = employee_id;END IF;END LOOP my_loop;CLOSE cursor_name;
END$$DELIMITER ;

四、處理程序（HANDLER）

定義

處理程序（Handler）用于捕獲和處理存儲過程或函數執行過程中發生的特定條件或異常。

聲明

DECLARE [CONTINUE | EXIT | UNDO] HANDLER FOR condition-- 處理程序中的語句

示例

以下是一個使用處理程序的存儲過程示例，該存儲過程捕獲了查詢結果為空的情況：

DELIMITER $$CREATE PROCEDURE GetEmployeeSalary(IN employee_id INT)
BEGINDECLARE employee_salary DECIMAL(10, 2);DECLARE done INT DEFAULT 0;DECLARE CONTINUE HANDLER FOR NOT FOUNDSET done = 1;SELECT salary INTO employee_salaryFROM employeesWHERE employee_id = employee_id;IF done = 1 THENSELECT '未找到員工' AS message;ELSESELECT employee_salary AS Salary;END IF;
END$$DELIMITER ;

五、 事務控制

事務概述

事務是一組 SQL 語句的集合，這些語句要么全部成功執行，要么全部不執行。事務的目的是確保數據的完整性和一致性。

BEGIN、COMMIT、ROLLBACK 語句

• BEGIN 或 START TRANSACTION：開始一個新的事務。
• COMMIT：提交當前事務，使事務中的所有更改永久生效。
• ROLLBACK：回滾當前事務，撤銷事務中的所有更改。
• SAVEPOINT：設置一個保存點，可以在事務中回滾到這個點。

示例

以下是一個使用事務控制的存儲過程示例，該存儲過程更新員工的工資，并在發生錯誤時回滾事務：

DELIMITER $$CREATE PROCEDURE UpdateEmployeeSalary(IN employee_id INT, IN new_salary DECIMAL(10, 2))
BEGINDECLARE exit_handler INT DEFAULT 0;DECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGINSET exit_handler = 1;END;START TRANSACTION;UPDATE employeesSET salary = new_salaryWHERE employee_id = employee_id;IF exit_handler = 1 THENROLLBACK;SELECT '更新失敗' AS message;ELSECOMMIT;SELECT '更新成功' AS message;END IF;
END$$DELIMITER ;

六、 動態 SQL

動態 SQL 概述

動態 SQL 是指在運行時生成和執行 SQL 語句的技術。

PREPARE、EXECUTE、DEALLOCATE PREPARE 語句

• PREPARE：準備執行 SQL 語句。
• EXECUTE：執行準備好的 SQL 語句。
• DEALLOCATE PREPARE：釋放準備好的 SQL 語句。

示例

以下是一個使用動態 SQL 的存儲過程示例，該存儲過程根據表名動態生成和執行 SQL 語句：

DELIMITER $$CREATE PROCEDURE DynamicSQL(IN table_name VARCHAR(100))
BEGINDECLARE sql_statement VARCHAR(255);SET sql_statement = CONCAT('SELECT * FROM ', table_name);PREPARE stmt FROM sql_statement;EXECUTE stmt;DEALLOCATE PREPARE stmt;
END$$DELIMITER ;

八、 存儲過程案例：

批量數據處理示例：批量更新用戶積分

以下是一個存儲過程示例，該存儲過程根據用戶的消費金額批量更新用戶的積分：

DELIMITER $$CREATE PROCEDURE UpdateUserPoints()
BEGINDECLARE user_id INT;DECLARE consumption DECIMAL(10, 2);DECLARE done INT DEFAULT 0;DECLARE cursor_name CURSOR FORSELECT id, consumptionFROM users;DECLARE CONTINUE HANDLER FOR NOT FOUNDSET done = 1;OPEN cursor_name;my_loop: LOOPFETCH cursor_name INTO user_id, consumption;IF done = 1 THENLEAVE my_loop;END IF;UPDATE usersSET points = points + consumption * 10WHERE id = user_id;END LOOP my_loop;CLOSE cursor_name;
END$$DELIMITER ;

數據校驗示例：校驗用戶輸入數據

以下是一個存儲過程示例，該存儲過程校驗用戶輸入的用戶名和密碼是否符合要求：

DELIMITER $$CREATE PROCEDURE ValidateUser(IN username VARCHAR(100), IN password VARCHAR(100))
BEGINDECLARE valid INT DEFAULT 0;IF LENGTH(username) >= 6 AND LENGTH(password) >= 8 THENSET valid = 1;END IF;SELECT valid AS IsValid;
END$$DELIMITER ;

復雜業務邏輯封裝示例：計算訂單總金額

以下是一個存儲過程示例，該存儲過程根據訂單中的商品信息計算訂單的總金額：

DELIMITER $$CREATE PROCEDURE CalculateOrderTotal(IN order_id INT, OUT total DECIMAL(10, 2))
BEGINDECLARE item_price DECIMAL(10, 2);DECLARE item_quantity INT;DECLARE total_items INT DEFAULT 0;DECLARE total_amount DECIMAL(10, 2) DEFAULT 0.0;DECLARE cursor_name CURSOR FORSELECT price, quantityFROM order_itemsWHERE order_id = order_id;DECLARE CONTINUE HANDLER FOR NOT FOUNDSET total_items = 0;OPEN cursor_name;my_loop: LOOPFETCH cursor_name INTO item_price, item_quantity;IF total_items = 0 THENLEAVE my_loop;END IF;SET total_amount = total_amount + (item_price * item_quantity);END LOOP my_loop;CLOSE cursor_name;SET total = total_amount;
END$$DELIMITER ;