How to use SQL cursors cyclically

How to use SQL cursors cyclically

Mysql tutorial>
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 ', 'www .zhutiai.com', '000000', '000000', 19960725, 'toronto ', 'programmer ');
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 (2, 'alison ', 'mathews', '000000', '000000', 19760321, 'vancouver ', 'tester ');
Query OK, 1 row affected (0.02 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.02 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', 'www .111cn.cn', 19870730, 'New York ', '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 (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.00 sec)

Mysql>
Mysql>
Mysql>
Mysql> delimiter $
Mysql> create procedure myProc (in_customer_id INT)
-> BEGIN
->
-> DECLARE l_first_name VARCHAR (30 );
-> DECLARE l_id INT;
-> DECLARE l_city VARCHAR (30 );
-> DECLARE l_department_count INT;
-> DECLARE no_more_departments INT;
->
-> DECLARE dept_csr CURSOR
-> SELECT id, first_name, city
-> FROM employee;
->
-> Declare continue handler for not found set no_more_orders ments = 1;
->
-> SET no_more_departments = 0;
-> OPEN dept_csr;
-> Dept_loop: WHILE (no_more_orders ments = 0) DO
-> FETCH dept_csr INTO l_id, l_first_name, l_city;
-> IF no_more_ments = 1 THEN
-> LEAVE dept_loop;
-> End if;
-> SET l_department_count = l_department_count + 1;
-> Select l_id, l_first_name, l_city;
-> End while dept_loop;
-> CLOSE dept_csr;
-> SET no_more_departments = 0;
->
-> END $
Query OK, 0 rows affected (0.00 sec)

Mysql> delimiter;
Mysql>
Mysql> call myProc (1 );
+ ------ + -------------- + --------- +
| L_id | l_first_name | l_city |
+ ------ + -------------- + --------- +
| 1 | Jason | Toronto |
+ ------ + -------------- + --------- +
1 row in set (0.01 sec)

+ ------ + -------------- + ----------- +
| L_id | l_first_name | l_city |
+ ------ + -------------- + ----------- +
| 2 | Alison | Vancouver |
+ ------ + -------------- + ----------- +
1 row in set (0.01 sec)

+ ------ + -------------- + ----------- +
| L_id | l_first_name | l_city |
+ ------ + -------------- + ----------- +
| 3 | James | Vancouver |
+ ------ + -------------- + ----------- +
1 row in set (0.01 sec)

+ ------ + -------------- + ----------- +
| L_id | l_first_name | l_city |
+ ------ + -------------- + ----------- +
| 4 | Celia | Vancouver |
+ ------ + -------------- + ----------- +
1 row in set (0.01 sec)

+ ------ + -------------- + ----------- +
| L_id | l_first_name | l_city |
+ ------ + -------------- + ----------- +
| 5 | Robert | Vancouver |
+ ------ + -------------- + ----------- +
1 row in set (0.01 sec)

+ ------ + -------------- + ---------- +
| L_id | l_first_name | l_city |
+ ------ + -------------- + ---------- +
| 6 | Linda | New York |
+ ------ + -------------- + ---------- +
1 row in set (0.01 sec)

+ ------ + -------------- + ---------- +
| L_id | l_first_name | l_city |
+ ------ + -------------- + ---------- +
| 7 | David | New York |
+ ------ + -------------- + ---------- +
1 row in set (0.03 sec)

+ ------ + -------------- + ----------- +
| L_id | l_first_name | l_city |
+ ------ + -------------- + ----------- +
| 8 | James | Vancouver |
+ ------ + -------------- + ----------- +
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

Mysql>
Mysql>
Mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)

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

Mysql>