PL-SQL Programs…
· Program 1:-
Write a PL/SQL block to find the maximum number from given three numbers.
declare
a number;
b number;
c number;
begin
a:=&a;
b:=&b;
c:=&c;
if (a>b and a>c) then
dbms_output.put_line('a is maximum ' || a);
elsif (b>a and b>c) then
dbms_output.put_line('b is maximum ' || b);
else
dbms_output.put_line('c is maximum ' || c);
end if;
end;
· Program 2:-
Write a PL/SQL block to find the sum of first 100 natural nos.
declare
a number:=0;
begin
for i in 1..100
loop
a:=a+i;
end loop;
dbms_output.put_line('The sum of 100 natural nos is = '||a);
end;
· Program 3:-
Write a PL/SQL block to find the sum of first 100 odd nos. and even nos)
declare
odd number:=0;
even number:=0;
i number;
begin
for i in 1..100
loop
if(i mod 2 = 0) then
even:=even+i;
else
odd:=odd+i;
end if;
end loop;
dbms_output.put_line('The Sum of 100 even nos is ' || even);
dbms_output.put_line('The Sum of 100 odd nos is ' || odd);
end;
· Program 4:-
Write a PL/SQL block to display the Information of given student on following table Stud (sno, sname, address, city).
è Table Creation…
create table stud(
sno number primary key,
sname char(15),
addr varchar(30),
city char(15));
insert into stud values(1,'hiral','2,krishna society','Mehsana.');
insert into stud values(2,'pinky','4,Kalyaneshwer society','Mehsana.');
insert into stud values(3,'Dhruvi','24,Pushpavati society','Mehsana');
è Program…
declare
no number;
n number;
name char(15);
add varchar(50);
c char(15);
begin
n:=&n;
select sno,sname,addr,city into no,name,add,c from stud where sno=n;
dbms_output.put_line('The Sno is ' || no);
dbms_output.put_line('The Sname is ' || name);
dbms_output.put_line('The address is ' || add);
dbms_output.put_line('The city is ' || c);
end;
· Program 5:-
Write a PL/SQL block for preparing a Net Salary, given employee on following table
Emp (eno, ename, address, city)
Salary (eno, basic, da, hra, it)
Net_Salary (eno, total_allowance, total_deduction, netpay)
Notes : D.A. = 59% of basic , H.R.A. = 500, I.T. = 2% of basic
Total_Allowance = Basic + D.A. + H.R.A., Total_Deduction = I.T.
Netpay = Total_Allowance – Total_Deduction.
è Table Creation…
create table emp(
eno number primary key,
ename char(15),
addr varchar(30),
city char(15));
insert into emp values(1,'hiral','2,krishna society','Mehsana.');
insert into emp values(2,'pinky','4,Kalyaneshwer society','Mehsana.');
insert into emp values(3,'Dhruvi','24,Pushpavati society','Mehsana');
create table salary(
eno number references emp,
basic number(10,2),
da number(10,2) default NULL,
hra number(10,2) default 500,
it number(10,2) default NULL);
insert into salary(eno,basic) values(1,20000);
insert into salary(eno,basic) values(2,30000);
insert into salary(eno,basic) values(3,40000);
update salary set da=basic*0.59,it=basic*0.02;
create table netsal(
eno number references emp,
totalallow number(10,2),
totalded number(10,2),
netpay number(10,2));
è Program…
declare
no number;
n number;
d number(10,2);
b number(10,2);
h number(10,2);
i number(10,2);
ta number(10,2);
td number(10,2);
np number(10,2);
begin
n:=&n;
select eno,basic,da,hra,it into no,b,d,h,i from salary where eno=n;
ta:=b+d+h;
td:=i;
np:=ta-td;
insert into netsal values(no,ta,td,np);
end;
· Program 6:-
Write a PL/SQL block to raise the salary by 20% of given employee on following table.
Emp_Salary (eno, ename, city, salary)
è Table Creation…
eno number primary key,
ename char(15),
city char(15),
sal number(10,2));
insert into empsal values(1,'Hiral','Mehsana',20000);
insert into empsal values(2,'Pinkey','Mehsana',15000);
insert into empsal values(3,'Dhruvi','Mehsana',10000);
è Program…
declare
n number;
s number(10,2);
begin
n:=&n;
--select sal into s from empsal where eno=n;
update empsal set sal=sal+(sal*0.20) where eno=n;
end;
Write a function to check whether the given number is prime or not.
set serveroutput on
create or replace function prime(n in number) return varchar2 is
i number(10);
pr number(10);
res varchar2(10);
begin
pr:=1;
for i in 2..n/2 loop
if mod(n,i)=0 then
pr:=0;
end if;
end loop;
if pr=1 then
res:='prime';
else
res:='Not Prime';
end if;
return res;
end;
To run:= select prime(5) from dual;
12. Write a function to find the sum of digits of accepted nos.
Pra12;
set serveroutput on
create or replace function sm(no in number)return number as
i number(4);
sm number(4);
rem number(4);
num number(4);
begin
num:=no;
sm:=0;
i:=0;
while i<=num+1
loop
rem:=mod(num,10);
dbms_output.put_line(rem);
sm:=sm+rem;
num:=num/10;
i:=i+1;
end loop;
return sm;
end;
Pra12a;
set serveroutput on
declare
nos number(10);
ans number(3);
begin
nos:=&nos;
ans:=sm(nos);
dbms_output.put_line('Sum of Digit is '||ans);
end;
27. Write a Trigger on insert to convert the name into capital letters.
create or replace trigger t2 before insert or update on stud1 for each row
declare
oper varchar2(10);
begin
oper:=:new.sname;
:new.sname:=upper(oper);
end;
28. Write a Trigger to ckeck the Pincode is exactly six degit or not.
create table citypin(
pin number(10));
insert into citypin values(&pin);
SQL> select * from citypin;
PIN
---------
2434355
3435465
122
123235467
set serveroutput on
create or replace trigger tri28 before insert on citypin for each row
declare
tpin citypin.pin%type;
begin
tpin:=:new.pin;
if length(to_char(tpin))!=6 then
raise_application_error(-20002,'Your Pincode is Invalid');
end if;
end;
29. Write a trigger that check the mark is not zero or negative.
create table astd(mark number(3));
insert into astd values(&mark);
SQL> select * from astd;
MARK
---------
23
0
800
0
0
set serveroutput on
create or replace trigger tri29 before insert on astd for each row
declare
tmark astd.mark%type;
begin
tmark:=:new.mark;
if tmark>700 or tmark<0 then
raise_application_error(-20003,'Invalid Mark');
end if;
end;
30. Write a trigger that check the student_id must be start with ‘M’.
create table student_id(sno varchar2(10));
insert into student_id values(‘&sno’)
SQL> select * from student_id;
SNO
---------
1
2
3
4
set serveroutput on
create or replace trigger tri30 before insert on student_id for each row
declare
tsno student_id.sno%type;
begin
tsno:=:new.sno;
dbms_output.put_line(tsno);
if tsno not like 'M%' then
raise_application_error(-20005,'Invalid Number');
end if;
end;
