ORACLE SQL PLSQL Cursor Training Institutes in Kukatpally Hyderabad

Cursor in SQL

In SQL, a cursor is a temporary workstation that is allocated by the database server during the execution of a statement.

It is a database object that allows us to access data of one row at a time.

This concept of SQL is useful when the user wants to update the rows of the table one by one.

The cursor in SQL is the same as the looping technique of other programming languages.

The collection of tuples held by the cursor is known as the active set.

In SQL database systems, users define the cursor using DECLARE statement and take the SELECT statement as the parameter, which helps in returning the set of rows.

In this SQL article, we will learn about the types of a cursor, the life cycle of a cursor, syntax of a cursor, and the implementation of a cursor.

Types of Cursor in SQL

Following are the two types of Cursor in Structured Query Language:

  1. Implicit Cursor
  2. Explicit Cursor

Implicit Cursor

These types of cursors are generated and allocated by the SQL server when the system performs INSERT, DELETE, and UPDATE operations on SQL queries.

This cursor is also referred to as the default cursor in SQL.

An implicit cursor is also created by the system when the SELECT query selects the single row.

Explicit Cursor

These types of cursors are created by the user using the SELECT query.

An explicit cursor holds multiple records but processes a single row at a time. It uses the pointer, which moves to another row after reading one row.

It is basically used for gaining extra control over the temporary workstation.

Life Cycle of Cursor

The life cycle of the cursor is described into the following five stages:

  1. Declare a Cursor
  2. Open Cursor
  3. Fetch Data from Cursor
  4. Close Cursor Connection
  5. Deallocate cursor

Let's discuss each stage in brief:

1. Declare a Cursor

First, we have to declare the cursor by using the following SQL syntax:

  1. DECLARE Cursor_Name CURSOR FOR Select_Statement;

In this syntax, we have to specify the name and data type of the cursor just after the DECLARE keyword. After that, we have to write the SELECT statement, which defines the result set for the cursor.

2. Open Cursor

It is the second stage that opens the cursor for storing the data retrieved from the result set. We can open the cursor by using the following SQL syntax:

  1. OPEN Cursor_Name;

3. Fetch Cursor

It is the third stage in the cursor life cycle that fetches the rows for performing the insertion, deletion, and updation operations on the currently active tuple in the cursor.

Following are the six options that are used in syntax for fetching data from the cursor:

i. FIRST: This option allows the system to access only the first record from the cursor table. The syntax for the FIRST option is given below:

  1. FETCH FIRST FROM Cursor_Name;

ii. LAST: This option allows the system to access only the last record from the cursor table. The syntax for the LAST option is as follows:

  1. FETCH LAST FROM Cursor_Name;

iii. NEXT: This method allows the system to access the data in the forward direction from the cursor table. It is the default option. The syntax of this method is as follows:

  1. FETCH NEXT FROM Cursor_Name;

iv. PRIOR: This method allows the system to access the data in the backward direction from the cursor table. The syntax of this option is as follows:

  1. FETCH PRIOR FROM Cursor_Name;

v. ABSOLUTE n: This method allows the system to access the data of the exact nth row from the cursor table. The syntax of this option is mentioned below:

  1. FETCH ABSOLUTE n FROM Cursor_Name;

vi. RELATIVE n: This method allows the system to access the data in both incremental and decremental processes. The syntax of this option is mentioned below:

  1. FETCH RELATIVE n FROM Cursor_Name;

4. Close Cursor

It is the fourth stage in the process of the cursor. When we complete the work with the cursor, we have to close the cursor in this stage. We can close the cursor in SQL by using the following query:

CLOSE Cursor_Name;

5. Deallocate Cursor

It is the last and fifth stage of the cursor life cycle. In this part, we have to erase the definition of the cursor and discharge all the system resources combined with the cursor.

Syntax of a Cursor in SQL

  1. DECLARE @YourVariables nvarchar(50)  //You have to declare all the required variables
  2. DECLARE My_Cursor_Name CURSOR    // You have to declare the Name of your Cursor
  3. [LOCAL | GLOBAL]                                // You have to specify the Scope of your Cursor
  4. [FORWARD_ONLY | SCROLL]             // You have to specify the movement direction of your Cursor
  5. [ KEYSET | DYNAMIC |STATIC | FAST_FORWARD]  // You have to specify the Basic type of your Cursor
  6. [  SCROLL_LOCKS | OPTIMISTIC |READ_ONLY ]  // You have to specify the Locks for your Cursor
  7. OPEN My_Cursor_Name  // You have to Open Your Cursor
  8. FETCH NEXT FROM My_Cursor_Name // This line fetches the data from your Cursor
  9. CLOSE My_Cursor_Name // Here, you have to close Your Cursor
  10. DEALLOCATE My_Cursor_Name // Here, you have to deallocate the cursor memory.

Basic Type of Cursor

Following are the four basic types of cursor in Structured Query Language:

  1. STATIC Cursor
  2. Forward Only cursor
  3. KEYSET Driven Cursor
  4. Dynamic Cursor

STATIC Cursor

The static cursor can move in forward as well as backward direction. This type of cursor populates the result set during the creation of a cursor. Compared to other cursors, it is slow and uses more space in the memory.

By default, these types of cursors are scrollable. The static cursor does not allow the database users to modify and delete data.

Forward Only Cursor

This type of cursor accesses and updates the data only in the forward direction through the result set. So, it is the fastest cursor among all the four cursors.

The main disadvantage of this cursor is that it does not support backward scrolling.

Following are the three types of 'Forward only Cursor':

  1. Forward_Only KEYSET,
  2. Forward_Only KEYSET,
  3. FAST_FORWARD

DYNAMIC Cursor

The dynamic cursor is just opposite to the static cursor. It allows us to execute the INSERT, DELETE, and UPDATE operations while the cursor is open.

It checks all the modifications done on the rows and values in the result set.

KEYSET Driven Cursor

This type of cursor accesses the data from the first to the last row and last to the first row. When the user opens the KEYSET cursor, it automatically creates the list of unique values which uniquely identify each row in the entire result set.

Example of Cursor in SQL

Create a Student table in SQL using the following query:

  1. CREATE TABLE Student
  2. (
  3. Student_RollNo INT PRIMARY KEY,
  4. Student_Name nvarchar(60) NOT NULL,
  5. Student_Course nvarchar(20) NOT NULL,
  6. Student_Age INTNOT NULL,
  7. Student_Marks INT NOT NULL
  8. ) ;

Now, insert some values into the above Student table as shown in the following block:

  1. INSERT INTO Student (Student_RollNo, Student_Name, Student_Course, Student_Age, Student_Marks) VALUES ( 1, Amit, BCA, 19, 88),
  2. ( 2, Rahul, MCA, 21, 98),
  3. ( 3, Jones, B.tech, 20, 93),
  4. ( 4, Riya, BCA, 19, 89),
  5. ( 5, Aaniya, BBA, 21, 92),
  6. ( 6, Saket, MCA, 19, 95),
  7. ( 7, Shobhit, MBA, 20, 90),
  8. ( 8, Ishika, BCA, 21, 89),
  9. ( 9, Parul, B.tech, 19, 91),
  10. ( 10, Yukti, BCA, 20, 96);

We can check the data of the Student table by using the following SELECT statement in SQL:

  1. SELECT * FROM Student;

This query shows the data of the Student table in the output:

Student_RollNo Student_Name Student_Course Student_Age Student_Marks
1 Amit BCA 19 88
2 Rahul MCA 21 98
3 Jones B.tech 20 93
4 Riya BCA 19 89
5 Aaniya BBA 21 92
6 Saket MCA 19 95
7 Shobhit MBA 20 90
8 Ishika BCA 21 89
9 Parul B.tech 19 91
10 Yukti BCA 20 96

Now, we will create the following cursor for displaying the record of students from the Student table:

  1. DECLARE @Student_RollNo INT, @Student_Name NVARCHAR(50), @Student_Course NVARCHAR(50)     /*Here, we declare the variables for holding data.  */
  2. /* Here, we declare and set counter */
  3. DECLARE @Counter INT
  4. SET @Counter = 1
  5. PRINT '-------- Record of Students --------';
  6. /* Declare the cursor*/
  7. DECLARE Print_Student_Details CURSOR
  8. FOR
  9. SELECT Student_RollNo, Student_Name, Student_Course FROM customer
  10. /* Open the cursor */
  11. OPEN Print_Student_Details
  12. /* Fetch the record from the cursor into the variables.  */
  13. FETCH NEXT FROM Print_Student_Details INTO
  14. @Student_RollNo, @Student_Name, @Student_Course
  15. /* LOOP UNTIL RECORDS ARE AVAILABLE.  */
  16. WHILE @@FETCH_STATUS = 0
  17. BEGIN
  18. IF @Counter = 1
  19. BEGIN
  20. PRINT 'Student_RollNo' + CHAR(9) + 'Student_Name' + CHAR(9) + CHAR(9) + 'Student_Course'
  21. PRINT '--------------------------'
  22. END
  23. /* This statement prints the current record */
  24. PRINT CAST(@ Student_RollNo AS NVARCHAR(10)) + CHAR(9) + @Student_Name + CHAR(9) + CHAR(9) + @Student_Course
  25. /* This statement increments the counter variable */
  26. SET @Counter = @Counter + 1
  27. /* This statament fetch the next record into the variables. */
  28. FETCH NEXT FROM Print_Student_Details INTO
  29. @Student_RollNo, @Student_Name, @Student_Course
  30. END
  31. /* This statement closes the cursor*/
  32. CLOSE Print_Student_Details
  33. /* This statement deallocates the cursor*/
  34. DEALLOCATE Print_Student_Details

The above cursor gives the following output:

Student_RollNo Student_Name Student_Course
1 Amit BCA
2 Rahul MCA
3 Jones B.tech
4 Riya BCA
5 Aaniya BBA
6 Saket MCA
7 Shobhit MBA
8 Ishika BCA
9 Parul B.tech
10 Yukti BCA