PL/SQL Programs

 

 

 

1. SWAP TWO NUMBERS

declare

a number(10);

b number(10);

c number(10);

begin

a:=&a;

b:=&b;

dbms_output.put_line('before');

dbms_output.put_line(a);

dbms_output.put_line(b);

c:=a;

a:=b;

b:=c;

dbms_output.put_line('after');

dbms_output.put_line(a);

dbms_output.put_line(b);

end;

/

 

2. SWAP TWO NUMBERS WITHOUT USING A THIRD VARIABLE

declare

a number(10);

b number(10);

begin

a:=&a;

b:=&b;

dbms_output.put_line('before');

dbms_output.put_line(a);

dbms_output.put_line(b);

a:=a+b;

b:=a-b;

a:=a-b;

dbms_output.put_line('after');

dbms_output.put_line(a);

dbms_output.put_line(b);

end;

/

 

3. LARGEST OF TWO NUMBERS

declare

a number(10);

b number(10);

begin

a:=&a;

b:=&b;

if a=b then

dbms_output.put_line('same');

elsif a>b then

dbms_output.put_line('greater is '||a);

else

dbms_output.put_line('greater is '||b);

end  if;

end;

/

 

4. CHECK WHETHER NUMBER IS ARMSTRONG

declare

a number(10);

b number(10);

s number(10);

t number(10);

begin

a:=&a;

s:=0;

t:=a;

while a>0

loop

b:=mod(a,10);

s:=s+(power(b,3));

a:=trunc(a/10);

end loop;

if s=t then

dbms_output.put_line('The number is Armstrong');

else

dbms_output.put_line('The number is not Armstrong');

end if;

end;

/

 

5. FACTORIAL OF A NUMBER

declare
a number(10);
b number(10);
i number(10);
begin
a:=&a;
b:=1;
for i in 1..a
loop
b:=b*i;
end loop;
dbms_output.put_line('Factorial is '||b);
end;
/

 

6. SUM OF DIGITS

declare

a number(10);

b number(10);

s number(10);

begin

a:=&a;

s:=0;

while a>0

loop

b:=mod(a,10);

s:=s+b;

a:=trunc(a,10);

end loop;

dbms_output.put_line('Sum of digits is '||s);

end;

/

 

7. FIBONACCI SERIES

declare

a number(10);

b number(10);

c number(10);

d number(10);

i number(10);

begin

a:=&a;

b:=0;

c:=1;

dbms_output.put_line('Fibonacci series is');

dbms_output.put_line(b);

dbms_output.put_line(c);

for i in 3..a

loop

d:=b+c;

dbms_output.put_line(d);

b:=c;

c:=d;

end loop;

end;

/

 

8. CHECK WHETHER A NUMBER IS PRIME

declare
a number(10);
b number(10);
i number(10);
c number(10);
begin
a:=&a;
if a=1 then
dbms_output.put_line('The number is neither prime nor composite');
elsif a=2 then
dbms_output.put_line('The number is prime');
elsif a>2 then
for i in 2..(a-1)
loop
b:=mod(a,i);
if b=0 then
c:=1;
end if;
end loop;
if c=1 then
dbms_output.put_line('The number is not prime');
else
dbms_output.put_line('The number is prime');
end if;
end if;
end;
/

 

9. DISPLAY PRIME NUMBER UPTO GIVEN NUMBER

declare

f number(10);

i number(10);

i number(10);

g number(10);

n number(10);

j number(10);

begin

n:=&n;

f:=0;

dbms_output.put_line('Prime numbers:');

for i in 2..n

loop

for j in 2..(i-1)

loop

g:=mod(i,j);

if g=0 then

f:=f+1;

end if;

end loop;

if f=0 then

dbms_output.put_line(i);

end if;

f:=0;

end loop;

end;

/

 

10. LARGEST OF THREE NUMBERS

