A cursor in SQL is a database object used to retrieve, manipulate, and navigate through a result set row-by-row. Cursors are particularly useful when you need to perform operations on each row individually, which can be essential for tasks that require procedural logic.
Key Concepts
- Declaration: Define the cursor and the SQL query it will use.
- Opening: Open the cursor to establish the result set.
- Fetching: Retrieve each row from the result set.
- Closing: Close the cursor to release resources.
Syntax
The general syntax for using a cursor in SQL Server is:
DECLARE cursor_name CURSOR FOR
SELECT_statement;
OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO variable_list;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Operation to perform on each row
FETCH NEXT FROM cursor_name INTO variable_list;
END;
CLOSE cursor_name;
DEALLOCATE cursor_name;
Example
Consider a table named employees
with columns employee_id
, first_name
, and salary
. We will use a cursor to update the salary of each employee by 10%.
-- Create a sample table
CREATE TABLE employees (
employee_id INT,
first_name VARCHAR(50),
salary DECIMAL(10, 2)
);
-- Insert sample data
INSERT INTO employees (employee_id, first_name, salary)
VALUES
(1, 'John', 50000.00),
(2, 'Jane', 60000.00),
(3, 'Jim', 55000.00);
-- Declare the cursor
DECLARE employee_cursor CURSOR FOR
SELECT employee_id, salary
FROM employees;
-- Declare variables to hold cursor output
DECLARE @employee_id INT, @salary DECIMAL(10, 2);
-- Open the cursor
OPEN employee_cursor;
-- Fetch the first row
FETCH NEXT FROM employee_cursor INTO @employee_id, @salary;
-- Loop through the result set
WHILE @@FETCH_STATUS = 0
BEGIN
-- Update the salary
UPDATE employees
SET salary = @salary * 1.10
WHERE employee_id = @employee_id;
-- Fetch the next row
FETCH NEXT FROM employee_cursor INTO @employee_id, @salary;
END;
-- Close and deallocate the cursor
CLOSE employee_cursor;
DEALLOCATE employee_cursor;
-- Check the updated table
SELECT * FROM employees;
Considerations
While cursors can be very useful, they come with some drawbacks:
- Performance: Cursors can be slower than set-based operations because they process rows one at a time. It's often more efficient to use set-based operations whenever possible.
- Resource Usage: Cursors consume resources such as memory and locks, which can impact database performance, especially with large result sets.
Alternatives
For many tasks, you can use set-based SQL operations instead of cursors. For example, the above salary update can be done more efficiently with a single UPDATE
statement:
UPDATE employees
SET salary = salary * 1.10;
Cursors in SQL are powerful tools for row-by-row processing of result sets. They are useful when procedural logic is necessary for operations on individual rows. However, due to their performance and resource usage considerations, it's often better to use set-based operations if possible. Understanding when and how to use cursors effectively is essential for advanced SQL programming and database management.