HOW TO USE MYSQL CURSOR

What is MySQL Cursor?

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

The Main purpose of a cursor is when you want to perform the operation in multiple tables for each row with the results of query operations.Another reason to use cursor is to use when there is some steps in the process are optional and you want to perform those steps on certain rows of the query. so with the 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 support cursor.

What properties Cursor have

1. Asensitive: can’t create copy of table.
2. Read-only: Cursor is read-only,can’t update it.
3. Non scalable: Can traverse only in one direction and can not skip rows

How many statements MySQL Cursor have

1. Declare
2. Open
3. Fetch
4. Close
5. Handler

STEPS:

Cursor can be used with following steps:

First of all, you must have declared the cursor.A DECLARE statement is used to define thecursor.

SYNTAX

1 2 3

DECLARE cursor_name CURSOR FOR SELECT_query

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

SYNTAX

1 2 3

OPEN cursor_name;

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

SYNTAX

1 2 3

FETCH cursor_name INTO variables;

NOTE: 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

1 2 3

DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;

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 the cursor and if you don’t close it, MySQL will automatically close it when the END statement is reached.

SYNTAX

1 2 3

CLOSE cursor_name

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

How to use cursor with Store Procedure

Let’s a have a look with one basic example which show how to use cursor with Store Procedure

1 2 3 4 5 6 7 8 9 10 11 12 13

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 tname and stored in testCursor so after that we can use retrieved data for further operations like I have used to insert data in another table.

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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 the cursor for multiple rows and how we can apply looping with the cursor.Here I have declared one CONTINUE HANDLER that for to break or exit from the loop if not data found.

There are couple of articles you like to read:

To use Store Procedure with PDO
Count Number of Columns in MySQL
PHP ODBC connection with Mysql

You can also use REPEAT statement.MySQL also supports a LOOP statement that can be used to repeat code until the LOOP is manually exited using a LEAVE statement like a gotostatement.

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

What are the advantages and disadvantages of Cursor?

Advantages:

1. Cursors are best used when you want each row or more than one row one by one.
2. It is efficient because with Cursor we are doing operations so there is no need to write complex queries(like joins)

Disadvantages:

1. Cursoe is faster than a while loop but it create more overhead in database.
2. Cursor fetching one by one data from database so if data is more, its take more execution time.