@author: Tobin
@date: 2019/11/7 16:56:50
游标主要用于交互式应用,其中用户需要滚动屏幕上的数据,并对数据做出浏览和更改。
在MySQL中,只能用于存储过程。
CREATE PROCEDURE processorders()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8, 2);
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
DECLARE CONTINUE HANDLER FOR SQLTATE '02000' SET done=1;
CREATE TABLE IF NOT EXISTS ordertotals
(order_num INT, total DECIMAL(8, 2));
OPEN ordernumbers;
REPEAT
FETCH ordernumbers INTO o;
CALL ordertotal(o, 1, t);
INSERT INTO ordertotals(order_num, total)VALUES(o, t);
UNTIL done END REPEAT;
CLOSE ordernumbers;
END;