PL/SQL Cursors

In PL/SQL, Cursor area also saying session cursor. because session cursor store information until the session end. Both way you can manage session cursor either implicit cursor or explicit cursor.

Using procedural statement you can get any information using session attribute.


How to Use Cursor

In PL/SQL block SELECT statement can not return more than one row at a time. So Cursor use to some group of rows (more than one row) for implementing certain logic to get all the group of records.


Classification of CURSORS

Cursors can be classified as:

  1. Implicit Cursor or Internal Cursor Manage for Oracle itself or internal process itself.
  2. Explicit Cursor or User-defined Cursor Manage for user/programmer or external processing.

Implicit Cursor

Oracle uses implicit cursors for its internal processing. Even if we execute a SELECT statement or DML statement Oracle reserves a private SQL area in memory called cursor.

Implicit cursor scope you can get information from cursor by using session attributes until another SELECT statement or DML statement execute. Read More...


Explicit Cursor

Explicit Cursor which are construct/manage by user itself call explicit cursor.

User itself to declare the cursor, open cursor to reserve the memory and populate data, fetch the records from the active data set one at a time, apply logic and last close the cursor.

You can not directly assign value to an explicit cursor variable you have to use expression or create subprogram for assign value to explicit cursor variable.

Step for Using Explicit Cursor

  1. Declare cursor
  2. Open cursor
  3. Loop
  4. Fetch data from cursor
  5. Exit loop
  6. Close cursor