SQL Cursor Types 🎯

Welcome to our in-depth guide on SQL Cursor Types! In this lesson, we'll explore different types of cursors in SQL, their usage, and practical examples. Let's get started!

What are SQL Cursors? 📝

Cursors in SQL are a control structure that allows you to manipulate a set of records one at a time. They provide a way to iterate through a result set and perform operations on each row.

Why use SQL Cursors? 💡

Cursors are useful when dealing with complex data manipulation tasks that cannot be achieved using standard SQL statements. They allow for more flexibility in processing data, making them valuable in specific scenarios.

Types of SQL Cursors 📝

SQL supports two types of cursors:

Implicit Cursors: They are automatically created by the SQL engine when executing a SELECT INTO statement. Implicit cursors are used for a single fetch operation and are not explicitly declared.
Explicit Cursors: These are manually declared cursors that can be opened, closed, and manipulated. Explicit cursors are used for multiple fetch operations.

Declaring an Explicit Cursor 💡

Here's an example of how to declare an explicit cursor:

sql

DECLARE cursor_name CURSOR FOR SELECT_statement;

Replace cursor_name with a unique identifier for your cursor and SELECT_statement with the SQL statement to be executed.

Opening and Closing Cursors 💡

Once a cursor is declared, it needs to be opened to begin processing the result set:

sql

OPEN cursor_name;

After the cursor has been used, it should be closed to release resources:

sql

CLOSE cursor_name;

Fetching Data from Cursors 💡

To fetch data from a cursor, use the FETCH statement:

sql

FETCH NEXT n FROM cursor_name;

Replace n with the number of rows to fetch (default is 1).

Example: Working with Cursors 💡

Let's create a simple example using an explicit cursor to update the salary of employees in a database.

sql

-- Declare the cursor
DECLARE emp_cursor CURSOR FOR
    SELECT employee_id, salary FROM employees WHERE salary < 50000;

-- Open the cursor
OPEN emp_cursor;

-- Define a variable to hold the fetched data
DECLARE @employee_id INT, @salary INT;

-- Loop through the cursor and update the salary of each employee
DECLARE CONTINUE HANDLER FOR NOT FOUND EXIT;

REPEAT
    -- Fetch the next row
    FETCH NEXT FROM emp_cursor INTO @employee_id, @salary;

    -- Update the salary
    UPDATE employees SET salary = @salary * 1.1 WHERE employee_id = @employee_id;

UNTIL NOT FOUND;

-- Close the cursor
CLOSE emp_cursor;

This example declares an explicit cursor that fetches employees with a salary less than 50,000. It then loops through the cursor, updating the salary of each employee by 10%.