Cursor in MySQL

Introduction to Cursor in MySQL

In this article, you will learn about MySQL cursor in stored procedures which controls a set of rows. A cursor is responsible to repeat a set of rows returned by a query of the SELECT statement where each row is processed separately. Thus, Cursor is a reserved SQL area from where information in a database can be retrieved. Stored procedures are the set of SQL statements that are kept in RDMS as a group with a name assigned for the aim of reuse and sharing functions by many programs.

Features of MySQL Cursor

Below are the Features of MySQL Cursor:

• Read-Only: You cannot modify any data in the primary table using the cursor.
• Non-Scrollable: Rows can be fetched only in the order followed by the SELECT statement, not in the reversed direction and neither rows can be skipped nor jumped to a specific one in the result set.
• Asensitive: Cursors are of two kinds: asensitive and insensitive cursors. In an asensitive cursor, the actual data is pointed while in an insensitive cursor, a provisional copy of the data is used. Thus, MySQL cursor is asensitive.

Types in MySQL

When any SQL statement is executed, then a temporary workspace is generated in the system memory, this space is Cursor. A cursor holds a set of rows which is known as Active set.

Cursors are of two types in MySQL:

1. Implicit Cursors

• Auto-created by Oracle when SQL is executed if there is no explicit cursor used.
• Users or programmers cannot control the information or programs in it
• Associated with INSERT, UPDATE and DELETE types of DML operation statements
• Attributes: SQL%FOUND, SQL%NOTFOUND, %ISOPEN, %ROWCOUNT

2. Explicit Cursors

• User-defined cursors which help to gain more control over the context part.
• It is defined in the declaration area of the SQL block.
• Created on SELECT statements that returns multiple records.
• Attributes: SQL%FOUND, SQL%NOTFOUND, %ISOPEN, %ROWCOUNT.

Syntax and steps for an explicit cursor:

CURSOR  cursorname  IS  selectstatement;

• Declaring the cursor to define a result set
• Opening the cursor to establish the result set
• Fetching the cursor to retrieve the data into local variables
• Closing the cursor when done

Cursor Actions in MYSQL

There are four actions performed by the Cursor in MySQL:

1. DECLARE: First, declare a cursor with a name associated with a SELECT statement.

DECLARE cursorname CURSOR FOR SELECTstatement;

The cursor should not be declared before any variable declaration otherwise MYSQL may show an error.

2. OPEN: Next, open the cursor using the OPEN statement that initializes the result set and allocates memory for the cursor before the rows are fetched from the result set.

OPEN cursorname;

3. FETCH: Then, the FETCH statement is used to access one row at a time and move to the next row if available.

FETCH cursorname INTO variables_list;

4. CLOSE: Finally, disable the cursor which releases the memory allocated.

CLOSE cursorname;

Note: When the FETCH statement is called, the next row is read in the result set each time. But a time comes when it reaches to the end of the set and no data is found there, so to handle this condition with MYSQL cursor we need to use a NOT FOUND handler.

Syntax:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET variable_name = 1;

Here, variable_name indicates the end of the result set. Let us also discuss the Cursor Attributes now. They are described below:

• %FOUND: If the record is fetched successfully by the recent operation then, it returns ‘TRUE’ otherwise ‘FALSE’.
• %NOTFOUND: It returns ‘FALSE’ if the record is retrieved but ‘TRUE’ if not.
• %ISOPEN: If the cursor is already opened, it results in ‘TRUE’ else ‘FALSE’
• %ROWCOUNT: When DML operations are executed, in a result set it provides the count of rows. 

Examples to Implement

For Example, let us take a table named ‘Customers’.  Fetching its fields by query:

Code:

SELECT * FROM Customers;

Following is an example to explain the concepts of cursors in MySQL:

Code:

DECLARE
CURSOR cust_record IS SELECT CustomerName FROM Customers;
cust_id Customers.CustomerID%TYPE;
cust_name Customers.CustomerName%TYPE;
cust_addr Customers.Address%TYPE;
BEGIN
OPEN cust_record;
LOOP
FETCH cust_record into cust_id, cust_name, cust_addr;
EXIT WHEN cust_record%notfound;
dbms_output.put_line(cust_id || ' ' || cust_name || ' ' || cust_addr);
END LOOP;
CLOSE cust_record;
END;
/

Output:

*SQL Procedure Successfully Completed.

Importance of Cursors in MYSQL

Below is the important points  of  Cursor in MySQL:

• MySQL cursors are applicable in stored procedures, triggers, and stored functions. The cursors can be assumed as Database Objects that are used to handle data or alter any records in a database table on a row-by-row base.
• Cursors are essential to return multiple rows when it processes rows individually for queries while a trigger is a program code part that triggers automatically when any particular events occur in a table of a database.
• Oracle generates a context area or a memory area that processes an SQL statement that holds all the information needed for handling the statement. Like, the number of rows processed in a result set, etc.
• In RDBMS, different operations are executed on a set of rows. For example, a result set is returned when the SELECT statement is made on a set of rows. But sometimes the work maybe with a row at a time, not with the entire result set needed by the application logic. In MySQL, the cursor is then used as a major function for one way of doing this.
• A database cursor in computer science is a responsible control structure that allows traversal over the data in a database using the DECLARE, OPEN, FETCH and CLOSE functions.

Conclusion

Just as on a computer screen the cursor shows the current position, likewise Cursor in MYSQL is also named so because it specifies the current position in the result set. We can conclude that a CURSOR simply returns a result set to us from a database and is not intended to be updated. It goes through the entire result set and shows everything found there. So, Cursor in MySQL is an important concept to be familiar with before moving on to their usages in ADO. But you can choose another alternative as a better choice if you want something more customized and able to skip rows in the result set. We can say that ‘Cursors Are Cool’, aren’t they? Writing this article supported me to study and share some important concepts on cursors which can further advance MySQL skills. I hope you like it, thanks for reading.

Recommended Articles

This is a guide to Cursor in MySQL. Here we discuss the Features, types, examples to implement, important and four actions performed by the Cursor. You can also go through our other related articles to learn more –

1. MySQL Aggregate Function
2. SELECT in MySQL
3. Joins in MySQL
4. Cursors in SQL