Cursor

Cursor variables are like C or Pascal pointers, which hold the address of some item instead of the item itself. Declaring a cursor variable creates a pointer, not an item. Simply speaking cursor provides a way of addressing a relation(may be some select statement) by a variable name.

Eg: cursor c_tax is select wid, salary from worker;

This means that the select statement output can be addressed by the c_tax variable. Using fetch statement a single row can be processed.

Example

The example given below explains the working of the cursor. We have worker table where the value of the tax field is 0 initially. We have to add 10% of salary as tax when the salary < 2000.

The initial setup is as given below.

SQL> create table worker(

wid number(2),

name varchar2(7),

salary number(4),

age number(3),

tax number(4)

);

SQL> create table pension(

wid number(2),

pension number(4)

);

SQL> select * from worker;

WID NAME SALARY AGE TAX

---------- --------------------- ---------- ---------- ----------

1 arun 1000 22 0

2 bibin 2000 22 0

3 amal 4000 22 0

5 ajesh 4000 24 0

cur_tax.sql

=========

declare

--cursor defenition

cursor c_tax is select wid, salary from worker;

--variables with same type as that of worker table

c_wid worker.wid%type;

c_salary worker.salary%type;

begin

--open curson

open c_tax;

loop

--fetch row into variables c_wid and c_salary

fetch c_tax into c_wid,c_salary;

--exit when no more rows are left to traverse

exit when c_tax%notfound;

if (c_salary < 2000) then

--updating the tax

update worker set tax=(salary*.1) where wid=c_wid;

-- ending if

end if;

-- ending loop

end loop;

end;

SQL> @cur_tax.sql;