PL/SQL Cursors

Whenever you try to execute an SQL statement, Oracle processes it in a separate memory zone called as ‘Context Area’. It contains information regarding information retrieved from database tables/records, SQL query, number of rows accessed by it and many more. This Context Area is controlled by a ‘Cursor’. A PL/SQL cursor can hold one or more rows returned by an SQL statement query. The set of rows that a Cursor holds at a single point of time is called as an ‘Active Set’.

A Cursor is basically a pointer to the Context Area. PL/SQL block controls the context area using a cursor. A cursor holds the one or more rows returned by a PL/SQL statement. Cursors and can be named as well as be kept anonymous. Named cursors can help for its reference so that it can be fetched in a program later on and process the rows returned by the SQL statement one by one. 

In Oracle SQL, there are two types of cursors:

1. Implicit cursors
2. Explicit cursors

Implicit Cursors

The Implicit Cursor is the Default Cursor in PL/SQL blocks. These are created when there is no explicit cursor for the statement or when PL/SQL encounters a SELECT statement that returns just one row and also when Data Manipulation Language (DML) statements like DELETE, INSERT and UPDATE are encountered. 

Some of the PL/SQL attributes that are used frequently are mentioned below:

• %ROWCOUNT: It Returns the number of rows influenced by an UPDATE, DELETE or an INSERT statement.
• %NOTFOUND: It Returns TRUE if an UPDATE, DELETE or an INSERT statement influenced zero rows or a SELECT INTO statement returned no rows. Else, it Returns a FALSE.
• %FOUND: It Returns TRUE if an UPDATE, DELETE or an INSERT statement influenced one or more rows or a SELECT INTO statement returned one or more rows. Else, it returns FALSE.
• %ISOPEN: It Returns FALSE for Implicit cursors as Oracle closes the PL/SQL cursor by default after processing its associated PL/SQL statement.

Explicit Cursors

Explicit cursors are used if you need to have better control over the Context Area via Cursor. They should be used when you are executing a SELECT statement query that will return more than one row. Cursors can process one record at a given point of time even though it stores more than one record. An explicit cursor is defined in the declaration section of the PL/SQL Block. Explicit Cursor and Implicit Cursor both have the same output, but accessing is different.

Syntax For An Explicit Cursor

Cursor Cursor-Name
IS
Select-Statement;

Processing an Explicit Cursor involves the following steps:

1. Initialize the Cursor: Initializing the Cursor also means the Declaration of the cursor in the Declaration Section of a PL/SQL Block. The name of the cursor requires to be defined along with the SELECT Statement.

Example

CURSOR admin_no IS SELECT ano, aname from  Admin;

2. Open the Cursor: Opening the Cursor also means allocating the memory for the Cursor in the Context Area which thereby makes it sufficient to fetch and store records in it.

Example

OPEN admin_no;

3. Fetch the Cursor: Fetching the Cursor involves retrieval of data using the Fetch statement. It is used to help the Cursor process and access records or rows at a time. 

Example

FETCH a_admin 
INTO
a_ano, a_aname;

4. Close the Cursor: Once the work associated for a Cursor to be completed is accomplished, it is necessary to release the Allocated Memory of the Cursor to let other tasks occupy memory with any memory down-time.

Example

CLOSE Admin_no;

Program to write a Cursor to list all the Administrators in the Database Table named as Admin

declare
CURSOR c1 IS
SELECT aname from Admin;
rec c1%rowtype;
begin
for rec in c1
loop
dbms_output.put_line(rec.aname);
end loop;
end;
/