Cursor in MySQL

Cursors
In MySQL, a cursor allows row-by-row processing of the result sets. A cursor is used for the result set and returned from a query. By using a cursor, you can iterate, or by step through the results of a query and perform certain operations on each row. The cursor allows you to iterate through the result set and then perform the additional processing only on the rows that require it.

In a cursor contains the data in a loop. Cursors  may be different from SQL commands that operate on all the rows in the returned by a query at one time.

MySQL cursor have three types of properties  :

1. Asensitive :  Server not necessary to make a copy of result table.
2. Read Only  :   In MySQL can not be updated.
3. Non_Scrollable : It can traverse only in one direction and can not skip rows.

There are some steps we have to follow, given below :

• Declare a cursor

• open a cursor statement

• Fetch the cursor

• close the cursor

1 . Declaration of  Cursor : To declare a cursor you must use the DECLARE statement. With the help of the variables, conditions and handlers we need to declare a cursor before we can use it. first of all we will give the cursor a name, this is how we will refer to it later in the procedure. We can have more than one cursor in a single procedure so its necessary to give it a name that will in some way tell us what its doing. We then need to specify the select statement we want to associate with the cursor. The SQL statement can be any valid SQL statement and it is possible to use a dynamic where clause using variable or parameters as we have seen previously.  

Syntax : DECLARE cursor_name CURSOR FOR select_statement;  

2 . Open a cursor statement : For open a cursor we must use the open statement.If we want to fetch rows from it you must open the cursor.

Syntax : OPEN cursor_name;

3 . Cursor fetch statement : When we have to retrieve the next row from the cursor and move the cursor to next row then you need to fetch the cursor.  

Synatx : FETCH cursor_name INTO var_name;

If any row exists, then the above statement fetches the next row and cursor pointer moves ahead to the next row.

4 . Cursor close statement : By this statement closed the open cursor.

Syntax: CLOSE_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.

Example :In the following example first of all we have to declare the Cursor and select the all records 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 :

delimiter //
CREATE PROCEDURE firstCurs()
BEGIN
DECLARE d INT DEFAULT 0;
DECLARE  c_id INT;
DECLARE c_name,c_address VARCHAR(20);
DECLARE cur CURSOR FOR SELECT * FROM company;
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 c_id,c_name,c_address;
INSERT INTO products VALUES(c_id,c_name,c_address);
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;