Open In App

Difference between Implicit and Explicit Cursors

Improve
Improve
Like Article
Like
Save
Share
Report

Databases such as ORACLE have a memory area, where processing of instructions and fetched data takes place.A cursor is a pointer which is pointing to this area.The data contained in this memory area is also known as Active Set. Cursors can be broadly classified into Implicit Cursors and Explicit Cursors

Difference between Implicit and Explicit Cursors : 
 

Implicit Cursors Explicit Cursors
Implicit cursors are automatically created when select statements are executed. Explicit cursors needs to be defined explicitly by the user by providing a name.
They are capable of fetching a single row at a time. Explicit cursors can fetch multiple rows.
Closes automatically after execution. Need to close after execution.
They are more vulnerable to errors such as Data errors, etc. They are less vulnerable to errors(Data errors etc.)
Provides less programmatic control to the users User/Programmer has the entire control.
Implicit cursors are less efficient. Comparative to Implicit cursors, explicit cursors are more efficient.

Implicit Cursors are defined as: 
 

BEGIN
SELECT attr_name from table_name
where CONDITION;
END

 

Explicit cursors are defined as: 
 

DECLARE
CURSOR cur_name IS
SELECT attr_name from table_name 
where CONDITION;
BEGIN
...

 

Implicit cursors requires anonymous buffer memory for storage purpose. Explicit cursors use user-defined memory space for storage purpose
Cursor attributes use prefix “SQL”. 
Structure for implicit cursors: SQL%attr_name 
Few implicit cursors attributes are: SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT
Structure for explicit cursors: cur_name%attr_name 

Few explicit cursors are: cur_name%FOUND, cur_name%NOTFOUND, cur_name%ROWCOUNT


Last Updated : 18 May, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads