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
- Asensitive: can’t create copy of table.
- Read-only: Cursor is read-only,can’t update it.
- Non scalable: Can traverse only in one direction and can not skip rows
How many statements MySQL Cursor have
- Declare
- Open
- Fetch
- Close
- 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 the cursor.
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 an error.
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 goto statement.
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:
- Cursors are best used when you want each row or more than one row one by one.
- It is efficient because with Cursor we are doing operations so there is no need to write complex queries(like joins)
Disadvantages:
- Cursoe is faster than a while loop but it create more overhead in database.
- Cursor fetching one by one data from database so if data is more, its take more execution time.
Let me know if you have used MySQL cursor in your application.If you have any comments or questions, feel free to use our comments section below. And as always, if you have enjoyed this article and found it useful, please share it on Facebook, Twitter and Google Plus.
Comments (4)