Cursors in SQL: Definition, Types and Lifecycle

In this article, we will talk about the definition, types and lifecycle of cursors in SQL. Do take a look to learn about SQL cursors in detail.

With large amounts of data stored in databases, it is of utmost importance for the users to travel through the databases. Cursors as the name suggest pinpointing the data to where you wish to allow traversal and allow the traversal of data. In this article on Cursors in SQL, we will discuss the following topics:

• What is a Cursor in SQL?
• Types of SQL Cursors
• Lifecycle of an SQL Cursor

What is a Cursor in SQL?

As mentioned above, Cursors are objects that allow the traversal of data over the rows. You can consider cursors as a pointer pointing to the present row from an arrangement of rows. 

It is a temporary space in the memory system during the execution of a SQL statement. It stores the retrieved data from the database and can process only a single row at a time. 

Also, all the rows held by a cursor are considered an active set.  

Syntax:

[code]
CURSOR CursorName IS SELECT Statement;
[/code]

Example:

[code]
CURSOR CursorPat IS SELECT PatientName;
[/code]

There are various types of cursors that perform different data operations. Next in this article let us understand the types of cursors in SQL.

Explore Free Online Courses with Certificates

Types of Cursors in SQL

There are two types of cursors in SQL:

• Implicit Cursor 
• Explicit Cursor

Let us understand each one of them one by one:

Implicit Cursor

Implicit Cursors are used and generated automatically when the INSERT, DELETE and UPDATE operations are performed.

This type of cursor is used only for internal processing and cannot be referred to or controlled outside the space that the users are working on. So basically, the implicit cursors can be used to refer to the most recent cursor. Also, these types of cursors only store/hold the rows affected by the data operation. 

The following are a few cursor attributes used to refer to the most recent cursor:

• %ROWCOUNT – Returns the count of rows affected by the INSERT, DELETE and UPDATE statements.
• %ISOPEN – Returns FALSE for the implicit cursors, as the cursor is closed post the execution of the SQL statement.
• %FOUND – Returns TRUE if the INSERT, DELETE, and UPDATE statement affect one or more rows.
• %NOTFOUND – Returns TRUE if the INSERT, DELETE, and UPDATE statements do not affect one or more rows.

Check out more articles on SQL

Explicit Cursor

Explicit cursors are used whenever users process data through SQL blocks. This type of cursor holds more than one row, but can only process a single row at a time. 

So users can create a context area or a defined SQL block for the data manipulation operations. One such example is that of SQL triggers. 

Moving ahead in this article let us understand the lifecycle of Cursors in SQL.

Lifecycle of an SQL Cursor

The lifecycle of the cursor has 6 stages:

• Declaring a cursor
• Opening a cursor
• Fetching a cursor
• Checking status
• Closing a cursor
• Deallocating a cursor

Declaring a cursor

Here, you have to specify the name and data type of the cursor.  You also have to mention the select statement in this step.

[code]
DECLARE CursorName CURSOR FOR SelectStatement;
[/code]

Opening a cursor

This stage of the lifecycle will let you open the cursor.

[code]
OPEN CursorName;
[/code]

Fetching a cursor

This step retrieves rows from the cursor and saves it in variables.

[code]
FETCH NEXT FROM CursorName INTO Variable;
[/code]

Checking Status

This stage of the lifecycle returns the status of the last FETCH statement executed against the cursor. In case you wish to fetch all rows from the cursor, then you must use the WHILE statement. If 0 is returned, then it indicates that the operation was successful.

[code]
WHILE @@FETCHSTATUS = 0
BEGIN
FETCH NEXT FROM CursorName;
END;
[/code] 

Closing a cursor

As the name suggests, this step will close the cursor.

[code]
CLOSE CursorName;
[/code]

Deallocating  a cursor

The final step will deallocate the cursor and free up the occupied memory space.

[code]
DEALLOCATE CursorName;
[/code]
 

With this, we end this article on Cursors in SQL. We hope you found it informative. You can refer to the article on MySQL Commands for a detailed walkthrough of all commands.

Top Trending Tech Articles:
Career Opportunities after BTech | Online Python Compiler | What is Coding | Queue Data Structure | Top Programming Language | Trending DevOps Tools | Highest Paid IT Jobs | Most In Demand IT Skills | Networking Interview Questions | Features of Java | Basic Linux Commands | Amazon Interview Questions

Recently completed any professional course/certification from the market? Tell us what you liked or disliked in the course for more curated content.

Click here to submit its review with Shiksha Online.