《MySQL必知必会》第二十四章:使用游标

@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;
posted @ 2019-11-07 18:39  我的小叮当  阅读(135)  评论(0编辑  收藏  举报