What is a PL/SQL cursor?

* A PL/SQL cursor is nothing but a pointer to an area of memory having SQL statements and the information of statement processing. This memory area is called a context area. This special area makes use of a special feature called cursor for the purpose of retrieving and processing more than one row.

* In short, the cursor selects multiple rows from the database and these selected rows are individually processed within a program.

* There are two types of cursors:

Implicit Cursor :

* Oracle automatically creates a cursor while running any of the commands - SELECT INTO, INSERT, DELETE or UPDATE implicitly.

* The execution cycle of these cursors is internally handled by Oracle and returns the information and status of the cursor by making use of the cursor attributes- ROWCOUNT, ISOPEN, FOUND, NOTFOUND.

Explicit Cursor :

* This cursor is a SELECT statement that was declared explicitly in the declaration block.

* The programmer has to control the execution cycle of these cursors starting from OPEN to FETCH and close.

* The execution cycle while executing the SQL statement is defined by Oracle along with associating a cursor with it.

Explicit Cursor Execution Cycle :

* Due to the flexibility of defining our own execution cycle, explicit cursors are used in many instances. The following diagram represents the execution flow of an explicit cursor:

Cursor Declaration :

* The first step to use an explicit cursor is its declaration.

* Declaration can be done in a package or a block.

* Syntax : CURSOR cursor_name IS query; where cursor_name is the name of the cursor, the query is the query to fetch data from any table.

Open Cursor :

* Before the process of fetching rows from cursor, the cursor has to be opened.

* Syntax to open a cursor: OPEN cursor_name;

* When the cursor is opened, the query and the bind variables are parsed by Oracle and the SQL statements are executed.

* The execution plan is determined by Oracle and the result set is determined after associating the cursor parameters and host variables and post these, the cursor is set to point at the first row of the result set.

Fetch from cursor :

* FETCH statement is used to place the content of the current row into variables.

* Syntax : FETCH cursor_name INTO variable_list;

* In order to get all the rows of a result set, each row needs to be fetched.

Close Cursor :

* Once all the rows are fetched, the cursor needs to be closed using the CLOSE statement.

* Syntax : CLOSE cursor_name;

* The instructions tell Oracle to release the memory allocated to the cursor.

* Cursors declared in procedures or anonymous blocks are by default closed post their execution.

* Cursors declared in packages need to be closed explicitly as the scope is global.

* Closing a cursor that is not opened will result in INVALID_CURSOR exception.