1.7（SQL学习笔记）游标

一、游标简介

　　SELECT语句得到的是一个结果集，有时我们需要对结果集中的单条数据进行处理。

　　这时就需要使用游标，游标定义时和一个SELECT语句的结果集关联在一起。

　　游标执行这个结果集，可以在结果集中一行一行的移动，并可以代表执行的单行数据。

　　游标就类似指针，可以指向一行数据，并且在数据集上移动。

　　

　　注：只能在存储过程中才能使用游标。

二、创建游标

　　

　　创建游标格式：

　　DECLARE 游标名 CURSOR FOR  select语句

　　这样就在指定的select语句的结果集上建立了游标。

DELIMITER //
CREATE PROCEDURE testCursor()
    BEGIN
        DECLARE cc CURSOR
        FOR 
        SELECT order_num FROM orders;
    END //
DELIMITER ;

　　游标的生命周期，只限于存储过程中有效。存储过程结束后，游标也就消失了。

三、开启、关闭游标

　　游标定义后还需要打开方可使用，当游标不需要使用时，将游标关闭。

　　

DELIMITER //
CREATE PROCEDURE testCursor()
    BEGIN
        DECLARE cc CURSOR --  创建游标
        FOR 
        SELECT order_num FROM orders;
        OPEN cc;  -- 打开游标
        CLOSE cc; -- 关闭游标
    END //
DELIMITER ;

四、使用游标

　　游标打开后，可以使用FEACH访问每一行，FEACH初始情况下指向第一行。

　　每一次执行FEACH都会后移一行，

　　

DELIMITER //
CREATE PROCEDURE testCursor()
    BEGIN
        DECLARE orderNum INT; -- 存放检索的order_num
        DECLARE cc CURSOR --  创建游标
        FOR 
        SELECT order_num FROM orders;
        OPEN cc;  -- 打开游标
        FETCH cc INTO o;  -- 如果结果集有多列，INTO value1,value2...即可
        CLOSE cc; -- 关闭游标
    END //
DELIMITER ;

将第一行数据放入变量o中，注意变量o的数据类型要和返回的数据类型一致。

如果游标有多列，cursor INTO value1,value2.。value变量的类型要和对应返回值类型一致。

五、使用游标遍历结果集

税率计算存储过程

DELIMITER //
CREATE PROCEDURE ordertotal(
        IN ordernumber DECIMAL(8,2),-- 订单编号
        IN tax BOOLEAN ,            -- 该商品是否计算税金，1为true计算，0位false不计算
        OUT total DECIMAL(8,2)      -- 最后价格
    )
BEGIN
    DECLARE total_tmp DECIMAL(8,2);  -- 含税价格
    DECLARE tax_t INT DEFAULT 6;     -- 定义默认税率，6%
    SELECT SUM(quantity*item_price) 
    FROM orderitems
    WHERE order_num = ordernumber
    INTO total_tmp;                  -- 首先计算商品不含税总价
    IF tax THEN    -- 如果tax为1（true) 计算含税总价
        SELECT total_tmp * (1 + (tax_t / 100)) INTO total_tmp; -- 
    END IF;
    SELECT total_tmp INTO total; -- 将最后价格赋给total
END//
DELIMITER ;
-- 示例
-- CALL ordertotal(20005,1, @total);  -- 计算20005号订单价格，含税， 1为true,0为false
-- SELECT @total; -- 显示计算后总价

游标遍历订单，并调用税率计算存储过程计算总价值　　

DELIMITER //
    CREATE PROCEDURE processorders()
    BEGIN
        DECLARE o INT; -- 
        DECLARE done BOOLEAN DEFAULT 0;
        DECLARE t DECIMAL(8,2);
        -- 创建游标
        DECLARE ordernumbers CURSOR
        FOR
        SELECT order_num FROM orders;
        
        -- 设置结束状态语句，可以看做一个监听器，当出现SQLSTATE为02000时执行SET done=1.
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
        
        -- 创建一张新表，保存计算税率之后的价格
        CREATE TABLE IF NOT EXISTS ordertotals
        (order_num INT, total DECIMAL(8,2));
        -- 打开游标
        OPEN ordernumbers;
        
        -- 循环获取数据
        REPEAT
            -- 使用FETCH访问游标的行，将数据(订单编号)放入变量o
            FETCH ordernumbers INTO o;
            -- 调用税率计算存储过程
            CALL ordertotal(o,1,t);
            INSERT INTO ordertotals(order_num,total)
            VALUES(o,t);
        UNTIL done  END REPEAT;-- done为1时终止循环
        -- 关闭游标
        CLOSE ordernumbers;
    END//
    
 DELIMITER ;
CALL processorders(); -- 调用
SELECT * FROM ordertotals;   -- 新表名

参考资料：

《MySQL必知必会》