AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (55 Blogs)
  • SQL Essentials Training and Certification (9 Blogs)
SEE MORE

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-widget-banner-bg

What is a Cursor in SQL and how to implement it?

Published on Oct 29,2019 60 Views
Swatee Chand
Research Analyst at Edureka. A techno freak who likes to explore different... Research Analyst at Edureka. A techno freak who likes to explore different technologies. Likes to follow the technology trends in market and write about...
10 / 37 Blog from Introduction to SQL

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

Cursors in SQL form an integral part of any database which basically helps a user in traversing through the database without much hassle. Through the medium of this article on Cursor in SQL I will be giving you all the necessary details that you must be needing before you get your feet wet with it.

Below are the topics I will be discussing in this article:

What is cursor in SQL?

Cursor in SQL is an object which allows traversal over the rows of any result set. With this, you can process an individual row of a database that is returned by a query. It is a temporary work area or context area that is created in the memory system during the execution of a SQL statement which stores the retrieved data from the database and aids in its manipulation. You can consider it as an arrangement of rows along with a pointer pointing to the present row. A cursor is a database object which can hold more than one row but it can process only one row at a point of time. The set of rows held by a cursor holds is known as an active set. Thus you can control the record of a table in a singleton technique i.e one row at any point in time. 

Now that you are familiar with what is a cursor in SQL, let’s now move ahead and take a look at its various types.

Types of SQL Cursor

SQL provides two types of cursors which I have listed below:

  1. Implicit Cursor

Whenever DML operations such as INSERT, UPDATE, and DELETE are processed in the database, implicit cursors are generated automatically and used by the framework. These types of cursors are used for internal processing and can’t be controlled or referred from another code area. Implicit cursors in SQL just hold the affected rows by the operation and can only refer to the most recent cursor using the cursor attributes which are shown below in the table.

AttributeDescription
%FOUNDIt will return TRUE in case an INSERT, UPDATE, or DELETE statement affects one or more rows or a SELECT INTO statement returns one or more rows. In other cases, it will return FALSE.
%NOTFOUNDIt is technically the opposite of %FOUND attribute. It returns TRUE in case an INSERT, UPDATE, or DELETE statement doesn’t affect any rows or a SELECT INTO statement returns no rows. Else it returns just FALSE.
%ISOPENThis attribute will always return FALSE for implicit cursors as the SQL cursor is automatically closed immediately after the associated SQL statement is executed.
%ROWCOUNTIt returns the total number of affected rows by an INSERT, UPDATE, or DELETE statement, or the rows returned by a SELECT INTO statement.
  1. Explicit Cursor

This type of cursor is generated whenever data is processed by a user through an SQL block. Generally, the use of the SELECT query triggers the creation of an explicit cursor and can hold more than one row but process just one at a time. This type of cursor is used to hold the records present in a column. This allows the programmers to create a named context area for executing their DML operations for better control. Also, it needs to be defined in the SQL block and in turn is created for a SELECT query using that code.

To get a better understanding of a cursor, let’s now see what is the syntax of a cursor in SQL.

Syntax of a SQL Cursor

Below is the general syntax for creating an explicit cursor.

CURSOR cursorName IS selectStatement;

Here:

cursorName – This represents a valid name for the cursor

selectStatement – This represents a select query that will return multiple rows

Let’s now proceed further with this article and see the lifecycle of a SQL cursor.

Cursor Lifecycle

There are basically 5 stages in the lifecycle of a cursor in SQL which I have listed below:

  1. Declare
DECLARE cursorName CURSOR
FOR selectStatement;

This step will help you in specifying the name and data type of the cursor and the SELECT statement will define its result set.

  1. Open
OPEN cursorName;

This step will let you open and populate the cursor by executing it.

  1. Fetch
FETCH NEXT FROM cursor INTO variableList;

This step will retrieve a row from the cursor and store it into one or more variables.

[Optional] CheckStatus

WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM cursorName;
END;

This function returns the status of the last FETCH statement that was executed against the cursor. In case this function returns 0 then it means the FETCH operation was successful. In order to fetch all the rows from the cursor, the WHILE clause is used.

  1. Close
CLOSE cursorName;

This step will help you in closing the cursor after the operations have been completed.

  1. Deallocate
DEALLOCATE cursor_name;

This step will help in deallocating the cursor and freeing up the memory space.

With this, I would like to conclude this article on Cursor in SQL. I hope this article has helped you in adding value to your knowledge. For more information on SQL or Databases, you can refer to our comprehensive reading list here: Databases Edureka.

If you wish to get a structured training on MySQL, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of ”Cursor in SQL” and I will get back to you.

Comments
0 Comments

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.