• The original content of GreatSQL community cannot be used without authorization. Please contact Xiaobian and indicate the source for reprint.

Content outline

1, Test environment construction

2, Execute process resolution

3, Precautions

1, Test environment construction

First, create a table and insert several rows of data fields:

CREATE TABLE t (s1 INT, s2 char(100),PRIMARY KEY (s1));
INSERT INTO t values(1,'aaa');
INSERT INTO t values(2,'bbb');
INSERT INTO t values(3,'ccc');

Then create the stored procedure, here v_total is used to determine the number of data rows.

Because there is no exit mechanism for the last line after the MySQL cursor is obtained. Therefore, if you continue to retrieve data without judging whether the last line is retrieved, an error will be reported.

DELIMITER ;;
  CREATE PROCEDURE test_mysql_cursor_loop()
    BEGIN
      declare v_total int default 0; 
      declare i int default 0; 
      declare str1 int;
      declare str2 varchar(255);
      DECLARE stuCursor CURSOR FOR SELECT s1,s2 FROM t;
      select count(s2) into v_total from t;
      OPEN stuCursor;
      stuLoop:LOOP
        IF i = v_total THEN LEAVE stuLoop; end if;
        FETCH stuCursor INTO str1,str2;
          SELECT str1,str2;
          set i = i+1;
      end loop stuLoop;
    END;;
DELIMITER ;

2, Execute process resolution

This stored procedure is converted into a stored procedure in MySQL. The instructions are as follows:

mysql> show PROCEDURE code test_mysql_cursor_loop;
+-----+---------------------------------------------+
| Pos | Instruction                                 |
+-----+---------------------------------------------+
|   0 | set v_total@0 0                             |
|   1 | set i@1 0                                   |
|   2 | set str1@2 NULL                             |
|   3 | set str2@3 NULL                             |
|   4 | cpush stuCursor@0: SELECT s1,s2 FROM t      |
|   5 | stmt "select count(s2) into v_total from t" |
|   6 | copen stuCursor@0                           |
|   7 | jump_if_not 9(9) (i@1 = v_total@0)          |
|   8 | jump 13                                     |
|   9 | cfetch stuCursor@0 str1@2 str2@3            |
|  10 | stmt "SELECT str1,str2"                     |
|  11 | set i@1 (i@1 + 1)                           |
|  12 | jump 7                                      |
|  13 | cclose stuCursor@0                          |
|  14 | cpop 1                                      |
+-----+---------------------------------------------+
15 rows in set (0.00 sec)

Of which:

• v_total@0 @ 0 represents that this is the first custom variable.
• stuCursor@0 The @ 0 of represents that this is the first cursor variable.

It can be seen from the instruction that the operation of cursor needs to go through several processes:

1,cpush

This is used to register a cursor and initialize the stack.

2,copen

Here we will run the SELECT s1,s2 FROM t instruction first, let cursor get the column information of the table, then call cpush, and then open a cursor through cpush->open to get the table data.

3,cfetch

The data obtained from cursor is assigned to the custom variables STR1 and STR2, and then passed to the client side for display. Note that jump 7 is used to fetch data circularly.

4,cclose

Close cursor.

5,cpop

Release the current cursor from the stack.

The above circular access and exit mechanisms are as follows:

jump_ if_ not 9(9) ( i@1 = v_total@0 )Used to judge i@1 Is it equal to v_total@0 , if not, the current instruction jumps to the 9th instruction and continues to execute cfetch.

If i@1 Equal to v_total@0 If yes, continue to execute the next jump 13, close cursor and exit the current cycle. set i@1 ( i@1 +1) it is used for counting, and jump 7 is used to continue cyclic data retrieval.

3, Precautions

The type and quantity of str1 and str2 must be consistent with that of declare cursor. If the declaration is incorrect, cfetch error will occur.

After declare str1 and str2, mysql will create a temporary table for these two columns. When cfetch data, it will store the data in this table. Subsequent users will take data from this temporary table to display when they select, so correct variable declaration must be made.

Enjoy MySQL :)

Article recommendation:

GreatSQL MGR FAQ
https://mp.weixin.qq.com/s/J6...

Wan Da #12, how can the MGR cluster automatically select the master without manual intervention
https://mp.weixin.qq.com/s/07...

"2021 data technology Carnival · ON LINE": evolution and practice of MySQL high availability architecture
https://mp.weixin.qq.com/s/u7...

Packet capture analysis of where an sql statement is slow
https://mp.weixin.qq.com/s/AY...

Wan Da #15, what are the conditions that may cause the MGR service to fail to start
https://mp.weixin.qq.com/s/in...

Technology sharing | why MGR consistency mode does not recommend AFTER
https://mp.weixin.qq.com/s/rN...

About GreatSQL

GreatSQL is a MySQL branch maintained by Wanli database. It focuses on improving the reliability and performance of MGR and supports the parallel query feature of InnoDB. It is a branch version of MySQL suitable for financial applications.

Gitee:
https://gitee.com/GreatSQL/Gr...

GitHub:
https://github.com/GreatSQL/G...

Bilibili:
https://space.bilibili.com/13...

Wechat & QQ group:
You can search and add GreatSQL community assistant wechat friends, send verification information "add group" to join GreatSQL/MGR communication wechat group

QQ group: 533341697
Wechat assistant: wanlidbc

This article is composed of blog one article multi posting platform OpenWrite release!