DBMS_15.sql

MySQL Cursor

In MySQL stored programs (procedures, functions, triggers, events), cursors are used to retrieve query results row by row, instead of all at once.

Theyre mainly read-only, forward-only, non-scrollable cursors. Let’s break down the cursor attributes you mentioned:

/*
you use cursors within stored procedures, 
triggers, and functions where you need 
to process individual rows returned 
by a query one at a time.

This is different from a standard SQL query, 
which operates on the entire set of rows at once (a "set-based" operation). Cursors are necessary for tasks 
that require row-by-row processing, such as:

Applying complex logic to each row that can't be handled by a single SQL statement.
Performing calculations or updates based on the data in the current row and previous rows.
Iterating through a result set and inserting the data into another table, one record at a time.


Use a cursor only when your task requires a level of logic that must be applied to one row at a time. Examples include:

Calling a separate stored procedure or function for each row.
Performing calculations that rely on the previous row's value.
Migrating data where each row requires different, complex validation or transformation logic.

*/

-- declare a cursor
DECLARE cursor_name CURSOR FOR 
SELECT column1, column2 
FROM your_table 
WHERE your_condition;

-- open the cursor
OPEN cursor_name;

FETCH cursor_name INTO variable1, variable2;
-- process the data

-- close the cursor
CLOSE cursor_name;


To declare a NOT FOUND handler, you use the following syntax:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;