Detailed explanation of MySQL cursor concepts and usage

This article uses examples to explain the concept and usage of MySQL cursors. Share with you for your reference, the details are as follows:

1. The concept of cursor (Cursor)

One SQL statement corresponds to N resources. The interface for retrieving resources is the cursor. Following the cursor, one row can be retrieved at a time. If you have developed Android, you should know that there is an API called Cursor, which is also used to read SQLite database, which is somewhat similar to this one.

2. Steps to use cursor

(1) Statement

Declare using declare

declare cursor name cursor for select_statement

(2) Open the cursor

Use open to open

open cursor name

(3) Get value from cursor

Use fetch to retrieve values

fetch cursor name into var1,var2[,...] -- Assign the fetched row to multiple variables

(4) Close the cursor

Use close to close the cursor

close cursor name

3. Create a simple cursor

Requirement: Read the first row of data from the product table

Goods table data:

Note: I have changed the MySQL end identifier to $ here. If you want to know how to set it to $, please refer to the previous article: MySQL trigger.

definition:

create procedure p12()
begin
  /*Define three variables to store product ID, product name, and product inventory*/
  declare row_gid int; 
  declare row_name varchar(20);
  declare row_num int;
  declare getgoods cursor for select gid,name,num from goods; --Define cursor open getgoods; --Open cursor fetch getgoods into row_gid,row_name,row_num;--Get value from cursor select row_name,row_num; --Display operation close getgoods; --Close cursor end$

Output:

4. Multiple value operations

create procedure p13()
begin
  declare row_gid int;
  declare row_name varchar(20);
  declare row_num int;
  declare getgoods cursor for select gid,name,num from goods;  
  open getgoods;
  fetch getgoods into row_gid,row_name,row_num;
  select row_name,row_num;
  fetch getgoods into row_gid,row_name,row_num;
  select row_name,row_num;
  fetch getgoods into row_gid,row_name,row_num;
  select row_name,row_num;
  fetch getgoods into row_gid,row_name,row_num;
  select row_name,row_num;
  close getgoods;
end$

Output:

Note: When the cursor reaches the end, an error will occur if you continue to retrieve values.

5. Cursor loops through all the data in the table

(1) Using a counter to loop

create procedure p14()
begin 
  declare cnt int default 0;
  declare i int default 0;
  declare row_gid int;
  declare row_name varchar(20);
  declare row_num int;
  declare getgoods cursor for select gid,name,num from goods;
  select count(*) into cnt from goods;
  open getgoods;
  repeat 
    fetch getgoods into row_gid,row_name,row_num;
  select row_name,row_num;
  set i:= i+1;
  until i >= cnt end repeat;
  close getgoods;
end$

Output:

(2) Use the out-of-bounds flag to control the loop

In mysql cursor, you can declare declare continue handler to operate an out-of-bounds flag

grammar:

declare continue handler for NOT FOUND statement;

use:

create procedure p15()
begin
  declare row_gid int;
  declare row_name varchar(20);
  declare row_num int;
  declare have int default 1;
  declare getgoods cursor for select gid,name,num from goods;
  declare continue handler for NOT FOUND set have:= 0;
  open getgoods;
  repeat 
    fetch getgoods into row_gid,row_name,row_num;
  select row_name,row_num;
  until have = 0 end repeat;
  close getgoods;
end$

Output:

Note: An error occurred here. Four rows of data were output, but there were only three rows of data in the table. In addition, a warning was displayed. We will explain how to solve this problem later.

Program execution logic:

Loop cursor -> fetch the third data -> display -> fetch the fourth data -> no data -> set have=0 operation -> execute continue Handler -> program does not exit, execute display operation -> still display the third data

6. The difference between continue and exit

continue: If no data is returned, the program continues and sets the variable IS_FOUND to 0. This happens when select XX into XXX from tablename.
exit: If no data is returned, exit the program and set the variable IS_FOUND to 0. This happens when select XX into XXX from tablename.

Use exit to replace continue:
If you use exit, the above situation will not occur. The program execution logic is:

Loop cursor -> fetch the third data -> display -> fetch the fourth data -> no data -> set have=0 -> program exits directly

Therefore, the fourth data is not displayed.

create procedure p16()
begin
  declare row_gid int;
  declare row_name varchar(20);
  declare row_num int;
  declare have int default 1;
  declare getgoods cursor for select gid,name,num from goods;
  declare exit handler for NOT FOUND set have:= 0;
  open getgoods;
  repeat 
    fetch getgoods into row_gid,row_name,row_num;
  select row_name,row_num;
  until have = 0 end repeat;
  close getgoods;
end$

Output:

7. Correct cursor loop

In some special cases, the data we read may be empty, or there may be errors in the SQL statement. We cannot avoid this situation, so we must use cursor loop operations correctly.

First, you should create a cursor. After opening the cursor, you should manually fetch a row of data. Then, through a loop, process the content first and then fetch it. In this way, if no data is obtained during the manual data acquisition period, have = 0 will be executed. If it is a repeat loop, then the repeat loop will be entered, null data will be output first, and then it will be acquired again. In this way, the loop will be exited when it runs to until; if it is a while loop, it will not enter the while loop at all, and there will be no output of any 1 line.

(1) Repeat loop:

create procedure p17()
begin
  declare row_gid int;
  declare row_name varchar(20);
  declare row_num int;
  declare have int default 1;
  declare getgoods cursor for select gid,name,num from goods where 0;
  declare continue handler for NOT FOUND set have:= 0;
  open getgoods;
  fetch getgoods into row_gid,row_name,row_num;
  repeat 
  select row_name,row_num;
    fetch getgoods into row_gid,row_name,row_num;
  until have = 0 end repeat;
  close getgoods;
end$

Output:

(2) While loop:

create procedure p18()
begin
  declare row_gid int;
  declare row_name varchar(20);
  declare row_num int;
  declare have int default 1;
  declare getgoods cursor for select gid,name,num from goods where 0;
  declare continue handler for NOT FOUND set have:= 0;
  open getgoods;
  fetch getgoods into row_gid,row_name,row_num;
  while have = 1 do 
  select row_name,row_num;
    fetch getgoods into row_gid,row_name,row_num;
  end while;
  close getgoods;
end$

Output:

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.