(Subject Notes) Informatics Practices

Cursors

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:

• %FOUND ... true whenever a row is successfully fetched
• %ISOPEN ... true if cursor is currently active
• %NOTFOUND ... true after last row has been read
• %ROWCOUNT ... returns number of rows in cursor-relation

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;

  Go To Page :   1,   2,   3,   4 ,  5 ,  6   >>