Back to Top

How to use MySQL Cursor

MySQL CURSOR Explained

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 the cursor.

SYNTAX

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

SYNTAX

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

SYNTAX

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

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

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

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

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:

  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.

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)

  1. I ran into this article accidentally, amazingly, this is a fantastic website. Your website owner features carried out an excellent job regarding putting it together, the data here is really and helpful when i do research. You just attached yourself a guarenteed readers.

  2. I became more than happy to seek out this net-site.We wished to thank you on your moment for this excellent learn!! My spouse and i definitely savoring every little little bit of the idea and I have you bookmarked to take a look at new stuff you weblog post.

  3. I am trying to make dyanamic order by in Stored procedure … any help???

  4. raghunadhan says:

    REALLY EASY , CLEAR AND SIMPLE

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

How to upload file using WordPress

Posted on 5 years ago

Bhumi

Step by step Node JS Tutorial for Beginner

Posted on 2 years ago

Bhumi

How to Export data using YUI DataTable

Posted on 2 years ago

Bhumi