declare
a number(10);
b number(10);
c number(10);
begin
a:=&a;
b:=&b;
c:=&c;
if a>b and a>c then
dbms_output.put_line('Largest is '||a);
elsif c>a and c>b then
dbms_output.put_line('Largest is '||c);
elsif b>a and b>c then
dbms_output.put_line('Largest is '||b);
end if;
end;
/

 

11. CUBE OF A NUMBER (USING FUNCTION)

create or replace from cube(n in number)
return number
IS
c number(5);
begin
c:=n*n*n;
return c;
end;
declare
n number(5);
c number(5);
begin
n:=&n;
c:=cube(n);
dbms_output.put_line('Cube = '||c);
end;
/

 

12. FIND TOTAL MARKS (USING FUNCTION)

create or replace function mark(n1 in number, n2 in number, n3 in number)

return number

IS

c number(5);

begin

c=n1+n2+n3;

return c;

end;

declare

n1 number(5);

n2 number(5);

n3 number(5);

c number(5);

begin

n1:=&n1;

n2:=&n2;

n3:=&n3;

c:=mark(n1,n2,n3);

dbms_output.put_line('Total = '||c);

end;

/

 

13. FACTORIAL OF A NUMBER (USING FUNCTION)

 

 

 

 

 

 

 

FUNCTION

create or replace function factfn(n in number)

return number

is

f number(5);

i number(5);

begin

f:=1;

for i in 1..n

loop

f:=f*i;

end loop;

return f;

end;

/

MAIN PROGRAM

declare

n number(5);

f number(5);

i number(5);

begin

n:=&n;

f:=factfn(n);

dbms_output.put_line('Factorial is'||f);

end;

/

 

14. BANK TRANSACTION (USING FUNCTION)

 

 

 

 

 

 

 

FUNCTION 1

create or replace function func1(req in number, bal in number)

return  number is bala number(10);

begin

bala:=bala-req;

return bala;

end;

/

FUNCTION 2

create or replace function func2(req in number, bal in number)

return  number is bala number(10);

begin

bala:=bala+req;

return bala;

end;

/

MAIN PROGRAM

declare

req number(10);

bal1 number(10);

bal2 number(10);

bala number(10);

accno1 number(10);

accno2 number(10);

s1 number(10);

s2 number(10);

begin

accno1:=&accno1;

accno2:=&accno2;

req:=&req;

select bal into bal1 from acc1 where accno=accno1;

select bal into bal2 from acc1 where accno=accno2;

s1:=func1(req,bal1);

s2:=func2(req,bal2);

update acc1 set bal=s1 where accno=accno1;

update acc1 set bal=s2 where accno=accno2;

end;

/

 

15. INTERNAL MARK CALCULATION (USING PROCEDURE)

 

 

 

 

 

 

 

PROCEDURE

create or replace procedure average(m1 in number, m2 in number, A out number)

IS

begin

A:=(m1+m2)/2;

end;

/

MAIN PROGRAM

declare

m1 number(5);

m2 number(5);

assignment1 number(5);

assignment2 number(5);

attendance number(5);

total number(5);

begin

m1:=&m1;

m2:=&m2;

assignment1:=&assignment1;

assignment2:=&assignment2;

attendance:=&attendance;

average(m1, m2, A);

total:=A;

if attendance>75 then

total:=total+10;

else

total:=total+(attendance*0.1);

end if;

average(assignment1, assignment2, A);

total:=total+A;

dbms_output.put_line('Internal = '||total);

end;

/

 

16. UPDATE PHONE NUMBER OF A PERSON (USING PROCEDURE). CREATE A TABLE 'address' WITH FIELDS 'serialno', 'name', 'address', 'phoneno'

 

 

 

 

 

 

 

 

 

 

 

 

CREATING TABLE

create table address(serialno number(5), name varchar(15), address varchar(50), phoneno(10));

PROCEDURE

create or replace procedure add(name in varchar, phoneno in number)

IS

begin

update address set phoneno=phoneno where name=name;

end;

/

MAIN PROGRAM

declare

name variable(15);

phoneno number(10);

begin

name:=&name;

phoneno:=&phoneno;

add(name phoneno);

end;

/

 

