Example for Creating FOR LOOP Cursor statement in Oracle

For using FOR LOOP Cursor, all the thing implicitly done like cursor variable, opening of cursor, fetching and closing of the cursor. It’s easy way to write the code and use explicit cursor.

Syntax FOR LOOP Cursor statement:

DECLARE
CURSOR <cursor_name> IS <SELECT statement>;
BEGIN
  FOR n IN <cursor_name>
  LOOP
  .....
  END LOOP;
END;

Example of FOR LOOP Cursor Statement:

set serveroutput on
Declare 
--Create cursor in declare section
cursor emp_curr is select first_name from employees;
begin
for emp_val in emp_curr 
loop
dbms_output.put_line(' Employee Name: '|| emp_val.First_name);
end loop;
end;
/

Example of Cursor with FOR LOOP directly in FOR statement:

set serveroutput on
Declare 
begin
for emp_val in (select first_name from employees); 
loop
dbms_output.put_line(' Employee Name: '|| emp_val.First_name);
end loop;
end;
/

Example of create Cursor with Parameter:

set serveroutput on
Declare 
--Create cursor in declare section
cursor emp_curr is select first_name,dept_id from employees;
cursor dep_curr(v_dept_id in number) is select dept_name from department where dept_id = v_dept_id;
begin
	for emp_val in emp_curr 
	loop
		for dep_val in dep_curr(emp_val.dept_id)
		loop
			dbms_output.put_line(' Employee Name: '|| emp_val.First_name);
			dbms_output.put_line(' Department Name: '|| dep_val.dept_name);
		end loop;
end loop;
end;
/