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

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?
• Types of SQL Cursors
• Syntax of a SQL Cursor
• Cursor Lifecycle

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.

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.

2. Open

OPEN cursorName;

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

3. 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.

4. Close

CLOSE cursorName;

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

5. 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.

For details, You can even check out how to manage databases on SQL Server and its concepts with the SQL certification course.

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