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
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.
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)