Cursor in MySQL

INTRODUCTION

 
In this tutorial, I am going to explain about MySQL Cursors with examples. This article covers the following topics:
  1. Introduction to the Cursors
  2. Working with MySQL Cursors
  3. MySQL Cursor Life Cycle
  4. MySQL Cursors Example
  5. Conclusion

CURSORS

 
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 in the 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 asensitive cursor is used to points the actual data, whereas a temporary copy of the data is used by an insensitive cursor used. An asensitive 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 the 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 its necessary to give it a name that will in some way tell us what its 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 variable 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 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 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.

mysql1
 
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 the all records from a table, i.e., “GetVatsaCursor”. And after we open the cursor we fetch the record one by one from 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.
 
Create a database
 
CREATE DATABASE Cursors_db;
 
Create a few tables
  1. USE Cursors_db;  
  2.   
  3. CREATE TABLE GetVatsaCursor(  
  4.   C_ID INT PRIMARY KEY AUTO_INCREMENT,  
  5.   c_name VARCHAR(50),  
  6.   c_address VARCHAR(200)  
  7. );  
  8.   
  9. CREATE TABLE Vbackupdata(  
  10.   C_ID INT,  
  11.   c_name VARCHAR(50),  
  12.   c_address VARCHAR(200)  
  13. ); 
Now, insert some dummy data into it.
  1. INSERT INTO GetVatsaCursor(c_name, c_address) VALUES('Test''132, Vatsa Colony'),  
  2.   ('Admin''133, Vatsa Colony'),  
  3.   ('Vatsa''134, Vatsa Colony'),  
  4.   ('Onkar''135, Vatsa Colony'),  
  5.   ('Rohit''136, Vatsa Colony'),  
  6.   ('Simran''137, Vatsa Colony'),  
  7.   ('Jashmin''138, Vatsa Colony'),  
  8.   ('Anamika''139, Vatsa Colony'),  
  9.   ('Radhika''140, Vatsa Colony'); 
Now, by using the SELECT query, see the data inside both the tables.
  1. SELECT * FROM GetVatsaCursor;  
  2. SELECT * FROM Vbackupdata; 
Cursor Example
  1. delimiter //  
  2. CREATE PROCEDURE firstCurs()  
  3. BEGIN  
  4. DECLARE d INT DEFAULT 0;  
  5. DECLARE c_id INT;  
  6. DECLARE c_name, c_address VARCHAR(20);  
  7.   
  8. DECLARE Get_cur CURSOR FOR SELECT * FROM GetVatsaCursor;  
  9. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'  
  10. SET d = 1;  
  11. DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'  
  12. SET d = 1;  
  13.   
  14. OPEN Get_cur;  
  15. lbl: LOOP  
  16. IF d = 1 THEN  
  17. LEAVE lbl;  
  18. END IF;  
  19. IF NOT d = 1 THEN  
  20.   
  21. FETCH Get_cur INTO c_id, c_name, c_address;  
  22. INSERT INTO Vbackupdata VALUES(c_id, c_name, c_address);  
  23. END IF;  
  24. END LOOP;  
  25.   
  26. CLOSE Get_cur;  
  27. END;  
  28. // 
Now, test the output by typing the following query.
  1. CALL cursors_db.firstCurs();  
Now, check the result by typing the foolowing query.
  1. SELECT * FROM Vbackupdata; 
 

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!