What is a cursor in MySQL? How to use cursors?

What is a cursor? How to use cursors? The following article will take you to understand the cursors in MySQL and introduce the steps to use cursors. I hope it will be helpful to you!

To understand what a cursor is, you must first understand the stored procedure. A stored procedure is a SQL statement that has been compiled in advance and stored in the database. It can accept parameters or Use IF statements, set variables, loops, etc., such as the following statement to create a stored procedure. [Related recommendations: mysql video tutorial]

delimiter $$
create procedure select_all() begin select * from user; end;$$

Call the stored procedure.

mysql>  call select_all;$$

Stored procedures can reduce the transmission between the database and the application server, which is good for improving database processing efficiency. The cursor (Cursor) is also called the cursor in some places and can be used to modify the results during the stored procedure. The set is processed in a loop, but currently, MySQL only allows us to obtain each row in the result set from the beginning to the end of the SELECT statement. We cannot obtain the first row from the last row, nor can we jump directly to the result set. Specify row.

There are several steps to use a cursor.

1. Cursor definition

DECLARE cursor_name CURSOR FOR select_statement

2. Open the cursor

OPEN cursor_name;

3. Get 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;

Example

Create 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);


create table cursor_table_user(name varchar(10));

Below we use the cursor to traverse the cursor_table table and add those who are older than 30 The person's name is stored in cursor_table_user

drop procedure getTotal;
delete from cursor_table_user ;

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  
       insert cursor_table_user values(sname);
       SET total = total + 1;  
       FETCH cur INTO sid, sname, sage;  
       
   END WHILE;  
   CLOSE cur;  
   SELECT total;  
END

call getTotal();

mysql> select * from cursor_table_user;
+--------+
| name   |
+--------+
| 张三   |
| 李四   |
| 王五   |
+--------+
3 rows in set (0.00 sec)

There is a very important line in this program, DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true; , which means that if the cursor or SELECT statement has no data, it will doneThe value of the variable is set to true to exit the loop.

The following is to traverse sequentially through WHILE.

Original address: https://juejin.cn/post/7003237966952792077

For more programming-related knowledge, please visit: Programming Video! !

The above is the detailed content of What is a cursor in MySQL? How to use cursors?. For more information, please follow other related articles on the PHP Chinese website!