Introduction

A cursor is a pointer that leads to a query’s result. There are two sorts of cursors in PL/SQL: implicit and explicit cursors.

What is a cursor in Oracle

In PL/SQL, cursors are used. For each SQL statement, Oracle constructs a memory region known as the context area, which holds all the information needed to process the query, for example, the number of rows processed, etc.

This context area is indicated by a cursor. A cursor is used by PL/SQL to control the context region. The rows (one or more) returned by a SQL statement are stored in a cursor. The active set refers to the set of rows that the cursor is now holding.

Types of Cursors

A cursor can be given a name so that it can be used in a program to retrieve and process the rows of a SQL statement one by one. Cursors are divided into two categories.

1) Explicit cursors

A SELECT statement that is declared explicitly in the current block’s declaration section or a package specification is known as an explicit cursor.

From OPEN, FETCH, and CLOSE, you can manage the execution cycle of an explicit cursor.

Oracle specifies an execution cycle in which a SQL statement is executed, and a cursor is associated with it.

2) Implicit cursor

Oracle creates an implicit cursor whenever it executes a SQL command like SELECT INTO, INSERT, UPDATE, or DELETE.

When the query returns zero or numerous rows, the implicit cursor is inefficient, resulting in the NO DATA FOUND or TOO MANY ROWS exceptions.

How to declare a cursor in Oracle

Declaring the cursor offers it a name and the SELECT statement that goes with it.

For example:

How to use cursor in a function

Let’s take an example to understand how to use cursor in a function, Consider there are two tables Orders and Order_Items

The sentence below creates a view that returns customer sales revenues:

CREATE VIEW sales AS
SELECT customer_id,
       SUM(unit_price * quantity) total,
       ROUND(SUM(unit_price * quantity) * 0.05) credit
FROM order_items
INNER JOIN orders USING (order_id)
WHERE status = 'Shipped'
GROUP BY customer_id;

How to open a cursor

The cursor’s memory is allocated when it is opened, and it is ready to receive the rows produced by the SQL statement. For instance, let’s open the above-mentioned cursor as follows:

OPEN c_employees

How to fetch rows from a cursor

By accessing one row at a time, the cursor may be found. For instance, we can get rows from the above-opened by executing the following query:

FETCH c_employees INTO c_id,c_name,c_addr;

How to close a cursor

When the cursor is closed, the allocated memory is released. For example, the above-opened cursor will be closed as follows:

CLOSE c_employees;

Cursor within cursor

Let’s see an example:

create or replace procedure MULTIPLE_CURSORS_PROC is
               v_owner varchar2(40);
               v_table_name varchar2(40);
               v_column_name varchar2(100);
cursor firstCursor is
select distinct tbl.owner, tbl.table_name
from all_tables tbl
where tbl.owner = 'SYSTEM';
cursor secondCursor is
select distinct col.column_name
from all_tab_columns col
where col.owner = v_owner
and col.table_name = v_table_name;
begin
open firstCursor;
loop
fetch firstCursor into v_owner, v_table_name;
dbms_output.put_line('v_owner: '||v_owner);
dbms_output.put_line('v_table_name: '||v_table_name);
open secondCursor;
loop
fetch secondCursor into v_column_name;
dbms_output.put_line('v_column_name: '||v_column_name);
end loop;
close secondCursor;
end loop;
close firstCursor;
end
  /
SQL>     exec MULTIPLE_CURSORS_PROC();