What is a Cursor

A cursor is a temporary  context  area created in the system  when we executes  SQL

statements.

A cursor contains information on a select statement and the rows of data accessed by it.

This temporary work area is used to hold  the data retrieved from the database, and

manipulate this data. A cursor can hold more than one row, but can process only one row

at a time. The set of rows the cursor holds is called the active set.

There are two types of cursors in PL/SQL:

There are two types of cursors - implicit and explicit cursor.

Implicit cursor: Oracle automatically  processes the information of SQL statement

executed. In this process, the user is not aware about  implicit cursor. These actions Oracle

automatically performs the OPEN, FETCH, and CLOSE operations.

Explicit cursor: Explicit  cursors are explicitly declared in the DECLARE section of the

PL/SQL block. This declaration allows to process each row of data as the cursor returns it.

In explicit cursor DECLARE,OPEN,FETCH,and CLOSE operations are done by the

programmer.

The process of working with an explicit cursor:

Declare: The cursor is initialised into temporary memory area.
Open: The cursor is opened which is declared, and the temporary memory area is allotted.
Fetch: Cursor which is declared and opened can now retrieve rows from data.
Close: The CLOSE statement disables the cursor, and releases the temporary memory area.





The syntax for creating an explicit cursor is :

CURSOR cursor_name IS select_statement;

Explicite  cursor involves four steps:

1.Declaring the cursor for initializing in the memory
2. Opening the cursor for allocating memory
3.  Fetching the cursor for retrieving data
4.Closing the cursor to release allocated memory

--When the above code is executed at SQL prompt, it produces the following result:
declare
dno number;
eno employees.employee_id%Type;
fname employees.first_name%Type;
Jobid  employees.job_id%Type;
cursor emprec is select employee_id,first_name,job_id from employees
where department_id=&dno;
Begin
open emprec;
loop
fetch  emprec into  eno,fname,jobid ;
dbms_output.put_line('Empno '||eno||'  '||fname||'  '||' '||jobid);
exit when emprec%Notfound;
end loop;
close emprec;
End;
/


Enter value for dno: 30
old   7: where department_id=&dno;
new   7: where department_id=30;

Empno 114  Den   PU_MAN
Empno 115  Alexander   PU_CLERK
Empno 116  Shelli   PU_CLERK
Empno 117  Sigal   PU_CLERK
Empno 118  Guy   PU_CLERK
Empno 119  Karen   PU_CLERK
Empno 119  Karen   PU_CLERK