Table of Contents
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.
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:
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:
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();