Blog Blog Posts Business Management Process Analysis

Cursor in SQL: Implicit & Explicit Cursors Types

Cursors are a robust feature in SQL that allows professionals to work with data one row at a time. Let’s read about their types and syntax in detail below.

What is Cursor in SQL?
Types of Cursors in SQL
How to Use a Cursor in SQL?
SQL Cursor Syntax
SQL Cursor Example
Conclusion

Watch this Video on MS SQL Training for Beginners

{
“@context”: “https://schema.org”,
“@type”: “VideoObject”,
“name”: “video”,
“description”: “Cursor in SQL: Implicit & Explicit Cursors Types”,
“thumbnailUrl”: “https://img.youtube.com/vi/LGTbdjoEBVM/hqdefault.jpg”,
“uploadDate”: “2023-06-22T08:00:00+08:00”,
“publisher”: {
“@type”: “Organization”,
“name”: “Intellipaat Software Solutions Pvt Ltd”,
“logo”: {
“@type”: “ImageObject”,
“url”: “https://intellipaat.com/blog/wp-content/themes/intellipaat-blog-new/images/logo.png”,
“width”: 124,
“height”: 43
}
},
“embedUrl”: “https://www.youtube.com/embed/LGTbdjoEBVM”
}

What is Cursor in SQL?

A cursor in SQL is a database object that allows you to retrieve and manipulate data one row at a time. Cursors are typically used when performing operations on each result set row, such as updating or deleting. Cursors are similar to pointers in programming, allowing you to move through a result set and access the data in each row.

Want to get Certified in SQL? Learn from our SQL Expert with Intellipaat’s Microsoft SQL Certification and give a head-start to your Career in SQL!

Types of Cursors in SQL

When working with cursors in SQL, there are two main types: Implicit Cursors and Explicit Cursors. These cursors serve similar purposes, but there are some significant differences.

Implicit Cursors

Implicit cursors are created by the SQL engine whenever you execute a SELECT statement. The cursor is automatically opened, and you can fetch rows from the result set one at a time.

Implicit cursors are helpful when retrieving a small amount of data and performing simple operations on each row.

Explicit Cursors

Explicit cursors are created by the programmer to retrieve and manipulate data from a result set. They provide greater control over the cursor, such as the ability to open and close it, fetch rows, and also, perform operations on the data.

In addition, Explicit cursors are specifically useful while dealing with large amounts of data and also performing complex operations on each row. Thus, they enable the programmer to process the data one row at a time. This can improve performance and reduce memory usage.

Wish to crack SQL job interviews? I Hope Intellipaat’s Top SQL Interview Questions will make an impact to crack the job for you.

How to Use a Cursor in SQL?

Cursors are a valuable asset for developers working with databases. They enable efficient processing of vast amounts of data and are widely utilized in numerous database applications.

To use a cursor in SQL, you first need to declare the cursor and specify the SELECT statement that will be used to retrieve the data.

Here is an example of how to declare a cursor:

DECLARE cursor_name CURSOR FOR
SELECT column1, column2, column3
FROM table_name
WHERE condition;

Once you have declared the cursor, you can open it and fetch rows from the result set using the FETCH statement. Here is an example of how to open and fetch rows from a cursor:

OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO variable1, variable2, variable3;

You can then perform data operations using the variables you fetched. Once you have finished processing the row, you can re-fetch the next row using the FETCH statement.

Here is an example of how to fetch the next row:

FETCH NEXT FROM cursor_name INTO variable1, variable2, variable3;

You can continue fetching rows and performing operations on them until you have processed all the rows in the result set. Once you are finished, you can close the cursor using the CLOSE statement.

Check out our Database Certification courses to get professionally fitted as a Database specialist!

SQL Cursor Syntax

The SQL feature known as cursors makes it possible to traverse and manipulate data one row at a time. But to use a cursor, you need to know the correct syntax when declaring and working with it.

Here is the basic syntax for creating a cursor in SQL:

DECLARE cursor_name CURSOR FOR
SELECT column1, column2, column3
FROM table_name
WHERE condition;
OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO variable1, variable2, variable3;
WHILE @@FETCH_STATUS = 0
BEGIN

— Do some processing here

FETCH NEXT FROM cursor_name INTO variable1, variable2, variable3;
END
CLOSE cursor_name;
DEALLOCATE cursor_name;

SQL Cursor Example

Let’s take an example of how to use an explicit cursor in SQL. In this example, we will create a cursor that retrieves all the employees from the “employees” table and calculates their average salary.

DECLARE employee_cursor CURSOR FOR
SELECT employee_id, first_name, last_name, salary
FROM employees;
DECLARE @employee_id INT;
DECLARE @first_name VARCHAR(50);
DECLARE @last_name VARCHAR(50);
DECLARE @salary DECIMAL(18, 2);
DECLARE @total_salary DECIMAL(18, 2) = 0;
DECLARE @num_employees INT = 0;
DECLARE @avg_salary DECIMAL(18, 2) = 0;
OPEN employee_cursor;
FETCH NEXT FROM employee_cursor INTO @employee_id, @first_name, @last_name, @salary;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @total_salary = @total_salary + @salary;
SET @num_employees = @num_employees + 1;
FETCH NEXT FROM employee_cursor INTO @employee_id, @first_name, @last_name, @salary;
END
CLOSE employee_cursor;
DEALLOCATE employee_cursor;
SET @avg_salary = @total_salary / @num_employees;
SELECT 'Average Salary' AS Metric, @avg_salary AS Value;

In this given example, a cursor called “employee_cursor”, retrieves all the employees from the “employees” table. It then reflects declare variables to hold the employee data and initialize variables to calculate the average salary.

We open the cursor and fetch the first row into the variables.

Then enter a loop that calculates the total salary and the number of employees. We fetch the next row until there are no more rows to fetch.

Once we have processed all the rows, we close and deallocate the cursor. We then calculate the average salary by dividing the total salary by the number of employees and output the result.

Check out the SQL Tutorial to learn more about its concepts!

Career Transition

Conclusion

In conclusion, cursors in SQL can be a helpful tool for handling complex queries requiring individual row processing or executing a stored procedure or trigger for each row. However, they can also have some drawbacks, such as decreased efficiency and potential locking issues in a multi-user environment.

Understanding when and how to use cursors properly is essential to avoid these potential problems. Generally, it is recommended to use cursors sparingly and only when necessary. It is best to use set-based operations whenever possible to improve performance when working with large datasets.

Have more queries about SQL? Visit our SQL Community and get them clarified!

The post Cursor in SQL: Implicit & Explicit Cursors Types appeared first on Intellipaat Blog.