《数据库系统实训》实验报告——游标

本是古典何须时尚 2023-01-15 06:59 238阅读 0赞

# 游标 #

## 第一部分：样例库的应用 ##

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;

查询结果：  
![在这里插入图片描述][20210422113129921.png]

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;

处理结果：  
![在这里插入图片描述][20210422113643608.png]

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

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

查询结果：  
![在这里插入图片描述][2021042211375551.png]

## 参考文章 ##

[20210422113129921.png]: /images/20221022/07f6a8f1b1ed47a0812d39a4630091b1.png
[20210422113643608.png]: /images/20221022/10498ba478c346d28faaec49cb8f5f8a.png
[2021042211375551.png]: /images/20221022/acf08fcd17994a6c8c46cb18eb83d396.png