《MySQL 基礎篇》九：儲存過程、流程控制和觸發器

ACatSmiling發表於2024-09-22
MySql儲存過程觸發器

Author: ACatSmiling

Since: 2024-09-20

儲存過程與函式

MySQL 從 5.0 版本開始支援儲存過程和函式。儲存過程和函式能夠將複雜的 SQL 邏輯封裝在一起，應用程式無須關注儲存過程和函式內部複雜的 SQL 邏輯，而只需要簡單地呼叫儲存過程和函式即可。

儲存過程概述

含義：儲存過程的英文是 Stored Procedure 。它的思想很簡單，就是一組經過預先編譯的 SQL 語句的封裝。

執行過程：儲存過程預先儲存在 MySQL 伺服器上，需要執行的時候，客戶端只需要向伺服器端發出呼叫儲存過程的命令，伺服器端就可以把預先儲存好的這一系列 SQL 語句全部執行。

好處：

  • 簡化操作，提高了 SQL 語句的重用性，減少了開發程式設計師的壓力。
  • 減少操作過程中的失誤，提高效率。
  • 減少網路傳輸量（客戶端不需要把所有的 SQL 語句透過網路發給伺服器） 。
  • 減少了 SQL 語句暴露在網上的風險，也提高了資料查詢的安全性。

和檢視、函式的對比：

  • 它和檢視有著同樣的優點，清晰、安全，還可以減少網路傳輸量。不過它和檢視不同，檢視是虛擬表，通常不對底層資料表直接操作，而儲存過程是程式化的 SQL，可以直接操作底層資料表，相比於面向集合的操作方式，能夠實現一些更復雜的資料處理。
  • 一旦儲存過程被建立出來，使用它就像使用函式一樣簡單，直接透過呼叫儲存過程名即可。相較於函式，儲存過程是沒有返回值的。

儲存過程的引數型別可以是 IN、OUT 和 INOUT。根據這點分類如下：

  • 沒有引數（無引數無返回）。
  • 僅僅帶 IN 型別（有引數無返回） 。
  • 僅僅帶 OUT 型別（無引數有返回） 。
  • 既帶 IN 又帶 OUT（有引數有返。回）。
  • 帶 INOUT（有引數有返回）
  • 注意：IN、OUT、INOUT 都可以在一個儲存過程中帶多個。

建立儲存過程

語法：

CREATE PROCEDURE 儲存過程名(IN|OUT|INOUT 引數名 引數型別,...)
[characteristics ...]
    BEGIN
        儲存過程體
    END

  • 引數前面的符號的意思：

    • IN：當前引數為輸入引數，也就是表示入參；儲存過程只是讀取這個引數的值。如果沒有定義引數種類， 預設就是 IN ，表示輸入引數。
    • OUT：當前引數為輸出引數，也就是表示出參；執行完成之後，呼叫這個儲存過程的客戶端或者應用程式就可以讀取這個引數返回的值了。
    • INOUT：當前引數既可以為輸入引數，也可以為輸出引數。

  • 形參型別可以是 MySQL 資料庫中的任意型別。

  • characteristics 表示建立儲存過程時指定的對儲存過程的約束條件，其取值資訊如下：

    LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
    • LANGUAGE SQL：說明儲存過程執行體是由 SQL 語句組成的，當前系統支援的語言為 SQL。
    • [NOT] DETERMINISTIC：指明儲存過程執行的結果是否確定。DETERMINISTIC 表示結果是確定的，每次執行儲存過程時，相同的輸入會得到相同的輸出。NOT DETERMINISTIC 表示結果是不確定的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個值，預設為 NOT DETERMINISTIC。
    • { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程式使用SQL語句的限制。
      • CONTAINS SQL：表示當前儲存過程的子程式包含 SQL 語句，但是並不包含讀寫資料的 SQL 語句；
      • NO SQL：表示當前儲存過程的子程式中不包含任何 SQL 語句；
      • READS SQL DATA：表示當前儲存過程的子程式中包含讀資料的 SQL 語句；
      • MODIFIES SQL DATA：表示當前儲存過程的子程式中包含寫資料的 SQL 語句。
      • 預設情況下，系統會指定為 CONTAINS SQL。
    • SQL SECURITY { DEFINER | INVOKER }：執行當前儲存過程的許可權，即指明哪些使用者能夠執行當前儲存過程。
      • DEFINER：表示只有當前儲存過程的建立者或者定義者才能執行當前儲存過程；
      • INVOKER：表示擁有當前儲存過程的訪問許可權的使用者能夠執行當前儲存過程。
      • 如果沒有設定相關的值，則 MySQL 預設指定值為 DEFINER。
    • COMMENT 'string'：註釋資訊，可以用來描述儲存過程。

  • 儲存過程體中可以有多條 SQL 語句，如果僅僅一條 SQL 語句，則可以省略 BEGIN 和 END。編寫儲存過程並不是一件簡單的事情，可能儲存過程中需要複雜的 SQL 語句。

    BEGIN…END：BEGIN…END 中間包含了多個語句，每個語句都以 ';' 號為結束符
DECLARE：DECLARE 用來宣告變數，使用的位置在於 BEGIN…END 語句中間，而且需要在其他語句使用之前進行變數的宣告
SET：賦值語句，用於對變數進行賦值
SELECT… INTO：把從資料表中查詢的結果存放到變數中，也就是為變數賦值

  • 需要設定新的結束標記。

    DELIMITER 新的結束標記

    # 示例
DELIMITER $

CREATE PROCEDURE 儲存過程名(IN|OUT|INOUT 引數名 引數型別,...)
[characteristics ...]
    BEGIN
        sql語句1;
        sql語句2;
    END $

DELIMITER ;
    • MySQL 預設的語句結束符號為分號 ";"，為了避免與儲存過程中 SQL 語句結束符相沖突，需要使用 DELIMITER 重新設定儲存過程的結束符。比如："DELIMITER //" 語句的作用是將 MySQL 的結束符設定為 //，並以 "END //" 結束儲存過程。儲存過程定義完畢之後再使用 "DELIMITER ;" 恢復預設結束符。
    • DELIMITER 也可以指定其他符號作為結束符。當使用 DELIMITER 命令時，應該避免使用反斜槓 "\" 字元，因為反斜線是 MySQL 的跳脫字元。

示例：

# 建立儲存過程 select_all_data()，檢視 employees 表的所有資料
DELIMITER $

CREATE PROCEDURE select_all_data()
    BEGIN
        SELECT * FROM employees;
    END $

DELIMITER ;

# 建立儲存過程 avg_employee_salary()，返回所有員工的平均工資
DELIMITER //

CREATE PROCEDURE avg_employee_salary()
    BEGIN
        SELECT AVG(salary) AS avg_salary FROM employees;
    END //

DELIMITER ;

# 建立儲存過程 show_max_salary()，用來檢視 employees 表的最高薪資值
DELIMITER //

CREATE PROCEDURE show_max_salary()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT '檢視最高薪資'
    BEGIN
        SELECT MAX(salary) FROM employees;
    END //

DELIMITER ;

# 建立儲存過程 show_min_salary()，檢視 employees 表的最低薪資值，並將最低薪資透過 OUT 引數 ms 輸出
DELIMITER //

CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
    BEGIN
    	SELECT MIN(salary) INTO ms FROM employees;
    END //

DELIMITER ;

# 建立儲存過程 show_someone_salary()，檢視 employees 表的某個員工的薪資，並用 IN 引數 empname 輸入員工姓名
DELIMITER //

CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
    BEGIN
    	SELECT salary FROM employees WHERE ename = empname;
    END //
    
DELIMITER ;

# 建立儲存過程 show_someone_salary2()，檢視 employees 表的某個員工的薪資，並用 IN 引數 empname 輸入員工姓名，用 OUT 引數 empsalary 輸出員工薪資
DELIMITER //

    CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20), OUT empsalary DOUBLE)
    BEGIN
    	SELECT salary INTO empsalary FROM emps WHERE ename = empname;
    END //
    
DELIMITER ;

# 建立儲存過程 show_mgr_name()，查詢某個員工領導的姓名，並用 INOUT 引數 empname 輸入員工姓名，輸出領導的姓名
DELIMITER //

CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20))
    BEGIN
    	SELECT ename INTO empname FROM emps
    	WHERE eid = (SELECT MID FROM emps WHERE ename = empname);
    END //
    
DELIMITER ;

呼叫儲存過程

儲存過程有多種呼叫方法。儲存過程必須使用 CALL 語句呼叫，並且儲存過程和資料庫相關，如果要執行其他資料庫中的儲存過程，需要指定資料庫名稱，例如 CALL dbname.procname。

語法：

CALL 儲存過程名(實參列表);

  • 呼叫 IN 模式的引數：

    CALL sp1('值');

  • 呼叫 OUT 模式的引數：

    SET @name;
CALL sp1(@name);
SELECT @name;

  • 呼叫 INOUT 模式的引數：

    SET @name = 值;
CALL sp1(@name);
SELECT @name;

示例：

# 建立儲存過程，實現累加運算
mysql> DELIMITER //
mysql> CREATE PROCEDURE `add_num`(IN n INT)
    -> 	BEGIN
    -> 		DECLARE i INT;
    -> 		DECLARE sum INT;
    -> 		SET i = 1;
    -> 		SET sum = 0;
    -> 		WHILE i <= n DO
    -> 			SET sum = sum + i;
    -> 			SET i = i +1;
    -> 		END WHILE;
    -> 		SELECT sum;
    -> 	END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> CALL add_num(50); 
+------+
| sum  |
+------+
| 1275 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

儲存函式的使用

語法：

CREATE FUNCTION 函式名(引數名 引數型別,...)
RETURNS 返回值型別
[characteristics ...]
    BEGIN
        函式體 # 函式體中肯定有 RETURN 語句
    END
  • 引數列表：指定引數為 IN、OUT 或 INOUT 只對 PROCEDURE 是合法的，FUNCTION 中總是預設為 IN 引數。
  • RETURNS type 語句表示函式返回資料的型別；RETURNS 子句只能對 FUNCTION 做指定，對函式而言這是強制的。它用來指定函式的返回型別，而且函式體中也必須包含一個 RETURN value 語句。
  • characteristic 為建立函式時指定的對函式的約束，取值與建立儲存過程時相同。
  • 函式體使用 BEGIN…END 來表示 SQL 程式碼的開始和結束，如果函式體只有一條語句，可以省略 BEGIN…END。

呼叫儲存函式

在 MySQL 中，儲存函式的使用方法與 MySQL 內部函式的使用方法是一樣的。換言之，使用者自己定義的儲存函式與 MySQL 內部函式是一個性質的。區別在於，儲存函式是使用者自己定義的，而內部函式是 MySQL 的開發者定義的。

語法：

SELECT 函式名(實參列表);

示例：

# 建立儲存函式 email_by_name()，引數定義為空，該函式查詢 Abel 的 email 並返回，資料型別為字串型
mysql> DELIMITER //
mysql> CREATE FUNCTION email_by_name()
    -> RETURNS VARCHAR(25)
    -> DETERMINISTIC
    -> CONTAINS SQL
    -> 	BEGIN
    -> 		RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
    -> 	END //
Query OK, 0 rows affected (0.03 sec)

mysql> DELIMITER ;
mysql> SELECT email_by_name();
+-----------------+
| email_by_name() |
+-----------------+
| EABEL           |
+-----------------+
1 row in set (0.00 sec)

# 建立儲存函式 email_by_id()，引數傳入 emp_id，該函式查詢 emp_id 的 email 並返回，資料型別為字串型
mysql> DELIMITER //
mysql> CREATE FUNCTION email_by_id(emp_id INT)
    -> RETURNS VARCHAR(25)
    -> DETERMINISTIC
    -> CONTAINS SQL
    -> 	BEGIN
    -> 		RETURN (SELECT email FROM employees WHERE employee_id = emp_id);
    -> 	END //
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;
mysql> SET @emp_id = 102;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT email_by_id(102);
+------------------+
| email_by_id(102) |
+------------------+
| LDEHAAN          |
+------------------+
1 row in set (0.00 sec)

# 建立儲存函式 count_by_id()，引數傳入 dept_id，該函式查詢 dept_id 部門的員工人數並返回，資料型別為整型
mysql> DELIMITER //
mysql> CREATE FUNCTION count_by_id(dept_id INT)
    -> RETURNS INT
    -> LANGUAGE SQL
    -> NOT DETERMINISTIC
    -> READS SQL DATA
    -> SQL SECURITY DEFINER
    -> COMMENT '查詢部門平均工資'
    -> 	BEGIN
    -> 		RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
    -> 	END //
Query OK, 0 rows affected (0.03 sec)

