Definition and usage of cursors in MySql

Create a Cursor

First create a data table in MySql:

  2.   `id` int(11) NOT NULL AUTO_INCREMENT,  
  3.   `namevarchar(20) NOT NULL,  
  4.   `countint(11) NOT NULL DEFAULT '1',  
  5.   PRIMARY KEY (`id`)  
  8. INSERT INTO `store` (`id`, `name`, `count`) VALUES  
  9. (1, 'android', 15),  
  10. (2, 'iphone', 14),  
  11. (3, 'iphone', 20),  
  12. (4, 'android', 5),  
  13. (5, 'android', 13),  
  14. (6, 'iphone', 13);  

Now we're going to do a function with stored procedures, count the total stock of the iphone, and export the total to the console.

  1. --stay windows When writing stored procedures in a system, use declare Declare variables, you need to add this keyword, otherwise you will report an error.  
  2. delimiter //  
  3. drop procedure if exists StatisticStore;  
  4. CREATE PROCEDURE StatisticStore()  
  5. BEGIN  
  6.     --Create variables that receive cursor data  
  7.     declare c int;  
  8.     declare n varchar(20);  
  9.     --Create total variables  
  10.     declare total int default 0;  
  11.     --Create End Marker Variables  
  12.     declare done int default false;  
  13.     --Create a Cursor  
  14.     declare cur cursor for select name,count from store where name = 'iphone';  
  15.     --Specifies the return value at the end of the cursor loop  
  16.     declare continue HANDLER for not found set done = true;  
  17.     --Setting initial values  
  18.     set total = 0;  
  19.     --open  
  20.     open cur;  
  21.     --Start looping the data in the cursor  
  22.     read_loop:loop  
  23.     --According to a data that the cursor is currently pointing to  
  24.     fetch cur into n,c;  
  25.     --Determine whether the cursor loop ends  
  26.     if done then  
  27.         leave read_loop;    --Jump out of the cursor loop  
  28.     end if;  
  29.     --When you get a piece of data, you will count Values are accumulated, and you can do whatever you want.  
  30.     set total = total + c;  
  31.     --End the cursor loop  
  32.     end loop;  
  33.     --Close cursor  
  34.     close cur;  
  36.     --Output result  
  37.     select total;  
  38. END;  
  39. --Calling stored procedures  
  40. call StatisticStore();  

fetch is to get the data row currently pointed to by the cursor and point the pointer to the next row. Continuing to execute when the cursor has pointed to the last row will cause a cursor overflow.
When using the loop loop cursor, he will not monitor whether to the last data, such as the following code, which will cause a dead loop;

  1. read_loop:loop  
  2. fetch cur into n,c;  
  3. set total = total+c;  
  4. end loop;  

In MySql, a predefined NOT FOUND error in MySQL is triggered when a cursor overflow occurs, so the following code is used above to specify an event that defines a continue when a non-foundation error is raised, and to specify that the value of the done variable is modified when the event occurs.
  1. declare continue HANDLER for not found set done = true;  
So the following code is added to the loop:

  1. --Determine whether the cursor loop ends  
  2. if done then  
  3.     leave read_loop;    --Jump out of the cursor loop  
  4. end if;  

If the value of done is true, the loop ends. Continue to execute the following code.

Usage mode

There are three ways to use cursors:
The first is the implementation of the above, using loop loops.
The second way is to use the while loop as follows:

  1. drop procedure if exists StatisticStore1;  
  2. CREATE PROCEDURE StatisticStore1()  
  3. BEGIN  
  4.     declare c int;  
  5.     declare n varchar(20);  
  6.     declare total int default 0;  
  7.     declare done int default false;  
  8.     declare cur cursor for select name,count from store where name = 'iphone';  
  9.     declare continue HANDLER for not found set done = true;  
  10.     set total = 0;  
  11.     open cur;  
  12.     fetch cur into n,c;  
  13.     while(not done) do  
  14.         set total = total + c;  
  15.         fetch cur into n,c;  
  16.     end while;  
  18.     close cur;  
  19.     select total;  
  20. END;  
  22. call StatisticStore1();  

The third way is to use repeat to execute:

  1. drop procedure if exists StatisticStore2;  
  2. CREATE PROCEDURE StatisticStore2()  
  3. BEGIN  
  4.     declare c int;  
  5.     declare n varchar(20);  
  6.     declare total int default 0;  
  7.     declare done int default false;  
  8.     declare cur cursor for select name,count from store where name = 'iphone';  
  9.     declare continue HANDLER for not found set done = true;  
  10.     set total = 0;  
  11.     open cur;  
  12.     repeat  
  13.     fetch cur into n,c;  
  14.     if not done then  
  15.         set total = total + c;  
  16.     end if;  
  17.     until done end repeat;  
  18.     close cur;  
  19.     select total;  
  20. END;  
  22. call StatisticStore2();  

Vernier nesting

In mysql, each start end block is a separate scope area. Since events with the same error in MySql can only be defined once, Duplicate handler declared in the same block will be prompted at compile time if multiple definitions are used.

  1. drop procedure if exists StatisticStore3;  
  2. CREATE PROCEDURE StatisticStore3()  
  3. BEGIN  
  4.     declare _n varchar(20);  
  5.     declare done int default false;  
  6.     declare cur cursor for select name from store group by name;  
  7.     declare continue HANDLER for not found set done = true;  
  8.     open cur;  
  9.     read_loop:loop  
  10.     fetch cur into _n;  
  11.     if done then  
  12.         leave read_loop;  
  13.     end if;  
  14.     begin  
  15.         declare c int;  
  16.         declare n varchar(20);  
  17.         declare total int default 0;  
  18.         declare done int default false;  
  19.         declare cur cursor for select name,count from store where name = 'iphone';  
  20.         declare continue HANDLER for not found set done = true;  
  21.         set total = 0;  
  22.         open cur;  
  23.         iphone_loop:loop  
  24.         fetch cur into n,c;  
  25.         if done then  
  26.             leave iphone_loop;  
  27.         end if;  
  28.         set total = total + c;  
  29.         end loop;  
  30.         close cur;  
  31.         select _n,n,total;  
  32.     end;  
  33.     begin  
  34.             declare c int;  
  35.             declare n varchar(20);  
  36.             declare total int default 0;  
  37.             declare done int default false;  
  38.             declare cur cursor for select name,count from store where name = 'android';  
  39.             declare continue HANDLER for not found set done = true;  
  40.             set total = 0;  
  41.             open cur;  
  42.             android_loop:loop  
  43.             fetch cur into n,c;  
  44.             if done then  
  45.                 leave android_loop;  
  46.             end if;  
  47.             set total = total + c;  
  48.             end loop;  
  49.             close cur;  
  50.         select _n,n,total;  
  51.     end;  
  52.     begin  
  54.     end;  
  55.     end loop;  
  56.     close cur;  
  57. END;  
  59. call StatisticStore3();  

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

dynamic SQL

Mysql supports dynamic SQL functions.

  1. set @sqlStr='select * from table where condition1 = ?';  
  2. prepare s1 for @sqlStr;  
  3. --If there are multiple parameters separated by commas  
  4. execute s1 using @condition1;  
  5. --Manual release, or connection When closed, server Automatic recovery  
  6. deallocate prepare s1;  

Keywords: MySQL Android SQL Windows

Added by powergen on Tue, 21 May 2019 22:49:25 +0300