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