mysql> DELIMITER ;
mysql> SET @dept_id = 50;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count_by_id(@dept_id);
+-----------------------+
| count_by_id(@dept_id) |
+-----------------------+
|                    45 |
+-----------------------+
1 row in set (0.00 sec

儲存過程與儲存函式對比

關鍵字 呼叫語法 返回值 應用場景
儲存過程 PROCEDURE CALL 儲存過程() 理解為有 0 個或多個 一般用於更新
儲存函式 FUNCTION SELECT 函式() 只能是一個 一般用於查詢結果為一個值並返回時

此外，儲存函式可以放在查詢語句中使用，儲存過程不行。反之，儲存過程的功能更加強大，包括能夠執行對錶的操作（比如建立表，刪除表等）和事務操作，這些功能是儲存函式不具備的。

儲存過程和儲存函式的檢視、修改、刪除

檢視

MySQL 儲存了儲存過程和函式的狀態資訊，使用者可以使用 SHOW STATUS 語句或 SHOW CREATE 語句來檢視，也可直接從系統的 information_schema 資料庫中查詢。

方式 1 ：使用SHOW CREATE語句檢視儲存過程和儲存函式的建立資訊。

語法：

SHOW CREATE {PROCEDURE | FUNCTION} 儲存過程名或函式名

示例：

mysql> SHOW CREATE FUNCTION count_by_id;
+-------------+-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Function    | sql_mode                                                                                                              | Create Function                                                                                                                                                               | character_set_client | collation_connection | Database Collation |
+-------------+-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| count_by_id | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`%` FUNCTION `count_by_id`(dept_id INT) RETURNS int
    READS SQL DATA
BEGIN
RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END | utf8mb3              | utf8_general_ci      | utf8mb4_0900_ai_ci |
+-------------+-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

方式 2：使用SHOW STATUS語句檢視儲存過程和儲存函式的狀態資訊。

語法：

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
  • 返回子程式的特徵，如資料庫、名字、型別、建立者及建立和修改日期。
  • [LIKE 'pattern']：匹配儲存過程或函式的名稱，可以省略。當省略不寫時，會列出MySQL資料庫中存在的所有儲存過程或函式的資訊。

示例：

 mysql> SHOW FUNCTION STATUS LIKE 'count_by_id';
+-----------+-------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db        | Name        | Type     | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-----------+-------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| atguigudb | count_by_id | FUNCTION | root@%  | 2023-05-13 05:37:31 | 2023-05-13 05:37:31 | DEFINER       |         | utf8mb3              | utf8_general_ci      | utf8mb4_0900_ai_ci |
+-----------+-------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

方式 3：從 information_schema.Routines 表中檢視儲存過程和函式的資訊。

語法：

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME = '儲存過程或儲存函式名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

MySQL 中儲存過程和函式的資訊儲存在 information_schema 資料庫下的 Routines 表中，可以透過查詢該表的記錄來查詢儲存過程和函式的資訊。

如果在 MySQL 資料庫中存在儲存過程和函式名稱相同的情況，最好指定 ROUTINE_TYPE 查詢條件來指明查詢的是儲存過程還是儲存函式。

示例：

mysql> SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'count_by_id' AND ROUTINE_TYPE = 'FUNCTION';
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+----------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+---------+----------------------+----------------------+--------------------+
| SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | ROUTINE_BODY | ROUTINE_DEFINITION                                                               | EXTERNAL_NAME | EXTERNAL_LANGUAGE | PARAMETER_STYLE | IS_DETERMINISTIC | SQL_DATA_ACCESS | SQL_PATH | SECURITY_TYPE | CREATED             | LAST_ALTERED        | SQL_MODE                                                                                                              | ROUTINE_COMMENT | DEFINER | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+----------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+---------+----------------------+----------------------+--------------------+
| count_by_id   | def             | atguigudb      | count_by_id  | FUNCTION     | int       |                     NULL |                   NULL |                10 |             0 |               NULL | NULL               | NULL           | int            | SQL          | BEGIN
RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END |          NULL | SQL               | SQL             | NO               | READS SQL DATA  |     NULL | DEFINER       | 2023-05-13 05:37:31 | 2023-05-13 05:37:31 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@%  | utf8mb3              | utf8_general_ci      | utf8mb4_0900_ai_ci |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+----------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

修改

修改儲存過程或儲存函式，不影響儲存過程或儲存函式功能，只是修改相關特性。

語法：

ALTER {PROCEDURE | FUNCTION} 儲存過程或函式的名 [characteristic ...]

修改儲存過程使用 ALTER PROCEDURE 語句，修改儲存函式使用 ALTER FUNCTION 語句。除此之外，這兩個語句的結構是一樣的，語句中的所有引數也是一樣的。

其中，characteristic 指定儲存過程或函式的特性，其取值資訊與建立儲存過程、函式時的取值資訊略有不同。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
  • CONTAINS SQL：表示子程式包含 SQL 語句，但不包含讀或寫資料的語句。
  • NO SQL：表示子程式中不包含 SQL 語句。
  • READS SQL DATA：表示子程式中包含讀資料的語句。
  • MODIFIES SQL DATA：表示子程式中包含寫資料的語句。
  • SQL SECURITY { DEFINER | INVOKER }：指明誰有許可權來執行。
    • DEFINER：表示只有定義者自己才能夠執行。
    • INVOKER：表示呼叫者可以執行。
  • COMMENT 'string'：表示註釋資訊。

示例：

# 修改儲存過程，將讀寫許可權改為 MODIFIES SQL DATA，並指明呼叫者可以執行
ALTER PROCEDURE CountProc
MODIFIES SQL DATA
SQL SECURITY INVOKER

# 修改儲存函式，將讀寫許可權改為 READS SQL DATA，並加上註釋資訊 'FIND NAME'
ALTER FUNCTION CountProc
READS SQL DATA
COMMENT 'FIND NAME'

刪除

語法：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 儲存過程或函式的名

儲存過程的優缺點

優點

  • 儲存過程可以一次編譯多次使用。儲存過程只在建立時進行編譯，之後的使用都不需要重新編譯，這就提升了 SQL 的執行效率。
  • 可以減少開發工作量。將程式碼封裝成模組，實際上是程式設計的核心思想之一，這樣可以把複雜的問題拆解成不同的模組，然後模組之間可以重複使用，在減少開發工作量的同時，還能保證程式碼的結構清晰。
  • 儲存過程的安全性強。在設定儲存過程的時候可以設定對使用者的使用許可權，這樣就和檢視一樣具有較強的安全性。
  • 可以減少網路傳輸量。因為程式碼封裝到儲存過程中，每次使用只需要呼叫儲存過程即可，這樣就減少了網路傳輸量。
  • 良好的封裝性。在進行相對複雜的資料庫操作時，原本需要使用一條一條的 SQL 語句，可能要連線多次資料庫才能完成的操作，現在變成了一次儲存過程，只需要連線一次即可。

缺點

  • 可移植性差。儲存過程不能跨資料庫移植，比如在 MySQL、Oracle 和 SQL Server 裡編寫的儲存過程，在換成其他資料庫時都需要重新編寫。
  • 除錯困難。只有少數 DBMS 支援儲存過程的除錯。對於複雜的儲存過程來說，開發和維護都不容易。雖然也有一些第三方工具可以對儲存過程進行除錯，但要收費。
  • 儲存過程的版本管理很困難。比如資料表索引發生變化了，可能會導致儲存過程失效。在開發軟體的時候往往需要進行版本管理，但是儲存過程本身沒有版本控制，版本迭代更新的時候很麻煩。
  • 不適合高併發的場景。高併發的場景需要減少資料庫的壓力，有時資料庫會採用分庫分表的方式，而且對可擴充套件性要求很高，在這種情況下，儲存過程會變得難以維護， 增加資料庫的壓力 ，顯然就不適用了。

阿里開發規範

【強制】禁止使用儲存過程，儲存過程難以除錯和擴充套件，更沒有移植性。

變數、流程控制與遊標

變數

在 MySQL 資料庫中，變數分為系統變數以及使用者自定義變數。

系統變數

系統變數分類

系統變數由系統定義，不是使用者定義，屬於伺服器層面。啟動 MySQL 服務，生成 MySQL 服務例項期間，MySQL 將為 MySQL 伺服器記憶體中的系統變數賦值，這些系統變數定義了當前 MySQL 服務例項的屬性、特徵。這些系統變數的值要麼是編譯 MySQL 時引數的預設值，要麼是配置檔案 (例如 my.ini 等) 中的引數值。

可以透過網址 https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html 檢視 MySQL 文件的系統變數。

系統變數分為全域性系統變數（需要新增 global 關鍵字）以及會話系統變數（需要新增 session 關鍵字），有時也把全域性系統變數簡稱為全域性變數，把會話系統變數稱為 local 變數。如果不新增關鍵字，預設會話級別。靜態變數（在 MySQL 服務例項執行期間，它們的值不能使用 set 動態修改）屬於特殊的全域性系統變數。

MySQL 8.0 的新特性 — 全域性變數的持久化。

在 MySQL 資料庫中，全域性變數可以透過 SET GLOBAL 語句來設定。例如，設定伺服器語句超時的限制，可以透過設定系統變數 max_execution_time 來實現：

SET GLOBAL MAX_EXECUTION_TIME = 2000;

使用 SET GLOBAL 語句設定的變數值只會臨時生效。資料庫重啟後，伺服器又會從 MySQL 配置檔案中讀取變數的預設值。 MySQL 8.0 版本新增了SET PERSIST命令。例如，設定伺服器的最大連線數為 1000：

SET PERSIST GLOBAL max_connections = 1000;

MySQL 會將該命令的配置儲存到資料目錄下的mysqld-auto.cnf檔案中，下次啟動時會讀取該檔案，用其中的配置來覆蓋預設的配置檔案。

示例：

# 檢視全域性變數 max_connections 的值
mysql> SHOW VARIABLES LIKE '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 1000  |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.00 sec)

# 設定全域性變數 max_connections 的值
mysql> SET PERSIST max_connections = 1500;
Query OK, 0 rows affected (0.00 sec)

# 重啟 MySQL 伺服器, 再次查詢 max_connections 的值
mysql> SHOW VARIABLES LIKE '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 1500  |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.01 sec)

每一個 MySQL 客戶機成功連線 MySQL 伺服器後，都會產生與之對應的會話。會話期間，MySQL 服務例項會在 MySQL 伺服器記憶體中生成與該會話對應的會話系統變數，這些會話系統變數的初始值是全域性系統變數值的複製。如下圖：

image-20230520211210885

  • 全域性系統變數針對於所有會話（連線）有效，但不能跨重啟。
  • 會話系統變數僅針對於當前會話（連線）有效。會話期間，當前會話對某個會話系統變數值的修改，不會影響其他會話同一個會話系統變數的值。
  • 會話 1 對某個全域性系統變數值的修改，會導致會話 2 中同一個全域性系統變數值的修改。
  • MySQL 中，有些系統變數只能是全域性的，例如 max_connections，用於限制伺服器的最大連線數；有些系統變數作用域既可以是全域性又可以是會話，例如 character_set_client，用於設定客戶端的字符集；有些系統變數的作用域只能是當前會話，例如 pseudo_thread_id，用於標記當前會話的 MySQL 連線 ID。
檢視系統變數

檢視所有或部分系統變數：

# 檢視所有全域性變數
SHOW GLOBAL VARIABLES

# 檢視所有會話變數
SHOW SESSION VARIABLES
# 或
SHOW VARIABLES

# 檢視滿足條件的部分全域性變數
SHOW GLOBAL VARIABLES LIKE '%識別符號%'

# 檢視滿足條件的部分會話變數
SHOW SESSION VARIABLES LIKE '%識別符號%'

示例：

mysql> SHOW GLOBAL VARIABLES LIKE 'admin_%';
+------------------------+-----------------+
| Variable_name          | Value           |
+------------------------+-----------------+
| admin_address          |                 |
| admin_port             | 33062           |
| admin_ssl_ca           |                 |
| admin_ssl_capath       |                 |
| admin_ssl_cert         |                 |
| admin_ssl_cipher       |                 |
| admin_ssl_crl          |                 |
| admin_ssl_crlpath      |                 |
| admin_ssl_key          |                 |
| admin_tls_ciphersuites |                 |
| admin_tls_version      | TLSv1.2,TLSv1.3 |
+------------------------+-----------------+
11 rows in set (0.01 sec)

mysql> SHOW SESSION VARIABLES LIKE 'admin_%';
+------------------------+-----------------+
| Variable_name          | Value           |
+------------------------+-----------------+
| admin_address          |                 |
| admin_port             | 33062           |
| admin_ssl_ca           |                 |
| admin_ssl_capath       |                 |
| admin_ssl_cert         |                 |
| admin_ssl_cipher       |                 |
| admin_ssl_crl          |                 |
| admin_ssl_crlpath      |                 |
| admin_ssl_key          |                 |
| admin_tls_ciphersuites |                 |
| admin_tls_version      | TLSv1.2,TLSv1.3 |
+------------------------+-----------------+
11 rows in set (0.00 sec)

檢視指定系統變數：

作為 MySQL 編碼規範，MySQL 中的系統變數以 @@ 開頭，其中@@global.僅用於標記全域性系統變數，@@session.僅用於標記會話系統變數。@@ 首先標記會話系統變數，如果會話系統變數不存在，則標記全域性系統變數。

# 檢視指定的系統變數的值
SELECT @@global.變數名

# 檢視指定的會話變數的值
SELECT @@session.變數名
# 或者
SELECT @@變數名

修改系統變數的值：

有些時候，資料庫管理員需要修改系統變數的預設值，以便修改當前會話或者 MySQL 服務例項的屬性、特徵。具體方法：

方式 1：修改 MySQL 配置檔案，繼而修改 MySQL 系統變數的值（該方法需要重啟 MySQL 服務）。
方式 2：在 MySQL 服務執行期間，使用 set 命令重新設定系統變數的值。

# 為某個全域性變數賦值
SET @@global.變數名 = 變數值
# 或者
SET GLOBAL 變數名 = 變數值

# 為某個會話變數賦值
SET @@session.變數名 = 變數值
# 或者
SET SESSION 變數名 = 變數值

示例：

mysql> SELECT @@global.autocommit;
+---------------------+
| @@global.autocommit |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.01 sec)

