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. |
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-
|
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-
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-
|
DECLARE CURSOR <cursor_name> IS <SELECT statement>; BEGIN FOR I IN <cursor_name> LOOP . . END LOOP; END; |
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
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.