CURSOR FOR Loop

The CURSOR FOR loop is defined for explicit cursors or can be implemented directly on the SELECT statement. Generally, the CURSOR FOR loop is used to retrieve and manipulate each record fetched from a cursor.

The syntax for using the CURSOR FOR loop is as follows:

FOR record_index IN cursor_name
LOOP
{.statements.}
END LOOP;

The CURSOR FOR loop automatically does the following:

1. It implicitly declares a %ROWTYPE attribute and then uses it as a loop index.
2. It opens the cursor itself.
3. It fetches a row from the cursor for each loop iteration.
4. It closes the cursor automatically when all rows have been fetched or processed.

From the above statements, it can be concluded that the OPEN, FETCH, and CLOSE
statements are not necessary for using the CURSOR FOR loop.

Example:

DECLARE
CURSOR Emp_Cur IS SELECT * FROM Emp WHERE Sal > 2000;
Ctr NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Employees having salary more than 3000’);
DBMS_OUTPUT.PUT_LINE(‘-------------------------------------------------------’);
FOR employee_rec IN Emp_Cur LOOP
DBMS_OUTPUT.PUT_LINE(‘Employee ID: ’ ||
employee_rec.Empno);
DBMS_OUTPUT.PUT_LINE(‘Employee Name: ’ ||
employee_rec.Ename);
DBMS_OUTPUT.PUT_LINE(‘Employee Salary: ’ ||
employee_rec.Sal);
DBMS_OUTPUT.PUT_LINE(‘Employee Commission: ’ ||
employee_rec.Comm);
DBMS_OUTPUT.PUT_LINE(‘================’);
Ctr := Emp_Cur%ROWCOUNT;
END LOOP;
DBMS_OUTPUT.PUT_LINE(ctr || ‘ Employees have salary more than 3000’);
DBMS_OUTPUT.PUT_LINE(‘-------------------------------------------------------’);
END;
/

The CURSOR FOR loop can also accept parameters. It follows the same rules as the manual
cursor. For example, if the cursor is defined with a parameter, it must be opened with a
parameter; otherwise, PL/SQL will raise an exception.

Example:

DECLARE
CURSOR Emp_Cur(Desg IN VARCHAR) IS
SELECT * FROM Emp
WHERE Job = Desg;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘------------------------------------------------’);
FOR Emp_rec IN Emp_Cur (‘&Designation’) LOOP
DBMS_OUTPUT.PUT_LINE(‘Name: ’ || Emp_rec.Ename || ‘ ’ ||
‘Salary: ’ || Emp_rec.Sal);
END LOOP;
END;
/

The cursor Emp_Cur will be executed and the message "Enter value for designation:" will
be prompted. Enter the value for the designation as MANAGER.

As a result, the cursor will retrieve the records of the employees that have the designation as MANAGER.

Cursors

A cursor is a pointer to a memory location where the results of a SELECT statement is stored and we can manipulate the information by fetching the records.

There are two types of cursors: implicit and explicit. PL/SQL declares a cursor
implicitly for all SQL data manipulation statements, including queries that return only single row. Whereas the queries that return more than one row, an explicit cursor should be declared.

Example: Implicit Cursor

DECLARE
EmpRec Emp%ROWTYPE;
EmpId NUMBER(4);
BEGIN
EmpId := &Empid;
SELECT * INTO EmpRec FROM Emp
WHERE Empno= EmpId;
DBMS_OUTPUT.PUT_LINE(‘Employee Details’);
DBMS_OUTPUT.PUT_LINE(‘======================’);
DBMS_OUTPUT.PUT_LINE(‘Employee ID: ’ || EmpRec.Empno);
DBMS_OUTPUT.PUT_LINE(‘======================’);
DBMS_OUTPUT.PUT_LINE(‘Employee Name: ’ || EmpRec.Ename);
DBMS_OUTPUT.PUT_LINE(‘======================’);
DBMS_OUTPUT.PUT_LINE(‘Employee Designation: ’ || EmpRec.Job);
DBMS_OUTPUT.PUT_LINE(‘======================’);
DBMS_OUTPUT.PUT_LINE(‘Employee Join Date: ’ || EmpRec.Hiredate);
DBMS_OUTPUT.PUT_LINE(‘======================’);
DBMS_OUTPUT.PUT_LINE(‘Employee Salary: ’ || EmpRec.Sal);
DBMS_OUTPUT.PUT_LINE(‘======================’);
DBMS_OUTPUT.PUT_LINE(‘Employee Commission: ’ || EmpRec.Comm);
DBMS_OUTPUT.PUT_LINE(‘======================’);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Employee does not exist’);
END;
/

Attributes of the Implicit Cursor
The attributes of the implicit cursor are used to access information about the most recently executed SQL statement.

%FOUND: Returns TRUE, if the INSERT, UPDATE, or DELETE command affect one or more rows.

Example:

