MS SQL Server

MySQL Cursor Examples

In MySQL, the SELECT query returns a list of matching records after the execution called the result set. The cursor is a useful feature of MySQL to traverse each record separately from the result set. Sometimes it requires performing additional processing for all of the specific records of the result set before returning the query output. This can be done easily by using the cursor. The features, advantages, disadvantages, and uses of the MySQL cursor have been described in this tutorial.

Features of MySQL cursor:

  1. MySQL cursor is asensitive for the MySQL version 8+. It means that the database server may or may not make a copy of the result set.
  2. MySQL cursor is read-only. So it can’t be used for updating the record.
  3. MySQL cursor is not scrollable. It means that it can move forward only and cannot skip any record.

Steps to using a cursor:

The major steps of using the cursor in MySQL to read each result set record are shown below.

1. Declare a cursor:

DECLARE cursor_name CURSOR FOR select_statement;

The cursor variable requires to define for iterating the result set returned by the SELECT statement.

2. Open the cursor

OPEN cursor_name;

The cursor needs to open to point to the starting point of the result set.

3. Fetch the cursor

FETCH  cursor_name INTO variable_list;

The FETCH command is used to read the fields defined in the SELECT query that has been used at the time of declaring the cursor.

4. Close the cursor

CLOSE cursor_name;

The cursor is required to close to reset the result set and remove the reference to the result set.

Pre-requisites:

You have to create the necessary tables with data to know the use of a cursor in MySQL. Open the terminal and run the following command to connect with the MySQL server if there is no password for the root user.

$ sudo mysql -u root

Run the following command to select the database.

USE test_db;

Run the following command to create a database named test_db.

CREATE DATABASE test_db;

Run the following query to create a table named sales_persons with four fields.

CREATE TABLE sales_persons(

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL,

email VARCHAR(50),

contact_no VARCHAR(30));

Run the following query to insert three records into the sales_person table.

INSERT INTO `sales_persons` (`id`, `name`, `email`, `contact_no`) VALUES (NULL, 'Kamal Hasan', '[email protected]', '0191275634'),

(NULL, 'Nila Hossain', '[email protected]', '01855342357'),

(NULL, 'Abir Hossain', '[email protected]', '01634235698');

Run the following query to create a table named sales with four fields that contains a foreign key that will create the one-to-many relationship from the sales_persons table to the sales table.

CREATE TABLE sales(

id INT NOT NULL PRIMARY KEY,

sales_date DATE NOT NULL,

amount INT,

sp_id int,

CONSTRAINT fk_sp FOREIGN KEY (sp_id)

REFERENCES sales_persons(id)

ON DELETE CASCADE ON UPDATE CASCADE);

Run the following query to insert four records into the sales table.

INSERT INTO `sales` (`id`, `sales_date`, `amount`, `sp_id`) VALUES

('90', '2021-11-09', '800000', '1'),

('34', '2020-12-15', '5634555', '3'),

('67', '2021-12-23', '900000', '1'),

('56', '2020-12-31', '6700000', '1');

Use of MySQL Cursor:

The cursor is mainly used in MySQL procedure or function that contains one or more select query, and the declared cursor iterates each record of the result set returned by the query. Create a MySQL function named Calculate_Bonus with the following statement that will take an integer argument and return a float value. The SELECT query that has been used in the function will calculate the total sales amount of each salesperson and return the name of the salesperson and total sales in the result set. The function of the cursor is to calculate 15% of the total sales as a bonus if the total sales amount is more than 10000; otherwise, the bonus will be 10% of the total sales. The function will return the bonus amount based on the id value of the salesperson. Run the following statement from the MySQL prompt after selecting the database to create the function.

DELIMITER //

CREATE FUNCTION `Calculate_Bonus`(`spid` INT)

RETURNS VARCHAR(200)

READS SQL DATA

DETERMINISTIC

BEGIN

DECLARE done INT DEFAULT FALSE;

DECLARE total INT DEFAULT 0;

DECLARE person VARCHAR(50) DEFAULT "";

DECLARE bonus INT DEFAULT 0;

DECLARE output VARCHAR(200);

DECLARE cur CURSOR FOR

SELECT name, SUM(amount)

FROM sales, sales_persons

WHERE sales_persons.id=sales.sp_id and sp_id = spid

GROUP By sp_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

FETCH cur INTO person, total;

IF total > 10000 THEN

SET bonus = total*0.15;

ELSE

SET bonus = total*0.10;

END IF;

SET output = CONCAT("The bonus of ", person," is TK.", bonus, ".");

CLOSE cur;

RETURN output;

END;

//

Run the following query to read all records of the sales_persons table.

SELECT * FROM sales_persons;

Run the following query to read all records of the sales table.

SELECT * FROM sales;

Run the following query to call the Calculate_Bonus() function of MySQL with the argument value, 1. According to the sales table, there are three records for the salesperson whose id is 1. So, the bonus will be 15% off (6700000+900000+800000).

SELECT Calculate_Bonus (1);

Run the following query to call the Calculate_Bonus() function of MySQL with the argument value, 2. According to the sales table, there is no record for the salesperson whose id is 2. So, the bonus will be 0.

SELECT Calculate_Bonus (2);

Run the following query to call the Calculate_Bonus() function of MySQL with the argument value, 3. According to the sales table, there is one record for the salesperson whose id is 3. So, the bonus will be 15% of 5634555.

SELECT Calculate_Bonus (3);

Conclusion:

MySQL cursor makes the specific task easier by iterating each table row. MySQL cursor has been shown in this tutorial by using a MySQL function. The MySQL cursor can be used within the MySQL procedure also. I hope the MySQL cursor’s concept will be cleared for the user, and the user will be able to use the cursor after reading this tutorial.

About the author

Fahmida Yesmin

I am a trainer of web programming courses. I like to write article or tutorial on various IT topics. I have a YouTube channel where many types of tutorials based on Ubuntu, Windows, Word, Excel, WordPress, Magento, Laravel etc. are published: Tutorials4u Help.