preface

A stored procedure is a SQL statement that has been compiled and stored in a database. It can accept arguments, use IF statements, set variables, loop, etc. For example, the following statement creates a stored procedure.

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

Call the stored procedure.

mysql>  callselect_all; $$Copy the code

Stored procedures can reduce the transmission between the database and application server, is beneficial to provide database processing efficiency, and Cursor (Cursor) in some places, also called the Cursor, you can in the process of storage, circulation processing of the result set, but for now, MySQL from the SELECT statement from the beginning to the end only allow us to obtain the result set of each line, You cannot get to the first row from the last row or jump directly to the specified row in the result set.

There are several steps to using a cursor.

1. The cursor definition

DECLARE cursor_name CURSOR FOR select_statement
Copy the code

2. Open the cursor

OPEN cursor_name;
Copy the code

3. Get the data in the cursor

FETCH cursor_name INTO var_name [, var_name]...
Copy the code

4. Close the cursor

CLOSE cursor_name;
Copy the code

5. The release of the cursor

DEALLOCATE cursor_name;
Copy the code

The instance

Create a table

CREATE TABLE cursor_table
(id INT ,name VARCHAR(10),age INT
)ENGINE=innoDB DEFAULT CHARSET=utf8;
insert into cursor_table values(1.'Joe'.500);
insert into cursor_table values(2.'bill'.200);
insert into cursor_table values(3.'Cathy'.100);
insert into cursor_table values(4.'the old six'.20);


create table cursor_table_user(name varchar(10));
Copy the code

The cursor traverses the cursor_table table and places names older than 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;  
END

Copy the code

call getTotal();

mysql> select * from cursor_table_user;
+--------+
| name   |
+--------+
|Zhang SAN|
|Li si|
|Cathy|
+--------+
3 rows in set (0.00 sec)

Copy the code

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true; If the cursor or SELECT statement has no data, set the done variable to true to exit the loop.

So here’s the loop through WHILE.