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!
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.
There are two types of cursors-
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.
%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.

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.

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


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;
The cursor is declared in the DECLARE section along with the associated SELECT statement.
Syntax-
CURSOR CURSORNAME IS SELECT.......; 
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;
This operation involves fetching the data, accessing one row at a time.
Syntax-
FETCH cursor_name INTO record_name;
At the end of the SQL block, the cursor is closed.
Syntax-
CLOSE  cursor__name;

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.

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;

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.


A cursor life cycle has below five steps:
The SQL statement is used to declare a cursor.
A cursor is opened to store data from the result collection.
When a cursor is opened, rows can be fetched one at a time or in a block to perform data manipulation.
Following data manipulation, the cursor should be directly locked.
Cursors can be deallocated to erase the cursor description and free all resources associated with the cursor.
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.
Software Development Course typically range from a few weeks to several months, with fees varying based on program and institution.
Cohort Starts: 15 Apr, 2024
Cohort Starts: 16 Feb, 2024
Cohort Starts: 28 Feb, 2024
Cohort Starts: 19 Mar, 2024
The Ultimate Guide to Creating Chatbots
All You Need to Know About SQL Data Types
What Is SQL Injection: How to Prevent SQL Injection
A Guide on How to Become a Site Reliability Engineer (SRE)
Constraints in SQL : Specifying Rules for Data
What Is SQL Injection: How to Prevent SQL Injection
© 2009 -2024- Simplilearn Solutions
Follow us!
Company
Work with us
Discover
For Businesses
Learn On the Go!
Trending Post Graduate Programs
Trending Bootcamp Programs
Trending Master Programs
Trending Courses
Trending Categories
Trending Resources

source