Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use cursors in MySQL, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

To understand what a cursor is, you must first understand the stored procedure, which is a SQL statement that has been compiled in advance and stored in the database. It can accept parameters and use if statements, setting variables, loops, etc., for example, the following statement is used to create a stored procedure. [related recommendation: 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, and it is good to provide database processing efficiency, while Cursor is also called the cursor, which can loop the result set in the stored procedure, but at present, MySQL only allows us to get each row of the result set from the beginning to the end of the SELECT statement, but not the first row from the last row. Nor can you jump directly to the specified row in the result set.

There are several steps to using cursors.

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; instance

Create a tabl

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,6,20); create table cursor_table_user (name varchar (10))

Next, we use cursors to traverse the cursor_table table and store the names of people over 30 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; ENDcall getTotal (); mysql > select * from cursor_table_user +-+ | name | +-+ | Zhang San | | Li Si | | Wang Wu | +-+ 3 rows in set (0.00 sec)

This program has a very important line, DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;, which says that if the cursor or SELECT statement has no data, set the value of the done variable to true to exit the loop.

Thank you for reading this article carefully. I hope the article "how to use cursors in MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.