Definition :
A cursor is a database object that allows you to retrieve and manipulate a set of rows returned by a query one at a time, rather than all at once. Cursors are typically used in stored procedures or other programmatic constructs, where the developer needs to perform complex data manipulation or processing on the result set.
Types :
- Implicit : These cursors are created automatically by the database management system (DBMS) when a SQL statement is executed. They are used for executing single-row queries or DML statements (such as insert, update, and delete). Implicit cursors are easy to use, but they have limited functionality and are not suitable for processing large result sets.
Explicit : These cursors are defined and opened by the programmer using a SQL cursor statement. Explicit cursors give more control to the programmer and can be used to process large result sets. They are also suitable for performing complex data manipulation operations, such as traversing hierarchies or performing aggregations.
Scope :
- Global : A cursor with global scope is visible throughout the entire program or procedure in which it is declared. It can be used to process data across multiple blocks of code.
- Local : A cursor with local scope is visible only within the block of code in which it is declared. It can be used to process data in a specific part of a program or procedure.
Common use cases for cursors in SQL:
- Processing large result sets : When dealing with large result sets, cursors can be useful for processing data row by row rather than loading the entire result set into memory at once. This can help to reduce memory usage and improve performance.
- Hierarchical data : Cursors can be used to traverse hierarchical data structures, such as trees or graphs. By processing each node or edge in the hierarchy one at a time, it is possible to implement recursive algorithms and perform complex calculations.
- Complex aggregations : When performing complex aggregations that require multiple levels of grouping or filtering, cursors can be used to iterate over each group or subset of data and perform calculations or aggregations as needed.
- Business logic : Cursors can be used to implement complex business logic and data validation rules. For example, a cursor could be used to iterate over a set of orders and ensure that each order meets a set of business rules, such as minimum order amounts or product availability.
- Data migration and transformation : Cursors can be used to migrate or transform data from one schema to another. By iterating over each row in a source table and inserting or updating corresponding rows in a target table, it is possible to perform complex data transformations that cannot be easily achieved using set-based operations.
Life cycle of a cursor :
A SQL cursor is a programming construct that allows you to iterate over a result set returned by a SQL query. The life cycle of a SQL cursor typically involves the following steps:
- Declaration : The cursor is declared and its properties are set, such as the SELECT statement to retrieve data, the type of cursor, and the fetch direction.
- Initialization : The cursor is opened and initialized by executing the SELECT statement. The result set is then made available to the cursor.
- Fetching : The cursor is positioned on the first row of the result set. Data from the current row is fetched and processed by the application. The cursor is then positioned on the next row, and the process is repeated until all rows in the result set have been processed.
- Closing : Once all rows have been processed, the cursor is closed and any resources associated with it are released.
- De-allocation : The cursor is de-allocated and removed from memory.
It’s worth noting that the life cycle of a SQL cursor can be affected by the type of cursor used. For example, a static cursor will fetch a copy of the result set and store it in memory, while a dynamic cursor will fetch rows one at a time as needed. Additionally, the use of cursors in SQL is often discouraged due to potential performance issues and the availability of alternative approaches such as set-based operations.
Syntax:
DECLARE CURSOR
DECLARE Cursor_Name CURSOR
FOR SELECT Column_Name(s)
FROM
Table_Name WHERE Condition;
Or
DECLARE Cursor_Name CURSOR
FOR
SELECT * FROM Table_Name
OPEN CURSOR
OPEN Cursor_Name
CLOSE CURSOR
CLOSE Cursor_Name
DEALLOCATE CURSOR
DEALLOCATE Cursor_Name
Table used in examples
CREATE TABLE Customer(
ID INT PRIMARY KEY,
NAME VARCHAR(50),
CITY VARCHAR(20));
INSERT INTO
Customer (Id, Name, City)
VALUES
(1, ‘FRANCIS’, ‘LONDON’),
(2,’SHAROLN’, ‘NEWYORK’),
(3, ‘ELLA’, ‘DOVER’);
Example 1 Retrieving Data with a Cursor:
DECLARE
@FirstName VARCHAR(50);
DECLARE CursorExample CURSOR
FOR SELECT
Name
FROM
dbo.Customer
OPEN CursorExample
FETCH NEXT
FROM
CursorExample
INTO @FirstName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @FirstName
FETCH NEXT
FROM
CursorExample
INTO @FirstName
END
CLOSE CursorExample
DEALLOCATE CursorExample
Example 2 Updating Data with a Cursor:
DECLARE
@FirstName VARCHAR(50),
@CustomerID INT;
DECLARE CursorExample2 CURSOR
FOR SELECT
ID,
Name
FROM
dbo.Customer
OPEN CursorExample2
FETCH NEXT
FROM CursorExample2
INTO
@CustomerID,
@FirstName
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Customer
SET Name = @FirstName + ‘ Doe’
WHERE ID = @CustomerID
FETCH NEXT
FROM CursorExample2
INTO
@CustomerID,
@FirstName
END
CLOSE CursorExample2
DEALLOCATE CursorExample2
Break down for the code :
DECLARE is used to define a cursor variable
OPEN is used to initialise the cursor and make the first row available for processing.
FETCH NEXT retrieves the current row and assigns the values to the variables
WHILE is used to loop over the rows until there are no more rows to fetch (@@FETCH_STATUS = 0 means there are still rows to fetch).
Inside the loop, you can do whatever processing you need to do on the current row. In this case, we
are just printing out the name and age of each person in the table.
After processing the current row, FETCH NEXT is used again to move to the next row.
CLOSE is used to release any resources associated with the cursor.
DEALLOCATE is used to remove the cursor from memory.
Example 3 Deleting Data with a Cursor:
DECLARE
@CustomerID INT
DECLARE CursorExample3 CURSOR
FOR SELECT
Id
FROM dbo.Customer
WHERE city = ‘London’
OPEN CursorExample3
FETCH NEXT
FROM CursorExample3
INTO
@CustomerID
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM dbo.Customer
WHERE Cust_Id = @CustomerID
FETCH NEXT
FROM CursorExample3
INTO
@CustomerID
END
CLOSE CursorExample3
DEALLOCATE CursorExample3