MySQL 游标

DROP PROCEDURE IF EXISTS processorders;
CREATE PROCEDURE processorders()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE a INT;
DECLARE b VARCHAR(20);

-- 定义游标遍历时，作为判断是否遍历完全部记录的标记
DECLARE num INT DEFAULT 0;
DECLARE ordernumbers CURSOR FOR SELECT newprescription_id, prescription_name FROM ecst_newprescription;
-- 声明当游标遍历完全部记录后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET num=1;
CREATE TABLE IF NOT EXISTS zz_ordertotals(
order_num INT,
d_name VARCHAR(20)
);
OPEN ordernumbers;
-- while循环
WHILE num <> 1 DO
FETCH ordernumbers INTO a, b;
-- 如果不加判断，会多循环一次。最后一次读取游标时，no已经是1，但是进入循环之前是0，故可以进入循环，会多循环一次；
IF num <> 1 THEN
INSERT INTO zz_ordertotals(order_num,d_name) VALUES(a, b);
END IF;
END WHILE;
CLOSE ordernumbers;
END;

 

CALL processorders();