A cursor is a variable that can be used to access the result of a particular SQL query.
Cursors can move sequentially from row to row (cf. file pointers in C).
Every SQL query statement in PL/SQL has an implicit cursor.
It is also possible to declare and manipulate cursors explicitly:
DECLARE
CURSOR e IS
SELECT * FROM Employees
WHERE salary > 30000.00;
BEGIN
Cursors provide flexibility in processing rows of a query.
Simplest way to deal with a cursor is to loop over all rows using a FOR loop:
DECLARE
CURSOR e IS
SELECT * FROM Employees
WHERE salary > 30000.00;
total INTEGER := 0;
BEGIN
FOR emp IN e LOOP
total := total + emp.salary;
END LOOP;
dbms_output.put_line( 'Total Salaries: ' || total);
END;
Cursor loop variables are implicitly declared as the ROWTYPE for the SELECT result.
E.g. emp is implictly declared as Employees%ROWTYPE.
The cursor FOR loop is convenient shorthand for iteration implemented using the basic cursor operations:
-- assume declarations as before
OPEN e;
LOOP
FETCH e INTO emp;
EXIT WHEN e%NOTFOUND;
total := total + emp.salary;
END LOOP;
CLOSE e;
...
The FETCH operation can also extract components of a row:
FETCH e INTO my_id, my_name, my_salary;
There must be one variable, of the correct type, for each column in the result.
Cursors have several built-in attributes:
Yet another method for cursor iteration:
-- assume declarations as before
OPEN e;
FOR i IN 1..e%ROWCOUNT LOOP
FETCH e INTO emp;
-- process emp in some way
END LOOP;
The CURRENT OF operator allows us to operate on the current tuple via SQL.
Example: give low-paid workers a higher pay rise
DECLARE
CURSOR Emps IS SELECT * FROM Employees;
BEGIN
FOR e IN Emps LOOP
IF e.salary < 20000.00 THEN
UPDATE e SET e.salary = e.salary*1.20
WHERE CURRENT OF Emps;
ELSIF e.salary > 80000.00 THEN
UPDATE e SET e.salary = e.salary*1.05
WHERE CURRENT OF Emps;
ENDIF
END LOOP;
END:
Cursor Example
Consider the problem of buying football players to make up a team with the constraint of a "salary cap".
We want to buy the best (most expensive) players first, but we have to stop taking them once we reach the salary cap.
Assume we can then make up the rest of the team with "cheap" young players.
DECLARE
CURSOR potentialPlayers IS
SELECT * FROM Players
ORDER BY salary DESCENDING;
totalCost NUMBER := 0.00;
salaryCap CONSTANT NUMBER := 100000000.00;
BEGIN
FOR p IN potentialPlayers LOOP
EXIT WHEN totalCost+p.salary > salaryCap;
dbms_output.put_line(p.name);
totalCost := totalCost + p.salary;
END LOOP;
END;