mysql> SET GLOBAL autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.autocommit;
+---------------------+
| @@global.autocommit |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)


mysql> SELECT @@global.max_connections;
+--------------------------+
| @@global.max_connections |
+--------------------------+
|                      151 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL max_connections = 1000;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.max_connections;
+--------------------------+
| @@global.max_connections |
+--------------------------+
|                     1000 |
+--------------------------+
1 row in set (0.00 sec)

使用者變數

使用者變數分類

使用者變數是使用者自己定義的，作為 MySQL 編碼規範，MySQL 中的使用者變數以 @ 開頭。根據作用範圍不同，又分為會話使用者變數和區域性變數。

  • 會話使用者變數：作用域和會話變數一樣，只對當前連線會話有效。
  • 區域性變數：只在 BEGIN 和 END 語句塊中有效，區域性變數只能在儲存過程和函式中使用。
會話使用者變數

定義會話使用者變數：

# 方式一：'=' 或 ':='
SET @使用者變數 = 值
SET @使用者變數 := 值

# 方式二：':=' 或 INTO 關鍵字
SELECT @使用者變數 := 表示式 [FROM 等子句]
SELECT 表示式 INTO @使用者變數 [FROM 等子句]

檢視使用者變數的值 （檢視、比較、運算等）:

SELECT @使用者變數

示例：

mysql> SET @a = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


mysql> SELECT @num := COUNT(*) FROM employees;
+------------------+
| @num := COUNT(*) |
+------------------+
|              107 |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT @num;
+------+
| @num |
+------+
|  107 |
+------+
1 row in set (0.00 sec)


mysql> SELECT AVG(salary) INTO @avgsalary FROM employees;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @avgsalary;
+-------------------+
| @avgsalary        |
+-------------------+
| 6461.682242990654 |
+-------------------+
1 row in set (0.00 sec)


# 檢視某個未宣告的變數時，將得到 NULL 值
mysql> SELECT @big; 
+------------+
| @big       |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)
區域性變數

定義：可以使用DECLARE語句定義一個區域性變數。

作用域：僅僅在定義它的 BEGIN ... END 中有效。

位置：只能放在 BEGIN ... END 中，而且只能放在第一句。

BEGIN
    # 宣告區域性變數
    DECLARE 變數名1 變數資料型別 [DEFAULT 變數預設值];
    DECLARE 變數名2, 變數名3, ... 變數資料型別 [DEFAULT 變數預設值];
    
    # 為區域性變數賦值
    SET 變數名1 = 值;
    SELECT 值 INTO 變數名2 [FROM 子句];
    
    # 檢視區域性變數的值
    SELECT 變數1, 變數2, 變數3;
END

定義變數：

# 如果沒有 DEFAULT 子句，初始值為 NULL
DECLARE 變數名 型別 [default 值]

變數賦值：

# 方式一：一般用於賦簡單的值
SET 變數名 = 值;
SET 變數名 := 值;

# 方式二：一般用於賦表中的欄位值
SELECT 欄位名或表示式 INTO 變數名 FROM 表;

使用變數（檢視、比較、運算等）:

SELECT 區域性變數名;
會話使用者變數 VS 區域性變數

image-20230521005741612

定義條件與處理程式

定義條件是事先定義程式執行過程中可能遇到的問題，處理程式定義了在遇到問題時應當採取的處理方式，並且保證儲存過程或函式在遇到警告或錯誤時能繼續執行。這樣可以增強儲存程式處理問題的能力，避免程式異常停止執行。

說明：定義條件和處理程式在儲存過程、儲存函式中都是支援的。

案例分析

建立一個名稱為 UpdateDataNoCondition 的儲存過程：

mysql> DELIMITER //
mysql> CREATE PROCEDURE UpdateDataNoCondition()
    -> BEGIN
    -> SET @x = 1;
    -> UPDATE employees SET email = NULL WHERE last_name = 'Abel';
    -> SET @x = 2;
    -> UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
    -> SET @x = 3;
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

呼叫儲存過程：

mysql> CALL UpdateDataNoCondition();
ERROR 1048 (23000): Column 'email' cannot be null

mysql> SELECT @x;
+------+
| @x   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

可以看到，此時 @x 變數的值為 1。結合建立儲存過程的 SQL 語句程式碼可以得出：在儲存過程中未定義條件和處理程式，且當儲存過程中執行的 SQL 語句報錯時，MySQL 資料庫會丟擲錯誤，並退出當前 SQL 邏輯，不再向下繼續執行。

定義條件

定義條件就是給 MySQL 中的錯誤碼命名，這有助於儲存的程式程式碼更清晰。它將一個錯誤名字和指定的錯誤條件關聯起來。這個名字可以隨後被用在定義處理程式的 DECLARE HANDLER 語句中。

定義條件使用 DECLARE 語句，語法：

DECLARE 錯誤名稱 CONDITION FOR 錯誤碼(或錯誤條件)

錯誤碼說明：

  • MySQL_error_code和sqlstate_value都可以表示 MySQL 的錯誤。
    • MySQL_error_code 是數值型別錯誤程式碼。
    • sqlstate_value 是長度為 5 的字串型別錯誤程式碼。
  • 例如，在 ERROR 1418 (HY000) 中，1418 是 MySQL_error_code，'HY000' 是 sqlstate_value。
  • 例如，在 ERROR 1142 (42000) 中，1142 是 MySQL_error_code，'42000' 是 sqlstate_value。

