How to Use Cursor in mysql database _ MySQL

How to Use Cursor in a mysql database

DECLARE cursor_name cursor for SELECT_statement;

Cursor operation

OPEN cursor

OPEN cursor_name;

FETCH: obtains the record of the current pointer of the cursor and sends it to the specified variable list. Note that the number of variables must be the same as the number of fields returned by the MySQL cursor. to obtain multiple rows of data, run the FETCH statement in a loop.

FETCH cursor_name INTO variable list;

CLOSE cursor

CLOSE cursor_name;

Note: the MySQL cursor is read-only, that is, you can only read the result set sequentially from the beginning to the end, not from the back to the front, or directly jump to the middle of the record

Open instance

Mysql>
Mysql> create table Employee (
-> Id int,
-> First_name VARCHAR (15 ),
-> Last_name VARCHAR (15 ),
-> Start_date DATE,
-> End_date DATE,
-> Salary FLOAT (8, 2 ),
-> City VARCHAR (10 ),
-> Description VARCHAR (15)
-> );
Query OK, 0 rows affected (0.02 sec)

Mysql>
Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (1, 'Jason ', 'Martin', '000000', '000000', 19960725, 'Toronto ', 'programmer ');
Query OK, 1 row affected (0.01 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (2, 'Alison ', 'mathews', '000000', '000000', 19760321, 'Vancouver ', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (3, 'James ', 'Smith', '000000', '000000', 19781212, 'Vancouver ', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (4, 'cela', 'Rice ', '000000', '000000', 19821024, 'Vancouver', 'manager ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (5, 'Robert ', 'black', '000000', '000000', 19840115, 'Vancouver', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (6, 'Linda ', 'green', '000000', '000000', 19870730, 'New York', 'tester ');
Query OK, 1 row affected (0.01 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (7, 'David ', 'Larry', '123', '123', 19901231, 'New York ', 'manager ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (8, 'James ', 'cat', '000000', '000000', 19960917, 'Vancouver', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> select * from Employee;
+ ------ + ------------ + ----------- + ------------ + --------- + ----------- + ------------- +
| Id | first_name | last_name | start_date | end_date | salary | city | description |
+ ------ + ------------ + ----------- + ------------ + --------- + ----------- + ------------- +
| 1 | Jason | Martin | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 4322.78 | New York | Tester |
| 7 | David | Larry | 7897.78 | New York | Manager |
| 8 | James | Cat | 1232.78 | Vancouver | Tester |
+ ------ + ------------ + ----------- + ------------ + --------- + ----------- + ------------- +
8 rows in set (0.02 sec)

Mysql>
Mysql>
Mysql>
Mysql>
Mysql> DELIMITER // www.3ppt.com
Mysql> create function city_list () returns varchar (255)
-> BEGIN
->
-> DECLARE finished integer default 0;
-> DECLARE city_name VARCHAR (50) DEFAULT "";
-> DECLARE list VARCHAR (255) DEFAULT "";
-> DECLARE city_cur cursor for select city FROM employee;
-> Declare continue handler for not found set finished = 1;
->
-> OPEN city_cur;
->
-> Get_city: LOOP
-> FETCH city_cur INTO city_name;
-> IF finished THEN
-> LEAVE get_city;
-> End if;
-> SET list = CONCAT (list, ",", city_name );
-> End loop get_city;
->
-> CLOSE city_cur;
->
-> Return substr (list, 3 );
-> END
-> //
Query OK, 0 rows affected (0.00 sec)

Mysql> DELIMITER;
Mysql>
Mysql> SELECT city_list () AS cities;
+ Shards +
| Cities |
+ Shards +
| Toronto, Vancouver, New York, New York, Vancouver |
+ Shards +
1 row in set (0.00 sec)

Mysql>
Mysql> drop function city_list;
Query OK, 0 rows affected (0.00 sec)

Mysql>
Mysql>
Mysql>
Mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)

==========
Handler of the cursor

Mysql> DELIMITER //
Mysql> create function city_list () returns varchar (255)
-> BEGIN
->
-> DECLARE finished integer default 0;
-> DECLARE city_name VARCHAR (50) DEFAULT "";
-> DECLARE list VARCHAR (255) DEFAULT "";
-> DECLARE city_cur cursor for select city FROM employee;
-> Declare continue handler for not found set finished = 1;
->
-> OPEN city_cur;
->
-> Get_city: LOOP
-> FETCH city_cur INTO city_name;
-> IF finished THEN
-> LEAVE get_city;
-> End if;
-> SET list = CONCAT (list, ",", city_name );
-> End loop get_city;
->
-> CLOSE city_cur;
->
-> Return substr (list, 3 );
-> END
-> //
Query OK, 0 rows affected (0.00 sec)

Mysql> DELIMITER;
Mysql>
Mysql> SELECT city_list () AS cities;
+ Shards +
| Cities |
+ Shards +
| Toronto, Vancouver, New York, New York, Vancouver |
+ Shards +
1 row in set (0.02 sec)

Mysql>
Mysql> drop function city_list;
Query OK, 0 rows affected (0.00 sec)

Mysql>
Mysql>
Mysql>
Mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
====

Mysql cursor data output instance

Mysql> delimiter $
Mysql> create procedure myProc ()
-> READS SQL DATA
-> BEGIN
-> DECLARE l_last_row int default 0;
-> DECLARE l_dept_id INT;
-> DECLARE c_dept CURSOR
-> SELECT id
-> FROM employee;
->/* Handler to set l_last_row = 1 if a cursor returns no more rows */
-> DECLARE continue handler for not found set l_last_row = 1;
->
-> OPEN c_dept;
-> Dept_cursor: LOOP
-> FETCH c_dept INTO l_dept_id;
-> IF (l_last_row = 1) THEN
-> LEAVE dept_cursor;
-> End if;
-> Select l_dept_id;
->
-> End loop dept_cursor;
-> CLOSE c_dept;
->
-> END $
Query OK, 0 rows affected (0.01 sec)

Mysql>
Mysql> delimiter;