A Detail Guide on Cursor in SQL And Its Types

What Is a Cursor in SQL?

A cursor in SQL is a user-defined iterative variable that enables the user to access query results. It could be thought of as a pointer that points to the context area. A cursor is used by PL/SQL to navigate through the context area. It holds the rows that are returned by SQL statements. 

A cursor is defined using a DECLARE statement and taking SELECT as a parameter. A cursor is generally given a name so that in future, it could be referred to in the program to retrieve and execute the rows returned by SQL statements.

Types of Cursors

There are two types of cursors-

• Implicit Cursor

Whenever a DML statement like INSERT, UPDATE or DELETE is executed, an implicit cursor is generated by default, to process these statements. It is also created when a SELECT statement returning only a single row is executed. Since these cursors are automatically generated, these cursors or the information contained in them can not be controlled by the users. To check the state of these DML statements, Oracle provides a few attributes known as implicit cursor attributes, as given below.

Your Data Analytics Career is Around The Corner!

Data Analyst Master’s ProgramExplore Program

Example

Let’s consider a table named student, as given below.

Now, using the below program, we’ll update the table by decreasing the salary of each below by 1000. 

Here %ROWCOUNT attribute of Oracle has been used to find the number of rows that have been updated in the database.

Output-

Now, if we see our table, the rows have been updated and the salary of each employee has been reduced by 1000. 

• Explicit Cursor

An explicit cursor is a programmer-defined cursor that is created when we are executing a SELECT statement returning more than one row. Even though the cursor stores several records simultaneously, only one can be processed at a time, which is referred to as the current row. This cursor is defined for gaining extra control over the context area.

The syntax for defining an explicit cursor-

Working with an explicit cursor is not the same as with an implicit cursor, some steps have to be followed while using an explicit cursor. The steps are defined as follows;

• Declaring the Cursor

The cursor is declared in the DECLARE section along with the associated SELECT statement.

Syntax-

• Opening the Cursor

The cursor is opened in the execution section and doing so allocates the memory for the cursor to fetch the rows returned by the SQL statement.

Syntax-

• Fetching the Cursor

This operation involves fetching the data, accessing one row at a time.

Syntax-

• Closing the Cursor

At the end of the SQL block, the cursor is closed.

Syntax-

Example-

In the above code, we declare the cursor as s_student, and it is used to fetch the id, name and address of each student in the database.

Output

How to Create a Cursor?

A cursor in SQL can be created by using the above method, following the four steps, or we can also use the FOR LOOP for working with the cursor, by giving the cursor name instead of the range limit in the for loop statement. This will make the for loop run from the cursor’s first record to the cursor’s last record. The FOR loop will perform the cursor variable, cursor opening, cursor fetching, and cursor closing implicitly.

Syntax-

Example-

Here, the cursor is created as student_s. The for loop variable is named lv_s_name. The above code prints the name of the student in each iteration of the for a loop.

Output

Become an AI-powered Business Analyst

With Purdue University's Business Analysis ProgramExplore Now

Cursor Life Cycle

A cursor life cycle has below five steps:

• Cursor Declaration

The SQL statement is used to declare a cursor.

•  Cursor Opening

A cursor is opened to store data from the result collection.

• Cursor Fetching

When a cursor is opened, rows can be fetched one at a time or in a block to perform data manipulation.

• Cursor Closing

Following data manipulation, the cursor should be directly locked.

• Cursor Deallocation

Cursors can be deallocated to erase the cursor description and free all resources associated with the cursor.

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Certification Program. Enroll now!

Conclusion

So in this cursor in SQL tutorial, we covered everything from what is a cursor in SQL to the difference between implicit and explicit cursors, to creating a cursor in SQL to cursor life cycle too!

If you wish to learn the A-Z of SQL, Simplilearn’s SQL Certification Training should be your next stop. And if you have any queries related to the cursor in SQL, feel free to drop them in the comment section and our experts will answer them for you.