示例：

# 定義 'Field_Not_Be_NULL' 錯誤名，與 MySQL 中違反非空約束的錯誤型別 'ERROR 1048 (23000)' 對應
# 使用 MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
# 使用 sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

# 定義 'ERROR 1148(42000)' 錯誤，名稱為 command_not_allowed
# 使用 MySQL_error_code
DECLARE command_not_allowed CONDITION FOR 1148;
# 使用 sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
定義處理程式

可以為 SQL 執行過程中發生的某種型別的錯誤定義特殊的處理程式。

定義處理程式使用 DECLARE 語句，語法：

DECLARE 處理方式 HANDLER FOR 錯誤型別 處理語句

處理方式：有 3 個取值，CONTINUE、EXIT、UNDO。

  • CONTINUE：表示遇到錯誤不處理，繼續執行。
  • EXIT：表示遇到錯誤馬上退出。
  • UNDO：表示遇到錯誤後撤回之前的操作，MySQL 中暫時不支援這樣的操作。

錯誤型別：即條件，可以有如下取值。

  • SQLSTATE '字串錯誤碼'：表示長度為 5 的 sqlstate_value 型別的錯誤程式碼。

  • MySQL_error_code：匹配數值型別錯誤程式碼。

  • 錯誤名稱：表示 DECLARE ... CONDITION 定義的錯誤條件名稱。

  • SQLWARNING：匹配所有以 01 開頭的 SQLSTATE 錯誤程式碼。

  • NOT FOUND：匹配所有以 02 開頭的 SQLSTATE 錯誤程式碼。

  • SQLEXCEPTION：匹配所有沒有被 SQLWARNING 或 NOT FOUND 捕獲的 SQLSTATE 錯誤程式碼。

處理語句：如果出現上述條件之一，則採用對應的處理方式，並執行指定的處理語句。語句可以是像 "SET 變數 = 值" 這樣的簡單語句，也可以是使用 "BEGIN ... END" 編寫的複合語句。

定義處理程式的幾種方式，程式碼如下：

# 方法一：捕獲 sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';

# 方法二：捕獲 mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';

# 方法三：先定義條件，再呼叫
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';

# 方法四：使用 SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';

# 方法五：使用 NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';

# 方法六：使用 SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';
案例解決

在儲存過程中，定義處理程式，捕獲 sqlstate_value 值，當遇到 MySQL_error_code 值為 1048 時，執行 CONTINUE 操作，並且將 @proc_value 的值設定為 -1。

mysql> DELIMITER //
mysql> CREATE PROCEDURE UpdateDataWithCondition()
    -> BEGIN
    -> # 定義處理程式
    -> DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value = -1;
    -> SET @x = 1;
    -> UPDATE employees SET email = NULL WHERE last_name = 'Abel';
    -> SET @x = 2;
    -> UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
    -> SET @x = 3;
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> CALL UpdateDataWithCondition();
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @x, @proc_value;
+------+-------------+
| @x   | @proc_value |
+------+-------------+
|    3 |          -1 |
+------+-------------+
1 row in set (0.00 sec)

流程控制

解決複雜問題不可能透過一個 SQL 語句完成，而是需要執行多個 SQL 操作。流程控制語句的作用就是控制儲存過程中 SQL 語句的執行順序，是完成複雜操作必不可少的一部分。只要是執行的程式，流程就分為三大類：

  • 順序結構：程式從上往下依次執行。
  • 分支結構：程式按條件進行選擇執行，從兩條或多條路徑中選擇一條執行。
  • 迴圈結構：程式滿足一定條件下，重複執行一組語句。

針對 MySQL 的流程控制語句主要有 3 類。注意：只能用於儲存程式。

  • 條件判斷語句：IF 語句和 CASE 語句。
  • 迴圈語句：LOOP、WHILE 和 REPEAT 語句。
  • 跳轉語句：ITERATE 和 LEAVE 語句。

分支結構之 IF

語法：

IF 表示式1 THEN 操作1
[ELSEIF 表示式2 THEN 操作2]……
[ELSE 操作N]
END IF
  • 根據表示式的結果為 TRUE 或 FALSE 執行相應的語句。"[]" 中的內容是可選的。
  • 特點：① 不同的表示式對應不同的操作；② 使用在 BEGIN…END 中。

示例：

# 儲存過程 update_salary_by_eid1，定義 IN 引數 emp_id，輸入員工編號，判斷其薪資如果低於 8000 元且入職時間超過 5 年，就漲薪 500 元，否則不變
mysql> DELIMITER //
mysql> CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT)
    -> BEGIN
    -> DECLARE emp_salary DOUBLE;
    -> DECLARE hire_year DOUBLE;
    -> SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
    -> SELECT DATEDIFF(CURDATE(), hire_date)/365 INTO hire_year
    -> FROM employees WHERE employee_id = emp_id;
    -> IF emp_salary < 8000 AND hire_year > 5
    -> THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
    -> END IF;
    -> END //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

mysql> SELECT employee_id, hire_date, salary FROM employees WHERE employee_id = 105;
+-------------+------------+---------+
| employee_id | hire_date  | salary  |
+-------------+------------+---------+
|         105 | 1997-06-25 | 4800.00 |
+-------------+------------+---------+
1 row in set (0.00 sec)

mysql> CALL update_salary_by_eid1(105);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT employee_id, hire_date, salary FROM employees WHERE employee_id = 105;
+-------------+------------+---------+
| employee_id | hire_date  | salary  |
+-------------+------------+---------+
|         105 | 1997-06-25 | 5300.00 |
+-------------+------------+---------+
1 row in set (0.00 sec)

分支結構之 CASE

語法 1：

# 情況一：類似 switch
CASE 表示式
WHEN 值1 THEN 結果1或語句1(如果是語句, 需要加分號)
WHEN 值2 THEN 結果2或語句2(如果是語句, 需要加分號)
...
ELSE 結果n或語句n(如果是語句, 需要加分號)
END [case](如果是放在BEGIN...END中, 需要加上case, 如果放在SELECT後面不需要)

語法 2：

# 情況二：類似多重 if
CASE
WHEN 條件1 THEN 結果1或語句1(如果是語句, 需要加分號)
WHEN 條件2 THEN 結果2或語句2(如果是語句, 需要加分號)
...
ELSE 結果n或語句n(如果是語句, 需要加分號)
END [case](如果是放在BEGIN...END, 中需要加上case, 如果放在SELECT後面不需要)

示例：

# 語法一
CASE val
WHEN 1 THEN SELECT 'val is 1';
WHEN 2 THEN SELECT 'val is 2';
ELSE SELECT 'val is not 1 or 2';
END CASE;

# 語法二
CASE
WHEN val IS NULL THEN SELECT 'val is null';
WHEN val < 0 THEN SELECT 'val is less than 0';
WHEN val > 0 THEN SELECT 'val is greater than 0';
ELSE SELECT 'val is 0';
END CASE;

迴圈結構之 LOOP

LOOP 迴圈語句用來重複執行某些語句。LOOP 內的語句一直重複執行直到迴圈被退出（使用 LEAVE 子句），跳出迴圈過程。

語法：

[loop_label:] LOOP
迴圈執行的語句
END LOOP [loop_label]
  • loop_label 表示 LOOP 語句的標註名稱，可以省略。

示例：

# 使用 LOOP 語句進行迴圈操作，id 值小於 10 時將重複執行迴圈過程
DECLARE id INT DEFAULT 0;
add_loop:LOOP
    SET id = id +1;
    IF id >= 10 THEN LEAVE add_loop;
    END IF;
END LOOP add_loop;

