Introduction PL/SQL

Basic PL/SQL

Advance PL/SQL

PL/SQL Cursors For Loop


PL/SQL cursor FOR loop has one great advantage of loop continued until row not found. In sometime you require to use explicit cursor with FOR loop instead of use OPEN, FETCH, and CLOSE statement.

FOR loop iterate repeatedly and fetches rows of values from database until row not found.

Explicit Cursor FOR LOOP Example

following one emp_information table:

EMP_NO EMP_NAME EMP_DEPT EMP_SALARY
1 Forbs ross Web Developer 45k
2 marks jems Program Developer 38k
3 Saulin Program Developer 34k
4 Zenia Sroll Web Developer 42k

Display employee number wise first two employee details emp,

Example Code

cursor_for_loop.sql
SQL>set serveroutput on
SQL>edit cursor_for_loop
DECLARE
	cursor c is select * from emp_information
	where emp_no <=2;
	tmp emp_information%rowtype;
BEGIN 
	OPEN c;
	FOR tmp IN c LOOP
	FETCH c into tmp;
	dbms_output.put_line('EMP_No:    '||tmp.emp_no);
	dbms_output.put_line('EMP_Name:  '||tmp.emp_name);
	dbms_output.put_line('EMP_Dept:  '||tmp.emp_dept);
	dbms_output.put_line('EMP_Salary:'||tmp.emp_salary);			
	END Loop;
CLOSE c;
END;	
/

Example Result

SQL>@cursor_for_loop
EMP_No:    1
EMP_Name:  Forbs ross
EMP_Dept:  Web Developer
EMP_Salary:45k

EMP_No:    2
EMP_Name:  marks jems
EMP_Dept:  Program Developer
EMP_Salary:38k

PL/SQL procedure successfully completed.