PLSQL CURSOR
CURSOR
à In order to process SQL statements
oracle will allocate an area of memory known as context area. PL/SQL uses it to
store and execute SQL statements. The information retrieved from database which
is available in context area is known as active set. A CURSOR is pointer
which works on active set. CURSOR points one row at a time in context area. A
CURSOR is used to process multiple rows using PL/SQL.
à It is a private memory or buffer. It
is used to hold the data from tables or views in memory location for manipulation
purpose. It is valid in PL/SQL block only.
à CURSORs are variables that you can
define in the declaration section.
à
Types of CURSORs
1.Implicit Cursor
2.Explicit Cursor
à
1.Implicit Cursor
It is cursor that is
automatically declared by oracle every time SQL statement is executed.
They are identified by
“SQL % <CURSOR ATTRIBUTES>”.
When the executable part
of the block is issued SQL statement, PL/SQL creates an implicit cursor.
A CURSOR is
automatically associated with all DML statements like insert, update, delete.
CURSOR ATTRIBUTES: - there are 4 CURSOR attributes
available in PL/SQL
A.%FOUND: - It is Boolean attribute it
evaluates true if the most recent SQL statement affect one or more rows.
B.%NOTFOUND: - It is Boolean attribute
it evaluates true if the most recent SQL statement does not affect no rows.
C.%IS OPEN: - It is Boolean attribute
it evaluates true if the cursor is open else evaluates false.
D.%ROWCOUNT: - It is an integer value.
It counts No of rows affected by most recent SQL statement.
Example Programs.
declare
rows_deleted
varchar2(20);
V_empno
emp.empno%type:=7788;
begin
delete from emp where
empno=V_empno;
rows_deleted:=(SQL%ROWCOUNT||'
ROWs DELETED');
display(rows_deleted);
commit;
end;
output is 1 ROWS DELETED
Example 2
declare
v_job
emp.job%type:=:p_job;
begin
update emp set
sal=sal+1000 where job=:p_job;
display(SQL%ROWCOUNT||'
Rows are updated');
if SQL%NOTFOUND then
display('Data not found
so no updation done');
end if;
commit;
end;
Implicit cursor with
for loop
CREATE TABLE
UPDATE_TRACE
(EMPNO NUMBER, NAME VARCHAR2(50), BASIC NUMBER, DEPTNO NUMBER, DOU TIMESTAMP);
Begin
for i in (select
empno,ename,sal,deptno from emp)
loop
if i.sal<1000 then
i.sal:=i.sal+100;
update emp set sal=i.sal
where empno=i.empno;
insert into update_trace
values(i.empno,i.ename,i.sal,i.deptno,sysdate);
end if;
end loop;
end;
Example 2
begin
for i in (select
empno,ename,job,sal,comm from emp where deptno=30)
loop
if i.comm is null then
i.sal:=i.sal+500;
update emp set sal=i.sal
where empno=i.empno;
else
display(rpad(i.ename,8)||'
'||rpad(i.sal,6)||' '||i.comm);
end if;
end loop;
end;
à 2.Explicit Cursor: -
Explicit cursor are
explicitly defined by user or program.
The life cycle of
explicit cursor goes to four stages, Declare, Open, Fetch,
Close.
In declarative section
we can’t use ‘into’ clause. In between begin and end we must use into clause
for select statement.
Example
Declare
Cursor c1 is
Select
empno,ename,sal,deptno from emp;
Vempno number;
Vename varchar2(50);
Vsal number(7,2);
Vdeptno number;
Begin
Open c1;
Loop
Fetch c1 into
Vempno,Vename,Vsal,Vdeptno;
Exit when c1%NOTFOUND;
Display(Vempno||’
‘||Vename||’ ‘||Vsal||’ ‘||Vdeptno);
End loop;
Close c1;
End;
Write PL/SQL cursor to
give the comm is 250 when employee is not having anu comm , give comm 300 if
the employee has comm 0, other than these two conditions hike the comm as 25%
and also display output as empname,ename,comm,deptno?
Declare
Cursor c1 is
Select empno,ename,comm,deptno
from emp;
Vempno emp.empno%type;
Vename emp.ename%type;
Vcomm emp.comm%type;
Vdeptno emp.deptno%type;
Begin
Open c1;
Display(‘The employee
details are’);
Loop
Fetch c1 into
Vempno,Vename,Vcomm,Vdeptno;
Exit when c1%NOTFOUND;
If Vcomm is null then
Vcomm:=250;
Elsif Vcomm=0 then
Vcomm:=300;
Else
Vcomm:=Vcomm+Vcomm*0.25;
End if;
Update emp set
comm=Vcomm where empno=Vempno;
Display(Vempno||’
‘||Vename||’ ‘||Vcomm||’ ‘||Vdeptno);
End loop;
Commit;
Close c1;
End;
Write a PL/SQL cursor
to hike salary with following criteria
1.if emp sal is 0 to
1000 hike sal 100
2.if emp sal is 1001
to 2000 hike sal 200
3.other than these
two hike sal 300
After update emp sal
if emp sal has more than 3000 then enter employee details into UPDATE_TRACE
table?
declare
cursor cu is
select
empno,ename,sal,deptno from emp;
i emp%rowtype;
begin
open cu;
loop
fetch cu into
i.empno,i.ename,i.sal,i.deptno;
exit when cu%NOTFOUND;
if i.sal between 0 and
1000 then
i.sal:=i.sal+100;
elsif i.sal between 1001
and 2000 then
i.sal:=i.sal+200;
else
i.sal:=i.sal+300;
end if;
update emp set sal=i.sal
where empno=i.empno;
if i.sal>3000 then
insert into update_trace
values(i.empno,i.ename,i.sal,i.deptno,sysdate);
end if;
end loop;
commit;
close cu;
end;
write PL/SQL cursor
to check the entered username and password is correct or not?
Create table UTAB(USERID
varchar2(30),PASSWORD varchar2(30));
insert into UTAB
values(:userid,translate(upper(:password),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','1234567890!@#$%^&*()_+{}<>'));
we enter details as bellow
userid password
Kamal Welcome2021
Raj Welcome1990
declare
cursor PRIMARY_CUR is
select 'X' from UTAB
where
USERID=upper(:userid) and
PASSWORD=translate(upper(:password),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','1234567890!@#$%^&*()_+{}<>');
DUMMY_VAR varchar2(300);
begin
open PRIMARY_CUR;
fetch PRIMARY_CUR into
DUMMY_VAR;
if PRIMARY_CUR%FOUND
then
display('The username
and password is correct');
else
display('unknown userid
and password');
end if;
close PRIMARY_CUR;
end;
write PL/SQL cursor
to display deptno, no of employees, low sal, high sal, total sal, avg sal of
each dept?
declare
cursor gc is
select deptno,
count(empno) noe, min(sal) losal, max(sal) hisal, sum(sal) totalsal, avg(sal)
avgsal from emp group by deptno;
i gc%rowtype;
begin
open gc;
loop
fetch gc into i;
exit when gc%notfound;
display(i.deptno||’
‘||i.noe||’ ‘||i.losal||’ ‘||i.hisal||’ ‘||i.totalsal||’ ‘||round(i.avgsal));
end loop;
close gc;
end;
note: - alias name
should not keep with in “ “ .
to access the group
function results the alias names are mandatory.
Types of explicit
cursor
a.cursor with simple
loop
b.cursor with for
loop: - advantage
of cursor with for loop
·
No
need to declare explicit variables.
·
No
need to open cursor.
·
No
need to fetch the data.
·
No
need to terminating loop by cursor attribute.
·
No
need to close the cursor
Note: - after
close the cursor we can’t use any cursor attribute
a.cursor with simple loop example
write a PL/SQL cursor
to display empno,basic,da,hra,pf and gross salary , if employee sal is more
than 3000 he is eligible for PF so display no of employee eligible for pf?
Declare
Cursor cpf is
Select empno,ename,sal
BASIC,sal*0.35 DA,sal*0.45 HRA,sal*0.5 PF from emp where sal>3000;
i cpf%rowtype;
Vgross number;
Begin
Open cpf;
Loop
Fetch cpf into i;
If cpf%found then
Vgross:=i.BASIC+i.DA+i.HRA-i.PF;
Display(i.empno||’
‘||i.ename||’ ‘||i.BASIC||’ ‘||i.DA||’ ‘||i.HRA||’ ‘||i.PF||’ ‘||Vgross);
Else
Exit;
End if;
End loop;
Display(‘The employee
eligible for PF is ‘||cpf%rowcount);
Close cpf;
End;
b.cursor with for
loop example
write a PL/SQL cursor
to display empno,ename,sal,hra,da,pf,gross salary and deptno, if employee sal
is more than 3000 he is eligible for PF so display no of employee eligible for
pf?
Declare
Cursor cpf is
Select empno,ename,sal
BASIC,sal*0.35 DA,sal*0.45 HRA,sal*0.5 PF,deptno from emp where sal>3000;
i cpf%rowtype;
Vgross number;
Cnt number:=0;
Begin
Display(‘The employee
details are’);
For i in cpf
Loop
Vgross:=i.BASIC+i.DA+i.HRA-i.PF;
Display(i.empno||’
‘||i.ename||’ ‘||i.BASIC||’ ‘||i.DA||’ ‘||i.HRA||’ ‘||i.PF||’ ‘||Vgross||’
‘||i.deptno);
Cnt:=Cnt+1;
End loop;
Display(‘The no of
employees eligible for PF is ‘||Cnt);
End;
à Parametric cursor
The cursor is defined
with parameter is called parametric cursor.
The parametric cursor
can be assigned with default values.
Receive values from
caller or program, no size for parametric variables.
Write a PL/SQL
parametric cursor to hike sal as 15% for each employee whose sal is less than
2000 and accept deptno,job is input to display output as ename,job,hiked
sal,deptno?
Declare
Cursor pc (pdno in
EMP.DEPTNO%type,pjob in emp.job%type)
is
select
empno,ename,job,sal,deptno from emp where deptno=pdno and job=pjob;
i pc%rowtype;
begin
open pc(:pdno,:pjob);
loop
fetch pc into i;
exit when pc%notfound;
if i.sal<2000 then
i.sal:=i.sal+i.sal*0.15;
update emp set sal=i.sal
where empno=i.empno;
display(rpad(i.ename,8)||'
'||rpad(i.job,8)||' '||rpad(i.sal,6)||' '||i.deptno);
end if;
end loop;
close pc;
end;
write a PL/SQL cursor
to display empno,ename,ename,job,sal,deptno by separate deptno wise?
Declare
Cursor DC
is
select deptno from dept;
cursor EC(pdno
dept.deptno%type)
is
select
empno,ename,job,sal,deptno from emp where deptno=pdno;
Vdno dept.deptno%type;
i EC%rowtype;
begin
open DC;
loop
fetch DC into Vdno;
exit when DC%notfound;
open EC(Vdno);
loop
fetch EC into i;
exit when EC%notfound;
display(i.empno||' '||rpad(i.ename,8)||'
'||rpad(i.job,8)||' '||rpad(i.sal,6)||' '||i.deptno);
end loop;
if EC%rowcount>0 then
display('------------------------------------------------------------------------------------');
end if;
close EC;
end loop;
close DC;
end;
output: - it displays
the result deptno wise, after one deptno wise date then place this line
------------
write a PL/SQL cursor
to display and insert empno,ename,basic,hra,da,pf,gross,deptno by separate
deptno wise and one empty row after one deptno wise result and enter these
values into EMP_REPORT table?
create table emp_report
as select empno ecode,ename name,sal basic,sal hra,sal da,sal pf,sal
gross,deptno from emp where 1=2;
declare
cursor dc is
select unique deptno
from emp;
cursor ec (pdno
emp.deptno%type)
is
select empno,ename,sal
basic,sal*0.35 hra,sal*0.45 da,sal*0.15 pf,deptno from emp where deptno=pdno;
vdno DEPT.DEPTNO%type;
i ec%rowtype;
vgross number;
begin
delete from emp_report;
open dc;
loop
fetch dc into vdno;
exit when dc%notfound;
open ec(vdno);
loop
fetch ec into i;
exit when ec%notfound;
vgross:=i.basic+i.hra+i.da-i.pf;
insert into emp_report values(i.empno,i.ename,i.basic,i.hra,i.da,i.pf,vgross,i.deptno);
end loop;
close ec;
insert into
emp_report(ecode) values(null);
end loop;
close dc;
end;
à Where current clause: -
It is to refer the
current record fetched from the explicit cursor.
In order to use this clause,
you need to lock the record fetched from the cursor.
For update clause: -
It explicitly locks the
records stored in private work area.
You need to provide an
explicit commit to release the lock acquired by for update clause.
Write a PL/SQL cursor
to display empno,ename,deptno if any employee belong to 50th dept
remove the employee after deleting that employee then display the remaining
employees?
declare
cursor lc
is
select
empno,ename,deptno from emp for update;
i lc%rowtype;
begin
display('The employee
details are');
display('empno'||' '||'empname'||' '||'deptno');
display('------'||' '||'--------'||' '||'-------');
open lc;
loop
fetch lc into i;
exit when lc%notfound;
display(i.empno||' '||i.ename||'
'||i.deptno);
if i.deptno=50 then
delete from emp where
current of lc;
end if;
end loop;
commit;
close lc;
display('After delete
50th dept employees');
open lc;
loop
fetch lc into i;
exit when lc%notfound;
display(i.empno||' '||i.ename||'
'||i.deptno);
end loop;
close lc;
end;
output: - 1st it display
all employees and then after deleting 50th dept employee then
display result without 50th dept employees.
Sub-Query in a cursor
à Write a PL/SQL program to display
deptno,dname,location and no of employees?
declare
cursor cs
is
select
d.deptno,d.dname,d.loc,v.noe from dept d,(select deptno,count(empno) noe from
emp group by deptno) v where d.deptno=v.deptno;
begin
for i in cs
loop
display(i.deptno||'
'||i.dname||' '||i.loc||' '||i.noe);
end loop;
end;
Cursor with ROWID
à Write a PL/SQL program to display
ename,sal,deptno for emp who update salary. If emp sal is less than 1000 then
add 200 to sal , then update these details in emp_stage table?
Create table emp_stage
as select ename,sal,deptno from emp;
Declare
Cursor rc
Is
Select
rowid,ename,sal,deptno from emp_stage;
Vrowid rowid;
Vename
emp_stage.ename%type;
Vsal emp_stage.sal%type;
Vdeptno
emp_stage.deptno%type;
begin
open rc;
loop
fetch rc into
Vrowid,Vename,Vsal,Vdeptno;
exit when rc%notfound;
if Vsal<1000 then
Vsal:=Vsal+200;
update emp_stage set
sal=Vsal where rowid=Vrowid;
display(Vename||'
'||Vsal||' '||Vdeptno);
end if;
end loop;
close rc;
Comments
Post a Comment