Cursors in SQL

Imagine having a large box full of vibrant crayons. Each crayon presents a unique color. You desire to view and utilize every crayon individually. Yet, you don't want to pull all them out of the box at once. You'd rather select them from the box individually.

In SQL, a cursor is analogous to your hand. This hand reaches into the crayon box one crayon at a time. The cursor assists with passage through a list of items such as data in a table. This happens one at a time, not all at once.

Real-life Example

Consider a real-life example: You have a list of favorite toys in a table. Using SQL, you want to examine the name of each toy. You aim to play with each toy one by one. A cursor can assist in this scenario. It helps traverse your toy list. The cursor helps look at each toy's name. Finally, it lets you play with it. One toy at a time.

In Simple Words:

A cursor in SQL equates to our hand in the crayon box. It assists us in managing a single item from a list at a time. It is similar to how we handle toys or crayons.

What is a Cursor in SQL?

In SQL, a cursor functions as a database object and a programming construct. It enables you to access and manage data row by row from a result set. The cursor is a pointer that points to a specific row within a set of query outcomes. You can use the cursor to execute operations on individual data rows.

Cursor Usage in SQL

Certainly, here is an illustration of cursor usage in SQL. Imagine you have a table named students with columns student_id and student_name. The task is to make the names of the students visible one by one using a cursor.

SQL Example:

-- Declare a cursor
DECLARE student_cursor CURSOR FOR
SELECT student_name
FROM students;

-- Declare a variable to contain student name
DECLARE @student_name NVARCHAR(255);

-- Cursor opening
OPEN student_cursor;

-- Fetching the first row 
FETCH NEXT FROM student_cursor INTO @student_name;

-- We loop through cursor. Student names are printed
WHILE @@FETCH_STATUS = 0
BEGIN

-- We print the student name
PRINT @student_name;

-- Next row gets fetched
FETCH NEXT FROM student_cursor INTO @student_name;
END;

-- Close and deallocate cursor
CLOSE student_cursor;
DEALLOCATE student_cursor;

Explanation:

• Cursor is declared as student_cursor. The goal is to get student_name from the students table.
• Variable @student_name is declared to hold each student's name.
• The cursor is opened, starting the action.
• Using a WHILE loop, each name of the student is fetched and printed with the PRINT statement.
• The fetching and printing process continues until there are no more rows to fetch (@@FETCH_STATUS = 0).
• The cursor is closed, and its resources are freed by deallocating it.

Cursor Definition:

A cursor in SQL is a database programming feature. This feature offers a method to move through the records of a result set, generated by a SELECT query. Cursors permit actions such as:

• Fetching data
• Updating values
• Deleting rows one by one within the result set

When to Use Cursors:

Cursors find applications in data processing scenarios. They are termed sequential because they deal with data row by row. Such scenarios are common in procedural code found in stored procedures or triggers. However, cursors should be used judiciously because they can impact performance. In cases of large datasets, set-based operations are usually the preferred method.