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