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;
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 + 標籤 | 複雜流程控制、多層嵌套 |
| REPEAT | UNTIL condition END REPEAT,至少執行一次 | 後測試循環 |
| WHILE | WHILE condition DO ... END WHILE,先判斷 | 前測試循環 |
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;
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_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,否則第二個游標不會執行。
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;
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();