Folosirea clauzei WHERE CURRENT OF

Cind ne referim la rindul curent dintr-un cursor explicit, comenzile SQL pot folosi WHERE CURRENT OF, cu numele cursorului. Aceasta permite actualizarea sau stergera in punctul in care ne aflam, fara a fi necesara folosirea explicita a Rowid. Trebuie totusi sa includeti FOR UPDATE in cererea cursorului, astfel incit rindurile sa fie blocate la deschidere.

Exemplu:

 FETCH c1 INTO emp_record ;
        IF emp_record.ename  = ' KING' THEN
                DELETE FROM emp WHERE CURRENT OF c1;

Se considera un exemplu complet. In blocul de mai jos se prelucreaza fiecare rind din tabela 'dep', mutind departamentul SALES in locatia Dallas, si celelalte departamente la New York. De asemenea se tine un contor al departamentelor plasate in fiecare locatie.
DECLARE
                CURSOR c1 IS
                        SELECT dname, loc FROM dept
                        FOR UPDATE OF loc ;
dept_rec        c1%ROWTYPE;
sales_count     NUMBER:=0 ;
non_sales       NUMBER := 0;
        BEGIN
                OPEN  c1;
                LOOP
                        FETCH c1 INTO dept_rec;
                EXIT WHEN c1%NOTFOUND;
                        IF dept_rec.dname = 'SALES' AND dept_rec.loc != 'DALLAS'
                        THEN
                                UPDATE dept SET loc = 'DALLAS' WHERE
                                        CURRENT OF c1;
sales_count := sales_count + 1;
                        ELSIF dept_rec.dname != 'SALES' AND dept_rec.loc != 'NEW YORK'
                        THEN
                                UPDATE dept SET loc = 'NEW YORK' WHERE CURRENT OF c1;
non_sales := non_sales +1 ;
                        END IF;
                END LOOP;
                CLOSE c1;
                INSERT INTO counts (sales_set, non_sales_set )
                        VALUES (sales_count, non_sales);
                COMMIT;
        END;