What is a Cursor in MySQL?

What is a Cursor in MySQL?

A MySQL cursor is an object that is used to process data returned by an SQL query, one row at a time. It provides you with a way to iterate over the result set and perform operations on each row individually. Cursors are commonly used in stored procedures, triggers, and other database objects where you might need to process data on a row-by-row basis.

The cursors that are provided with MySQL are embedded cursors and have the following properties:

• Read-Only: Using cursors, you can iterate over a result set row by row but you cannot make changes to the table or update any table.
• Non-Scrollable: Cursors in MySQL allow you to iterate and retrieve data from the result set in only one direction, that is from top to bottom. You cannot go up and down as you want while using cursors in MySQL.
• A sensitive: One of the main features of cursors in MySQL is that they are not affected by any changes that are made to the original table, which means any modifications that are done to the original table will not be reflected in the cursor. So for example, if you create a cursor that holds all the records of a table and then you go ahead and add some more records to your table, then these new records will not be reflected in the cursor you previously created.

You will need to follow these steps to use a cursor in MySQL.

• First thing that you'll have to do is to declare a cursor using the DECLARE statement which defines the SELECT statement
• Next step is to open the cursor using the OPEN statement.
• Once you have opened a cursor in MySQL, you can start fetching the data you want using the FETCH statement
• Finally, if you want to close your cursor, you can use the CLOSE statement.

Let us understand each of these statements in detail and see how each of them works.

Declare Cursor

DECLARE statement is used to declare a cursor in MySQL and define a SELECT statement that will be used to fetch or retrieve data.

Syntax

The DECLARE statement for a cursor in MySQL has the following syntax:

Parameters

cursor_name

This parameter specifies the name of the cursor that we are declaring. The name should be unique within the scope of the database object where the cursor is declared.

select_statement

This parameter specifies the SELECT statement that the cursor will use to fetch data. The SELECT statement should return a result set that the cursor can iterate over row by row.

Open Cursor

The OPEN statement is used to open a cursor in MySQL. This will run the SELECT statement that was specified in the previous step and will prepare the cursor to fetch the first row of data.

Syntax

The OPEN statement to open a cursor in MySQL has the following syntax:

Parameters

cursor_name

This parameter specifies the name of the cursor that we want to be opened.

Fetch Cursor

After opening a cursor, you can fetch data using the FETCH statement and it will start retrieving `the next rows of data one by one.

Syntax

The FETCH statement to fetch data from a cursor in MySQL has the following syntax:

Parameters

cursor_name

This parameter specifies the name of the cursor that will be used to fetch data.

variable_list

This parameter specifies a list of variables that will receive the values from the current row of data fetched by the cursor.

Close Cursor

The CLOSE statement is used to close a cursor in MySQL. It will release any resources that were held up by the cursor. This is why it is a good practice to always close a cursor when it is no longer used.

Syntax

The CLOSE statement used to close a cursor in MySQL has the following syntax:

Parameters

cursor_name

This parameter specifies the name of the cursor that will be closed.

Example For the Cursor

We have seen what a cursor in MySQL is and how we can use it theoretically. But now it's time to look at an example to understand the concept in detail.

In MySQL, you can use cursors to process large amounts of data in a controlled and efficient manner. In this example, we will demonstrate how to use a cursor to iterate over the rows of a table and perform a simple operation on each row.

Open the Database and Table

Let's assume that we have a database called example_db and a table called example_table. This table has three columns: id, name, and age. Our goal is to iterate over the rows of this table and calculate the average age of the people in the table.

First, we need to open the database and table that we will be working with. This can be done using the following SQL commands:

Create the Cursor

Next, we need to create a cursor that will allow us to iterate over the rows of the table. The cursor is defined using a SELECT statement, which specifies the columns that we want to retrieve from the table. In this case, we want to retrieve the age column:

Once we have defined the cursor, we can open it to start iterating over the rows of the table. This is done using the OPEN statement:

Next, we need to define some variables that will hold the values of the columns that we are retrieving from the table. In this case, we only need to retrieve the age column, so we define a variable called cur_age:

Call the cursor

We can now use a loop to iterate over the rows of the table. Inside the loop, we use the FETCH statement to retrieve the next row of the result set and store the value of the age column in the cur_age variable:

Let us declare another variable by the name of total_age and assign it a default value of 0. This variable will help us track the sum of all the ages so far.

We then update the variable total_age to keep track of the total age of all the people in the table:

We also need a variable to keep track of the number of rows that we have processed so far or you can say the number of records that we have processed so far. This variable will be used to calculate the average of all ages in the end. So, let us declare a variable by the name num_rows and assign it a default value of 0.

Finally, we increment the variable num_rows to keep track of the number of rows in the table:

Once we have gone through all the records, we have the sum of the ages of all the people in our table, stored in the variable total_age and the number of rows stored in the variable num_rows. So, now we can calculate the average age of all the people in our table by dividing total_age by num_rows.

But before that, we need to declare another variable to store the average age. Let us declare a variable by the name of avg_age:

Now, let us calculate the average age.

Once we have processed all of the rows in the table, we can close the cursor using the CLOSE statement:

In this example, we have demonstrated how to use a cursor in MySQL to iterate over the rows of a table and perform a simple operation on each row. Cursors are a powerful tool that can be used to process large amounts of data in a controlled and efficient manner, making them an essential tool for any database developer.