MariaDB CURSOR

MariaDB CURSOR

示例一：

简单创建CURSO并打开，关闭操作

DELIMITER //

CREATE PROCEDURE processorders() 

BEGIN

    DECLARE ordernumbers CURSOR FOR

    SELECT order_num FROM orders;

END;//

DELIMITER ;

DELIMITER //

CREATE OR REPLACE PROCEDURE processorders() 

BEGIN

    -- Declare the cursor 

    DECLARE ordernumbers CURSOR FOR

    SELECT order_num FROM orders;

    -- Open the cursor 

    OPEN ordernumbers;

    -- Close the cursor 

    CLOSE ordernumbers;

END;//

DELIMITER ;

示例二：

打开CURSOR对象并将值FETCH到指定变量中

DELIMITER //

CREATE OR REPLACE PROCEDURE processorders() 

BEGIN

    -- Declare local variables 

    DECLARE o INT;

    -- Declare the cursor 

    DECLARE ordernumbers CURSOR FOR

    SELECT order_num FROM orders;

    -- Open the cursor 

    OPEN ordernumbers;

    -- Get order number

    FETCH ordernumbers INTO o;

    -- Close the cursor 

    CLOSE ordernumbers;

END;//

DELIMITER ;

示例三：

定义CONTINUE HANDLER并重复FETCH，直到CURSOR结束

DELIMITER //

CREATE OR REPLACE PROCEDURE processorders() 

BEGIN

    -- Declare local variables 

    DECLARE done BOOLEAN DEFAULT 0; 

    DECLARE o INT;

    -- Declare the cursor 

    DECLARE ordernumbers CURSOR FOR

    SELECT order_num FROM orders;

    -- Declare continue handler

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

    -- Open the cursor 

    OPEN ordernumbers;

    -- Loop through all rows 

    REPEAT

        -- Get order number

        FETCH ordernumbers INTO o;

    -- End of loop

    UNTIL done END REPEAT;

    -- Close the cursor 

    CLOSE ordernumbers;

END;//

DELIMITER ;

示例四：

综合

DELIMITER //

CREATE OR REPLACE PROCEDURE processorders() 

BEGIN

    -- Declare local variables 

    DECLARE done BOOLEAN DEFAULT 0; 

    DECLARE o INT;

    DECLARE t DECIMAL(8,2);

    -- Declare the cursor 

    DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;

    -- Declare continue handler

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

    -- Create a table to store the results 

    CREATE TABLE IF NOT EXISTS ordertotals(order_num INT, total DECIMAL(8,2));

    -- Open the cursor 

    OPEN ordernumbers;

    -- Loop through all rows 

    REPEAT

        -- Get order number

        FETCH ordernumbers INTO o;

        -- Get the total for this order 

        CALL ordertotal(o, 1, t);

        -- Insert order and total into ordertotals 

        INSERT INTO ordertotals(order_num, total) VALUES(o, t);

    -- End of loop

    UNTIL done END REPEAT;

    -- Close the cursor 

    CLOSE ordernumbers;

END;//

DELIMITER ;

(jlive)[crashcourse]>CALL processorders();

Query OK, 1 row affected (0.01 sec)

(jlive)[crashcourse]>SELECT * FROM ordertotals;

+-----------+---------+

| order_num | total   |

+-----------+---------+

|     20005 |  158.86 |

|     20006 |   58.30 |

|     20007 | 1060.00 |

|     20008 |  132.50 |

|     20009 |   40.78 |

|     20009 |   40.78 |

+-----------+---------+

6 rows in set (0.00 sec)

去CALL的时候不会显示任何数据，而是把通过CURSOR FETCH到的值传入PROCEDURE ordertotal计算出total值，最后将order_num,total插入新表ordertotals中