Shulou(Shulou.com)06/01 Report--

Advanced MySQL database (9)-- cursor 1, cursor introduction 1, cursor introduction

The design of cursors is the idea of a data buffer, which is used to store the results of the execution of SQL statements. A cursor is a mechanism that can extract one record at a time from a result set that includes multiple data records.

Although the cursor can traverse all rows in the result, it points to only one row at a time.

The purpose of the cursor is to traverse the records returned by the query database in order to operate accordingly.

2. The characteristics of cursors

Cursors have three properties:

A, insensitive (Asensitive): the database can choose not to copy the result set

B, read only (Read only)

C, do not scroll (Nonscrollable): the cursor can only move in one direction and cannot skip any row of data.

3. Advantages of cursors

Cursors operate on rows, and it is a separate idea that each row of the result set obtained from the SELECT query in the database can be operated separately and independently. Cursor is a bridge between set-oriented and line-oriented design ideas.

4. Shortcomings of cursors

The main disadvantage of cursors is their low performance.

The overhead of a cursor is related to the operations performed in the cursor, and it can be very expensive if complex operations are performed in the cursor. If the set-oriented SQL statement is used, the scanning cost is O (N), but if the scanning cost of using the collection-oriented SQL statement is O (negative N), then the use of cursors may lead to performance improvement.

The disadvantage of cursors is that they can only operate on one line. In the case of large amount 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 deadlocks, affecting other business operations, which is not desirable. When the amount of data is large, the use of cursors will cause insufficient memory.

5. Applicable scenarios for cursors

In MySQL databases, cursors can be used in stored procedures, functions, triggers, and events.

Operation of cursors 1. Definition of cursors

DECLARE cursor_name CURSOR FOR select_statement

2. Open the cursor

OPEN cursor_name

3. Fetch the data in the cursor

FETCH cursor_name INTO var_name [, var_name]...

4. Close the cursor

CLOSE cursor_name

5. Release the cursor

DEALLOCATE cursor_name

3. Cursor instance 1. Create a cursor test table 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 to create a stored procedure in three ways to count the number of records older than 30.

2. Loop loop CREATE PROCEDURE getTotal () BEGIN DECLARE total INT; # # create variable DECLARE sid INT; DECLARE sname VARCHAR (10) to receive cursor data; # create total variable DECLARE sage INT; # create end flag variable DECLARE done INT DEFAULT false; # create cursor DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age > 30 # 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 sage = 0; SET total=0; # open the cursor OPEN cur; # start looping the data in the cursor read_loop:loop # according to a data FETCH cur INTO sid,sname,sage currently pointed to by the cursor # determine whether the cursor loop ends IF done THEN LEAVE read_loop; # when jumping out of the cursor loop END IF; # to get a piece of data, add the count value, here you can do whatever you want, SET total = total + 1; # end the cursor loop END LOOP; # close the cursor CLOSE cur # output result SELECT total; END# calls stored procedure call getTotal (); 3. While loop CREATE PROCEDURE getTotal () BEGIN DECLARE total INT; # # create variable DECLARE sid INT; DECLARE sname VARCHAR (10) that receives cursor data; # create total variable DECLARE sage INT; # create end flag variable DECLARE done INT DEFAULT false # create cursor DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age > 30; # specify the return value DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true; SET total = 0 at the end of the cursor loop; 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; END4, Repeat loop CREATE getTotal () BEGIN DECLARE total INT; # # create variable DECLARE sid INT; DECLARE sname VARCHAR (10) to receive cursor data; # create total variable DECLARE sage INT; # create end flag variable DECLARE done INT DEFAULT false; # create cursor DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age > 30 # specify the return value DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true; SET total = 0 at the end of the cursor loop; 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.