1, Introduction to cursors

1. Introduction to cursors

Cursor is a database query stored on MySQL server. It is not a select statement, but a result set retrieved by the statement. With the cursor, the result set can be processed row by row conveniently. The design of cursors is the idea of data buffer, which is used to store the results of SQL statement execution. A cursor is a mechanism that can extract one record at a time from a result set containing multiple data records.
Although the cursor can traverse all the rows in the result, it only points to one row at a time.
The function of the cursor is to traverse the records returned from the query database for corresponding operations.

2. Properties of cursors

A cursor has three properties:
A. Insensitive: database can choose not to copy result set
B. Read only
C. Non scrollable: a cursor can only move in one direction and cannot skip any row of data.

3. Advantages of cursors

Cursors are for row operations. It is a separation idea that each row in the result set obtained from the SELECT query in the database can be operated separately and independently. Cursors are a bridge between set oriented and row oriented design ideas.

4. Disadvantages of cursors

The main disadvantage of cursors is poor performance.
The cost of cursors is related to the operations in cursors. If complex operations are performed in cursors, the cost will be very high. If the collection oriented SQL statement is used, the scanning cost is O(N); but if the collection oriented SQL statement is used, the scanning cost is O(N*N), the use of cursors may lead to performance improvement.
The disadvantage of cursors is that they can only operate one line at a time. In the case of large amount of data, the speed is too slow. Most of the databases are set oriented, and the business will be more complex, while the use of cursors will have deadlock, affecting other business operations, which is not desirable. When the amount of data is large, the use of cursors will cause insufficient memory.
Its function is to traverse the records returned from the query database for corresponding operations.

5. Applicable scenarios of cursors

In MySQL database, you can use cursors in stored procedures, functions, triggers, and events.

2, Cursor operation

1. Definition of cursor

DECLARE cursor_name CURSOR FOR select_statement

2. Open cursor

OPEN cursor_name;

3. Fetch data in cursor

FETCH cursor_name INTO var_name [, var_name]...

4. Close cursor

CLOSE cursor_name;

5. Release cursor

DEALLOCATE cursor_name;

3, Cursor instance

1. Create a test table for the cursor

CREATE TABLE cursor_table
(
id INT ,
name VARCHAR(10),
age INT
)ENGINE=innoDB DEFAULT CHARSET=utf8;
insert into cursor_table values(1, 'Sun WuKong', 500);
insert into cursor_table values(2, 'Zhu Bajie', 200);
insert into cursor_table values(3, 'Sha Wujing', 100);
insert into cursor_table values(4, 'Tang Monk', 20);

There are three ways to use cursors to create a stored procedure to count the number of records older than 30.

2. Loop loop

CREATE  PROCEDURE getTotal()
BEGIN  
    DECLARE total INT; 
    ##Create variables to receive cursor data  
    DECLARE sid INT;  
    DECLARE sname VARCHAR(10);  
    #Create total variable  
    DECLARE sage INT;  
    #Create end flag variable  
    DECLARE done INT DEFAULT false;  
    #Create cursor  
    DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;  
    #Specifies the return value at the end of the cursor loop  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;  
    #Set initial value  
    SET sage = 0;  
    SET total=0;
    #Open cursor  
    OPEN cur;  
    #Start looping data in cursor  
    read_loop:loop  
    #According to a piece of data that the cursor currently points to  
    FETCH cur INTO sid,sname,sage;  
    #Determine whether the cursor's loop ends  
    IF done THEN  
        LEAVE read_loop;    #Jump out of cursor loop  
    END IF;  
    #When obtaining a piece of data, add the count value. Here you can do whatever you want,  
    SET total = total + 1;  
    #End cursor loop  
    END LOOP;  
    #Close cursor  
    CLOSE cur;  

    #Output results  
    SELECT total;  
END

#Call stored procedure  
call getTotal();  

3. While loop

CREATE  PROCEDURE getTotal()
BEGIN  
    DECLARE total INT; 
    ##Create variables to receive cursor data  
    DECLARE sid INT;  
    DECLARE sname VARCHAR(10);  
    #Create total variable  
    DECLARE sage INT;  
    #Create end flag variable  
    DECLARE done INT DEFAULT false;  
    #Create cursor  
    DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;  
    #Specifies the return value at the end of the cursor loop  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;   
    SET total = 0;  
    OPEN cur;  
    FETCH cur INTO sid, sname, sage;  
    WHILE(NOT done) 
    DO  
        SET total = total + 1;  
        FETCH cur INTO sid, sname, sage;  
    END WHILE;  

    CLOSE cur;  
    SELECT total;  
END

4. Repeat loop

CREATE getTotal()
BEGIN  
    DECLARE total INT; 
    ##Create variables to receive cursor data  
    DECLARE sid INT;  
    DECLARE sname VARCHAR(10);  
    #Create total variable  
    DECLARE sage INT;  
    #Create end flag variable  
    DECLARE done INT DEFAULT false;  
    #Create cursor  
    DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age > 30;  
    #Specifies the return value at the end of the cursor loop  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;   
    SET total = 0;  
    OPEN cur;  
    REPEAT  
    FETCH cur INTO sid, sname, sage;   
    IF NOT done THEN  
        SET total = total + 1;  
    END IF;  
    UNTIL done END REPEAT;  
    CLOSE cur;  
    SELECT total;  
END

5. Using cursor data

The first example retrieves the first row from the cursor:

create procedure processorders()
begin
	declare o int;
	
	declare ordernumbers cursor
	for
	select order_num from orders;
	
	open ordernumbers;
	-- utilize fetch Retrieve the order_num Stored in a file named o In the local variable of.
	fetch ordernumbers into o;
	close ordernumbers;
end;

The second example retrieves all the rows in the cursor, from the first row to the last row:

create procedure processorders()
begin
	declare done boolean default 0;
	declare o int;
	
	declare ordernumbers cursor
	for
	select order_num from orders;
	
	/*
		This statement defines a continue handler, which is the code executed when the condition occurs. Here, it points out that when
		sqlstate '02000'When it appears, set done = 1. sqlstate '02000' is a condition not found when
		repeate This condition occurs when there are no more rows to loop and cannot continue.
	*/
	declare continue handler for sqlstate '02000' set done = 1;
	
	open ordernumbers;
	-- When done Ends the loop when true (non-zero).
	repeat
		fetch ordernumbers into o;
	until done end repeat;
	close ordernumbers;
end;

The third example does some practical processing on the data taken from the cursor

create procedure processorders()
begin
	declare done boolean default 0;
	declare o int;
	declare t decimal(8,2);
	
	declare ordernumbers cursor
	for
	select order_num from orders;
	
	declare continue handler for sqlstate '02000' set done = 1;
	
	-- Create a table to hold the results
	create table if not exists ordertotals
		(order_num int, total decimal(8,2));
		
	open ordernumbers;
	repeat
		fetch ordernumbers into o;
		-- ordertotal For a stored procedure created in the previous chapter to calculate the total with tax
		call ordertotal(o, 1, t);
		insert into ordertotals(order_num, total)
		values(o, t);
	until done end repeat;
	close ordernumbers;
end;
-- This stored procedure does not return data, but it can create and populate another table.