{"id":27182,"date":"2018-09-03T10:05:01","date_gmt":"2018-09-03T10:05:01","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=27182"},"modified":"2021-03-11T21:00:36","modified_gmt":"2021-03-11T15:30:36","slug":"cursor-in-sql","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/cursor-in-sql\/","title":{"rendered":"Cursor in SQL – Types, Uses, and Terminologies"},"content":{"rendered":"

In this tutorial, we will try to learn about the Cursors in SQL. In this article, we will learn in-depth about the Cursor, its types and properties. We will also have a look at the steps we need to follow to create and use a cursor. Let us now dive into Cursors in SQL.<\/p>\n

What are Cursors in SQL?<\/h3>\n

Cursors are user-defined iterative variables that allow us to access the query results or the results of the stored procedure.<\/p>\n

Cursors are objects we define using a Declare statement and take a Select statement as the parameter. The Select statement returns the result, which then becomes the iterator space of the cursor.<\/p>\n

Cursors are supported on the stored procedures.<\/p>\n

To understand stored procedures in detail, make sure to have a look at the stored procedure tutorial. They are read-only variables and are helpful to read the data i.e., they can\u2019t update, delete or insert the records.<\/p>\n

Properties of SQL Cursors<\/h3>\n

Some of the major properties of SQL cursors are as follows:<\/p>\n

1. Asensitive<\/strong><\/p>\n

We have two types of cursors available to us one is Asensitive Cursor and the other variation is the Insensitive Cursor.<\/p>\n

An asensitive cursor points to the data, while the insensitive cursor creates and uses a temporary copy of the data.<\/p>\n

An asensitive cursor is faster and efficient than the insensitive cursor because it avoids making a temporary copy of data.<\/p>\n

While using asensitive cursor, we need to be careful as if we update the data when a asensitive cursor is at work, the data would update and the results could cause ambiguity. SQL cursor is asensitive.<\/p>\n

2. Read-only<\/strong><\/p>\n

When we talk about the SQL cursor, it is read-only i.e., it is not able to update, or delete the data.<\/p>\n

Cursors are read-only objects and thus can\u2019t update, delete or insert data in the database.<\/p>\n

3. Non-scrollable<\/strong><\/p>\n

The SQL cursor is non-scrollable i.e, we can traverse only in one direction. Also, we can’t skip the rows or jump to rows in the query result.<\/p>\n

When we use a cursor, we can\u2019t access the data in reverse order as random access is under the check of the controller. Also, we don\u2019t have the freedom to jump to specific result sets in the query output.<\/p>\n

Types of Cursor<\/h3>\n

1. Explicit Cursor<\/strong><\/p>\n

An explicit cursor requires a declaration by the user by the use of the SELECT statement. It goes over each record but only a single row is processed at a time. Once the reading of one row is complete it moves to another row.<\/p>\n

2. Implicit Cursor<\/strong><\/p>\n

An implicit cursor is put internally by SQL whenever the user runs any DML query. Also, implicit cursors are made for one single row of the database.<\/p>\n

Thus, implicit cursors are put on a trigger whenever one column is being shown by the Select statement.<\/p>\n

Demo Database<\/h3>\n

Let us now have a look at our demo database – DataFlair.
\nQuery:<\/strong><\/p>\n

USE DataFlair;\r\nSELECT * FROM DataFlair ;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
Cursors in Deep<\/h3>\n
Let us now look at the steps of creating and using a cursor:<\/p>\n