DECLARE
EmpID NUMBER;
BEGIN
EmpID := &EmpID;
UPDATE Emp SET Sal = Sal + Sal * 0.25
WHERE Empno = EmpID;
IF SQL%FOUND THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE(‘Salary of the employee updated
successfully’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Employee ID does not exist’);
END IF;
END;
/

%ISOPEN: Implicit cursors are closed automatically after the execution of the SQL DML statements. As a result, the %ISOPEN attribute always returns FALSE.

%NOTFOUND: Returns TRUE, if the INSERT, UPDATE, or DELETE command does not affect any row.

Example:

DECLARE
EmpID NUMBER;
BEGIN
DELETE FROM Emp WHERE Empno= &EmpID;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Employee does not exist’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘The employee details have been
deleted successfully.’);
END IF;
END;
/

%ROWCOUNT: Returns the number of rows affected by the INSERT, UPDATE, or DELETE command.

Example:

DECLARE
Deptname VARCHAR2(20);
Rows_affected NUMBER;
BEGIN
Deptname := ‘&Deptname’;
UPDATE Emp set Sal = Sal + Sal * 0.25
WHERE Deptno= (SELECT Deptno FROM Dept
WHERE Dname = Deptname);
Rows_affected := SQL%ROWCOUNT;
IF Rows_affected > 0 THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE(‘Salary of ’ || Rows_affected ||
‘ employees of the ’ || Deptname || ‘ department is updated
successfully’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Department does not exist’);
ROLLBACK;
END IF;
END;
/

Explicit Cursor
The explicit cursors are those cursors that are assigned to a SELECT statement explicitly. It is used when more than one row has to be retrieved by a SELECT statement.

Example:

DECLARE
CURSOR myEmpCursor(Emp_Id NUMBER) IS
SELECT Empno, Ename FROM Employee
WHERE Empno = Emp_Id;
BEGIN
Emp_Id := 101; /* Illegal reference */
OPEN myEmpCursor (Emp_Id);
END;
/

Attributes of the Explicit Cursor
The attributes of the explicit cursor are used to provide information about the status of a cursor.


%FOUND: It is used to determines whether the last fetch returns a row or not.

Example:

DECLARE
CURSOR E_Cur IS
SELECT Ename, Sal FROM Emp
WHERE ROWNUM < 11; my_Name Emp.Ename%TYPE; my_Salary Emp.Sal%TYPE; BEGIN OPEN E_Cur; LOOP FETCH E_Cur INTO my_Name, my_Salary; IF E_Cur%FOUND THEN -- fetch succeeded DBMS_OUTPUT.PUT_LINE(‘Name = ’ || my_Name || ‘ Salary = ’ || my_Salary); ELSE -- fetch failed, so exit loop EXIT; END IF; END LOOP; END; /

%ISOPEN: It is used to check whether the cursor is open.

Example:

DECLARE
CURSOR E_Cur IS
SELECT Ename, Sal FROM Emp
WHERE ROWNUM <= 5; my_Name Emp.Ename%TYPE; my_salary Emp.Sal%TYPE; BEGIN IF NOT E_Cur%ISOPEN THEN OPEN E_Cur; END IF; LOOP FETCH E_Cur INTO my_Name, my_salary; IF E_Cur%FOUND THEN -- fetch succeeded DBMS_OUTPUT.PUT_LINE(‘Name = ’ || my_Name || ‘ Salary = ’ || my_salary); ELSE -- fetch failed, so exit loop EXIT; END IF; END LOOP; END; /

%NOTFOUND: This attribute is logically opposite to the %FOUND attribute.

Example:

DECLARE
CURSOR myEmpCursor IS
SELECT Empno, Ename, Sal FROM Emp
WHERE Sal > 2000
ORDER BY Sal DESC;
myID Emp.Empno%TYPE;
myName Emp.Ename%TYPE;
mySalary Emp.Sal%TYPE;
BEGIN
OPEN myEmpCursor;
LOOP
FETCH myEmpCursor INTO myID, myName, mySalary;
EXIT WHEN myEmpCursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘=================’);
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE ID: ’ || myID);
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE Name: ’ || myName);
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE Salary: ’ || mySalary);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘======================’);
END;
/

%ROWCOUNT: It works like a counter and returns zero when the cursor is opened for the first time. You can use this attribute if you want only a few rows of the result set to be returned by the cursor.

Example:

DECLARE
CURSOR myEmpCursor IS
SELECT Empno, Ename FROM Emp
ORDER BY Empno;
myID Emp.Empno%TYPE;
myName Emp.Ename%TYPE;
BEGIN
OPEN myEmpCursor;
LOOP
FETCH myEmpCursor INTO myID, myName;
EXIT WHEN myEmpCursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(myID ||‘ ’|| myName);
EXIT WHEN myEmpCursor%ROWCOUNT > 4;
END LOOP;
CLOSE myEmpCursor;
END;
/