MySql存儲過程循環的使用分析詳解

Posted on by

簡介

每一門數據庫語言語法都基本相似，但是對於他們各自的一些特性（函數、存儲過程等）的用法就不大相同瞭，就好比Oracle與Mysql存儲過程寫起來就很多不同的地方，在這裡主要是跟大傢分享一下MySql存儲過程中使用遊標循環的處理方法。

場景描述

我們舉一個簡單的場景，首先我們可能會有這樣一種情況，考試成績表(t_achievement)有一堆的sql腳本處理，需要依賴另一個學生表(t_student)數據對部分學生做考試成績匯總記錄到成績匯總表（t_achievement_report）。

解決方案

  • 有一種方式就是通過代碼優先將要匯總的學生表數據獲取出來，然後按成績匯總流程逐個將學生信息數據傳遞到成績匯總業務代碼進行處理。
  • 另一種方式也是我們今天的主題，那就是通過存儲過程的方式去做。

案例

建表語句：

-- 學生信息表
DROP TABLE IF EXISTS t_student;
CREATE TABLE `t_student` (
  `id` BIGINT(12) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `code` VARCHAR(10) NOT NULL COMMENT '學號',
  `name` VARCHAR(20) NOT NULL COMMENT '姓名',
  `age` INT(2) NOT NULL COMMENT '年齡',
  `gender` CHAR(1) NOT NULL COMMENT '性別（M：男，F：女）',
  PRIMARY KEY (`id`),
  UNIQUE KEY UK_STUDENT (`code`)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 學生成績表
DROP TABLE IF EXISTS t_achievement;
CREATE TABLE `t_achievement` (
  `id` BIGINT(12) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `year` INT(4) NOT NULL COMMENT '學年',
  `subject` CHAR(2) NOT NULL COMMENT '科目（01：語文，02：數學，03：英語）',
  `score` INT(3) NOT NULL COMMENT '得分',
  `student_id` BIGINT(12) NOT NULL COMMENT '所屬學生id',
  PRIMARY KEY (`id`) 
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 成績匯總表
DROP TABLE IF EXISTS t_achievement_report;
CREATE TABLE `t_achievement_report` (
  `id` BIGINT(12) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `student_id` BIGINT(12) NOT NULL COMMENT '學生id',
  `year` INT(4) NOT NULL COMMENT '學年',
  `total_score` INT(4) NOT NULL COMMENT '總分',
  `avg_score` DECIMAL(4,2) NOT NULL COMMENT '平均分',
  PRIMARY KEY (`id`) 
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

初始化數據：

INSERT INTO t_student(id, CODE, NAME, age, gender) VALUES
(1, '2022010101', '小張', 18, 'M'),
(2, '2022010102', '小李', 18, 'F'),
(3, '2022010103', '小明', 18, 'M');
INSERT INTO t_achievement(YEAR, SUBJECT, score, student_id) VALUES
(2022, '01', 80, 1),
(2022, '02', 85, 1),
(2022, '03', 90, 1),
(2022, '01', 60, 2),
(2022, '02', 90, 2),
(2022, '03', 98, 2),
(2022, '01', 75, 3),
(2022, '02', 100, 3),
(2022, '03', 85, 3);

存儲過程：

在這裡主要以上面的場景為例，使用存儲過程循環去處理數據。寫一個存儲過程，將以上數據每個學生的成績進行匯總。

-- 如果存儲過程存在，先刪除存儲過程
DROP PROCEDURE IF EXISTS statistics_achievement;
DELIMITER $$
-- 定義存儲過程
CREATE PROCEDURE statistics_achievement()
BEGIN
        -- 定義變量記錄循環處理是否完成
	DECLARE done BOOLEAN DEFAULT FALSE;
        -- 定義變量傳遞學生id
	DECLARE studentid BIGINT(12);
	-- 定義遊標
	DECLARE cursor_student CURSOR FOR SELECT id FROM t_student;
	-- 定義CONTINUE HANDLER，當循環結束時 done=true
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=TRUE;
	-- 打開遊標
	OPEN cursor_student;
	-- 重復遍歷
	REPEAT 
		-- 每次讀取一次遊標
		FETCH cursor_student INTO studentid;
                -- 計算總分、平均分插入匯總表
		INSERT INTO t_achievement_report(student_id, `YEAR`, total_score, avg_score)
		SELECT studentid, `YEAR`, SUM(score), ROUND(SUM(score) / 3, 2) FROM t_achievement t1 WHERE student_id = studentid AND NOT EXISTS(
			SELECT 1 FROM t_achievement_report t2 WHERE student_id = studentid AND t1.year = t2.year
		) GROUP BY `YEAR`;
	-- 結束循環，意思是等到done=true時，結束循環REPEAT
	UNTIL done END REPEAT;
	-- 查詢結果，僅會展示查出的最後一條
	SELECT studentid;
	-- 關閉遊標
	CLOSE cursor_student;
END$$
DELIMITER ;
-- 執行存儲過程
CALL statistics_achievement();
  • 執行結果，返回查詢結果3，即最後一條學生記錄id

總結

存儲過程也有很強大的功能，如果是一名DBA那麼寫存儲過程是分分鐘的事，但是作為一名專做業務的碼農還是不建議去使用存儲過程寫業務代碼。前公司同事適應瞭寫存儲過程，有業務改動時不時的直接用存儲過程搞定瞭，到最後直接就是一大堆堆存儲過程代碼，一個存儲過程下來幾百上千行sql代碼頭都看暈掉，出問題巨難維護，稍有不熟的人員都不敢輕舉妄動，今天在這裡也隻是為瞭講解存儲過程中的循環而舉瞭個栗子請別介意。

總之我認為存儲過程主要還是用來臨時處理一些數據方便而用一下，特別有些業務改造大，需要做數據割接總不能挨個去寫一個業務代碼吧。

到此這篇關於MySql存儲過程循環的使用分析詳解的文章就介紹到這瞭,更多相關MySql存儲過程循環內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：