MySQL 自学第24章 使用游标

游标

使用游标

创建游标

打开和关闭游标

OPEN 游标名;

CLOSE 游标名;

使用游标数据

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 

	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//