SQL Cursors

SQL Cursors Interview with follow-up questions

1. What is a SQL Cursor and when is it used?

A SQL cursor is a database object that provides a mechanism to iterate over a query result set one row at a time, enabling row-by-row processing within procedural code.

Standard SQL is set-based — queries operate on and return entire result sets. Cursors bridge the gap when you need to process each row individually with conditional logic between rows.

Cursor lifecycle:

  1. DECLARE — define the cursor with a SELECT query
  2. OPEN — execute the query and populate the result set
  3. FETCH — retrieve the next row into variables
  4. Process the row
  5. Loop back to FETCH until no more rows
  6. CLOSE — release the result set
  7. DEALLOCATE — free the cursor object

Example (SQL Server):

DECLARE @emp_id INT, @salary DECIMAL(10,2);

DECLARE emp_cursor CURSOR FOR
    SELECT employee_id, salary FROM employees WHERE department_id = 5;

OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @emp_id, @salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process each row
    PRINT 'Employee: ' + CAST(@emp_id AS VARCHAR) + ', Salary: ' + CAST(@salary AS VARCHAR);
    FETCH NEXT FROM emp_cursor INTO @emp_id, @salary;
END;

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

When cursors are appropriate:

  • Complex row-by-row processing that cannot be expressed as set operations
  • Sequential operations where each row's result depends on the previous
  • Calling a stored procedure or external API for each row

When to avoid them: Cursors are slow compared to set-based SQL. If an operation can be expressed as a single UPDATE, INSERT...SELECT, or window function, always prefer that. Cursor-based loops on large tables are a common performance anti-pattern.

↑ Back to top

Follow-up 1

Can you describe the life cycle of a SQL Cursor?

The life cycle of a SQL Cursor typically involves the following steps:

  1. Declaration: The cursor is declared and associated with a specific SELECT statement.
  2. Opening: The cursor is opened, which executes the associated SELECT statement and creates a result set.
  3. Fetching: Rows from the result set are fetched one at a time using the FETCH statement.
  4. Processing: Each fetched row is processed as required.
  5. Closing: The cursor is closed to release the resources associated with it.

Follow-up 2

What are the different types of SQL Cursors?

There are three types of SQL Cursors:

  1. Forward-only Cursors: These cursors can only move forward through the result set. They do not support scrolling or moving backward.
  2. Scrollable Cursors: These cursors allow you to move both forward and backward through the result set. They support scrolling and positioning at any row.
  3. Dynamic Cursors: These cursors reflect all changes made to the rows in the result set, even if the changes occur after the cursor is opened.

Follow-up 3

What are the advantages and disadvantages of using SQL Cursors?

Advantages of using SQL Cursors:

  • Cursors allow you to process rows individually, which can be useful for performing row-level operations.
  • Cursors provide a way to iterate over a large result set in a step-by-step manner, which can help conserve memory.

Disadvantages of using SQL Cursors:

  • Cursors can be slower than set-based operations, especially when processing large result sets.
  • Cursors require additional resources and can lead to increased network traffic.
  • Cursors can be more complex to use and maintain compared to set-based operations.

Follow-up 4

Can you provide an example of a situation where a SQL Cursor would be necessary?

One example of a situation where a SQL Cursor would be necessary is when you need to perform row-level updates or deletions based on certain conditions. For example, let's say you have a table of employees and you want to update the salary of all employees who have been with the company for more than 5 years. In this case, you can use a cursor to fetch each employee row, check the years of service, and update the salary if the condition is met. This allows you to process each row individually and perform the necessary updates.

2. How do you declare a cursor in SQL?

Cursor declaration syntax varies by database. The general pattern is to name the cursor and associate it with a SELECT statement.

SQL Server (T-SQL):

