Cursors in DBMS

In DBMS, a cursor is a very important tool that allows developers to work with only one piece of information at a time instead of dealing with lots of data all at once. It's handy for handling complicated queries and making quick updates to data. In this session, we will see how this tool is used for adding, updating, and removing information in Database Management Systems (DBMS).

What is a Cursor in DBMS?

A cursor is a short-term memory space formed in the computer's memory when a SQL statement runs. It contains details about a select statement and the data rows it traverses. This temporary space is employed to keep and adjust the retrieved data. While a cursor can grasp multiple rows, it processes only one at a time, known as the active set of rows because it is designed to fetch and handle data one row at a time, not the entire dataset simultaneously. The cursor is mainly beneficial for programmers managing individual records, especially for hard queries or real-time data updates.

It proves a handy tool for large result sets, enhancing data access efficiency without needing the complete dataset retrieval.

PL/SQL has two kinds of cursors:

• Implicit cursors.
• Explicit cursors.

Implicit Cursors

Implicit cursors are automatically generated by the database system when you run an SQL statement. They're like behind-the-scenes assistants handling single-row queries without any human intervention. These cursors get started, retrieve data, and finish their task on their own.

Whenever you perform basic database actions like INSERT, UPDATE, DELETE, or a SELECT that returns just one row, these cursors silently come into play.

The implicit cursor in DBMS comes with the following attributes:

• SQL%FOUND:
  This attribute is like a boolean flag. It returns TRUE if the most recent SQL statement affected at least one row, and FALSE if there was no impact. It is generated after the execution of the SELECT INTO command that retrieves one or more rows, or after DML actions like INSERT, DELETE, and UPDATE.

• SQL%NOTFOUND:
  This attribute is the reverse of SQL%FOUND. It returns TRUE if the recent SQL statement did not alter any rows and FALSE if it did. It is also used after a SELECT INTO or DML operation.

• SQL%ROWCOUNT:
  This attribute provides the count of rows affected by the last SQL statement. It's valuable for determining how many rows were updated, deleted, or inserted. After a SELECT INTO or DML operation, it shows the number of rows involved.

• SQL%ISOPEN:
  This attribute indicates whether the implicit cursor is currently active or open. However, for implicit cursors, it always returns FALSE. Implicit cursors automatically close after executing the related SQL statements, so they are never seen as open.

Let's now understand this with the help of an example of "emp" table:

This example updates the rows in the "emp" table based on the provided salary value. It then reports whether the update was successful, the number of rows updated, and the total row count with the help of the cursor attributes.

Output:

If the update is successful and affects three rows, the output might look like this:

If the update fails to affect any rows, the output could be:

Explicit Cursors

Explicit cursors are required when executing a SELECT statement with more than one row. Despite storing multiple records, these cursors process only one record at a time, known as the current row. When fetching a row, the current row position moves to the next one.

Programmers create explicit cursors in the declaration section of a PL/SQL Block, offering greater control over the result set than implicit cursors. They are commonly used when dealing with multi-row result sets, allowing programmers to manipulate data more flexibly within the PL/SQL environment.

Syntax

• Cursor Declaration:
  This is used to declare a cursor to initialize memory.

• Cursor Opening:
  This is used to open the cursor to allocate memory.

• Cursor Fetching:
  This is used to fetch data from the cursor into respective variables.

• Cursor Closing:
  This is used to close the cursor to release the allocated memory.

Let us now understand this concept with the help of an example:

The above code declares two variables (id, name) to store employee information and an explicit cursor named c_employees is declared to fetch data from the "employees" table.

Output:

This cursor opening (OPEN c_employees) allocates memory for the cursor, preparing it for fetching rows, and the cursor Closing (CLOSE c_employees) releases the allocated memory for the cursor.