存儲過程補充——定義條件、處理程序及游標使用

文章目錄

1. 定義條件與處理程序
- 1.1 定義條件
- 1.2 處理程序
- 1.3 案例演示
2. 游標
- 2.1 使用游標
- - 第一步，聲明游標
  - 第二步，打開游標
  - 第三步，使用游標（從游標中取得數據）
  - 第四步，關閉游標
- 2.2 舉例
- 2.3 小結

在 MySQL 數據庫的開發過程中，為了提升程序的健壯性、可靠性以及數據處理的靈活性，常常需要面對錯誤處理和結果集逐行操作的問題。定義條件與處理程序、游標就是 MySQL 提供的強大工具，它們分別用于更精細地控制和處理錯誤異常情況，以及對查詢結果集中的每一行數據進行訪問和操作。本文將深入介紹這些重要特性，通過詳細的語法說明和豐富的案例演示，幫助大家更好地掌握和運用它們。

1. 定義條件與處理程序

在 MySQL 中，定義條件 與 處理程序 主要用于存儲過程、函數、觸發器等復雜的程序單元里，幫助開發者對錯誤和異常情況進行更精細的控制和處理，從而提升程序的健壯性和可靠性。

定義條件 是事先定義程序執行過程中可能遇到的問題；
處理程序 定義了在遇到問題時應當采取的處理方式，保證存儲過程或函數在遇到警告或錯誤時能繼續執行。

1.1 定義條件

定義條件是一種命名異常的方式，將一個錯誤碼或錯誤條件與一個自定義的名稱關聯起來，讓代碼更具可讀性和可維護性。

當程序執行過程中出現錯誤時，使用自定義的名稱來指代特定的錯誤，避免在代碼里直接使用錯誤碼，使代碼更易理解和管理。

定義條件使用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';

1.2 處理程序

處理程序用于指定當特定錯誤或異常發生時要執行的操作。在程序運行過程中，一旦出現預定義的錯誤或異常，就會執行相應的處理程序，從而避免程序崩潰，同時可以根據具體情況進行錯誤日志記錄、回滾事務等操作。

定義處理程序時，使用 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 編寫的復合語句。

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

#方法1：捕獲sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';#方法2：捕獲mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';#方法3：先定義條件，再調用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';#方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';#方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';#方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

1.3 案例演示

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

DELIMITER //CREATE PROCEDURE UpdateDataNoCondition()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 //
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)

在存儲過程中，定義處理程序，當執行 SELECT * FROM non_existent_table; 語句時，如果表不存在（錯誤碼 1146），就會觸發處理程序，記錄錯誤日志，然后繼續執行后續語句。

-- 創建錯誤日志表
CREATE TABLE error_log (id INT AUTO_INCREMENT PRIMARY KEY,error_message VARCHAR(255),error_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);DELIMITER //CREATE PROCEDURE test_procedure()
BEGIN-- 定義條件DECLARE table_not_found CONDITION FOR 1146;-- 定義處理程序DECLARE CONTINUE HANDLER FOR table_not_foundBEGIN-- 記錄錯誤日志INSERT INTO error_log (error_message) VALUES ('Table not found');-- 可以在這里添加其他處理邏輯END;-- 嘗試查詢一個可能不存在的表SELECT * FROM non_existent_table;-- 繼續執行后續語句SELECT 'Procedure continues...';
END //DELIMITER ;

2. 游標

游標是指向查詢結果集中某一行的指針，通過游標可以在結果集中進行移動，從而對每一行數據進行訪問和處理。在 SQL 語句中，查詢操作通常會返回一個結果集，而游標提供了一種機制，允許按順序逐個處理結果集中的行，這在需要對每一行數據進行復雜操作時非常有用。

在這里插入圖片描述

2.1 使用游標

游標必須在聲明處理程序之前被聲明，并且變量和條件還必須在聲明游標或處理程序之前被聲明。

第一步，聲明游標

在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;DECLARE cursor_fruit CURSOR FOR
SELECT f_name, f_price FROM fruits ;

第二步，打開游標

打開游標的語法如下：

OPEN cursor_name;

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

OPEN cur_emp ;

第三步，使用游標（從游標中取得數據）

從游標中取得數據語法如下：

FETCH cursor_name INTO var_name [, var_name] ...

這句的作用是使用 cursor_name 這個游標來讀取當前行，并且將數據保存到 var_name 這個變量中，游標指針指到下一行。如果游標讀取的數據行有多個列名，則在 INTO 關鍵字后面賦值給多個變量名即可。

注意：var_name必須在聲明游標之前就定義好。

FETCH cur_emp INTO emp_id, emp_sal ;

注意：游標的查詢結果集中的字段數，必須跟 INTO 后面的變量數一致，否則，在存儲過程執行的時候，MySQL 會提示錯誤。

第四步，關閉游標

關閉游標語法如下：

CLOSE cursor_name;

當使用完游標后需要關閉掉該游標。因為游標會占用系統資源，如果不及時關閉，游標會一直保持到存儲過程結束，影響系統運行的效率。而關閉游標的操作，會釋放游標占用的系統資源。

關閉游標之后，我們就不能再檢索查詢結果中的數據行，如果需要檢索只能再次打開游標。

CLOSE cur_emp;

2.2 舉例

創建存儲過程“get_count_by_limit_total_salary()”，聲明IN參數 limit_total_salary，DOUBLE類型；聲明 OUT參數total_count，INT類型。函數的功能可以實現累加薪資最高的幾個員工的薪資值，直到薪資總和達到limit_total_salary參數的值，返回累加的人數給total_count。

DELIMITER //CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)
BEGINDECLARE 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_salaryEND REPEAT;SET total_count = emp_count;#關閉游標CLOSE emp_cursor;
END //
DELIMITER ;