# 儲存過程 update_salary_loop()，宣告 OUT 引數 num，儲存過程中實現迴圈漲薪，薪資漲為原來的 1.1 倍，直到全公司的平均薪資達到 12000 結束
DELIMITER //
CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
    DECLARE avg_salary DOUBLE;
    DECLARE loop_count INT DEFAULT 0;
    SELECT AVG(salary) INTO avg_salary FROM employees;
    label_loop:LOOP
        IF avg_salary >= 12000 THEN LEAVE label_loop;
        END IF;
        UPDATE employees SET salary = salary * 1.1;
        SET loop_count = loop_count + 1;
        SELECT AVG(salary) INTO avg_salary FROM employees;
    END LOOP label_loop;
    SET num = loop_count;
END //
DELIMITER ;

迴圈結構之 WHILE

WHILE 語句建立一個帶條件判斷的迴圈過程。WHILE 在執行語句時，先對指定的表示式進行判斷，如果為真，就執行迴圈內的語句，否則退出迴圈。

語法：

[while_label:] WHILE 迴圈條件 DO
迴圈體
END WHILE [while_label];
  • while_label 為 WHILE 語句的標註名稱，可以省略；如果迴圈條件結果為真，WHILE 語句內的語句或語句群被執行，直至迴圈條件為假，退出迴圈。

示例：

mysql> DELIMITER //
mysql> CREATE PROCEDURE test_while()
    -> BEGIN
    -> DECLARE i INT DEFAULT 0;
    -> WHILE i < 10 DO
    -> SET i = i + 1;
    -> END WHILE;
    -> SELECT i;
    -> END //
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;
mysql> CALL test_while();
+------+
| i    |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

# 儲存過程 update_salary_while()，宣告 OUT 引數 num，儲存過程中實現迴圈降薪，薪資降為原來的 90%，直到全公司的平均薪資達到 5000 結束
DELIMITER //
CREATE PROCEDURE update_salary_while(OUT num INT)
BEGIN
    DECLARE avg_sal DOUBLE ;
    DECLARE while_count INT DEFAULT 0;
    SELECT AVG(salary) INTO avg_sal FROM employees;
    WHILE avg_sal > 5000 DO
        UPDATE employees SET salary = salary * 0.9;
        SET while_count = while_count + 1;
        SELECT AVG(salary) INTO avg_sal FROM employees;
    END WHILE;
    SET num = while_count;
END //
DELIMITER ;

迴圈結構之 REPEAT

REPEAT 語句建立一個帶條件判斷的迴圈過程。與 WHILE 迴圈不同的是，REPEAT 迴圈首先會執行一次迴圈，然後在 UNTIL 中進行表示式的判斷，如果滿足條件就退出，即 END REPEAT；如果條件不滿足，則會就繼續執行迴圈，直到滿足退出條件為止。

語法：

[repeat_label:] REPEAT
迴圈體的語句
UNTIL 結束迴圈的條件表示式
END REPEAT [repeat_label]
  • repeat_label 為 REPEAT 語句的標註名稱，可以省略；REPEAT 語句內的語句或語句群被重複，直至 expr_condition 為真。

示例：

mysql> DELIMITER //
mysql> CREATE PROCEDURE test_repeat()
    -> BEGIN
    -> DECLARE i INT DEFAULT 0;
    -> REPEAT
    -> SET i = i + 1;
    -> UNTIL i >= 10
    -> END REPEAT;
    -> SELECT i;
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> CALL test_repeat();
+------+
| i    |
+------+
|   10 |
+------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

# 儲存過程 update_salary_repeat()，宣告 OUT 引數 num，儲存過程中實現迴圈漲薪，薪資漲為原來的 1.15 倍，直到全公司的平均薪資達到 13000 結束
DELIMITER //
CREATE PROCEDURE update_salary_repeat(OUT num INT)
BEGIN
    DECLARE avg_sal DOUBLE ;
    DECLARE repeat_count INT DEFAULT 0;
    SELECT AVG(salary) INTO avg_sal FROM employees;
    REPEAT
        UPDATE employees SET salary = salary * 1.15;
        SET repeat_count = repeat_count + 1;
        SELECT AVG(salary) INTO avg_sal FROM employees;
        UNTIL avg_sal >= 13000
    END REPEAT;
    SET num = repeat_count;
END //
DELIMITER ;

迴圈結構對比

  • 三種迴圈都可以省略名稱，但如果迴圈中新增了迴圈控制語句（LEAVE 或 ITERATE），則必須新增名稱。

  • LOOP：一般用於實現簡單的 "死" 迴圈；WHILE：先判斷後執行；REPEAT：先執行後判斷，無條件至少執行一次。

跳轉語句之 LEAVE

LEAVE 語句：可以用在迴圈語句內，或者以 BEGIN 和 END 包裹起來的程式體內，表示跳出迴圈或者跳出程式體的操作，可以把 LEAVE 理解為 break。

語法：

LEAVE 標記名
  • label 參數列示迴圈的標誌。LEAVE 和 BEGIN ... END 或迴圈一起被使用。

示例：

# 儲存過程 leave_begin()，宣告 INT 型別的 IN 引數 num，給 BEGIN...END 加標記名，並在 BEGIN...END 中使用 IF 語句判斷 num 引數的值：
# 	如果 num <= 0，則使用 LEAVE 語句退出 BEGIN...END
# 	如果 num = 1，則查詢 employees 表的平均薪資
# 	如果 num = 2，則查詢 employees 表的最低薪資
# 	如果 num > 2，則查詢 employees 表的最高薪資
mysql> DELIMITER //
mysql> CREATE PROCEDURE leave_begin(IN num INT)
    -> begin_label: BEGIN
    -> IF num<=0
    -> THEN LEAVE begin_label;
    -> ELSEIF num=1
    -> THEN SELECT AVG(salary) FROM employees;
    -> ELSEIF num=2
    -> THEN SELECT MIN(salary) FROM employees;
    -> ELSE
    -> SELECT MAX(salary) FROM employees;
    -> END IF;
    -> SELECT COUNT(*) FROM employees;
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> CALL leave_begin(0);
Query OK, 0 rows affected (0.00 sec)

mysql> CALL leave_begin(1);
+-------------+
| AVG(salary) |
+-------------+
| 6466.355140 |
+-------------+
1 row in set (0.00 sec)

+----------+
| COUNT(*) |
+----------+
|      107 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL leave_begin(2);
+-------------+
| MIN(salary) |
+-------------+
|     2100.00 |
+-------------+
1 row in set (0.00 sec)

+----------+
| COUNT(*) |
+----------+
|      107 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL leave_begin(3);
+-------------+
| MAX(salary) |
+-------------+
|    24000.00 |
+-------------+
1 row in set (0.00 sec)

+----------+
| COUNT(*) |
+----------+
|      107 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

# 儲存過程 leave_while()，宣告 OUT 引數 num，儲存過程中使用 WHILE 迴圈降低薪資為原來薪資的 90%，直到全公司的平均薪資小於等於 10000
DELIMITER //
CREATE PROCEDURE leave_while(OUT num INT)
BEGIN
    DECLARE avg_sal DOUBLE; # 記錄平均工資
    DECLARE while_count INT DEFAULT 0; # 記錄迴圈次數
    SELECT AVG(salary) INTO avg_sal FROM employees; # ① 初始化條件
    while_label:WHILE TRUE DO # ② 迴圈條件
        # ③ 迴圈體
        IF avg_sal <= 10000 THEN
        LEAVE while_label;
        END IF;
        UPDATE employees SET salary = salary * 0.9;
        SET while_count = while_count + 1;
        # ④ 迭代條件
        SELECT AVG(salary) INTO avg_sal FROM employees;
    END WHILE;
    # 賦值
    SET num = while_count;
END //
DELIMITER ;

跳轉語句之 ITERATE

ITERATE 語句：只能用在迴圈語句（LOOP、REPEAT 和 WHILE 語句）內，表示重新開始迴圈，將執行順序轉到語句段開頭處。可以把 ITERATE 理解為 continue。

語法：

ITERATE label
  • label 參數列示迴圈的標誌。ITERATE 語句必須跟在迴圈標誌前面。

示例：

