MySQL Cursor

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.

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.

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. 

• MySQL Introduction
• MySQL Connection
• MySQL: Case Statement
• Update Query in MySQL
   

You may refer to our Guided Path on Code Studios for enhancing your skill set on DSA, Competitive Programming, System 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!