Working with Cursors: Declare, Open, Fetch, Close0%

Stored Functions vs Stored Procedures Architecture

Working with Cursors: Declare, Open, Fetch, Close

Advanced14 min read•Updated: 2026-09-12

Working with Cursors: Declare, Open, Fetch, Close

SQL is inherently a set-based language. However, certain complex algorithms (such as iteratively calculating interest compounded across variable daily brackets or calling external webhooks per customer) require row-by-row iteration.

In MySQL stored programs, row-by-row iteration is accomplished using a Cursor.

The 4-Stage Lifecycle of a Cursor

1
DECLARE: Declares the cursor and associates it with a specific SELECT query.
2
OPEN: Initializes the cursor and executes the query to materialize the result set.
3
FETCH: Retrieves the current row into local variables and advances the internal pointer to the next row.
4
CLOSE: Releases the cursor and frees its allocated memory.

The Standard Pattern: Using a NOT FOUND Handler

Because a cursor does not know how many rows exist in advance, you must declare a CONTINUE HANDLER FOR NOT FOUND to signal when the cursor reaches the end of data:

1DELIMITER //
2 
3CREATE PROCEDURE ProcessAnnualBonuses()
4BEGIN
5    -- 1. Declare loop control variables
6    DECLARE done INT DEFAULT FALSE;
7    DECLARE v_emp_id INT;
8    DECLARE v_salary DECIMAL(10, 2);
9    DECLARE v_bonus DECIMAL(10, 2);
10 
11    -- 2. Declare Cursor (must come after variables!)
12    DECLARE emp_cursor CURSOR FOR 
13        SELECT emp_id, salary 
14        FROM employees 
15        WHERE is_active = 1;
16 
17    -- 3. Declare NOT FOUND Handler (must come after cursor!)
18    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
19 
20    -- 4. Open Cursor
21    OPEN emp_cursor;
22 
23    -- 5. Loop through rows
24    read_loop: LOOP
25        -- Fetch next row
26        FETCH emp_cursor INTO v_emp_id, v_salary;
27 
28        -- Exit loop if no more rows
29        IF done THEN
30            LEAVE read_loop;
31        END IF;
32 
33        -- Business Logic per row:
34        SET v_bonus = v_salary * 0.10;
35        
36        INSERT INTO bonus_payouts (emp_id, bonus_amount, payout_date)
37        VALUES (v_emp_id, v_bonus, CURRENT_DATE());
38    END LOOP;
39 
40    -- 6. Close Cursor
41    CLOSE emp_cursor;
42END //
43 
44DELIMITER ;

Cursor Characteristics in MySQL

MySQL cursors possess three immutable characteristics:

1
Asensitive: The server may or may not make a copy of the result table.
2
Read-Only: You cannot update rows directly through the cursor pointer (no UPDATE ... WHERE CURRENT OF).
3
Non-Scrollable: A cursor can only move forward, one row at a time. It cannot move backward or jump to an arbitrary index.

Performance Warning: When NOT to Use Cursors!

Cursors are notoriously slow because they perform row-by-row iteration in memory, disabling MySQL's multi-threaded set-based query optimizations. Always prefer set-based SQL whenever possible: ```sql -- FAST Set-Based Alternative (Runs 100x faster than a cursor!): INSERT INTO bonus_payouts (emp_id, bonus_amount, payout_date) SELECT emp_id, salary * 0.10, CURRENT_DATE() FROM employees WHERE is_active = 1; ``` Reserve cursors exclusively for logic that cannot be expressed via standard SQL joins and aggregates.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What are the four mandatory lifecycle steps of a MySQL cursor in sequential order?

Question 2

How is the termination of a cursor loop typically handled in MySQL?

Question 3

Which of the following is a fundamental characteristic of MySQL cursors?

Question 4

In what order must elements be declared inside a BEGIN...END block containing cursors?

Question 5

Why should set-based SQL (INSERT INTO ... SELECT) be preferred over cursors whenever possible?

Next Lesson

Stored Functions vs Stored Procedures Architecture

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Deterministic vs Non-Deterministic Functions	Stored Functions vs Stored Procedures Architecture

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Stored Functions vs Stored Procedures Architecture

Working with Cursors: Declare, Open, Fetch, Close

Advanced14 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Working with Cursors: Declare, Open, Fetch, Close

In MySQL stored programs, row-by-row iteration is accomplished using a Cursor.

The 4-Stage Lifecycle of a Cursor

1
DECLARE: Declares the cursor and associates it with a specific SELECT query.
2
OPEN: Initializes the cursor and executes the query to materialize the result set.
3
FETCH: Retrieves the current row into local variables and advances the internal pointer to the next row.
4
CLOSE: Releases the cursor and frees its allocated memory.

The Standard Pattern: Using a NOT FOUND Handler

Because a cursor does not know how many rows exist in advance, you must declare a CONTINUE HANDLER FOR NOT FOUND to signal when the cursor reaches the end of data:

1DELIMITER //
2 
3CREATE PROCEDURE ProcessAnnualBonuses()
4BEGIN
5    -- 1. Declare loop control variables
6    DECLARE done INT DEFAULT FALSE;
7    DECLARE v_emp_id INT;
8    DECLARE v_salary DECIMAL(10, 2);
9    DECLARE v_bonus DECIMAL(10, 2);
10 
11    -- 2. Declare Cursor (must come after variables!)
12    DECLARE emp_cursor CURSOR FOR 
13        SELECT emp_id, salary 
14        FROM employees 
15        WHERE is_active = 1;
16 
17    -- 3. Declare NOT FOUND Handler (must come after cursor!)
18    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
19 
20    -- 4. Open Cursor
21    OPEN emp_cursor;
22 
23    -- 5. Loop through rows
24    read_loop: LOOP
25        -- Fetch next row
26        FETCH emp_cursor INTO v_emp_id, v_salary;
27 
28        -- Exit loop if no more rows
29        IF done THEN
30            LEAVE read_loop;
31        END IF;
32 
33        -- Business Logic per row:
34        SET v_bonus = v_salary * 0.10;
35        
36        INSERT INTO bonus_payouts (emp_id, bonus_amount, payout_date)
37        VALUES (v_emp_id, v_bonus, CURRENT_DATE());
38    END LOOP;
39 
40    -- 6. Close Cursor
41    CLOSE emp_cursor;
42END //
43 
44DELIMITER ;

Cursor Characteristics in MySQL

MySQL cursors possess three immutable characteristics:

1
Asensitive: The server may or may not make a copy of the result table.
2
Read-Only: You cannot update rows directly through the cursor pointer (no UPDATE ... WHERE CURRENT OF).
3
Non-Scrollable: A cursor can only move forward, one row at a time. It cannot move backward or jump to an arbitrary index.

Performance Warning: When NOT to Use Cursors!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What are the four mandatory lifecycle steps of a MySQL cursor in sequential order?

Question 2

How is the termination of a cursor loop typically handled in MySQL?

Question 3

Which of the following is a fundamental characteristic of MySQL cursors?

Question 4

In what order must elements be declared inside a BEGIN...END block containing cursors?

Question 5

Why should set-based SQL (INSERT INTO ... SELECT) be preferred over cursors whenever possible?

Next Lesson

Stored Functions vs Stored Procedures Architecture

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Deterministic vs Non-Deterministic Functions	Stored Functions vs Stored Procedures Architecture

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next