Local and Global Cursors :-
- If cursor is local then the cursor can access with in the program only.
- If cursor is global then the cursor can be declared in one progress can be used in another program.
Global is default
Program (I) :-
Declare c1 cursor global
For select ename,sal,comm from emp.
Declare @ename varchar(20)
Declare @sal smallmoney @comm smallmoney.
Declare @totsal smallmoney.
Open c1
Fetch next from c1 into @ename,@sal,@comm
While (@@fetch_status=0)
Begin
Set @totsal=@sal+isnull(@comm,0)
Print @ename+’earns’+cast(@total as varchar)
Fetch next from c1 into @ename,@sal,@comm
End.
Close c1.
Note :-
Program(II) :- Calculating annual salary using cursor c1:
Declare @ename varchar(20)
Declare @sal smallmoney,@comm smallmoney
Declare @totsal smallmoney
Opn c1
Fetch next from c1 into @ename,@sal,@comm
While (@@fetch_status=0)
Begin
Set @totsal=(@sal+isnull(@comm,0))*12
Print @ename+’earns’+cast(@totsal as varchar)
Fetch next from c1 into @ename,@sal,@comm
End
Deallocate c1.
Order_details items
Ordno icode qty icode descrpt price
1000 1 10 1 100
1000 2 30 2 150
1000 3 15 3 50
1001 1 20
To display like :-
Item code dept rate qty value
1 — 100 10 1000
2 — 150 20 3000
3 — 50 15 750
Total bill=4750
Program :-
Declare c1 cursor
For select icode,descno,rate,qty
From order-details,items
Where order_details icode=items.icode
And andno=1000.
Declare @icode int,@descrip varchar(20)
Declare @rate int,@qty int
Declare @value int
Declare @tbil int.
Set @tbil=0
Open c1
Fetch next from c1 into @icode,@descrip,@rate,@qty
While (@@fetch_status=0)
Begin
Set @value=@qty*@rate
Set @tbill=@tbill+@value
Print cast (@icode as varchar)+’ ’+@descrip+
‘ ‘+cast(@rate as varchar) +’ ‘+cast+@qty
As varchar)+cast(@value as varchar)
Fetch next from c1 into @icode,@descrip @rate@qty.
End
Print ‘total bill :-‘+cast(@tbill as varchar)
Close c1
Deallocate c1.