How to Use the CURSOR in MySQL

08/06/2021

Contents

In this article, you will learn how to use the CURSOR in MySQL.

Using the CURSOR in MySQL

In MySQL, a cursor is a database object that allows you to traverse the results of a SELECT statement one row at a time. A cursor is typically used in stored procedures, functions, and triggers to perform complex database operations.

Syntax

The basic syntax of a cursor in MySQL is as follows:

DECLARE cursor_name CURSOR FOR select_statement;

The cursor_name is a user-defined identifier that represents the cursor, and the select_statement is a SELECT statement that retrieves the rows to be processed by the cursor. Once a cursor is declared, you can open it, fetch rows, and close it.

Examples

Using a cursor to traverse a result set

In this example, we will use a cursor to traverse a result set and print each row.

DECLARE cursor_name CURSOR FOR 
    SELECT column1, column2 FROM table_name;

OPEN cursor_name;

FETCH cursor_name INTO var1, var2;

WHILE @@FETCH_STATUS = 0 DO
    SELECT var1, var2;
    FETCH cursor_name INTO var1, var2;
END WHILE;

CLOSE cursor_name;

Output:

value1 value2
value3 value4
value5 value6
...

The DECLARE statement declares a cursor named cursor_name that retrieves the values of column1 and column2 from table_name. The OPEN statement opens the cursor, and the FETCH statement retrieves the first row and assigns the values to variables var1 and var2. The WHILE loop continues fetching rows and processing them until there are no more rows to fetch. The CLOSE statement closes the cursor.

Using a cursor to update rows

In this example, we will use a cursor to update rows in a table.

DECLARE cursor_name CURSOR FOR 
    SELECT column1, column2 FROM table_name;

OPEN cursor_name;

FETCH cursor_name INTO var1, var2;

WHILE @@FETCH_STATUS = 0 DO
    UPDATE table_name SET column1 = var1 * 2 WHERE column2 = var2;
    FETCH cursor_name INTO var1, var2;
END WHILE;

CLOSE cursor_name;

This example is similar to the previous one, but instead of printing the rows, we use the UPDATE statement to modify the value of column1 based on the value of column2. The cursor traverses the result set, and for each row, it multiplies the value of var1 by 2 and updates the corresponding row in the table.

Using a cursor to delete rows

In this example, we will use a cursor to delete rows from a table.

DECLARE cursor_name CURSOR FOR 
    SELECT column1, column2 FROM table_name;

OPEN cursor_name;

FETCH cursor_name INTO var1, var2;

WHILE @@FETCH_STATUS = 0 DO
    DELETE FROM table_name WHERE column1 = var1 AND column2 = var2;
    FETCH cursor_name INTO var1, var2;
END WHILE;

CLOSE cursor_name;

This example demonstrates how to use a cursor to delete rows from a table. The cursor retrieves the values of column1 and column2 from the table, and for each row, it deletes the corresponding row from the table.

#MySQL