Cursors in MySQL

 

IMPLEMENTATION OF CURSORS 

IN MYSQL

---

1. AIM

To understand the concept of cursors in MySQL and implement them inside stored procedures with examples.

---

2. THEORY

✔ What is a Cursor?

A cursor in MySQL is a database object used to retrieve and process query results row-by-row.

Normally, SQL works in a set-oriented way (all rows at once).
But sometimes you need record-by-record processing, such as:

• Calculating values per row

• Generating summaries

• Performing row-by-row validations

• Creating logs for each row

This is where cursors are useful.

---

3. CURSOR COMPONENTS

To use a cursor in MySQL, you need:

✔ 1. Declare variables

Used to store individual column values from each row.

✔ 2. Declare a cursor

Specifies the SELECT query whose rows you want to process.

DECLARE cursor_name CURSOR FOR SELECT query;

✔ 3. Declare a CONTINUE HANDLER

This handles the situation when the cursor reaches the end.

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

✔ 4. Open the cursor

OPEN cursor_name;

✔ 5. Fetch rows inside LOOP

FETCH cursor_name INTO var1, var2;

✔ 6. Close the cursor

CLOSE cursor_name;

---

4. GENERAL STRUCTURE OF A CURSOR

DECLARE finished INT DEFAULT 0;
DECLARE var1, var2 datatype;

DECLARE my_cursor CURSOR FOR
    SELECT column1, column2 FROM table;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

OPEN my_cursor;

read_loop: LOOP
    FETCH my_cursor INTO var1, var2;

    IF finished = 1 THEN
        LEAVE read_loop;
    END IF;

    -- Process row here

END LOOP;

CLOSE my_cursor;

---

5. LAB EXAMPLES

---

Example 1: Cursor to Display Employee Names One by One

Create table:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2)
);

INSERT INTO employees VALUES
(1,'Alice',50000),
(2,'Bob',45000),
(3,'Charlie',60000);

---

Create procedure using cursor:

DELIMITER $$

CREATE PROCEDURE show_employee_names()
BEGIN
    DECLARE finished INT DEFAULT 0;
    DECLARE emp_name VARCHAR(100);

    DECLARE emp_cursor CURSOR FOR
        SELECT name FROM employees;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN emp_cursor;

    read_loop: LOOP
        FETCH emp_cursor INTO emp_name;

        IF finished = 1 THEN 
            LEAVE read_loop;
        END IF;

        SELECT emp_name AS employee_name;
    END LOOP;

    CLOSE emp_cursor;
END$$

DELIMITER ;

Execute:

CALL show_employee_names();

---

Example 2: Cursor to Calculate Bonus for Each Employee

Bonus = 10% of salary

Add to log table.

---

Create log table:

CREATE TABLE bonus_log (
    emp_id INT,
    name VARCHAR(100),
    bonus DECIMAL(10,2)
);

---

Procedure using cursor:

DELIMITER $$

CREATE PROCEDURE calculate_bonus()
BEGIN
    DECLARE finished INT DEFAULT 0;
    DECLARE eid INT;
    DECLARE ename VARCHAR(100);
    DECLARE esalary DECIMAL(10,2);
    DECLARE bonus DECIMAL(10,2);

    DECLARE emp_cursor CURSOR FOR
        SELECT id, name, salary FROM employees;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN emp_cursor;

    read_loop: LOOP
        FETCH emp_cursor INTO eid, ename, esalary;

        IF finished = 1 THEN 
            LEAVE read_loop;
        END IF;

        SET bonus = esalary * 0.10;

        INSERT INTO bonus_log VALUES (eid, ename, bonus);

    END LOOP;

    CLOSE emp_cursor;
END$$

DELIMITER ;

Execute:

CALL calculate_bonus();
SELECT * FROM bonus_log;

---

Example 3: Cursor to Update Salary for Each Employee

Increase salary by 5%.

DELIMITER $$

CREATE PROCEDURE update_salary_cursor()
BEGIN
    DECLARE finished INT DEFAULT 0;
    DECLARE eid INT;
    DECLARE esalary DECIMAL(10,2);

    DECLARE emp_cursor CURSOR FOR
        SELECT id, salary FROM employees;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN emp_cursor;

    read_loop: LOOP
        FETCH emp_cursor INTO eid, esalary;

        IF finished = 1 THEN 
            LEAVE read_loop;
        END IF;

        UPDATE employees
        SET salary = esalary * 1.05
        WHERE id = eid;

    END LOOP;

    CLOSE emp_cursor;
END$$

DELIMITER ;

Execute:

CALL update_salary_cursor();
SELECT * FROM employees;

---

⭐ Example 4: Cursor with IF Condition

Show only employees earning more than 50,000.

DELIMITER $$

CREATE PROCEDURE high_salary()
BEGIN
    DECLARE finished INT DEFAULT 0;
    DECLARE eid INT;
    DECLARE ename VARCHAR(100);
    DECLARE esalary DECIMAL(10,2);

    DECLARE emp_cursor CURSOR FOR
        SELECT id, name, salary FROM employees;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN emp_cursor;

    read_loop: LOOP
        FETCH emp_cursor INTO eid, ename, esalary;

        IF finished = 1 THEN 
            LEAVE read_loop;
        END IF;

        IF esalary > 50000 THEN
            SELECT ename AS High_Salary_Employee, esalary;
        END IF;

    END LOOP;

    CLOSE emp_cursor;
END$$

DELIMITER ;

---

6. IMPORTANT POINTS ABOUT CURSORS

Feature	Value
Row-by-row processing	✔
Used only inside stored procedures	✔
Needs OPEN, FETCH, CLOSE	✔
Requires NOT FOUND handler	✔
Slow for large records	✔ because row-by-row

---

7. LAB PRACTICE QUESTIONS

1. Write a cursor to display all student names one by one.

2. Create a cursor to calculate total marks of every student and insert into a result table.

3. Use a cursor to apply a 5% discount to all products.

4. Create a cursor to copy only customers from a specific city into another table.

5. Write a cursor to find employees with salary less than average salary.

---

8. RESULT

Cursors were successfully created, executed, and tested in MySQL using stored procedures.