游标

第一部分：样例库的应用

1）创建游标

CREATE PROCEDURE processorders()
BEGIN
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;
END

由于没有打开游标，此过程不会把数据检索出来。
2）使用游标数据

CREATE 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;    
	SELECT O;    
	-- Close the cursor    
	CLOSE ordernumbers; 
END; 

查询结果：

3）循环检索数据

CREATE 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;       
		 SELECT o;    
	 -- End of loop    
	 UNTIL done END REPEAT;    
	 -- Close the cursor    
	 CLOSE ordernumbers; 
END;
```查询结果：
 ![在这里插入图片描述](https://img-blog.csdnimg.cn/2021042211350375.png)

4）循环处理数据 

```sql
CREATE 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,SQLSTATE '02000' 是一个“未找到”条件    
	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; 

处理结果：

第二部分：所选课题数据库的应用

1）查询语句：

CREATE PROCEDURE `calculateprice`()
BEGIN
   DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
   DECLARE t INT;
   DECLARE remainss CURSOR
   FOR
   SELECT goods_id FROM order_detail;
   CREATE TABLE IF NOT EXISTS pricetotals
      (id INT,total INT);
   OPEN remainss;
   REPEAT
      FETCH remainss INTO o;
			CALL pricetotal(o,t);
      INSERT INTO pricetotals(id,total)
      VALUES(o,t);
   UNTIL done END REPEAT;
   CLOSE remainss;
END

以下为创建用来计算price*remains的函数：

CREATE PROCEDURE `pricetotal`(IN `inumber` int,OUT `ptotal` decimal)
BEGIN
	#Routine body goes here...
	SELECT Sum(total_price)
   FROM order_detail
   WHERE goods_id = inumber
   INTO ptotal;
END

查询结果：

参考文章