PL/SQL CURSORS

CURSORS:

1.Cursor is a Temporary sql work area for its internal processing in order to execute sql statements.
2.This work area is private to sql's operations and is called CURSOR.
3.The data that is stored in the cursor is called the'ACTIVE DATA SET'.
4.So, Cursor size depends on the number of records holds in the ACTIVE DATA SET.
5.The values retrieved from a table are held in a cursor opened in memory by Oracle Engine.
6.This data is then transferred to the client machine via network.
7.In order to hold this data, a cursor is opened at the client end.

SELECT * FROM EMP WHERE DEPTNO=20;

When a cursor loads multiple rows,oracle engine opens and maintains a row pointer.

TYPES OF CURSOR:

Cursors are classified into two types depending on the circumstance under which they are opened.
1)IMPLICIT CURSOR:If the Oracle Engine opens a cursor for its internal processing is called IMPLICIT CURSOR.
  EXAMPLE:SQL
2)EXPLICIT CURSOR:If the user opened the cursor for processing the data through pl/sql block on demand,then it is known as EXPLICIT CURSOR.

Whenever we are working with cursor we need to perform the following operations.
*Declare cursor
*Open Cursor
*Fetch the records from the cursor
*close cursor

Syntax to declare the cursor: CURSOR <cursor_name> IS SELECT * FROM <table_name>;
Example: CURSOR c IS SELECT * FROM emp WHERE DEPTNO=10;

Syntax to open the cursor: OPEN <cursor_name>;
Example: OPEN c;

Syntax to fetch the records:FETCH <cursor_name> INTO <list_of_variables>;
Example:FETCH c into X,Y,Z;

Syntax to close the cursor: CLOSE c;
Example: CLOSE c;