Cursor Example

Simple Cursor with for loop
============================

create or replace procedure proc1(p_dno employees.department_id%TYPE)
is
cursor cur_emp is
select * from employees where department_id = p_dno;
begin
for emp_rec in cur_emp
loop
dbms_output.put_line('Emp id '||emp_rec.employee_id||' salary is '||emp_rec.salary);
end loop;
end;
/


==================================
Open Cursor-Close Cursor example
==================================

create or replace procedure proc2 as
cursor cur_emp(p_dno employees.department_id%type) is
select * from employees where department_id = p_dno;
rec_emp cur_emp%rowtype;
begin
open cur_emp(100);
dbms_output.put_line('Employees in department 100 are:');
loop
fetch cur_emp into rec_emp;
dbms_output.put_line('Employee Name: '|| rec_emp.last_name);
exit when cur_emp%NOTFOUND;
end loop;
close cur_emp;
open cur_emp(30);
dbms_output.put_line('Employees in department 30 are:');
loop
fetch cur_emp into rec_emp;
dbms_output.put_line('Employee Name: '|| rec_emp.last_name);
exit when cur_emp%NOTFOUND;
end loop;
close cur_emp;
end;
/

SQL> exec proc2();
Employees in department 100 are:
Employee Name: Greenberg
Employee Name: Faviet
Employee Name: Chen
Employee Name: Sciarra
Employee Name: Urman
Employee Name: Popp
Employee Name: Popp
Employees in department 30 are:
Employee Name: Raphaely
Employee Name: Khoo
Employee Name: Baida
Employee Name: Tobias
Employee Name: Himuro
Employee Name: Colmenares
Employee Name: Colmenares

PL/SQL procedure successfully completed.