Cursor in SQL

SQL is one of the most rewarding and popular programming languages today. In this tutorial, you will learn everything about cursor in SQL. Let’s start!

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.

Attribute

Description

%FOUND

Returns TRUE if DML statement was executed successfully or SELECT statement returns one or more rows, otherwise returns FALSE.

%NOTFOUND

It is the logical opposite of the above attribute. Returns TRUE if no statement was executed or no record was fetched, otherwise returns FALSE.

%ISOPEN

Returns TRUE if the cursor is open, otherwise FALSE. 

The point to be noted here is that an implicit cursor is automatically closed by Oracle once the statement associated with it gets executed. So, in this case, it will always return FALSE. 

%ROWCOUNT

Returns the number of statements from the cursor at a time.

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

Example

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

CursorinSQL_1.

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

CursorinSQL_2

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

Output-

CursorinSQL_3

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

CursorinSQL_4

  • 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-

CURSOR cursor_name IS select_statement;

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-

CURSOR CURSORNAME IS SELECT.......; 

  • 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-

OPEN cursor_name;

  • Fetching the Cursor

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

Syntax-

FETCH cursor_name INTO record_name;

  • Closing the Cursor

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

Syntax-

CLOSE  cursor__name;

Example-

CursorinSQL_5

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

CursorinSQL_6

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-

DECLARE

CURSOR <cursor_name> IS <SELECT statement>;

BEGIN

  FOR I IN <cursor_name>

  LOOP

  .

  .

  END LOOP;

END;

Example-

CursorinSQL_7.

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

CursorinSQL_8

Post Graduate Program in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
Post Graduate Program in Business Analysis

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.

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.