Cursor in MySQL

Introduction

In this tutorial, I am going to explain MySQL Cursors with examples. This article covers the following topics.

Introduction to the Cursors
Working with MySQL Cursors
MySQL Cursor Life Cycle
MySQL Cursors Example
Conclusion

What is Cursor in MySQL?

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

A cursor contains the data in a loop. Cursors may be different from SQL commands that operate on all the rows returned by a query at one time. MySQL cursor has three types of properties.

1. Read Only

The data in the underlying table cannot be modified via a cursor.

2. Non_Scrollable

Only rows can be retrieved in the order specified by the SELECT statement. In the reverse order, users can not retrieve records. Additionally, in the result set, users cannot skip rows or jump to a particular row.

3. Asensitive

An insensitive cursor is used to point to the actual data, whereas a temporary copy of the data is used by an insensitive cursor used. An insensitive cursor performs faster than an insensitive cursor because it does not have to make a temporary copy of data.

Working with MySQL Cursors

There are some steps we have to follow while using MySQL Cursors. Let’s see.

Declare a Cursor
Open a Cursor
Fetch the Cursor
Close the Cursor

1. Declaration of a Cursor

To declare a cursor, you must use the DECLARE statement. With the help of the variables, we need conditions and handlers 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 it's necessary to give it a name that will, in some way, tell us what it's 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 variables or parameters, as we have seen previously.

Syntax

DECLARE<cursor_name>CURSOR FOR<select_statement>;

2. Open a Cursor

For opening a cursor, we must use the open statement. If we want to fetch rows from it, then you must have to open the cursor.

Syntax

OPEN <cursor_name>;

3. Fetch the Cursor

When we have to retrieve the next row from the cursor and move the cursor to the next row, then you need to fetch the cursor. If any row exists, then the below statement fetches the next row, and the cursor pointer moves ahead to the next row.

Syntax

FETCH <cursor_name> INTO <variable_list>;

4. Close the Cursor

This statement closes the open cursor, and it will deactivate the cursor and release the memory. 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 the compound statement in which that was declared.

Syntax

CLOSE <cursor_name>;

MySQL Cursor Life Cycle

The following diagram will show you the working of MySQL Cursors. Let’s see.

cursorsql

Note.

The cursor follows the same function as in programming. In programming, we use a loop such as FOR, WHILE, or Do While to iterate over one component at a time.

MySQL Cursors Examples

In the following example, first of all, we have to declare the Cursor and select all records from a table, i.e., “GetVatsaCursor”. And after we open the cursor, we fetch the record one by one from the cursor. And then insert these records in another table, i.e., “Vbackupdata”. Without wasting time, let’s create a database and a table and insert some rows into it.

Step 1. Create a Database.

CREATE DATABASE Cursors_db;

Step 2. Create few tables.

USE Cursors_db;
CREATE TABLE GetVatsaCursor (
    C_ID INT PRIMARY KEY AUTO_INCREMENT,
    c_name VARCHAR(50),
    c_address VARCHAR(200)
);
CREATE TABLE Vbackupdata (
    C_ID INT,
    c_name VARCHAR(50),
    c_address VARCHAR(200)
);

Step 3. Now, insert some dummy data into it.

INSERTINTOGetVatsaCursor(c_name,c_address)VALUES('Test','132,VatsaColony'),
('Admin','133,VatsaColony'),
('Vatsa','134,VatsaColony'),
('Onkar','135,VatsaColony'),
('Rohit','136,VatsaColony'),
('Simran','137,VatsaColony'),
('Jashmin','138,VatsaColony'),
('Anamika','139,VatsaColony'),
('Radhika','140,VatsaColony');

Step 4. Now, by using the SELECT query, see the data inside both tables.

SELECT*FROMGetVatsaCursor;
SELECT*FROMVbackupdata;

Cursor Example

DELIMITER //
CREATE PROCEDURE firstCurs()
BEGIN
    DECLARE d INT DEFAULT 0;
    DECLARE c_id INT;
    DECLARE c_name, c_address VARCHAR(20);

    DECLARE Get_cur CURSOR FOR SELECT * FROM GetVatsaCursor;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
    SET d = 1;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
    SET d = 1;
 OPEN Get_cur;
lbl: 
    LOOP
        IF d = 1 THEN
            LEAVE lbl;
        END IF;
        IF NOT d = 1 THEN
            FETCH Get_cur INTO c_id, c_name, c_address;
            INSERT INTO Vbackupdata VALUES (c_id, c_name, c_address);
        END IF;
    END LOOP;
 CLOSE Get_cur;
END;
//
DELIMITER ;

Now, test the output by typing the following query

CALL cursors_db.firstCurs();

Now, check the result by typing the following query

SELECT*FROM Vbackupdata;

cursorinsql

Conclusion

In this article, I have discussed the concept of MySQL Cursors with various examples. I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL. Thanks for reading this article!