萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 預存程序游標完整指南:基礎、循環控制與多游標實戰

游標基礎語法

DECLARE cursor_name CURSOR FOR SELECT ...;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cursor_name;
FETCH cursor_name INTO var1, var2...;
CLOSE cursor_name;

範例 1:簡單游標遍歷更新匯總表

DELIMITER $$
DROP PROCEDURE IF EXISTS getUserInfo $$
CREATE PROCEDURE getUserInfo(IN date_day DATETIME)
BEGIN
    DECLARE _userName VARCHAR(12);
    DECLARE _chinese INT;
    DECLARE _math INT;
    DECLARE done INT DEFAULT 0;

    DECLARE rs_cursor CURSOR FOR
        SELECT username, chinese, math
        FROM userInfo
        WHERE DATEDIFF(createDate, date_day) = 0;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    IF date_day IS NULL THEN
        SET date_day = DATE_ADD(NOW(), INTERVAL -1 DAY);
    END IF;

    OPEN rs_cursor;
    cursor_loop: LOOP
        FETCH rs_cursor INTO _userName, _chinese, _math;
        IF done = 1 THEN
            LEAVE cursor_loop;
        END IF;
        UPDATE infoSum SET total = _chinese + _math WHERE UserName = _userName;
    END LOOP cursor_loop;
    CLOSE rs_cursor;
END $$
DELIMITER ;

三種循環結構對比

結構語法特點適用場景
LOOP需配合 LEAVE/ITERATE + 標籤複雜流程控制、多層嵌套
REPEATUNTIL condition END REPEAT,至少執行一次後測試循環
WHILEWHILE condition DO ... END WHILE,先判斷前測試循環

REPEAT 範例

DECLARE num INT DEFAULT 1;
DECLARE my_string VARCHAR(255) DEFAULT '';
REPEAT
    SET my_string = CONCAT(my_string, num, ',');
    SET num = num + 1;
UNTIL num >= 5
END REPEAT;

WHILE 範例

DECLARE num INT DEFAULT 1;
DECLARE my_string VARCHAR(255) DEFAULT '';
WHILE num < 10 DO
    SET my_string = CONCAT(my_string, num, ',');
    SET num = num + 1;
END WHILE;

LOOP + ITERATE/LEAVE 範例

loop_label: LOOP
    IF num >= 10 THEN
        LEAVE loop_label;   -- 相當於 break
    END IF;
    SET num = num + 1;
    IF (num % 3) = 0 THEN
        ITERATE loop_label; -- 相當於 continue
    END IF;
    SET my_string = CONCAT(my_string, num, ',');
END LOOP;

多游標處理關鍵點

聲明順序固定:變量 → 游標 → HANDLER。多游標時,每個游標使用前必須重置 done=0,否則第二個游標不會執行。

方式一:順序執行 + 重置 done

OPEN cur_1;
REPEAT FETCH cur_1 INTO ...; UNTIL done END REPEAT;
CLOSE cur_1;

SET done = 0;  -- 關鍵!

OPEN cur_2;
REPEAT FETCH cur_2 INTO ...; UNTIL done END REPEAT;
CLOSE cur_2;

方式二:BEGIN...END 塊隔離變量作用域(推薦)


OPEN cur_1;
REPEAT ... UNTIL done END REPEAT;
CLOSE cur_1;

BEGIN
    DECLARE done INT DEFAULT 0;  -- 新作用域,互不干擾
    DECLARE tid INT;
    DECLARE tname VARCHAR(50);
    DECLARE cur_2 CURSOR FOR SELECT id, name FROM netingcn_proc_test;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cur_2;
    REPEAT ... UNTIL done END REPEAT;
    CLOSE cur_2;
END;

完整多游標範例(建表+數據+雙游標存儲過程)

DROP TABLE IF EXISTS netingcn_proc_test;
CREATE TABLE netingcn_proc_test (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(20),
    password VARCHAR(20),
    PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO netingcn_proc_test (name, password) VALUES
('procedure1','pass1'),('procedure2','pass2'),
('procedure3','pass3'),('procedure4','pass4');

DROP PROCEDURE IF EXISTS test_proc_2;
DELIMITER //
CREATE PROCEDURE test_proc_2()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE tname VARCHAR(50);
    DECLARE tpass VARCHAR(50);
    DECLARE cur_1 CURSOR FOR SELECT name, password FROM netingcn_proc_test;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur_1;
    REPEAT
        FETCH cur_1 INTO tname, tpass;
        IF NOT done THEN SELECT tname, tpass; END IF;
    UNTIL done END REPEAT;
    CLOSE cur_1;

    BEGIN
        DECLARE done INT DEFAULT 0;
        DECLARE tid INT;
        DECLARE tname VARCHAR(50);
        DECLARE cur_2 CURSOR FOR SELECT id, name FROM netingcn_proc_test;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
        OPEN cur_2;
        REPEAT
            FETCH cur_2 INTO tid, tname;
            IF NOT done THEN SELECT tid, tname; END IF;
        UNTIL done END REPEAT;
        CLOSE cur_2;
    END;
END //
DELIMITER ;

CALL test_proc_2();

避坑清單

  • 變量名不可與游標 SELECT 列名相同,否則 FETCH 結果為 NULL
  • HANDLER 必須在游標之後聲明
  • 多游標務必重置 done或用BEGIN 塊隔離
  • 游標只能單向讀取,不支援捲動/回滾
copyright © 萬盛學電腦網 all rights reserved