mysql> DELIMITER //
mysql> CREATE PROCEDURE test_iterate()
    -> BEGIN
    -> DECLARE num INT DEFAULT 0;
    -> my_loop:LOOP
    -> SET num = num + 1;
    -> IF num < 10
    -> THEN ITERATE my_loop;
    -> ELSEIF num > 15
    -> THEN LEAVE my_loop;
    -> END IF;
    -> SELECT 'AABBCC';
    -> END LOOP my_loop;
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> CALL test_iterate();
+--------+
| AABBCC |
+--------+
| AABBCC |
+--------+
1 row in set (0.00 sec)

+--------+
| AABBCC |
+--------+
| AABBCC |
+--------+
1 row in set (0.00 sec)

+--------+
| AABBCC |
+--------+
| AABBCC |
+--------+
1 row in set (0.00 sec)

+--------+
| AABBCC |
+--------+
| AABBCC |
+--------+
1 row in set (0.00 sec)

+--------+
| AABBCC |
+--------+
| AABBCC |
+--------+
1 row in set (0.00 sec)

+--------+
| AABBCC |
+--------+
| AABBCC |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

遊標

遊標是什麼

雖然可以透過篩選條件 WHERE 和 HAVING，或者是限定返回記錄的關鍵字 LIMIT 返回一條記錄，但是，卻無法在結果集中像指標一樣，向前定位一條記錄、向後定位一條記錄，或者是隨意定位到某一條記錄，並對記錄的資料進行處理。

這個時候，就可以用到遊標 (或游標)。遊標，提供了一種靈活的操作方式，讓我們能夠對結果集中的每一條記錄進行定位，並對指向的記錄中的資料進行操作的資料結構。遊標讓 SQL 這種面向集合的語言有了程序導向開發的能力。

在 SQL 中，遊標是一種臨時的資料庫物件，可以指向儲存在資料庫表中的資料行指標。這裡遊標 充當了
指標的作用 ，我們可以透過操作遊標來對資料行進行操作。

MySQL 中游標可以在儲存過程和函式中使用。

遊標使用步驟

遊標必須在宣告處理程式之前被宣告，並且變數和條件還必須在宣告遊標或處理程式之前被宣告。

如果想要使用遊標，一般需要經歷四個步驟。不同的 DBMS 中，使用遊標的語法可能略有不同。

第一步，宣告遊標。

在 MySQL 中，使用DECLARE 關鍵字宣告遊標，語法：

DECLARE cursor_name CURSOR FOR select_statement;

上述語法適用於 MySQL，SQL Server，DB2 和 MariaDB。如果是用 Oracle 或者 PostgreSQL，需要寫成：

DECLARE cursor_name CURSOR IS select_statement;

要使用 SELECT 語句來獲取資料結果集，而此時還沒有開始遍歷資料，select_statement 代表的是 SELECT 語句，返回一個用於建立遊標的結果集。

示例：

DECLARE cur_emp CURSOR FOR SELECT employee_id, salary FROM employees;

第二步，開啟遊標。

當定義好遊標之後，如果想要使用遊標，必須先開啟遊標。開啟遊標的時候，SELECT 語句的查詢結果集就會送到遊標工作區，為後面遊標的逐條讀取結果集中的記錄做準備。語法：

OPEN cursor_name;

示例：

OPEN cur_emp;

第三步，使用遊標（從遊標中取得資料）。

語法：

FETCH cursor_name INTO var_name [, var_name] ...

作用是使用 cursor_name 這個遊標來讀取當前行，並且將資料儲存到 var_name 這個變數中，然後遊標指標指到下一行。如果遊標讀取的資料行有多個列名，則在 INTO 關鍵字後面賦值給多個變數名即可。

注意：

  • var_name 必須在宣告遊標之前就定義好。

  • 遊標的查詢結果集中的欄位數，必須跟 INTO 後面的變數數一致。否則，在儲存過程執行的時候，MySQL 會提示錯誤。

示例：

FETCH cur_emp INTO emp_id, emp_sal;

第四步，關閉遊標。

有 OPEN 就會有 CLOSE，也就是開啟和關閉遊標。當使用完遊標後需要關閉掉該遊標，因為遊標會佔用系統資源 ，如果不及時關閉，遊標會一直保持到儲存過程結束，影響系統執行的效率。關閉遊標的操作，會釋放遊標佔用的系統資源。

語法：

CLOSE cursor_name;

關閉遊標之後，就不能再檢索查詢結果中的資料行，如果需要檢索只能再次開啟遊標。

示例：

CLOSE cur_emp;

遊標使用示例

建立儲存過程 get_count_by_limit_total_salary()，宣告 IN 引數 limit_total_salary，DOUBLE型別；宣告 OUT 引數 total_count，INT 型別。儲存過程的功能：實現累加薪資最高的幾個員工的薪資值，直到薪資總和達到 limit_total_salary 引數的值，返回累加的人數給 total_count。

mysql> DELIMITER //
mysql> CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)
    -> BEGIN
    -> DECLARE sum_salary DOUBLE DEFAULT 0; # 記錄累加的總工資
    -> DECLARE cursor_salary DOUBLE DEFAULT 0; # 記錄某一個工資值
    -> DECLARE emp_count INT DEFAULT 0; # 記錄迴圈個數
    -> # 定義遊標
    -> DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
    -> # 開啟遊標
    -> OPEN emp_cursor;
    -> REPEAT
    -> # 使用遊標（從遊標中獲取資料）
    -> FETCH emp_cursor INTO cursor_salary;
    -> SET sum_salary = sum_salary + cursor_salary;
    -> SET emp_count = emp_count + 1;
    -> UNTIL sum_salary >= limit_total_salary
    -> END REPEAT;
    -> SET total_count = emp_count;
    -> # 關閉遊標
    -> CLOSE emp_cursor;
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

mysql> SET @total_count='';
Query OK, 0 rows affected (0.00 sec)

mysql> CALL get_count_by_limit_total_salary(100000, @total_count);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @total_count;
+--------------+
| @total_count |
+--------------+
|            7 |
+--------------+

mysql> CALL get_count_by_limit_total_salary(150000, @total_count);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @total_count;
+--------------+
| @total_count |
+--------------+
|           11 |
+--------------+
1 row in set (0.00 sec)

遊標總結

遊標是 MySQL 的一個重要的功能，為逐條讀取結果集中的資料，提供了完美的解決方案。跟在應用層面實現相同的功能相比，遊標可以在儲存程式中使用，效率高，程式也更加簡潔。

但同時也會帶來一些效能問題，比如在使用遊標的過程中，會對資料行進行加鎖，這樣在業務併發量大的時候，不僅會影響業務之間的效率，還會消耗系統資源，造成記憶體不足，這是因為遊標是在記憶體中進行的處理。

觸發器

在實際開發中，經常會遇到這樣的情況：有 2 個或者多個相互關聯的表，如商品資訊和庫存資訊，分別存放在 2 個不同的資料表中，在新增一條新商品記錄的時候，為了保證資料的完整性，必須同時在庫存表中新增一條庫存記錄。

這樣一來，我們就必須把這兩個關聯的操作步驟寫到程式裡面，而且要用事務包裹起來，確保這兩個操作成為一個原子操作，要麼全部執行，要麼全部不執行。要是遇到特殊情況，可能還需要對資料進行手動維護，這樣就很容易忘記其中的一步，導致資料缺失。

這個時候，可以使用觸發器。建立一個觸發器，讓商品資訊資料的插入操作自動觸發庫存資料的插入操作。這樣一來，就不用擔心因為忘記新增庫存資料而導致的資料缺失了。

概述

MySQL 從 5.0.2 版本開始支援觸發器。MySQL 的觸發器和儲存過程一樣，都是嵌入到 MySQL 伺服器的一段程式。

觸發器是由事件來觸發某個操作，這些事件包括INSERT、UPDATE、DELETE事件。所謂事件就是指使用者的動作或者觸發某項行為。如果定義了觸發程式，當資料庫執行這些語句時候，就相當於事件發生了，就會自動激發觸發器執行相應的操作。

當對資料表中的資料執行插入、更新和刪除操作，需要自動執行一些資料庫邏輯時，可以使用觸發器來實現。

觸發器的建立

語法：