17. BANK TRANSACTION (USING PROCEDURE)

 

 

 

 

 

 

CREATING TABLE

create table acc(accno number(10), name varchar(15), bal number(10));

insert into acc values(1234, ‘John’, 80000);

insert into acc values(1567, ‘Name’, 10000);

PROCEDURE

create or replace procedure accproc(ids in number, s in number)

is begin

update acc set bal=s where accno=ids;

end;

/

MAIN PROGRAM

declare

b number(10);

a1 number(10);

a2 number(10);

b1 number(10);

b2 number(10);

t1 number(10);

t2 number(10);

begin

a1:=&a1;

a2:=&a2;

b:=&b;

select bal into b1 from acc where accno=a1;

select bal into b2 from acc where accno=a2;

if b<b1 then

t1:=b1-b;

t2:=b2+b;

accproc(a1,t1);

accproc(a2,t2);

dbms_outut.put_line(‘Can process’);

else

dbms_outut.put_line(‘Cant process’);

end if;

end;

/

 

18. PROGRAM FOR NOT ALLOWING INSERTION INTO TABLE ON SATURDAY AND SUNDAY (USIING TRIGGER)

 

 

 

 

 

 

 

 

 

 

 

CREATING TABLE

create table student(rollno number(2), name varchar(10), age number(2), address(15));

 MAIN PROGRAM

create or replace trigger tr before insert on student for each row

declare

temp varchar(5);

begin

temp:=TO_CHAR(sysdate,’DY’);

if temp=’SAT’ or temp=’SUN’ then

RAISE_APPLICATION_ERROR(‘-20002’,’TRANSACTION NOT ALLOWED’);

Else

dbms_output.put_line(‘Transaction allowed’);

end if;

end;

/ 

 19. PROGRAM FOR SHOWING TABLE UPDATE DATE (USING TRIGGER)

 

 

CREATING TABLE

create table student(rollno number(2), name varchar(15), age number(2));

insert into student values(40, ‘NAME’, 21);

MAIN PROGRAM

create or replace trigger tr2 after insert on student for each row

declare

temp varchar(5);

begin

temp:=to_char(sysdate,’DY’);

dbms_output.put_line(‘Record updated on’||temp);

temp:=to_char(sysdate,’DD-MM-YY’);

dbms_output.put_line(‘Date: ’||temp);

end;

/

 

20. PROGRAM TO GET EMPLOYEE NAME, OLD SALARY AND 10% INCREMENT SALARY (USING CURSOR). CREATE TABLE 'employee' WITH FIELDS 'id', 'name', 'salary'

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

CREATING TABLE

create table employee (id number(10), name varchar(30), salary number(10));

MAIN PROGRAM

declare

n employee.name%type;

s1 employee.salary%type;

s2 employee.salary%type;

cursor cr2 is select name, salary from employee;

cursor cr3 is select salary from employee;

begin

dbms_output.put_line('name'||' '||'old salary'||' '||'new salary');

open cr2;

update employee set salary=salary+(salary*0.1);

open cr3;

loop

fetch cr2 into n,s1;

fetch cr3 into s2;

dbms_output.put_line(n||' '||s1||' '||s2);

exit when cr2%not found;

end loop;

close cr2;

close cr3;

end;

/

 

21. PROGRAM FOR DISPLAYING NAME AND AGE FROM A TABLE

 

 

 

 

 

CREATING TABLE

create table student(rollno number(5), name varchar(10), age number(5), address varchar(30));

insert into student values(10, ‘Gokul’, 21, ‘Kochi’);

insert into student values(6, ‘Name’, 26, ‘Kerala’);

MAIN PROGRAM

declare

name1 student.name%type;

age1 student.age%type;

cursor cr1 is select name,age from student;

begin

dbms_output.put_line(‘Name and age from student’);

dbms_output.put_line(‘name’||’ ‘||’age’);

open cr1;

loop

fetch cr1 into name1,age1;

dbms_output.put_line(name1||’ ‘||age1);

exit when cr1% notfound;

end loop;

close cr1;

end;

/