CURSORS

mysql> delimiter //
mysql> CREATE PROCEDURE DemoCurs1()
    -> BEGIN
    -> DECLARE d INT DEFAULT 0;
    -> DECLARE id,sal,perk INT;
    -> DECLARE name,city,desig VARCHAR(20);
    -> DECLARE cur CURSOR FOR SELECT * FROM Emp;
    -> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET d=1;
    -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET d=1;
    -> OPEN cur;
    -> lbl: LOOP
    -> IF d=1 THEN
    -> LEAVE lbl;
    -> END IF;
    -> IF NOT d=1 THEN
    -> FETCH cur INTO id,name,city,desig,sal,perk;
    -> INSERT INTO Emp2 VALUES(id,name,city,desig,sal,perk);
    -> END IF;
    -> END LOOP;
    -> CLOSE cur;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> CALL DemoCurs1();
Query OK, 1 row affected (0.12 sec)

mysql> SELECT * FROM Emp2;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi    | Manager           | 10300  | 853   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10300  | 853   |
| 3   | Chandan | Banglore | Team Leader       | 15450  | 999   |
| 5   | Tapan   | Pune     | Developer         | 20600  | 1111  |
| 6   | Amar    | Chennai  | Developer         | 16000  | 1124  |
| 7   | Santosh | Delhi    | Designer          | 10000  | 865   |
| 8   | Suman   | Pune     | Web Designer      | 20000  | 658   |
+-----+---------+----------+-------------------+--------+-------+
7 rows in set (0.00 sec)