MySQL database Advanced (ix)--Cursors

MySQL database Advanced (ix)--cursor One, cursor INTRODUCTION 1, Cursor introduction

The design of a cursor is the idea of a data buffer that is used to hold the results of the SQL statement execution. A cursor is a mechanism that extracts one record at a time from a result set that includes multiple data records.
Although a cursor can traverse all the rows in the result, it only points to one row at a time.
Cursors are used to traverse the records returned by the query database for the appropriate operation.

2, the characteristics of the cursor

A cursor has three properties:
A, Insensitive (asensitive): Database can choose not to copy result set
B, ReadOnly (Read only)
C, no Scrolling (nonscrollable): Cursors can only move in one direction and cannot skip any row of data.

3. Advantages of Cursors

A cursor is a separate idea for a row operation that separates the same or different operations from each row of a result set obtained from a select query in a database. Cursors are a bridge between the set-oriented and line-oriented design concepts.

4, the shortcomings of the cursor

The main disadvantage of cursors is that performance is not high.
The cost of the cursor is related to what is done in the cursor, which can be expensive if you are doing complex operations in the cursor. If a collection-oriented SQL statement is used, the scan cost is O (N), but if the scan cost for a collection-oriented SQL statement is O (n*n), the use of cursors can lead to performance gains.
The disadvantage of cursors is that they can only be one row at a line. In the case of large amounts of data, the speed is too slow. Most of the database is facing the collection, the business will be more complex, and the use of cursors will have deadlock, affecting other business operations, undesirable. When the volume of data is large, using cursors can cause out-of-memory behavior.

5, the application of the cursor scenario

In a MySQL database, you can use cursors in stored procedures, functions, triggers, and events.

Second, cursor operation 1, the definition of the cursor

DECLARE?cursor_name?CURSOR?FOR?select_statement??

2. Open cursor

OPEN?cursor_name;

3. Take the data in the cursor

FETCH?cursor_name?INTO?var_name?[,?var_name]...

4. Close the cursor

CLOSE cursor_name;

5. Releasing the cursor

DEALLOCATE cursor_name;

Iii. Cursor Instance 1, creating a test table for a cursor

CREATE TABLE cursor_table(id INT ,name VARCHAR(10),age INT)ENGINE=innoDB DEFAULT CHARSET=utf8;insert into cursor_table values(1, ‘孙悟空‘, 500);insert into cursor_table values(2, ‘猪八戒‘, 200);insert into cursor_table values(3, ‘沙悟净‘, 100);insert into cursor_table values(4, ‘唐僧‘, 20);

Use cursors in three ways to create a stored procedure that counts the number of records older than 30.

2, Loop loop

CREATE  PROCEDURE getTotal()BEGIN      DECLARE total INT;     ##创建接收游标数据的变量      DECLARE sid INT;      DECLARE sname VARCHAR(10);      #创建总数变量      DECLARE sage INT;      #创建结束标志变量      DECLARE done INT DEFAULT false;      #创建游标      DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;      #指定游标循环结束时的返回值      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;      #设置初始值      SET sage = 0;      SET total=0;    #打开游标      OPEN cur;      #开始循环游标里的数据      read_loop:loop      #根据游标当前指向的一条数据      FETCH cur INTO sid,sname,sage;      #判断游标的循环是否结束      IF done THEN          LEAVE read_loop;    #跳出游标循环      END IF;      #获取一条数据时，将count值进行累加操作，这里可以做任意你想做的操作，      SET total = total + 1;      #结束游标循环      END LOOP;      #关闭游标      CLOSE cur;      #输出结果      SELECT total;  END

#调用存储过程  call getTotal();  

3. While loop

CREATE  PROCEDURE getTotal()BEGIN      DECLARE total INT;     ##创建接收游标数据的变量      DECLARE sid INT;      DECLARE sname VARCHAR(10);      #创建总数变量      DECLARE sage INT;      #创建结束标志变量      DECLARE done INT DEFAULT false;      #创建游标      DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;      #指定游标循环结束时的返回值      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;       SET total = 0;      OPEN cur;      FETCH cur INTO sid, sname, sage;      WHILE(NOT done)     DO          SET total = total + 1;          FETCH cur INTO sid, sname, sage;      END WHILE;      CLOSE cur;      SELECT total;  END

4. Repeat cycle

CREATE getTotal()BEGIN      DECLARE total INT;     ##创建接收游标数据的变量      DECLARE sid INT;      DECLARE sname VARCHAR(10);      #创建总数变量      DECLARE sage INT;      #创建结束标志变量      DECLARE done INT DEFAULT false;      #创建游标      DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age > 30;      #指定游标循环结束时的返回值      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;       SET total = 0;      OPEN cur;      REPEAT      FETCH cur INTO sid, sname, sage;       IF NOT done THEN          SET total = total + 1;      END IF;      UNTIL done END REPEAT;      CLOSE cur;      SELECT total;  END

MySQL database Advanced (ix)--Cursors