Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is MySQL Cursor?
3.
How to use MySQL Cursor?
4.
Example
4.1.
Creating a sample employees table.
4.2.
Creating a stored procedure using a cursor
4.3.
Declaring the Cursor
4.4.
Declaring Handler 
4.5.
Opening the Cursor
4.6.
Fetching Rows from the Cursor
4.7.
Exit Condition
4.8.
Processing the row
4.9.
Close the cursor
4.10.
Calling the stored procedure to process the employees
4.11.
Retrieve the updated records from the table
5.
Frequently Asked Questions
5.1.
What is MySQL?
5.2.
Why is MySQL used?
5.3.
What is MySQL cursor?
5.4.
Why is MySQL cursor used?
6.
Conclusion
Last Updated: Mar 27, 2024
Easy

MySQL Cursor

Author Aditya Gupta
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

MySQL is a relational database management system in which data is stored structurally in rows and columns. You might have faced problems with writing SQL statements again and again. MySQL cursor helps us to store the variables in a stored procedure so that the SQL code can be used again.

MySQL Cursor

In this blog, we will discuss about MySQL cursor, its usage, and steps to use MySQL cursor, along with examples.

What is MySQL Cursor?

A cursor in MySQL is an iterator used to store the variables in a stored procedure. It helps us to iterate through a result given by a query. Results are in the form of a set of rows, and MySQL cursor help to process each row one at a time.
 

Let us look at some of the characteristics of MySQL cursor to understand it in a better way.

  • MySQL cursor cannot be used to update the data in the table.
     
  • As the cursor is an iterator, it moves only forward direction in the order of records.
     
  • The cursor creates a copy of the data we are working with, making them asensitive.
     

MySQL cursor is used when we need to do some operations on each row as it processes each row individually. Cursors are also used to display a large result set in the form of chunks or pages.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

How to use MySQL Cursor?

Let us look at all the steps below to use the cursor in MySQL.

Step 1: Firstly, we declare a cursor by using the DECLARE statement.

DECLARE cursor_name CURSOR FOR
SELECT statement;


We must note that before declaring the cursor, we should declare a variable to avoid any errors. Also, it is always necessary to use the SELECT statement with the cursor.

Step 2: Now, we will open the cursor using the OPEN statement.

OPEN cursor_name;


OPEN statement initialises the cursor, which is why it is necessary to OPEN the cursor before fetching the data from the result set.
Step 3: After opening the cursor, we will fetch the row or column into a variable list.

FETCH cursor_name INTO variables list;


The FETCH statement is used to get the next row to which the cursor will point.

Step 4: As we have stored the data in a variable list. Now, we will close the cursor by using the CLOSE statement. It helps us to release the memory associated with the cursor.

CLOSE cursor_name;


Note: While working with MySQL cursor, we should also use the NOT FOUND condition to handle the condition when the next row is not present.

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;


In the above statement, The finished keyword is used to define the condition that we have reached the end of the result set, and we should not continue after this.

Example

We will take an example by using a sample dataset to explain how cursors are used in MySQL. Let us consider an employees table containing information about an employee and the salary of the employee. We will use MySQL cursor to fetch the records from the table and try to increase the salary of the employee.

Creating a sample employees table.

-- Creating a sample table
CREATE TABLE employees (
	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255),
	department VARCHAR(255),
	salary DECIMAL(10, 2)
);

-- Inserting some sample data
INSERT INTO employees (name, department, salary) VALUES
	('Rahul Sharma', 'Sales', 50000.00),
	('Priya Patel', 'HR', 60000.00),
	('Amit Gupta', 'IT', 70000.00),
	('Sneha Verma', 'Finance', 55000.00);


The employee table would look like this.

Table

Creating a stored procedure using a cursor

DELIMITER //
CREATE PROCEDURE process_employees()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE employee_id INT;
DECLARE employee_name VARCHAR(255);
DECLARE employee_department VARCHAR(255);
DECLARE employee_salary DECIMAL(10, 2);

Declaring the Cursor

DECLARE cur CURSOR FOR SELECT id, name, department, salary FROM employees;

Declaring Handler 

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

Opening the Cursor

OPEN cur;

Fetching Rows from the Cursor

read_loop: LOOP
-- Fetch the next row
FETCH cur INTO employee_id, employee_name, employee_department, employee_salary;

Exit Condition

IF done THEN
LEAVE read_loop;
END IF;

Processing the row

SET employee_salary = employee_salary * 1.1;
-- Updating the salary in the employees table
UPDATE employees SET salary = employee_salary WHERE id = employee_id;
END LOOP;

Close the cursor

CLOSE cur;
END //
DELIMITER ;

Calling the stored procedure to process the employees

CALL process_employees();

Retrieve the updated records from the table

SELECT * FROM employees;


The updated table would look something like this.

Updated Table

Explanation

In this example, firstly, we created employees database. We declared variable names such as done, employee_id, employee_name, employee_department, and employee_salary. Then we created a stored procedure named process_employees() that uses a cursor to fetch the records from the employees table. 

Updated the exit condition so that the cursor would stop after there are no records available. After fetching the records, we updated the salary of the employees and closed the cursor.

Frequently Asked Questions

What is MySQL?

MySQL is a relational database management system in which data is stored structurally in tables that consist of rows and columns. It uses structured query language to access, delete or manipulate data.

Why is MySQL used?

The reasons for using MySQL database are its high availability and security. It is easy to use, fast and reliable.

What is MySQL cursor?

A cursor in MySQL is an iterator used to store the variables in a stored procedure. It helps us to iterate through a result given by a query.

Why is MySQL cursor used?

MySQL cursor is used when we need to do some operations on each row as it processes each row individually, and they are also used to display a large result set in the form of chunks or pages.

Conclusion

In this article, we discussed MySQL Cursor. We learnt about MySQL cursor, its usage and steps to use MySQL cursor, along with an example. Alright! So now that you have learnt about MySQL Cursor, you can also refer to other similar articles. 

You may refer to our Guided Path on Code Studios for enhancing your skill set on DSACompetitive ProgrammingSystem Design, etc. Check out essential interview questions, practice our available mock tests, look at the interview bundle for interview preparations, and so much more!

Happy Learning, Ninja!

Previous article
Second Highest Salary in SQL
Next article
What is a Delimiter In MySQL
Live masterclass