Part 7 : How to create Cursor PostgreSQL

Cursor :

Cursor is temporary memory it is allocated  by database server when we are perform DML operation and  Cursor is a behave like pointer so it can jump particular and one by one scan row.

Task : write cursor find IT department and print one by one

do 

$$

declare c1 cursor for select * from employee ;

r1 record;

begin

open c1;

loop 

fetch c1 into r1;  exit when not found;

if r1.job ='IT' then

raise notice '% % % % %',r1.empcode,r1.empfname,r1.job,r1.salary,r1.salary+500;

end if;

end loop;

close c1;

end

$$

OUTPUT :

NOTICE: 9999 Tarun IT 8900 9400 NOTICE: 9956 MARK IT 4400 4900 NOTICE: 9957 Jone IT 4400 4900 DO

Now using loop in cursor

Example :Cursor Salary update according Department.

Example :Cursor update email id according Department.

Example : If else with loop in cursor

Example :- How to create cursor for insert first row and last row from table 

If else statement with loop in cursor

Fetch Prior : is retrieve data before pointer of curser 

Q:- Increment salary according to Department .

do 

$$            

declare  c1 cursor for  select * from employee ; 

r1 record ; g int;

begin

open c1;

loop 

fetch c1 into r1; exit when not found;

if r1.job='PRESIDENT'  then g:='3000' ;

elsif r1.job='ANAYLYST' then g:='2500' ;

elsif r1.job='ANALYST' then g:='2000' ;

elsif r1.job='SOFTWARE ENGINEER' then g:='1500' ;

elsif r1.job='MANAGER' then g:='1000' ;

elsif r1.job='SALESMAN' then g:='500' ;

elsif r1.job='IT' then g:='4000' ;

end if;

update employee set salary=r1.salary + g where job =r1.job;

              -- raise notice '% % % % %',r1.empcode,r1.empfname,r1.job,r1.salary,r1.salary + g;

end loop;

close c1;

end

$$

-- select * from employee

Fetch Absolute : Suppose if you want to fetch particular row from table you have to fetch absolute  

fetch next => it is used for Print next row. 

fetch relative => calculate row no and go to move after position. 

fetch relative -2  

fetch next => it is used for Print next row. 

fetch relative => calculate row no and go to move after position. 

fetch relative -2  

Move keyword : it can move cursor particular row so we can use it

Move first from c1 ;                                               This line point the cursor first row 

fetch next from c1 into r1;                                     next is print second line