Introduction to Database Cursors

Row-by-Row Processing

Databases are designed to work with sets of data. When you run a SELECT query, you don't get one row back at a time; you get the entire collection of rows that match your criteria. This is called a result set. Set-based operations are powerful and efficient for most tasks, like calculating a total or finding an average.

But what if you need to handle each row individually? Imagine you need to perform a complex, multi-step validation for every single customer in a query's result set, with each step depending on the last. Handling this with a single query would be difficult. This is where cursors come in.

cursor

noun

A pointer to a private memory area that holds the result set of a SQL query, allowing an application to process the rows one at a time.

Think of a cursor as a bookmark in your result set. It lets you navigate through the rows, pausing at each one to perform specific actions before moving to the next. This gives you fine-grained control over your data processing.

Implicit vs Explicit Cursors

There are two main types of cursors, and you've likely used one without even knowing it.

Implicit Cursors An implicit cursor is automatically created and managed by the database system every time you run a data manipulation statement like INSERT, UPDATE, or DELETE. The system uses it behind the scenes to process the statement. You don't declare or control it. It's the database's internal tool for getting the job done.

Explicit Cursors An explicit cursor is one you, the developer, define and manage in your code. You create it to handle the result set of a SELECT statement that returns multiple rows. When you need to loop through results and process each one individually, you'll use an explicit cursor. These are the cursors we'll focus on.

The Cursor Lifecycle

Working with an explicit cursor involves four distinct steps. You can think of it as a lifecycle: you bring the cursor into existence, use it, and then clean it up.

Let's look at each step with a simple example in PL/SQL, a common procedural language for SQL.

Step 1: DECLARE First, you declare the cursor and associate it with a query. This step names the cursor and defines the data it will hold, but it doesn't run the query yet.

-- Declaring a cursor to get employee names
DECLARE
  -- A variable to hold the fetched employee name
  v_employee_name employees.first_name%TYPE;

  -- The cursor declaration
  CURSOR c_employees IS
    SELECT first_name FROM employees WHERE department_id = 50;

Here, c_employees is the name of our cursor. It's tied to a SELECT statement that will retrieve the first names of all employees in department 50.

Step 2: OPEN Next, you open the cursor. This action executes the SELECT query. The database finds all matching rows and makes them available for processing.

BEGIN
  -- Open the cursor to execute the query
  OPEN c_employees;

Once opened, the cursor points to a position just before the first row of the result set.

Step 3: FETCH This is where the row-by-row processing happens. The FETCH command retrieves the current row and advances the cursor to the next one. You'll typically place this inside a loop to process every row in the result set.

LOOP
  -- Fetch the data from the current row into a variable
  FETCH c_employees INTO v_employee_name;

  -- Exit the loop if there are no more rows to fetch
  EXIT WHEN c_employees%NOTFOUND;

  -- Process the fetched data (e.g., print the name)
  DBMS_OUTPUT.PUT_LINE(v_employee_name);
END LOOP;

The loop continues until %NOTFOUND becomes true, meaning the last fetch didn't return a row.

Step 4: CLOSE Finally, after you're done fetching, you must close the cursor. This is a crucial step that releases the memory and resources held by the result set. Forgetting to close a cursor can lead to memory leaks and performance problems.

  -- Close the cursor to free up resources
  CLOSE c_employees;
END;

Advantages and Disadvantages

Cursors are a specialized tool, and it's important to know when to use them.

The main advantage of cursors is control. They allow for complex, stateful processing of each row that would be impossible with standard set-based SQL. For example, you could use a cursor to read through a list of transactions, calculating a running total that resets based on certain conditions in the data.

The biggest disadvantage is performance. Processing data row by row is inherently slower and more resource-intensive than a set-based operation. Each fetch requires communication between the SQL engine and the procedural code, creating overhead. Whenever a task can be accomplished with a single SQL statement, that is almost always the better option.

Rule of thumb: If you can do it with a SELECT, JOIN, or other set-based operation, do that. Only turn to cursors when you truly need to inspect and act on each row individually.

Understanding how to use cursors adds a powerful tool to your database programming toolkit, especially for complex procedural tasks.

Mastering Database Cursors