mysql>use course;
Reading table informationforcompletion of table and column names
You can turn offthis feature to get a quicker startup with -A
Database changed
mysql> select * fromteacher;+----+-----------+---------+
| id | name | dept_id |
+----+-----------+---------+
| 1 | Zhang san | 1 |
| 2 | Li si | 1 |
| 3 | Wang wu | 2 |
| 4 | Liu liu | 3 |
| 5 | Ding qi | 3 |
+----+-----------+---------+
5 rows in set (0.00sec)
mysql>desc teacher;+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(64) | YES | | NULL | |
| dept_id | int(11) | NO | MUL | NULL | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.01sec)
mysql>SELECT id,name,dept_id FROM teacher;+----+-----------+---------+
| id | name | dept_id |
+----+-----------+---------+
| 1 | Zhang san | 1 |
| 2 | Li si | 1 |
| 3 | Wang wu | 2 |
| 4 | Liu liu | 3 |
| 5 | Ding qi | 3 |
+----+-----------+---------+
5 rows in set (0.00sec)mysql> delimiter //mysql>CREATE PROCEDURE curdemo()->BEGIN-> DECLARE done INT DEFAULT true;-> DECLARE v_name varchar(64);-> DECLARE v_id int;-> DECLARE v_dept_id int;->DECLARE cur1 CURSOR FOR SELECT id,name,dept_id FROM teacher;-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = false;->open cur1;-> while done do
->fetch cur1 into v_id,v_name,v_dept_id;-> selectv_id,v_name,v_dept_id;-> select 'row-----------'abc;-> end while;->end;-> //Query OK, 0 rows affected (0.04sec)
mysql>delimiter ;
mysql>mysql>call curdemo();+------+-----------+-----------+
| v_id | v_name | v_dept_id |
+------+-----------+-----------+
| 1 | Zhang san | 1 |
+------+-----------+-----------+
1 row in set (0.01sec)+----------------+
| abc |
+----------------+
| row----------- |
+----------------+
1 row in set (0.01sec)+------+--------+-----------+
| v_id | v_name | v_dept_id |
+------+--------+-----------+
| 2 | Li si | 1 |
+------+--------+-----------+
1 row in set (0.01sec)+----------------+
| abc |
+----------------+
| row----------- |
+----------------+
1 row in set (0.01sec)+------+---------+-----------+
| v_id | v_name | v_dept_id |
+------+---------+-----------+
| 3 | Wang wu | 2 |
+------+---------+-----------+
1 row in set (0.01sec)+----------------+
| abc |
+----------------+
| row----------- |
+----------------+
1 row in set (0.01sec)+------+---------+-----------+
| v_id | v_name | v_dept_id |
+------+---------+-----------+
| 4 | Liu liu | 3 |
+------+---------+-----------+
1 row in set (0.01sec)+----------------+
| abc |
+----------------+
| row----------- |
+----------------+
1 row in set (0.01sec)+------+---------+-----------+
| v_id | v_name | v_dept_id |
+------+---------+-----------+
| 5 | Ding qi | 3 |
+------+---------+-----------+
1 row in set (0.01sec)+----------------+
| abc |
+----------------+
| row----------- |
+----------------+
1 row in set (0.01sec)+------+---------+-----------+
| v_id | v_name | v_dept_id |
+------+---------+-----------+
| 5 | Ding qi | 3 |
+------+---------+-----------+
1 row in set (0.01sec)+----------------+
| abc |
+----------------+
| row----------- |
+----------------+
1 row in set (0.01sec)
Query OK,0 rows affected (0.01sec)mysql> select * fromteacher;+----+-----------+---------+
| id | name | dept_id |
+----+-----------+---------+
| 1 | Zhang san | 1 |
| 2 | Li si | 1 |
| 3 | Wang wu | 2 |
| 4 | Liu liu | 3 |
| 5 | Ding qi | 3 |
+----+-----------+---------+
5 rows in set (0.00sec)
mysql> delimiter //mysql>CREATE PROCEDURE curdemo2()->BEGIN-> DECLARE done INT DEFAULT true;-> DECLARE v_name varchar(64);-> DECLARE v_id int;-> DECLARE v_dept_id int;->DECLARE cur1 CURSOR FOR SELECT id,name,dept_id FROM teacher;-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = false;->open cur1;->fetch cur1 into v_id,v_name,v_dept_id;-> while done do
-> selectv_id,v_name,v_dept_id;-> select 'row-----------'abc;->fetch cur1 into v_id,v_name,v_dept_id;-> end while;->end;-> //Query OK, 0 rows affected (0.10sec)
mysql>delimiter ;
mysql>mysql>call curdemo2();+------+-----------+-----------+
| v_id | v_name | v_dept_id |
+------+-----------+-----------+
| 1 | Zhang san | 1 |
+------+-----------+-----------+
1 row in set (0.01sec)+----------------+
| abc |
+----------------+
| row----------- |
+----------------+
1 row in set (0.01sec)+------+--------+-----------+
| v_id | v_name | v_dept_id |
+------+--------+-----------+
| 2 | Li si | 1 |
+------+--------+-----------+
1 row in set (0.01sec)+----------------+
| abc |
+----------------+
| row----------- |
+----------------+
1 row in set (0.01sec)+------+---------+-----------+
| v_id | v_name | v_dept_id |
+------+---------+-----------+
| 3 | Wang wu | 2 |
+------+---------+-----------+
1 row in set (0.01sec)+----------------+
| abc |
+----------------+
| row----------- |
+----------------+
1 row in set (0.01sec)+------+---------+-----------+
| v_id | v_name | v_dept_id |
+------+---------+-----------+
| 4 | Liu liu | 3 |
+------+---------+-----------+
1 row in set (0.01sec)+----------------+
| abc |
+----------------+
| row----------- |
+----------------+
1 row in set (0.01sec)+------+---------+-----------+
| v_id | v_name | v_dept_id |
+------+---------+-----------+
| 5 | Ding qi | 3 |
+------+---------+-----------+
1 row in set (0.01sec)+----------------+
| abc |
+----------------+
| row----------- |
+----------------+
1 row in set (0.01sec)
Query OK,0 rows affected (0.01 sec)