cursor in stored procedures ( MySQL )

A cursor allow us to use set of resultsets returned by mysql query in one by one pattern. With the use of cursor we can perform operations on set of resultset on each returned row.Like you are fetching multiple data by any operation and you want to operate those data in loop. so with the use of cursor you can loops through the results.Cursor is convienient to use when you are performing on a complex resultset.Cursor can be used inside the stored procedures, functions and triggers.

An Main purpose of a cursor is when you want to perform operation in multiple tables for each row with the results of a query operations.Another reason to use cursor is to use when there is some steps in process are optional and you want to perfom those steps on certain rows of query. so with cursor you can fetch the result set and then perform the additional processing only on the rows that require it.Latest Version of Mysql 5 and greater.

Cursor can be used with following steps:

1. First of all you must have declare cursor.A DECLARE statement is used to define the cursor.

syntax :

          DECLARE cursor_name CURSOR FOR SELECT_query

2. After that you need to open cursor by OPEN Statement.When the OPEN statement is executed,the select query is retrieve data.

syntax:

          OPEN cursor_name;

3. After a cursor is opened, you can use FETCH statement to fetch each row

syntax:

          FETCH cursor_name INTO variables;

4. In MySQL, we must have to declare a HANDLER for the built in NOT FOUND condition, when there is no more data found in Cursor.Mostly prefer to use CONTINUE handler.

syntax:

           DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;

5. Finally,After process regarding cursor is finished, you can close cursor using a CLOSE statement.CLOSE frees up any internal memory and resources used by the cursor so better to close cursor and if you don’t close it, MySQL will automatically close it when the END statement is reached.

syntax:

           CLOSE cursor_name

Tip: 

        Always declare other variables before the declaration of cursor,  otherwise you will get error.

Example

                    DELIMITER $

                    CREATE PROCEDURE `users`()

                    BEGIN

                    DECLARE n VARCHAR(200);

                    DECLARE s VARCHAR(200);

                    DECLARE testCursor CURSOR FOR SELECT name,status

                    from test.tname WHERE id =1;

                    OPEN testCursor;

                    FETCH testCursor INTO n,s;

                    INSERT into friend_status (name,status_update)

                    VALUES (n,s);

                    CLOSE testCursor;

                    END

Above example is simply shows how we can use cursor and with cursor we can retrieve data from table named tname and stored in cursor nametestCursor 

so after that we retrieve data for further operations like we have used to insert data into other table

Next is about to use cursor with complex Store procedure or in a loop

                  DELIMITER $$

                  CREATE PROCEDURE `userCursor`()

                  BEGIN

                  DECLARE flag TINYINT DEFAULT 0;

                  DECLARE n VARCHAR(200);

                  DECLARE s VARCHAR(200);

                  DECLARE testCursor CURSOR

                  FOR SELECT name,status from test.tname;

                  DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1;

                  SET flag=0;

                  OPEN testCursor;

                  WHILE (NOT flag) DO

                  FETCH testCursor INTO n,s;

                  IF NOT flag THEN

                  INSERT into friend_status

                  (name,status_update) VALUES (n,s);

                  END IF;

                  END WHILE;

                  SELECT * FROM friend_status;

                  CLOSE testCursor;

                  END

Above example is shows how we can use cursor for multiple rows and how we can apply looping with cursor.Here we have declared one

CONTINUE HANDLER

that for to break or exit from the loop if not data found. 

Tip:

           Must declare cursor first and then declare a NOT FOUND handler otherwise you will get an error.