Definition

We often come across a situation like this , We need to traverse the results of our query , And to traverse to each data processing , In this case, the cursor will be used .
therefore : The cursor (Cursor) It's a way to process data stored in MySQL Database query method on the server , In order to view or process the data in the result set , Provides the ability to traverse data one line at a time in the result set .
Cursors are mainly used for loop processing 、 stored procedure 、 function 、 trigger in .

The function of cursors

Like the one above us students Student , Each user needs to be traversed , Then add or subtract points based on their other evaluations . At this time, we need to query all the students' information ( Include grades ).
1 select studentid,studentname,score from students; 
The student data set returned after execution , If we need to traverse the student data one by one , Then add points according to the specific situation , Then you need to use a cursor .
A cursor is equivalent to a pointer , This pointer points to select First row of data for , You can traverse the following data by moving the pointer . 

The use of cursors

declare cursor : Create a cursor , And specify what the cursor needs to traverse select Inquire about , When you declare a cursor, you don't execute this sql.
Open cursor : When opening the cursor , Will execute the cursor corresponding to select sentence .
Traversal data : Use the cursor loop to traverse select Each row of data in the result , And then deal with it .
Business operations : The process of operating on each row of data traversed , You can place any executed statement that needs to be executed ( Additions and deletions ): It depends on the circumstances
Close cursor : The cursor must be released after use .
notes : The temporary fields used need to be declared before the cursor is defined .

declare cursor

1 DECLARE cursor_name CURSOR FOR select_statement; 
Declare a cursor . You can also define multiple cursors in a subroutine , But every cursor in a block must have a unique name . After the cursor is declared, it is also a single operation , however SELECT Statement cannot have INTO Clause .
One begin end Only one cursor can be declared in .

Open cursor

1 OPEN cursor_name; 
Open the previously declared cursor .

Traversing cursor data

