Features of MySQL cursor:
- 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.
- MySQL cursor is read-only. So it can’t be used for updating the record.
- 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:
The cursor variable requires to define for iterating the result set returned by the SELECT statement.
2. Open the cursor
The cursor needs to open to point to the starting point of the result set.
3. Fetch the cursor
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
The cursor is required to close to reset the result set and remove the reference to the result set.
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.
Run the following command to select the database.
Run the following command to create a database named test_db.
Run the following query to create a table named sales_persons with four fields.
Run the following query to insert three records into the sales_person table.
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.
Run the following query to insert four records into the sales table.
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.
CREATE FUNCTION `Calculate_Bonus`(`spid` INT)
READS SQL DATA
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;
FETCH cur INTO person, total;
IF total > 10000 THEN
SET bonus = total*0.15;
SET bonus = total*0.10;
SET output = CONCAT("The bonus of ", person," is TK.", bonus, ".");
Run the following query to read all records of the sales_persons table.
Run the following query to read all records of the sales table.
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).
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.
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.
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.