PL/SQL Cursor examples

example 1 - In this example we use cursor to fetch the name of the employee whose department is IM.

declare
temp varchar2(20);
  cursor emp_details is
  select emp_details from employee where emp_dept ='IM';
begin
  open emp_details;
 
  Loop
  fetch emp_details into temp;
  if emp_details%NOTFOUND
   then exit;
   end if;
   dbms_output.put_line(temp);
  end Loop;
  close emp_details;
end; 

example 2-  In this example we use cursor to fetch the Address of the employee whose department is IM with the help of procedures.

 create or replace procedure fetchthat(employee_depo in varchar2)

AS 

temp employee.emp_address%type;

  cursor c1 is

  select emp_address  from employee where emp_dept = employee_depo;

begin

  open c1;

loop

  fetch c1 into temp;

 exit when c1%NOTFOUND;

 dbms_output.put_line(temp);

 END LOOP;

 dbms_output.put_line('Total Rows Fecthed = ' || c1%rowcount);

  close c1;

  end;  

-------------Procedure Created

declare 

emp_dep varchar2(20) := 'IM';

begin

fetchthat(emp_dep);           ---------Calling Procedure

end;

/