1 FETCH cursor_name INTO var_list;
This statement reads the next line with the specified open cursor ( If there's a next line ), And forward cursor pointer . Take the result of the current line , Put the result in the corresponding variable , And the cursor pointer to the next row of data .
When calling fetch When , Will get the data of the current row , If the current row has no data , May trigger mysql Inside NOT FOUND error .

Close cursor

1 CLOSE cursor_name; 
Remember to close the cursor after use .

Cursor example

Write a function , It contains right students Calculation of student user scores and additional points
Data base
 1 mysql> select * from students;
2 +-----------+-------------+-------+---------+
3 | studentid | studentname | score | classid |
4 +-----------+-------------+-------+---------+
5 | 1 | brand | 97.5 | 1 |
6 | 2 | helen | 96.5 | 1 |
7 | 3 | lyn | 96 | 1 |
8 | 4 | sol | 97 | 1 |
9 | 5 | b1 | 81 | 2 |
10 | 6 | b2 | 82 | 2 |
11 | 7 | c1 | 71 | 3 |
12 | 8 | c2 | 72.5 | 3 |
13 | 9 | lala | 73 | 0 |
14 | 10 | A | 99 | 3 |
15 | 16 | test1 | 100 | 0 |
16 | 17 | trigger2 | 107 | 0 |
17 | 22 | trigger1 | 100 | 0 |
18 +-----------+-------------+-------+---------+
19 13 rows in set 
Write functions that contain cursors

The notes here are very clear , Key knowledge points have been marked in red

 1 mysql>
2 /* If the judgment function exists, it will be deleted */
3 DROP FUNCTION IF EXISTS fun_test;
4 /* The declaration terminator is $*/
5 DELIMITER $
6 /* Create a function , Add points to each student who meets the requirements , The added score cannot exceed the given value max_score*/
7 CREATE FUNCTION fun_test(max_score decimal(10,2))
8 RETURNS int
9 BEGIN
10 /* Define real time StudentId The variable of */
11 DECLARE var_studentId int DEFAULT 0;
12 /* Define the variables of the calculated fraction */
13 DECLARE var_score decimal(10,2) DEFAULT 0;
14 /* Define the cursor end flag variable */
15 DECLARE var_done int DEFAULT FALSE;
16 /* Create cursors */
17 DECLARE cur_test CURSOR FOR SELECT studentid,score from students where classid<>0;
18 /* At the end of the cursor var_done by true, Can be used later var_done To determine whether the cursor ends */
19 DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done=TRUE;
20 /* Open cursor */
21 OPEN cur_test;
22 /* Use Loop Loop through cursor */
23 select_loop:LOOP
24 /* First get the data of the current row , Then put the data of the current row into var_studentId,var_score in , If there is no data line ,var_done Will be set to true*/
25 FETCH cur_test INTO var_studentId,var_score;
26 /* adopt var_done To determine if the cursor is over , Exit loop */
27 IF var_done THEN
28 LEAVE select_loop;
29 END IF;
30 /* Yes var_score Values add random values , No more than the given score */
31 set var_score = var_score + LEAST(ROUND(rand()*10,0),max_score);
32 update students set score = var_score where studentId= var_studentId;
33 END LOOP;
34 /* Close cursor */
35 CLOSE cur_test;
36 /* Return results : You can return the required content according to the actual situation */
37 RETURN 1;
38 END $
39 /* The ending character is set to ;*/
40 DELIMITER ;
41 Query OK, 0 rows affected
Call function
1 mysql>
2 /* Parameter is 8, The upper limit of bonus is 8 */
3 select fun_test(8);
4 +-------------+
5 | fun_test(8) |
6 +-------------+
7 | 1 |
8 +-------------+
9 1 row in set
View results

Compared with the original score value , I found that the score added a random value , But not more than a given score 8

 1 mysql> select * from students;
2 +-----------+-------------+-------+---------+
3 | studentid | studentname | score | classid |
4 +-----------+-------------+-------+---------+
5 | 1 | brand | 105.5 | 1 |
6 | 2 | helen | 98.5 | 1 |
7 | 3 | lyn | 97 | 1 |
8 | 4 | sol | 97 | 1 |
9 | 5 | b1 | 89 | 2 |
10 | 6 | b2 | 90 | 2 |
11 | 7 | c1 | 76 | 3 |
12 | 8 | c2 | 73.5 | 3 |
13 | 9 | lala | 73 | 0 |
14 | 10 | A | 100 | 3 |
15 | 16 | test1 | 100 | 0 |
16 | 17 | trigger2 | 107 | 0 |
17 | 22 | trigger1 | 100 | 0 |
18 +-----------+-------------+-------+---------+
19 13 rows in set
Check the trigger log

Those who meet the conditions to be modified are 9 Data , It has been recorded in the log by trigger

 1 mysql>
2 /* The last one wrote triggers , When modifying students The log record is triggered when the table is opened */
3 select * from triggerlog;
4 +----+--------------+---------------+-----------------------------------------+
5 | id | trigger_time | trigger_event | memo |
6 +----+--------------+---------------+-----------------------------------------+
7 | 1 | after | insert | new student info,id:21 |
8 | 2 | after | update | update student info,id:21 |
9 | 3 | after | update | delete student info,id:21 |
10 | 4 | after | update | from:test2,101.00 to:trigger2,106.00 |
11 | 5 | after | update | from:trigger2,106.00 to:trigger2,107.00 |
12 | 6 | after | update | delete student info,id:11 |
13 | 7 | after | update | from:brand,97.50 to:brand,105.50 |
14 | 8 | after | update | from:helen,96.50 to:helen,98.50 |
15 | 9 | after | update | from:lyn,96.00 to:lyn,97.00 |
16 | 10 | after | update | from:sol,97.00 to:sol,97.00 |
17 | 11 | after | update | from:b1,81.00 to:b1,89.00 |
18 | 12 | after | update | from:b2,82.00 to:b2,90.00 |
19 | 13 | after | update | from:c1,71.00 to:c1,76.00 |
20 | 14 | after | update | from:c2,72.50 to:c2,73.50 |
21 | 15 | after | update | from:A,99.00 to:A,100.00 |
22 +----+--------------+---------------+-----------------------------------------+
23 15 rows in set  
Cursor execution
Follow the example above , Analyze the execution process of this cursor .
1、 We created a cursor , The data source is from student Student list .
2、 There's a pointer in the cursor , When opening the cursor , Will execute the cursor corresponding to select sentence , This pointer will point to select The first line in the result records .
3、 When calling fetch Cursor name , Will get the data of the current row , If the current row has no data , Will trigger NOT FOUND abnormal .
When triggered NOT FOUND When out of the ordinary , We can use a variable to mark , As above :DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done=TRUE;
Put the variable var_done The value of is set to TURE, You can go through... In a cycle var_done The value of controls the exit of the loop :LEAVE select_loop;.
If the current row has data , Then save the current row data to the corresponding variable , And point the cursor pointer to the next row of data , Following statements :FETCH cur_test INTO var_studentId,var_score;

summary

1、 Cursors are used to traverse query results .
2、 How to use the cursor : declare cursor 、 Open cursor 、 Traversal cursor 、 Close cursor .
3、 Cursors are mainly used for loop processing 、 stored procedure 、 Use in a function , Used to query the result set .
4、 The disadvantage of cursors is that they can only operate line by line , In case of large amount of data , Not applicable , Too slow . Most databases are set oriented , The business will be more complicated , There will be deadlock when using cursor , Affect other business operations , Not an option . When the amount of data is large , Using cursors can cause out of memory .

MySQL Break down altogether 19: More articles about cursors

  1. build MySQL 5.7.19 Master slave copy , And copy implementation details analysis

    Master slave replication can make MySQL Database master server's master database , Copy to one or more MySQL From server to database , By default , Replication asynchrony ; According to the configuration , You can copy all the databases in the database , Selected databases or even selected tables . Mysql ...

  2. MySQL Middle function 、 The cursor 、 event 、 View

    MySQL Middle function . The cursor . event . View basic application example ( Code ) MySQL in function User defined functions c,fun,fun It's a process oriented implementation. You can only pass in parameters , Or do not pass in parameters , Cannot transfer out parameters , There must be a return value in the function ...

  3. Learn together ASP.NET Core 2.0 Learning notes ( Two ): ef core2.0 And mysql provider 、Fluent API Related configuration and migration

    It has to be said that Microsoft's technology iteration is still very fast , You have to follow her when you get on the boat of Microsoft , Let's learn together ASP.NET Core 2.0 Learning notes ( One ): CentOS Next .net core2 sdk nginx.superviso ...

  4. WordPress 4.8 Installation and configuration tutorial ( be based on centos 7.3, php 7.0, mysql 5.7.19, nginx 1.12.1)

    Recently, I want the whole blog, Record your work . A little bit of learning .Wordpress Nature is the first choice , Because content is the key , So I'm not afraid of being said too much low. Most of the Internet is about wordpress coordination apache Installation tutorial for ...

  5. mysql_linux(centos7 mysql 5.7.19)

    centos7  mysql 5.7.19 install 1. Unzip the file [root@centos3 ~]# tar -zxvf mysql-5.7.19-linux-glibc2.12-x86_64.tar.g ...

  6. 2.19 cookie The relevant operation

    2.19 cookie The relevant operation Foreword though cookie The related operation is in the ordinary ui Less used in Automation , It's used occasionally , For example, the login has a graphic verification code , By bypassing the captcha , add to cookie Method login . When I log in, I change my account , It can also be used as ...

  7. (2.14)Mysql And SQL Basics —— The cursor

    (2.14)Mysql And SQL Basics —— The cursor key word :Mysql The cursor -- (1) Define cursors declare cur_name cursor for select * from table_name wh ...

  8. MySQL(8)--- The cursor

    Mysql(8)- The cursor Last time I wrote about the syntax of stored procedures Mysql(7)--- stored procedure Cursors are rarely used in your work , But not to use does not mean not to understand it , But when you really need it to solve the problem , Taking the time to study is likely to ...

  9. MySQL Way of learning One : MySQL 5.7.19 Source code installation

    MySQL 5.7.19 Source code installation Check the system : # cat /etc/redhat-release CentOS Linux release 7.3.1611 (Core) Install dependency packages # yum - ...

  10. Share the actual combat MySQL And Django Version matching experience

    Abstract : About MySQL And Django Experience sharing of version matching knowledge . run: (env) E:\PythonPro\PyDjangoProDemo011\xuanyuaniotpro>python ...

Random recommendation

  1. butterknife Precautions for use

    Wrote a demo, Has been an error Caused by: java.lang.IllegalStateException: Required view 'tv1' with ID 2131492943 for ...

  2. Visual Studio 2012 Better plug-in recommendation

    For efficient development , The following I recommend a few very good plug-ins , For your convenience .   The installation method of the above controls is : And then download it through the Internet , After installation , Need to restart Visual Studio Just can use .

  3. Oracle Define constants and variables

    1. Defining variables A variable is a variable , The data storage structure of a program can be changed at any time during its operation Standard syntax format :< Variable name >< data type >[( length ):=< Initial value >] Example : declar ...

  4. iOS Program # The boot process

    [ App application ] Chinese name : default Foreign name :default pinyin :quē shěng paraphrase : System default state Full name : default state -------------- 1. Program startup sequence 1> main.m Program in ...

  5. [Swift]LeetCode70. climb stairs | Climbing Stairs

    You are climbing a stair case. It takes n steps to reach to the top. Each time you can either climb ...

  6. Use js, Write a simple scroll bar by yourself

    When we add to an element  overflow: auto;  When , Scroll bars will appear , But browsing is different , The style of the scroll bar is very different , Some are even very ugly . So I want to write a scroll bar , I need to make these points clear : 1. Scroll bar ...

  7. 【 turn 】 strrchr() function ---C Language

    from :https://baike.baidu.com/item/strrchr/4621437?fr=aladdin   The name of the function : strrchr The function prototype :char *strrchr(const ...

  8. Android------------------ System service call learning

    One .ServiceManager Methods : This method getService, Used to get the current... By name IBinder Agent for ( No direct access to services ), The function of service delivery depends on IBinder Indirectly called ( Return value IBind ...

  9. Object relation mapping (ORM)

    1. What is? object - Relation mapping object - Relation mapping (Object Relational Mapping, abbreviation ORM, Object relation mapping ) It is a technology to solve the mismatch between object-oriented and relational database . To put it simply ,OR ...

  10. Vue2.5 introduction -3

    Installation and use Global installation vue npm install --global vue-cli Create based on webpack New project for template vue init webpack my-project Installation dependency cd my- ...