DECLARE employee_cursor CURSOR
    [LOCAL | GLOBAL]
    [FORWARD_ONLY | SCROLL]
    [STATIC | DYNAMIC | KEYSET | FAST_FORWARD]
    [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
FOR
    SELECT employee_id, first_name, salary
    FROM employees
    WHERE department_id = 10;

PostgreSQL (PL/pgSQL — must be inside a function or DO block):

DO $$
DECLARE
    emp_record RECORD;
    emp_cursor CURSOR FOR
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 10;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO emp_record;
        EXIT WHEN NOT FOUND;
        -- process emp_record
        RAISE NOTICE 'Employee: %', emp_record.first_name;
    END LOOP;
    CLOSE emp_cursor;
END;
$$;

MySQL (inside a stored procedure):

DELIMITER //
CREATE PROCEDURE process_employees()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_id INT;
    DECLARE v_name VARCHAR(100);

    DECLARE emp_cursor CURSOR FOR
        SELECT employee_id, first_name FROM employees;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN emp_cursor;
    read_loop: LOOP
        FETCH emp_cursor INTO v_id, v_name;
        IF done THEN LEAVE read_loop; END IF;
        -- process row
    END LOOP;
    CLOSE emp_cursor;
END //
DELIMITER ;

Key point: In MySQL, the CONTINUE HANDLER FOR NOT FOUND is the standard way to detect end-of-cursor. PostgreSQL uses NOT FOUND system variable or EXIT WHEN NOT FOUND.

↑ Back to top

Follow-up 1

What happens when you open a cursor?

When you open a cursor in SQL, the SELECT statement specified in the cursor declaration is executed and the result set is made available for fetching. The cursor is positioned before the first row of the result set.

Follow-up 2

How do you fetch rows from a cursor?

To fetch rows from a cursor in SQL, you use the FETCH statement. There are different ways to fetch rows:

  • FETCH NEXT FROM cursor_name INTO variable1, variable2; fetches the next row from the cursor and assigns the values to the specified variables.
  • FETCH PRIOR FROM cursor_name INTO variable1, variable2; fetches the previous row from the cursor.
  • FETCH FIRST FROM cursor_name INTO variable1, variable2; fetches the first row from the cursor.
  • FETCH LAST FROM cursor_name INTO variable1, variable2; fetches the last row from the cursor.
  • FETCH ABSOLUTE n FROM cursor_name INTO variable1, variable2; fetches the row at the specified absolute position.
  • FETCH RELATIVE n FROM cursor_name INTO variable1, variable2; fetches the row at the specified relative position.

Follow-up 3

What is the purpose of the CLOSE statement in cursor operations?

The CLOSE statement in cursor operations is used to close a cursor that has been opened. When you close a cursor, the system resources associated with the cursor are released. Here's an example:

CLOSE cursor_name;

Follow-up 4

What happens if you don't close a cursor?

If you don't close a cursor in SQL, the system resources associated with the cursor may not be released, leading to potential memory leaks or other resource-related issues. It is good practice to always close a cursor after you have finished using it.

3. What is the difference between a static cursor and a dynamic cursor?

Cursor types determine how the cursor sees data changes that occur after the cursor is opened. The four main types (most explicitly available in SQL Server) are:

Static cursor

  • Takes a snapshot of the result set at the time OPEN is called
  • Changes made to the underlying data after opening are not visible
  • Supports bidirectional scrolling (FETCH PRIOR, FETCH ABSOLUTE, etc.)
  • Stores the snapshot in tempdb — higher memory use
  • Use when: you need a stable, unchanging view of data while processing

Dynamic cursor

  • Reflects all changes to the data while the cursor is open (inserts, updates, deletes)
  • Every FETCH may see different data
  • Supports bidirectional scrolling
  • Most resource-intensive — must check data on every fetch
  • Use when: you need to see real-time changes to the result set

Keyset cursor (SQL Server)

  • Membership of the result set is fixed at open time (which rows are included)
  • But changes to existing rows are visible when fetched
  • Deleted rows appear as a "hole" (@@FETCH_STATUS = -2)
  • A middle ground between static and dynamic

Forward-only cursor (also called FAST_FORWARD in SQL Server)

  • Can only fetch rows sequentially in one direction (next)
  • No scrolling backward
  • Most efficient cursor type — minimal overhead
  • MySQL only supports forward-only cursors (implicit)
Type Sees new rows Sees changed rows Sees deleted rows Direction
Static No No No Both
Dynamic Yes Yes Yes Both
Keyset No Yes Hole Both
Forward-only No Varies Varies Forward only
↑ Back to top

Follow-up 1

In what situations would you use a static cursor over a dynamic cursor?

A static cursor is useful when you need to retrieve a snapshot of the data at a specific point in time and do not require the ability to scroll backwards or modify the data. It can be more efficient in terms of memory usage and performance compared to a dynamic cursor, especially when dealing with large result sets.

Follow-up 2

What are the performance implications of using a dynamic cursor?

Using a dynamic cursor can have performance implications, especially when dealing with large result sets. Since a dynamic cursor reflects any changes made to the data while the cursor is open, it may require additional resources to track and update the cursor position. This can result in increased memory usage and slower performance compared to a static cursor.

Follow-up 3

Can you provide an example of a situation where a dynamic cursor would be necessary?

One situation where a dynamic cursor would be necessary is when you need to perform updates or deletions on the underlying data while iterating through the cursor. For example, if you have a cursor that represents a set of records to be processed and you need to update certain fields in each record as you iterate through the cursor, a dynamic cursor would allow you to do so. Here's an example in SQL Server:

DECLARE @Cursor CURSOR

SET @Cursor = CURSOR DYNAMIC FOR
SELECT * FROM TableName

OPEN @Cursor

FETCH NEXT FROM @Cursor INTO @Variable

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Perform updates or deletions on the underlying data
    -- ... 
    FETCH NEXT FROM @Cursor INTO @Variable
END

CLOSE @Cursor
DEALLOCATE @Cursor

4. What is a cursor variable in SQL?

A cursor variable (also called a ref cursor in Oracle/PostgreSQL) is a variable that holds a reference to a cursor — allowing the cursor to be opened, passed between procedures, and used dynamically.

Unlike a regular cursor (which is defined with a fixed SELECT statement at declaration), a cursor variable can be associated with different queries at runtime.

Oracle / PL/SQL (SYS_REFCURSOR):

CREATE OR REPLACE PROCEDURE get_employees(
    p_dept_id IN NUMBER,
    p_cursor  OUT SYS_REFCURSOR
)
AS
BEGIN
    OPEN p_cursor FOR
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = p_dept_id;
END;

The calling code receives the open cursor and fetches from it — useful for returning result sets from stored procedures.

PostgreSQL (refcursor):

CREATE OR REPLACE FUNCTION get_employees(p_dept_id INT)
RETURNS refcursor AS $$
DECLARE
    cur refcursor;
BEGIN
    OPEN cur FOR
        SELECT employee_id, first_name FROM employees WHERE department_id = p_dept_id;
    RETURN cur;
END;
$$ LANGUAGE plpgsql;

Key differences from regular cursors:

  • A regular cursor is bound to a specific query at declaration time
  • A cursor variable can be bound to different queries at runtime (dynamic)
  • Cursor variables can be passed as parameters between procedures
  • In Oracle and PostgreSQL, they enable returning result sets from stored procedures to the calling application

Practical relevance: Cursor variables are primarily an Oracle and PostgreSQL concept. In SQL Server, OUTPUT parameters with table types or sp_executesql are used for similar purposes.

↑ Back to top

Follow-up 1

How does a cursor variable differ from a regular cursor?

A cursor variable differs from a regular cursor in the following ways:

  1. A regular cursor is associated with a specific query, while a cursor variable can be associated with different queries at different times.
  2. A regular cursor is declared using the CURSOR keyword, while a cursor variable is declared using the REF CURSOR type.
  3. A regular cursor is opened and closed explicitly using OPEN and CLOSE statements, while a cursor variable is opened and closed using the OPEN and CLOSE methods of the cursor variable.
  4. A regular cursor can only be used within the scope of the PL/SQL block where it is declared, while a cursor variable can be passed as a parameter to other PL/SQL blocks or stored procedures.

Follow-up 2

What are the benefits of using cursor variables?

Using cursor variables in SQL has several benefits:

  1. Reusability: Cursor variables can be associated with different queries at different times, allowing for code reuse.
  2. Flexibility: Cursor variables can be passed as parameters to other PL/SQL blocks or stored procedures, enabling dynamic query execution.
  3. Reduced memory usage: Cursor variables consume less memory compared to regular cursors, as they only hold a reference to the result set rather than the entire result set.
  4. Improved performance: Cursor variables can be used to fetch and manipulate rows one at a time, which can be more efficient than fetching the entire result set at once.

Follow-up 3

Can you provide an example of how to use a cursor variable?

Certainly! Here's an example of how to use a cursor variable in SQL:

DECLARE
  TYPE emp_curtype IS REF CURSOR;
  emp_cursor emp_curtype;
  emp_record employees%ROWTYPE;
BEGIN
  OPEN emp_cursor FOR SELECT * FROM employees;
  LOOP
    FETCH emp_cursor INTO emp_record;
    EXIT WHEN emp_cursor%NOTFOUND;
    -- Process the current row
    DBMS_OUTPUT.PUT_LINE(emp_record.employee_id || ' ' || emp_record.first_name || ' ' || emp_record.last_name);
  END LOOP;
  CLOSE emp_cursor;
END;

In this example, a cursor variable emp_cursor of type emp_curtype is declared. The emp_cursor is then opened to hold the result set of the query SELECT * FROM employees. The rows are fetched one at a time into the emp_record variable, and the details of each employee are printed using DBMS_OUTPUT.PUT_LINE. Finally, the cursor variable is closed.

5. What is the difference between a cursor and a SELECT statement in SQL?

A SELECT statement and a cursor both retrieve data, but they operate at different abstraction levels:

SELECT statement:

  • Returns the entire result set at once to the caller
  • Set-based — the database engine processes all rows in one optimized operation
  • The result can be processed by the client application or used in further SQL (subqueries, CTEs, JOINs)
  • Fast and efficient — the query optimizer can use indexes, parallelism, and join strategies on the full set

Cursor:

  • Wraps a SELECT and retrieves rows one at a time (or in small batches) via FETCH
  • Procedural — you loop through rows with explicit control flow
  • Requires DECLARE, OPEN, FETCH, CLOSE, DEALLOCATE lifecycle management
  • Significantly slower for large data sets — each FETCH is a round-trip to the result set
Aspect SELECT Cursor
Processing model Set-based Row-by-row
Performance Optimized by query planner Much slower at scale
Use case Most data retrieval Complex per-row logic
Code complexity Simple Verbose, more error-prone
Parallelism Yes (planner can parallelize) No

When to use a cursor over a SELECT:

  • You need to execute a stored procedure or trigger an external action for each row
  • Processing logic is conditional on the values of previous rows
  • The operation genuinely cannot be expressed as a set operation

Best practice: Before writing a cursor, ask whether the problem can be solved with a window function, UPDATE ... FROM, INSERT ... SELECT, or a CTE. Set-based solutions should always be the first choice.

↑ Back to top

Follow-up 1

Why would you use a cursor instead of a SELECT statement?

There are a few scenarios where using a cursor might be more appropriate than a SELECT statement:

  1. When you need to perform row-by-row processing: Cursors allow you to process each row individually, which can be useful when you need to perform complex calculations or apply business logic to each row.

  2. When you need to update or delete rows based on certain conditions: Cursors provide a way to navigate through a result set and perform updates or deletions based on specific criteria.

  3. When you need to retrieve a subset of data: Cursors can be used to retrieve a subset of data from a larger result set, allowing you to work with a smaller set of records at a time.

Follow-up 2

What are the performance implications of using a cursor over a SELECT statement?

Using a cursor can have performance implications compared to a SELECT statement:

  1. Increased overhead: Cursors require additional resources to maintain the cursor state and navigate through the result set. This can result in increased memory and CPU usage.

  2. Slower processing: Cursors process data row by row, which can be slower compared to retrieving the entire result set with a SELECT statement. This is especially true when dealing with large result sets.

  3. Locking and blocking: Cursors can hold locks on the underlying data, which can lead to blocking other transactions that need access to the same data.

It's important to consider these performance implications and evaluate whether using a cursor is necessary for your specific use case.

Follow-up 3

Can you provide an example of a situation where a cursor would be more appropriate than a SELECT statement?

Sure! Let's say you have a table called 'Orders' with the following columns: 'OrderID', 'CustomerID', and 'OrderDate'. You want to calculate the total number of orders for each customer and update a separate 'Customer' table with this information.

Here's an example of how you could use a cursor to achieve this:

DECLARE @CustomerID INT;
DECLARE @OrderCount INT;

DECLARE OrderCursor CURSOR FOR
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Orders
GROUP BY CustomerID;

OPEN OrderCursor;

FETCH NEXT FROM OrderCursor INTO @CustomerID, @OrderCount;

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Customer
    SET TotalOrders = @OrderCount
    WHERE CustomerID = @CustomerID;

    FETCH NEXT FROM OrderCursor INTO @CustomerID, @OrderCount;
END

CLOSE OrderCursor;
DEALLOCATE OrderCursor;

In this example, the cursor is used to iterate over the result set of the SELECT statement, calculate the order count for each customer, and update the 'Customer' table accordingly. This row-by-row processing is more appropriate in this case because it involves performing calculations and updating data based on specific conditions.

Live mock interview

Mock interview: SQL Cursors

Intermediate ~5 min Your own free AI key

Your voice and your AI key never touch our servers; the key stays in this browser and is sent only to Google. Only your round scores are saved to track progress.