Definition and usage of MySQL cursor

Creating a Cursor

First, create a data table in MySql:

CREATE TABLE IF NOT EXISTS `store` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL,
 `count` int(11) NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7;
 
INSERT INTO `store` (`id`, `name`, `count`) VALUES
(1, 'android', 15),
(2, 'iphone', 14),
(3, 'iphone', 20),
(4, 'android', 5),
(5, 'android', 13),
(6, 'iphone', 13);

We are now going to use a stored procedure to create a function that counts the total inventory of iPhones and outputs the total to the console.

--When writing a stored procedure in a Windows system, if you need to use declare to declare a variable, you need to add this keyword, otherwise an error will be reported.
delimiter //
drop procedure if exists StatisticStore;
CREATE PROCEDURE StatisticStore()
BEGIN
  --Create a variable to receive cursor data declare c int;
  declare n varchar(20);
  --Create total variable declare total int default 0;
  --Create end flag variable declare done int default false;
  --Create a cursor declare cur cursor for select name,count from store where name = 'iphone';
  --Specify the return value at the end of the cursor loop declare continue HANDLER for not found set done = true;
  --Set the initial value set total = 0;
  --Open the cursor open cur;
  --Start looping over the data in the cursor read_loop:loop
  --fetch cur into n,c according to the data currently pointed to by the cursor;
  --Judge whether the cursor loop is finished if done then
    leave read_loop; --Jump out of cursor loop end if;
  --When getting a piece of data, add the count value. You can do whatever you want here.
  set total = total + c;
  --End the cursor loop end loop;
  --Close the cursor close cur;
 
  --Output results select total;
END;
--Call the stored procedure call StatisticStore();

Fetch is to obtain the data row currently pointed to by the cursor and point the pointer to the next row. Continuing execution when the cursor has already pointed to the last row will cause the cursor to overflow.
When using a loop cursor, it will not monitor whether it has reached the last piece of data. The following code will cause an infinite loop.

read_loop:loop
fetch cur into n,c;
set total = total+c;
end loop;

In MySql, when a cursor overflow occurs, the MySQL predefined NOT FOUND error will be triggered. Therefore, the following code is used above to define a continue event when a not found error is triggered, and to specify that the value of the done variable be modified when this event occurs.

declare continue HANDLER for not found set done = true;

So I added the following code to the loop:

--Judge whether the cursor loop is finished if done then
  leave read_loop; --Jump out of cursor loop end if;

If the value of done is true, the loop ends. Continue executing the code below.

How to use

There are three ways to use cursors:
The first one is the above implementation, using loop;
The second way is as follows, using a while loop:

drop procedure if exists StatisticStore1;
CREATE PROCEDURE StatisticStore1()
BEGIN
  declare c int;
  declare n varchar(20);
  declare total int default 0;
  declare done int default false;
  declare cur cursor for select name,count from store where name = 'iphone';
  declare continue HANDLER for not found set done = true;
  set total = 0;
  open cur;
  fetch cur into n,c;
  while(not done) do
    set total = total + c;
    fetch cur into n,c;
  end while;
  
  close cur;
  select total;
END;
 
call StatisticStore1();

The third way is to use repeat execution:

drop procedure if exists StatisticStore2;
CREATE PROCEDURE StatisticStore2()
BEGIN
  declare c int;
  declare n varchar(20);
  declare total int default 0;
  declare done int default false;
  declare cur cursor for select name,count from store where name = 'iphone';
  declare continue HANDLER for not found set done = true;
  set total = 0;
  open cur;
  repeat
  fetch cur into n,c;
  if not done then
    set total = total + c;
  end if;
  until done end repeat;
  close cur;
  select total;
END;
 
call StatisticStore2();

Nested cursors

In MySQL, each begin end block is an independent scope area. Since the same error event can only be defined once in MySQL, if it is defined multiple times, it will prompt Duplicate handler declared in the same block during compilation.

drop procedure if exists StatisticStore3;
CREATE PROCEDURE StatisticStore3()
BEGIN
  declare _n varchar(20);
  declare done int default false;
  declare cur cursor for select name from store group by name;
  declare continue HANDLER for not found set done = true;
  open cur;
  read_loop:loop
  fetch cur into _n;
  if done then
    leave read_loop;
  end if;
  begin
    declare c int;
    declare n varchar(20);
    declare total int default 0;
    declare done int default false;
    declare cur cursor for select name,count from store where name = 'iphone';
    declare continue HANDLER for not found set done = true;
    set total = 0;
    open cur;
    iphone_loop:loop
    fetch cur into n,c;
    if done then
      leave iphone_loop;
    end if;
    set total = total + c;
    end loop;
    close cur;
    select _n,n,total;
  end;
  begin
      declare c int;
      declare n varchar(20);
      declare total int default 0;
      declare done int default false;
      declare cur cursor for select name,count from store where name = 'android';
      declare continue HANDLER for not found set done = true;
      set total = 0;
      open cur;
      android_loop:loop
      fetch cur into n,c;
      if done then
        leave android_loop;
      end if;
      set total = total + c;
      end loop;
      close cur;
    select _n,n,total;
  end;
  begin
  
  end;
  end loop;
  close cur;
END;
 
call StatisticStore3();

The above is to implement a nested loop, of course this example is far-fetched. Just take a look at it for now. .

Dynamic SQL

Mysql supports dynamic SQL functions.

set @sqlStr='select * from table where condition1 = ?';
prepare s1 for @sqlStr;
--If there are multiple parameters, separate them with commas execute s1 using @condition1;
--Manual release, or when the connection is closed, the server automatically recycles deallocate prepare s1;

The above is the detailed content of the definition and usage of MySQL cursor. For more information about MySQL cursor, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in: