MySQL Cursor cursor reference manual

MySQL tutorial cursor cursor definition

DECLARE cursor_name cursor for select_statement;

Cursor operations

Open cursor

OPEN cursor_name;

Fetch gets the record of the cursor's current pointer and passes it to the specified list of variables, noting 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, and use a loop statement to execute the FETCH

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, not from the back, or directly to the middle record.

Open instance

Mysql>
mysql> CREATE TABLE Employee (
   ->     id             int,
   ->     first_name    VARCHAR,
   ->     last_name      VARCHAR,
   ->     start_date    Date,
   ->     end_date      date,
    ->     salary        FLOAT (8,2),
    ->     city          VARCHAR (10) ,
   ->     description   VARCHAR
   - >);
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 ', ' 19960725 ', ' 20060725 ', 1234.56, ' 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 ', ' 19760321 ', ' 19860221 ', 6661.78, ' 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 ', ' 19781212 ', ' 19900315 ', 6544.78, ' 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, ' Celia ', ' Rice ', ' 19821024 ', ' 19990421 ', 2344.78, ' 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 ', ' 19840115 ', ' 19980808 ', 2334.78, ' 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 ', ' 19870730 ', ' 19960104 ', 4322.78, ' 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 ', ' 19901231 ', ' 19980212 ', 7897.78, ' 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 ', ' 19960917 ', ' 20020415 ', 1232.78, ' 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 | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 9 2 David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in Set (0.02 sec)

Mysql>
Mysql>
Mysql>
Mysql>
Mysql> DELIMITER//
Mysql> CREATE FUNCTION city_list () RETURNS VARCHAR (255)
-> BEGIN
->
-> DECLARE finished INTEGER DEFAULT 0;
-> DECLARE city_name VARCHAR () 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;
+----------------------------------------------------- -------------------------------+
| cities                                                                                 |
+------------------------------------------------------------------------------------+
| Toronto, Vancouver, Vancouver, Vancouver, Vancouver, New York, New York, Vancouver |
+------------------------------------------------------------------------------------+
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 Cursors

Mysql> DELIMITER//
Mysql> CREATE FUNCTION city_list () RETURNS VARCHAR (255)
-> BEGIN
->
-> DECLARE finished INTEGER DEFAULT 0;
-> DECLARE city_name VARCHAR () 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;
+----------------------------------------------------- -------------------------------+
| cities                                                                                 |
+------------------------------------------------------------------------------------+
| Toronto, Vancouver, Vancouver, Vancouver, Vancouver, New York, New York, Vancouver |
+------------------------------------------------------------------------------------+
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 for
-> 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;