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;
/