CURSORS
Cursors are used when the SQL Select statement is expected to return more than one row. Cursors are supported inside procedures and functions. Cursors must be declared and its definition contains the query. The cursor must be defined in the DECLARE section of the program. A cursor must be opened before processing and close after processing.
Syntax to declare the cursor :
DECLARE <cursor_name> CURSOR FOR <select_statement>
Multiple cursors can be declared in the procedures and functions but each cursor must have a unique name. And in defining the cursor the select_statement cannot have INTO clause.
Syntax to open the cursor :
OPEN <cursor_name>
By this statement we can open the previously declared cursor.
Syntax to store data in the cursor :
FETCH <cursor_name> INTO <var1>,<var2>??.
The above statement is used to fetch the next row if a row exists by using the defined open cursor.
Syntax to close the cursor :
CLOSE <cursor_name>
By this statement we can close the previously opened cursor. If it is not closed explicitly then a cursor is closed at the end of compound statement in which that was declared.
In the following example firstly we are declaring the Cursor and selecting the all record from Emp table. And after opened the cursor we fetch the record one by one from cursor. And then insert these record in Emp2 table. Example :
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) |