Assignment:2
Q-1.Write
Pl/sql Program to Display SUM OF odd NUMBERS accept USER INPUT (using for loop)
declare
i number:=1;
ans number:=0;
a number;
begin
a:=&a;
for i in 1..a
loop
if
i mod 2=1 then
ans:=ans+i;
end
if;
end loop;
dbms_output.put_line('sum
of odd numbers given by user :'||ans);
end;
Q-2. Write
Pl/sql Program to
accept user salary
& CALCULATION OF
NET SALARY and Display it.
Da
=15% HRA =41%
Basic
salary <3000 Pf = 5 %
Basic salary <=5000 Pf = 7 %
Basic
salary <=8000 Pf = 8 %
Basic
salary >=8000 Pf = 10 %
Net
salary: =basic + da + hra -pf
declare
sal number;
da number;
hra number;
pf number;
ns number;
begin
sal:=&sal;
da:=sal*0.15;
hra:=sal*0.41;
if sal<3000
then
pf:=sal*0.05;
elsif
sal<=5000 then
pf:=sal*0.07;
elsif
sal<=8000 then
pf:=sal*0.08;
else
pf:=sal*0.10;
end if;
ns:=sal+da+hra-pf;
dbms_output.put_line('net
salary :'||ns);
end;
/
Q-3.
Write pl/sql Program to Display area of circle from 10 numbers (o/p :
4,16,36,64,100)
declare
pi constant
number(4,2):=3.14;
radius number(5);
area
number(14,2);
begin
radius:=1;
dbms_output.put_line('radius area');
while
radius<=10
loop
area:=pi*power(radius,2);
dbms_output.put_line(radius||' '||area);
radius:=radius+1;
end loop;
end;
/
Q-4.
Write a pl/sql block of code for reverse a number 4356 to 6534. (Use length
function).
declare
n varchar(10);
l number(2);
a varchar(10);
begin
n:=&n;
l:=length(n);
for i in reverse
1..l
loop
a:=a||substr(n,i,1);
end loop;
dbms_output.put_line('given
number :'||n);
dbms_output.put_line('inverted
number :'||a);
end;
/
Q-5. Create table mytable with (nos_col,char_col) Insert
10 rows into MyTable with the current value of the loop counter.
create table mytable
(
nos_col number
primary key,
char_col
varchar(20)
);
declare
i number:=1;
charcol
varchar2(20);
begin
while i<=10
loop
charcol:='&charcol';
insert
into mytable values(i,charcol);
i:=i+1;
end loop;
end;
/
Q-6.
Write pl/sql code block accept empno from user and check salary is greater then 1000 display record of
particular employee details also display message salary is not greater and emp is not found Emp(empno,name,salary)
drop table emp;
create table emp
(
no varchar2(5)
primary key,
name
varchar2(20),
salary number(20)
);
insert into emp values('1','janak','8000');
insert into emp values('2','dhaval','5200');
insert into emp values('3','smita','1000');
insert into emp values('4','rutva','5000');
insert into emp values('5','mayur','2000');
declare
eno number;
a number;
ename
varchar2(20);
esalary
number(20);
begin
eno:=&eno;
a:=eno;
select
no,name,salary into eno,ename,esalary from emp where no=eno;
if eno=a then
if
esalary>1000 then
dbms_output.put_line(eno||' '||ename||'
'||esalary);
else
dbms_output.put_line('salary
not greater then');
end
if;
end if;
exception
when
NO_DATA_FOUND then
dbms_output.put_line('emp
not found');
end;
/
Q-7.
Write a program which will raise custom exception when deleting records in emp
table if salary is less than 5000 then do not allow deletion and give
appropriate message Emp(empno,name,salary)
declare
esalary
empmst.salary%type;
eno emp.no%type;
empDeletionException exception;
begin
eno:='&eno';
select salary
into esalary from empmst where eno=no;
if
esalary>5000 then
delete
from empmst where eno=no;
else
raise
empDeletionException;
end if;
exception
when
empDeletionException then
dbms_output.put_line('emp
salary is less then 5000 so you can''t delete this record');
when
NO_DATA_FOUND then
dbms_output.put_line('emp
not found');
end;
/
Q-8. Write a custom exception which is
implement while inserting records emp table. If sal<5000 then do not allow
insertion operation and raise the exception and give appropriate message.
Emp(empno,ename,dateofjoin,sal).
alter table empmst add(doj date);
update empmst set doj='4-dec-97';
declare
eno
empmst.no%type;
ename
empmst.name%type;
esalary
empmst.salary%type;
edoj
empmst.doj%type;
empInsertion
exception;
begin
eno:='&eno';
ename:='&ename';
esalary:='&esalary';
edoj:='&edoj';
if esalary<5000
then
raise
empInsertion;
else
insert
into empmst values(eno,ename,esalary,edoj);
end if;
exception
when empInsertion
then
dbms_output.put_line('emp
salary is less then 5000 so you can''t insert this record');
when
NO_DATA_FOUND then
dbms_output.put_line('emp
not found');
end;
/
Comments
Post a Comment