CBSE Book Class 12 Informatics Practices Iteration And Cursors

Chapter – 2

Iteration And Cursors

EXITWHEN Cond;

is shorthand for

IF Cond THEN EXIT; END IF;

Loops can be named to allow multi-level exits

<<outer>>LOOP

    ___

     <<inner>>LOOP

     ____ EXIT outer WHEN i > 100; ___

END LOOP;

___

END LOOP;

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;

    EXITWHEN e%NOTFOUND;

     total := total + emp.salary;

END LOOP;

CLOSE e;

___

Please refer to attached file for CBSE Classs 12 Informatics Practices Iteration And Cursors