Cursors.
Cursors:
A cursor is a memory area
in to which the rows of a select statement are loaded and processed.
Records Pointer: When a
cursor is created oracle creates a pointer called record pointer that points to the current record among the set of the records in the
cursor
Cursor
attributes
A part from the creating a
record pointer after the cursor is created oracle also creates some variables
called cursor attributes
which are used in knowing the status of the cursor like to know whether the cursor
is open,close,found,not found
1)%Open = Return the status of the cursor
2)%Found = Determines
whether any records exists of that
select stmt assigned to the cursor
3)%NOT Found = Determines
whether any records not exists of that
select stmt assigned to the cursor
4)%ROWCOUNT = gives the no
of rows in the cursor...
----------------------------------------------------------------------
There
are 2 types of cursors
1)
Implicit cursor:
If the cursor is created by oracle then it is
said to be Implicit Cursor
The Name of the Implicit cursor is always be
SQL
Plsql
Block to demostrate Implicit cursor....
Ex1:
Create or replace
procedure p_upd_emp(p_deptno number)
Is
begin
UPDATE EMP
SET sal = sal+100
where deptno = p_deptno;
IF SQL%FOUND THEN
dbms_output.put_line('Cursor found');
END IF;
IF SQL%NOTFOUND THEN
dbms_output.put_line('Cursor not found ');
END IF;
exception
when others then
dbms_output.put_line('Error in the p_upd_emp
procedure'||sqlerrm);
End;
-----------------------------------
plsql
block to demostrate to kow how many records got deleted
or
updated...
Create or replace
procedure p_upd_emp(p_deptno number)
Is
begin
UPDATE EMP
SET sal = sal+100
where deptno = p_deptno;
IF SQL%FOUND THEN
dbms_output.put_line('Cursor found');
END IF;
IF SQL%NOTFOUND THEN
dbms_output.put_line('Cursor not found ');
END IF;
dbms_output.put_line('no
of records updated are '||sql%rowcount);
exception
when others then
dbms_output.put_line('Error in the p_upd_emp
procedure'||sqlerrm);
End;
/
--
Create or replace
procedure p_upd_emp(p_deptno number)
Is
ln_cnt number;
begin
UPDATE EMP
SET sal = sal+100
where deptno = p_deptno;
IF SQL%FOUND THEN
dbms_output.put_line('Cursor found');
END IF;
IF SQL%NOTFOUND THEN
dbms_output.put_line('Cursor not found ');
END IF;
ln_cnt:= sql%rowcount;
dbms_output.put_line('no
of records updated are '||ln_cnt);
exception
when others then
dbms_output.put_line('Error in the p_upd_emp
procedure'||sqlerrm);
End;
/
--------------------------------------------------------------------------------
2)
Explicit cursor:
If it is created by the programmer with the
help of cursor keyword in the declaration sections , it is called
explicit cursor...
The Name of the explicit
cursor is user defined.....
Steps for creating the
Explicit Cursor....
1) Declare the cursor in
the declaration section via using the CURSOR Keyword...
Syntax: CURSOR CURSORNAME
IS Select
Statement;
Ex: Cursor c_emp
is select * from emp;
When the above statement
is executed then the pl/sql engine just makes a note of the cursor name(c_emp)
with its associated select statement but doesnot associate memory for the o/p
if the
select statement.
Memory allocation is done
only when the cursor is opened.
OIpen the cursor in the
executable part...this is associated via the OPEN Keyword.
Syntax: Open cursor_name;
Ex: Open C_emp;
When the above select
statement is executed then the pl/sql engine executes the select statement
associated with the cursor
c_emp and then allocates memory for the output of the select statement.
Start the loop
Syntax :LOOP
Fetch the values of the
current record from the cursor in to the variables which are already declared.
SYNTAX: Fetch Cursor name
into var1,var2....
Ex: Fetch c_emp into
v_empno,v_ename
The Number of variables in
the fetch stmt into clause must be same with the no of columns present in the
cursor.... in terms of both datatype and size.
EXIT when the cursor finds
no records left to be processed in the
loop
End the loop and CLOSE the
Cursor
Ex: END LOOP;
EX: Close c_emp;
After close of the cursor
the records in the cursor memory is released.
-----------------------------------------------
EX:
Print the empname,sal,comm,job,dname,locations for those whose
salary is greater then the min sal of the deptno 20;
Create or replace
procedure xx_emp_dtls(p_deptno number)
is
cursor c_emp is
select e.ename
,e.sal
-- ,nvl(e.comm,0)
comm
-- ,e.job
-- ,d.dname
,d.loc
from emp e
,dept d
where d.deptno(+) =
e.deptno
and sal >(select
min(sal) from emp where deptno = p_deptno);
v_ename emp.ename%type;
v_sal number;
v_loc dept.loc%type;
begin
open c_emp;
LOOP
FETCH c_emp into v_ename,v_sal,v_loc;
EXIT WHEN c_emp%notfound;
dbms_output.put_line('ename'||v_ename);
dbms_output.put_line('salary'||v_sal);
dbms_output.put_line('location'||v_loc);
END LOOP;
--
close c_emp;
END xx_emp_dtls;
/
EX:
Print the empname,sal, locations by passing the deptno as parameter.
If no records found for the given deptno value then print invalid deptno
and if deptno is valid then print the emp details and if the deptno is passed
as null then print the data for all the deptno satisfying the query….
Create or replace
procedure xx_emp_dtls(p_deptno number)
is
cursor c_emp is
select e.ename
,e.sal
,d.loc
from emp e
,dept d
where d.deptno(+) =
e.deptno
and e.deptno = nvl(p_deptno,e.deptno);
v_ename emp.ename%type;
v_sal number;
v_loc dept.loc%type;
ln_flag char(1):= 'N';
begin
--
open c_emp;
LOOP
FETCH c_emp into v_ename,v_sal,v_loc;
EXIT WHEN c_emp%notfound;
ln_flag:= 'Y';
dbms_output.put_line('emp detials are
'||v_ename||'----'||v_sal||'----'||v_loc);
END LOOP;
--
if ln_flag = 'N' then
dbms_output.put_line('Invalid deptno passed
'||p_deptno);
end if;
close c_emp;
END xx_emp_dtls;
/
2) write a plsql procedure to increase the salary of the employee of the
emp
table by 10% if job is manager
6% if job is clerk
5% is job is salesman
2% is any other.
Pass deptno and empno as parameters and
do the updation for all the records if the deptno and empno is passed as null
and for the passed combination of
deptno,empno values if there are no records in the query then print the message
no data exists for the passed input values
Create or replace
procedure xx_upate_sal(p_deptno number,p_empno number)
is
cursor c_emp is
select ename,job,empno,sal
from emp
where deptno = nvl(p_deptno,deptno)
and empno
= nvl(p_empno,empno);
v_ename emp.ename%type;
v_job emp.job%type;
v_empno emp.empno%type;
v_sal emp.sal%type;
ln_flag char(1):= 'N';
begin
open c_emp;
LOOP
FETCH c_emp into
v_ename,v_job,v_empno,v_sal;
EXIT WHEN c_emp%notfound;
ln_flag:= 'Y';
dbms_output.put_line('old sal '||v_sal||' for empno '||v_empno);
IF upper(v_job) = 'MANAGER' THEN
UPDATE emp
set sal = sal +
(sal * 10/100)
where empno =
v_empno;
ELSIF upper(v_job) = 'CLERK' THEN
UPDATE emp
set sal = sal +
(sal * 6/100)
where empno =
v_empno;
ELSIF upper(v_job) = 'SALESMAN' THEN
UPDATE emp
set sal = sal +
(sal * 5/100)
where empno =
v_empno;
ELSE
UPDATE emp
set sal = sal +
(sal * 2/100)
where empno =
v_empno;
END IF;
END LOOP;
close c_emp;
COMMIT;
if ln_flag = 'N' then
dbms_output.put_line('Invalid input
parameters');
end if;
END;
/
How to declare the cursor
variable....
%ROWTYPE:
It is used to assign the
cursor records structure to a variable. Instead of fetching the values in to
independent local variable we can dump them in to one local variable by
declaring the variable with the data type as cursor%rowtype.
---
Execises:
write a plsql procedure to increase the salary of the employee of the
emp
table by 10% if job is manager
6% if job is clerk
5% is job is salesman
2% is any other.
Pass deptno and empno as parameters and
do the updation for all the records if the deptno and empno is passed as null
and for the passed combination of
deptno,empno values if there are no records in the query then print the message
no data exists for the passed input values.
Print the old salary and as well new
updated salary laong with the employee name and job.
Also include no of records getting
printed.
EX:
create or replace
procedure xx_rk_sal(p_deptno number,p_empno number)
is
cursor c_emp is
select ename,job,empno,sal
from emp
where deptno
=nvl(p_deptno,deptno)
and empno =nvl(p_empno,empno);
ln_flag char(1):='N';
v_cnt number:=0;
v_newsal number;
r_emp c_emp%rowtype;
begin
open c_emp;
LOOP
FETCH c_emp into r_emp;
EXIT WHEN c_emp%notfound;
ln_flag:='y';
IF upper(r_emp.job)='MANAGER'THEN
UPDATE emp
SET sal=sal+(sal*10/100)
WHERE empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*10/100);
ELSIF
upper(r_emp.job)='CLERK'THEN
UPDATE emp
set sal= sal+(sal*6/100)
where empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*6/100);
ELSIF
upper(r_emp.job)='SALESMAN' THEN
UPDATE emp
set sal= sal+(sal*5/100)
where empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*5/100);
ELSE
UPDATE emp
set sal=sal+(sal*2/100)
where empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*2/100);
end if;
v_cnt:=v_cnt+sql%rowcount;
dbms_output.put_line('old
sal was '||r_emp.sal||'for empno is '||r_emp.empno||'and new salary
is'||v_newsal);
end loop;
close c_emp;
commit;
dbms_output.put_line('No
of rows updated = '||v_cnt);
IF ln_flag ='N' then
dbms_output.put_line('invalid
input parameters');
end if;
END;
/
write a plsql procedure to display all student
marks,grade,name,sex,age
details by passing sno as paramter.
if the passed sno is invalid
print the message as Invalid sno
passed,if sno is passed as null print all the detials
--
write a plsql procedure to display all
transaction details by passing acctno as paramter.
if the passed acctno is invalid or
if there are no tansactions for the
passed acctno then
print the message as Invalid acctno or
no transactions for the [passed
account no...
Cursor For LOOP
------------------------
With the help of the
cursor for loop the different steps of opening, fetching, closing
of the cursor are
automatic, also the memory variables are not required to store the cursor
columns
instead they are dumped in
to the cursor variable as one unit which are individually accessed by giving
the column name after the dot which is proceeded by the cursor variable name
Syntax: FOR C_varc in
cursor_name
loop
End loop;
-----------------------------------------------
Ex:
Write a plsql procedure to increase the salary of the employee of the
emp
table by 10% if job is manager
6% if job is clerk
5% is job is salesman
2% is any other.
Pass deptno and empno as parameters and
do the updation for all the records if the deptno and empno is passed as null
and for the passed combination of
deptno,empno values if there are no records in the query then print the message
no data exists for the passes input values.
Exercise:
create or replace
procedure xx_rk_sal(p_deptno number,p_empno number)
is
cursor c_emp is
select ename,job,empno,sal
from emp
where deptno
=nvl(p_deptno,deptno)
and empno =nvl(p_empno,empno);
ln_flag char(1):='N';
v_cnt number:=0;
v_newsal number;
r_emp c_emp%rowtype;
begin
FOR r_emp in c_emp
LOOP
ln_flag:='Y';
IF
upper(r_emp.job)='MANAGER'THEN
UPDATE emp
SET sal=sal+(sal*10/100)
WHERE empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*10/100);
ELSIF
upper(r_emp.job)='CLERK'THEN
UPDATE emp
set sal= sal+(sal*6/100)
where empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*6/100);
ELSIF
upper(r_emp.job)='SALESMAN' THEN
UPDATE emp
set sal= sal+(sal*5/100)
where empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*5/100);
ELSE
UPDATE emp
set sal=sal+(sal*2/100)
where empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*2/100);
end if;
v_cnt:=v_cnt+sql%rowcount;
dbms_output.put_line('old
sal was '||r_emp.sal||'for empno is '||r_emp.empno||'and new salary
is'||v_newsal);
END LOOP;
commit;
dbms_output.put_line('No
of rows updated = '||v_cnt);
IF ln_flag ='N' then
dbms_output.put_line('invalid
input parameters');
end if;
END;
/
-----------------------------------------
Write a plsql proceudre to increase the
salary of the employee of the emp
table by 10% if job is manager
6% if job is clerk
5% is job is salesman
2% is any other
If no of records updated is more then
that means
the data will repeatedly updated which
is wrong...
whenever this happens
donot do any updation just rollback everything...
create or replace
procedure xx_rk_sal(p_deptno number,p_empno number)
is
ln_flag char(1):='N';
v_cnt number:=0;
v_newsal number;
u_exp exception;
begin
FOR r_emp in (select
ename,job,empno,sal,deptno
from emp
where deptno =nvl(p_deptno,deptno)
and
empno =nvl(p_empno,empno))
LOOP
ln_flag:='Y';
IF
upper(r_emp.job)='MANAGER'THEN
UPDATE emp
SET sal=sal+(sal*10/100)
WHERE empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*10/100);
ELSIF
upper(r_emp.job)='CLERK'THEN
UPDATE emp
set sal= sal+(sal*6/100)
where deptno =
r_emp.deptno;
v_newsal:=r_emp.sal+(r_emp.sal*6/100);
ELSIF
upper(r_emp.job)='SALESMAN' THEN
UPDATE emp
set sal= sal+(sal*5/100)
where empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*5/100);
ELSE
UPDATE emp
set sal=sal+(sal*2/100)
where empno = r_emp.empno;
v_newsal:=r_emp.sal+(r_emp.sal*2/100);
end if;
if sql%rowcount > 1
then
raise u_exp;
end if;
v_cnt:=v_cnt+sql%rowcount;
dbms_output.put_line('old
sal was '||r_emp.sal||'for empno is '||r_emp.empno||'and new salary
is'||v_newsal);
END LOOP;
commit;
dbms_output.put_line('No
of rows updated = '||v_cnt);
IF ln_flag ='N' then
dbms_output.put_line('invalid
input parameters');
end if;
exception
when u_exp then
rollback;
dbms_output.put_line('no of records updation
is greater then 1, please contact IT suppprt team');
when others then
dbms_output.put_line('Error in the main
procedure '||sqlerrm);
END;
/
write a plsql procedure to increase the salary of the employee of the
emp
table by 10% passing the salgrade as the
parameter.Print the emp names ,empno, old salary and new salary and no of
records being updated.if the passed salgrade is invalid print the appropriate
message.
If the value of parameter is null then
update all the records…
------------------------------------------------------------------------
Parameterized
cursors
Ex: print the dept name
and the employees for those deptnames
create or replace
procedure xx_dtls(p_deptno number)
is
cursor c_dept is select *
from dept where deptno = nvl(p_deptno,deptno);
cursor c_emp(v_deptno
number)
is select * from emp where
deptno = v_deptno;
begin
for r_dept in c_dept
loop
dbms_output.put_line('----------------------');
dbms_output.put_line(r_dept.dname);
for r_emp in c_emp(r_dept.deptno)
loop
dbms_output.put_line(r_emp.ename);
end loop;
end loop;
exception
when others then
dbms_output.put_line('Error ins the xx_dtls
procedure');
END;
/
exception
when others then
dbms_output.put_line('Error ins the xx_dtls
procedure');
END;
/
----
Print the deptno details and then print
the emp details for each deptno and no of employees working in that deptno.If
no employees working for them then print it as no employees exists for that
deptno.
Take the deptno as parameter and if the
value is passed as null then pick all deptno information and if the value
passed is invalid then print as invalid deptno passed…
Print the subtotal salary for each deptno
Print the total salary for all the
deptno’s
o/p :
----------------------Start of the
report-----------------------------------------------
The Deptno is 10
The Department Name is Accounting
The Location is Newyork
The ename is rakesh patel
The empno
is 1234
The salary is 1000
------
The ename is ankul
The empno
is 123
The salary is 1000
The total salary for deptno 10 is 2000
The total no of employees working in the
deptno 10 is 2
The total salary for all
deptno’s is 10000
--------------------------End
of the Report -------------------------------------------------
Output :
create or replace
procedure dept_dtls(p_deptno number)
is
cursor c_dept
is
select *
from dept_ank
where deptno = nvl(p_deptno,deptno);
cursor c_emp(v_deptno
number) /*Parameterised Cursor*/
is select *
from emp_ank
where deptno = v_deptno;
/*Variables*/
dept_tot_sal number:=0;
emp_cnt number:=0;
tot_sal number:=0;
v_flag number:=0;
/*Execution Section*/
begin
dbms_output.put_line('----------------------Start
of the report-------------------------');
for r_dept in c_dept
loop
v_flag:=1;
dept_tot_sal:=0;
emp_cnt:=0;
dbms_output.put_line('Department No is: '||r_dept.deptno);
dbms_output.put_line('Department Name is: '||r_dept.dname);
dbms_output.put_line('Department Location is: '||r_dept.loc);
dbms_output.put_line('------------------------------------');
for r_emp in c_emp(r_dept.deptno)
loop
dbms_output.put_line('Employee Number: '||r_emp.empno);
dbms_output.put_line('Employee Name: '||r_emp.ename);
dbms_output.put_line('Salary: '||r_emp.sal);
dbms_output.put_line('---------------------------------');
dept_tot_sal:=dept_tot_sal+r_emp.sal;
emp_cnt:=emp_cnt+1;
end loop;
if emp_cnt=0 then
dbms_output.put_line('No employee working for
department no' ||r_dept.deptno);
else
dbms_output.put_line('The total no of
employees working in deptno'||r_dept.deptno|| ' is ' ||emp_cnt);
dbms_output.put_line('The total salary for
deptno'||r_dept.deptno|| ' is ' ||dept_tot_sal);
end if;
dbms_output.put_line('*****************************************************');
tot_sal:=tot_sal+dept_tot_sal;
end loop;
if v_flag=0 then
dbms_output.put_line('Invalid
department number. Please enter the valid department number.');
else
dbms_output.put_line('The
total salary of all the departments is ' ||tot_sal);
end if;
dbms_output.put_line('--------------------------End
of the Report------------------------');
exception
when others then
dbms_output.put_line('Error ins the dept_dtls
procedure'||sqlerrm);
END;
/
Using a loop in a loop,….
You can use either parameterized cursors or
we can use below way as well…
create or replace
procedure dept_dtls(p_deptno number)
is
/*Variables*/
dept_tot_sal number:=0;
emp_cnt number:=0;
tot_sal number:=0;
v_flag number:=0;
/*Execution Section*/
begin
dbms_output.put_line('----------------------Start
of the report-------------------------');
for r_dept in (select *
from
dept_ank
where
deptno = nvl(p_deptno,deptno))
loop
v_flag:=1;
dept_tot_sal:=0;
emp_cnt:=0;
dbms_output.put_line('Department No is: '||r_dept.deptno);
dbms_output.put_line('Department Name is: '||r_dept.dname);
dbms_output.put_line('Department Location is: '||r_dept.loc);
dbms_output.put_line('------------------------------------');
for r_emp in (select *
from
emp_ank
where
deptno = r_dept.deptno)
loop
dbms_output.put_line('Employee Number: '||r_emp.empno);
dbms_output.put_line('Employee Name: '||r_emp.ename);
dbms_output.put_line('Salary: '||r_emp.sal);
dbms_output.put_line('---------------------------------');
dept_tot_sal:=dept_tot_sal+r_emp.sal;
emp_cnt:=emp_cnt+1;
end loop;
if emp_cnt=0 then
dbms_output.put_line('No employee working for
department no' ||r_dept.deptno);
else
dbms_output.put_line('The total no of
employees working in deptno'||r_dept.deptno|| ' is ' ||emp_cnt);
dbms_output.put_line('The total salary for
deptno'||r_dept.deptno|| ' is ' ||dept_tot_sal);
end if;
dbms_output.put_line('*****************************************************');
tot_sal:=tot_sal+dept_tot_sal;
end loop;
if v_flag=0 then
dbms_output.put_line('Invalid
department number. Please enter the valid department number.');
else
dbms_output.put_line('The
total salary of all the departments is ' ||tot_sal);
end if;
dbms_output.put_line('--------------------------End
of the Report------------------------');
exception
when others then
dbms_output.put_line('Error ins the dept_dtls
procedure'||sqlerrm);
END;
/
---
Ex:
-----------------------------
2)Print the account details and print
the transaction details for each accno and no of transactions takes palce for
each accno. Pass the accno as parameter and if the accno parameter value is
passed as null then print for all
accno’s else if it is passed and if that accno is invalid then print the
appro[riate message as invalid parameter value.
Different
modes of parameters:
IN : The default parameter is IN. the value
should be passed in to procedure mandatory.
OUT: Need to pass some
variable where using out mode we can fetch the value from the procedure
INOUT: Pass the input
values and assign some other value in the procedure and print it outside the
procedure by fetching it in to variable.
create or replace
procedure p_emp_out(p_empno number
,p_ename out varchar2
,p_sal
out number)
is
BEGIN
BEGIN
select ename,sal
into
p_ename,p_sal
from
emp
where
empno = p_empno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_ename := null;
p_sal := null;
WHEN OTHERS THEN
dbms_output.put_line('Error in the select
statement'||SQLERRM);
END;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in the main
proceudre'||SQLERRM);
END p_emp_out;
declare
ln_ename emp.ename%type;
ln_sal emp.sal%type;
begin
p_emp_out(7369,ln_ename,ln_sal);
dbms_output.put_line('Ename
is -->'||ln_ename);
dbms_output.put_line('Salary
is -->'||ln_sal);
end;
Inout example
---------------------------
create or replace
procedure p_emp_out(p_empno in out number ,p_ename out varchar2
,p_sal out number)
is
BEGIN
BEGIN
select ename,sal
into
p_ename,p_sal
from
emp
where
empno = p_empno;
p_empno := 9999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_empno := 0000;
p_ename := null;
p_sal := null;
WHEN OTHERS THEN
dbms_output.put_line('Error in the select
statement'||SQLERRM);
END;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in the main
proceudre'||SQLERRM);
END p_emp_out;
declare
ln_ename emp.ename%type;
ln_sal emp.sal%type;
ln_empno number := 7369;
begin
p_emp_out(ln_empno,ln_ename,ln_sal);
dbms_output.put_line('Ename
is -->'||ln_ename);
dbms_output.put_line('Salary
is -->'||ln_sal);
dbms_output.put_line('empno
-->'||ln_empno);
end;
No comments:
Post a Comment