Explicit Cursor Example

The following short example of a PL/SQL anonymous block prints the names of
 all employees in  specified department  in the Employees  table,
 using the DBMS_OUTPUT package:

The syntax for creating an explicit cursor is :

CURSOR cursor_name IS select_statement;
Working with an explicit cursor involves four steps:

Declaring the cursor for initializing in the memory

Opening the cursor for allocating memory

Fetching the cursor for retrieving data

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