CREATE TRIGGER 觸發器名稱
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
觸發器執行的語句塊;
  • 表名：表示觸發器監控的物件。
  • BEFORE|AFTER：表示觸發的時間。BEFORE 表示在事件之前觸發；AFTER 表示在事件之後觸發。
  • INSERT|UPDATE|DELETE：表示觸發的事件。
    • INSERT：表示插入記錄時觸發；
    • UPDATE：表示更新記錄時觸發；
    • DELETE：表示刪除記錄時觸發。
  • 觸發器執行的語句塊：可以是單條 SQL 語句，也可以是由 BEGIN…END 結構組成的複合語句塊。

示例：

# 建立資料表
mysql> CREATE TABLE test_trigger (
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> t_note VARCHAR(30)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE test_trigger_log (
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> t_log VARCHAR(30)
    -> );
Query OK, 0 rows affected (0.04 sec)

# 建立名稱為 before_insert 的觸發器，向 test_trigger 資料表插入資料之前，向 test_trigger_log 資料表中插入 before_insert 的日誌資訊
mysql> DELIMITER //
mysql> CREATE TRIGGER before_insert
    -> BEFORE INSERT ON test_trigger
    -> FOR EACH ROW
    -> BEGIN
    -> INSERT INTO test_trigger_log (t_log)
    -> VALUES('before_insert');
    -> END //
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;

# 向 test_trigger 資料表中插入資料
mysql> INSERT INTO test_trigger (t_note) VALUES ('TEST BEFORE INSERT TRIGGER');
Query OK, 1 row affected (0.00 sec)

# 檢視 test_trigger_log 資料表中的資料
mysql> SELECT * FROM test_trigger;
+----+----------------------------+
| id | t_note                     |
+----+----------------------------+
|  1 | TEST BEFORE INSERT TRIGGER |
+----+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test_trigger_log;
+----+---------------+
| id | t_log         |
+----+---------------+
|  1 | before_insert |
+----+---------------+
1 row in set (0.00 sec)

# 建立名稱為 after_insert 的觸發器，向 test_trigger 資料表插入資料之後，向 test_trigger_log 資料表中插入 after_insert 的日誌資訊
mysql> DELIMITER //
mysql> CREATE TRIGGER after_insert
    -> AFTER INSERT ON test_trigger
    -> FOR EACH ROW
    -> BEGIN
    -> INSERT INTO test_trigger_log (t_log)
    -> VALUES('after_insert');
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

# 向 test_trigger 資料表中插入資料
mysql> INSERT INTO test_trigger (t_note) VALUES ('TEST AFTER INSERT TRIGGER');
Query OK, 1 row affected (0.01 sec)

# 檢視 test_trigger_log 資料表中的資料
mysql> SELECT * FROM test_trigger;
+----+----------------------------+
| id | t_note                     |
+----+----------------------------+
|  1 | TEST BEFORE INSERT TRIGGER |
|  2 | TEST AFTER INSERT TRIGGER  |
+----+----------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test_trigger_log;
+----+---------------+
| id | t_log         |
+----+---------------+
|  1 | before_insert |
|  2 | before_insert |
|  3 | after_insert  |
+----+---------------+
3 rows in set (0.00 sec)

觸發器的檢視和刪除

檢視觸發器

檢視觸發器是檢視資料庫中已經存在的觸發器的定義、狀態和語法資訊等。

方式 1：檢視當前資料庫的所有觸發器的定義。

SHOW TRIGGERS

方式 2：檢視當前資料庫中某個觸發器的定義。

SHOW CREATE TRIGGER 觸發器名

方式 3：從系統庫 information_schema 的 TRIGGERS 表中查詢 salary_check_trigger 觸發器的資訊。

SELECT * FROM information_schema.TRIGGERS;

刪除觸發器

觸發器也是資料庫物件，刪除觸發器也用 DROP 語句，語法：

DROP TRIGGER IF EXISTS 觸發器名稱

觸發器的優缺點

優點

1、觸發器可以確保資料的完整性。

假設用進貨單頭表（demo.importhead）來儲存進貨單的總體資訊，包括進貨單編號、供貨商編號、倉庫編號、總計進貨數量、總計進貨金額和驗收日期。用進貨單明細表（demo.importdetails）來儲存進貨商品的明細，包括進貨單編號、商品編號、進貨數量、進貨價格和進貨金額。

image-20230521232114165

每當錄入、刪除和修改一條進貨單明細資料的時候，進貨單明細表裡的資料就會發生變動。這個時候，在進貨單頭表中的總計數量和總計金額就必須重新計算，否則，進貨單頭表中的總計數量和總計金額就不等於進貨單明細表中數量合計和金額合計了，這就是資料不一致。

image-20230521232141988

為了解決這個問題，可以使用觸發器，規定每當進貨單明細表有資料插入、修改和刪除的操作時，自動觸發 2 步操作：

  • 1）重新計算進貨單明細表中的數量合計和金額合計；
  • 2）用第一步中計算出來的值更新進貨單頭表中的合計數量與合計金額。

這樣一來，進貨單頭表中的合計數量與合計金額的值，就始終與進貨單明細表中計算出來的合計數量與合計金額的值相同，資料就是一致的，不會互相矛盾。

2、觸發器可以幫助我們記錄操作日誌。
利用觸發器，可以具體記錄什麼時間發生了什麼。比如，記錄修改會員儲值金額的觸發器，就是一個很好的例子。這對還原操作執行時的具體場景，更好地定位問題原因很有幫助。

3、觸發器還可以用在運算元據前，對資料進行合法性檢查。

比如，超市進貨的時候，需要庫管錄入進貨價格。但是，人為操作很容易犯錯誤，比如說在錄入數量的時候，把條形碼掃進去了；錄入金額的時候，看串了行，錄入的價格遠超售價，導致賬面上的鉅虧……

這些都可以透過觸發器，在實際插入或者更新操作之前，對相應的資料進行檢查，及時提示錯誤，防止錯誤資料進入系統。

缺點

1、觸發器最大的一個問題就是可讀性差。

因為觸發器儲存在資料庫中，並且由事件驅動，這就意味著觸發器有可能不受應用層的控制，這對系統維護是非常有挑戰的。

比如，建立觸發器用於修改會員儲值操作。如果觸發器中的操作出了問題，會導致會員儲值金額更新失敗。例如：

mysql> update demo.membermaster set memberdeposit = 20 where memberid = 2;
ERROR 1054 (42S22): Unknown column 'aa' in 'field list'

結果顯示，系統提示錯誤，欄位 aa 不存在。這是因為，觸發器中的資料插入操作多了一個欄位，系統提示錯誤。可是，如果你不瞭解這個觸發器，很可能會認為是更新語句本身的問題，或者是會員資訊表的結構出了問題。說不定你還會給會員資訊表新增一個叫 aa 的欄位，試圖解決這個問題，結果只能是白費力。

2、相關資料的變更，可能會導致觸發器出錯。

特別是資料表結構的變更，都可能會導致觸發器出錯，進而影響資料操作的正常執行。這些都會由於觸發器本身的隱蔽性，影響到應用中錯誤原因排查的效率。

注意點

注意，如果在子表中定義了外來鍵約束，並且外來鍵指定了 ON UPDATE/DELETE CASCADE/SET NULL 子句，此時修改父表被引用的鍵值或刪除父表被引用的記錄行時，也會引起子表的修改和刪除操作，此時基於子表的 UPDATE 和 DELETE 語句定義的觸發器並不會被啟用。

例如：基於子表員工表（t_employee）的 DELETE 語句定義了觸發器 t1，而子表的部門編號（did）欄位定義了外來鍵約束，引用了父表部門表（t_department）的主鍵列部門編號（did），並且該外來鍵加了 ON DELETE SET NULL 子句，那麼如果此時刪除父表部門表（t_department）在子表員工表（t_employee）有匹配記錄的部門記錄時，會引起子表員工表（t_employee）匹配記錄的部門編號（did）修改為NULL。但是，此時不會啟用觸發器 t1，只有直接對子表員工表（t_employee）執行 DELETE 語句時，才會啟用觸發器 t1。

原文連結

https://github.com/ACatSmiling/zero-to-zero/blob/main/RelationalDatabase/mysql.md

相關文章