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

In this article:

• What is a cursor
• Creating a Cursor
• Using Cursors

Release date: 2018-04-18

What is a cursor:

• If you have looked at mysql functions before, you will find that it is not possible to use statements that return multiple rows of results. But if you really want to use it, you need to use a cursor, which can help you select a result (so that a single result can be returned).
• In addition, using a cursor, you can easily retrieve results that advance or go back one or more rows in the retrieved rows.
• The cursor can iterate over multiple rows of results returned.

Replenish:

• In MySQL, cursors are only applicable to stored procedures and functions.

Create a cursor:

• grammar:
  • 1. Define the cursor: declare cursor name cursor for select statement;
  • 2. Open the cursor: open cursor name;
  • Get results: fetch cursor name into variable name [, variable name];
  • Close cursor: close cursor name;

    create procedure p1()
    begin
      declare id int;
      declare name varchar(15);
      --Declare cursor declare mc cursor for select * from class;
      -- Open the cursor open mc;
      -- Get the result fetch mc into id,name;
      -- Here is to display the results select id,name;
      -- Close the cursor close mc;
      
    end;

  create procedure p2()
  begin
    declare id int;
    declare name varchar(15);
    --Declare cursor declare mc cursor for select * from class;
    -- Open the cursor open mc;
    -- Get result loop -- Loop and transfer the contents of the table to class2 fetch mc into id,name;
    -- Here is to display the result insert into class2 values(id,name);
    -- Close the cursor end loop;
    close mc;
    
  end;

Using cursors:

• Each time the cursor fetches, it gets a row of results. You can use variables to get the value of each column fetched.

create procedure p2()
begin
  declare id int;
  declare name varchar(15);
  --Declare cursor declare mc cursor for select * from class;
  -- Open the cursor open mc;
  -- Get result loop -- Loop and transfer the contents of the table to class2 fetch mc into id,name;
  -- Here is to display the result insert into class2 values(id,name);
  -- Close the cursor end loop;
  close mc;
  
end;

The above code will have an error If you keep looping, you will always reach the end of the table. When you reach the end, you can't continue fetching. Generally speaking, you should avoid errors. Before reaching the end, there will be a mysql-defined

create procedure p3()
begin
  declare id int;
  declare name varchar(15);
  declare flag int default 0;
  --Declare cursor declare mc cursor for select * from class;
  declare continue handler for not found set flag = 1;
  -- Open the cursor open mc;
  -- Get the result l2:loop 
  
  fetch mc into id,name;
  if flag=1 then -- when fetch fails, handler continues
    leave l2;
  end if;
  -- Here is to display the result insert into class2 values(id,name);
  -- Close the cursor end loop;
  close mc;
  
end;

call p3();-- no error select * from class2;

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.