PL/SQL Programs
NOTE: initialize the values of variables when working with LIVESQL
NOTE: Type PL/SqL block in notepad, save with .sql extension.
Execute the pl/sql program at sql prompt using the command SQL> @[Link]
(specify absolute/complete path of the file)
[Link] of 2 nos
DECLARE
n1 number;
n2 number;
sum1 number;
BEGIN
n1:=&n1;
n2:=&n2;
sum1:=n1+n2;
dbms_output.put_line('sum of 2 nos is'||sum1);
end;
/
[Link] of 2 nos
DECLARE
n1 number;
n2 number;
BEGIN
n1:=&n1;
n2:=&n2;
if n1>n2
THEN dbms_output.put_line('greatest of 2 nos is'||n1);
ELSE
dbms_output.put_line('greatest is '||n2);
END IF;
END;
/
[Link] n numbers
DECLARE
n1 number;
BEGIN
n1:=&n1;
FOR i in 1..n1
loop
dbms_output.put_line(''||i);
end loop;
end;
/
Q4. Write a program to find the sum of the digits of the number:
DECLARE
N number ;
S NUMBER :=0;
R NUMBER;
begin
n:=&N;
WHILE N<>0 LOOP
R := MOD(N,10);
S := S + R;
N := TRUNC(N/10);
end loop;
dbms_output.put_line('THE SUM OF THE DIGITS = ' || S);
end;
/
Q5. Program to accept a number from user and print number in reverse order.
declare
num1 number(5);
num2 number(5);
rev number(5);
begin
num1:=&num1;
rev:=0;
while num1>0
loop
num2:=num1 mod 10;
rev:=num2+(rev*10);
num1:=floor(num1/10);
end loop;
dbms_output.put_line('Reverse number is: '||rev);
end;
/
Q6. Program to reverse a string.
declare
str1 varchar2(30);
len number(3);
str2 varchar2(30);
i number(3);
begin
str1:='&str1';
len:=length(str1);
for i in reverse 1..len
loop
str2:=str2 || substr(str1,i,1);
end loop;
dbms_output.put_line('Reverse string is: '||str2);
end;
/
[Link] to find a number prime or not
declare
n number;
i number;
counter number;
begin
n:=&n;
i:=1;
counter:=0;
if n=1
then dbms_output.put_line('1 is neither prime nor composite.');
elsif n=2
then dbms_output.put_line('2 is even prime');
else
for i in 1..n loop
if mod(n,i)=0
then counter:=counter+1;
end if;
end loop;
end if;
if counter=2
then dbms_output.put_line(n||' is a prime No.');
else
dbms_output.put_line(n||' is a not prime No.');
end if;
end;
/
Q8. Program to print sum of first n natural numbers.
Declare
i number:=0;
n number;
sum1 number:=0;
Begin
n:=&n;
while i<n+1
loop
sum1:=sum1+i;
dbms_output.put_line(i);
i:=i+1;
end loop;
dbms_output.put_line('The sum is:'||sum1);
End;
/
[Link] to find fabonacci series
DECLARE
num NUMBER;
a NUMBER:= 0;
b NUMBER:= 1;
c NUMBER;
BEGIN
num:='&Input_Number';
DBMS_OUTPUT.PUT_LINE(a);
DBMS_OUTPUT.PUT_LINE(b);
FOR i in 3..num LOOP
c := a + b;
DBMS_OUTPUT.PUT_LINE(c);
a:=b;
b:=c;
END LOOP;
END;
/
[Link] to find factorial of a number
DECLARE
num NUMBER:='&Input_Number';
i NUMBER;
f NUMBER:=1;
BEGIN
FOR i IN 1..num LOOP
f := f * i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(f||' Is Factorial Of '||num);
END;
/
[Link] to find multiplication table of a given number
DECLARE
num1 NUMBER;
i NUMBER;
BEGIN
num1:='&INPUT_NUMBER';
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(num1*i);
EXIT WHEN i=10;
END LOOP;
END;
/
[Link] to find area of the circle
declare
c number(5);
r number:='&no';
a number(5);
begin
c:=3.14*r*r;
dbms_output.put_line('area of circle is'||c);
end;
/
Q13. program to check whether number is Armstrong or not.
declare
pnum number(5);
tot number(5);
lp number(3);
tmp number(5);
begin
pnum:=&pnum;
tmp:=pnum;
tot:=0;
while tmp>0
loop
lp:=tmp mod 10;
tot:= tot + (lp*lp*lp);
tmp:=floor(tmp/10);
end loop;
if(tot like pnum) then
dbms_output.put_line(pnum||' is armstrong.');
else
dbms_output.put_line(pnum||' is not armstrong.');
end if;
end;
/
[Link] to find greatest of three numbers
declare
a number;
b number;
c number;
begin
a:=&a;
b:=&b;
c:=&c;
if a=b and b=c and c=a then
dbms_output.put_line('ALL ARE EQUAL');
elsif a>b and a>c then
dbms_output.put_line('A IS GREATER');
elsif b>c then
dbms_output.put_line('B IS GREATER');
else
dbms_output.put_line('C IS GREATER');
end if;
end;
/
Q15. AREA OF CIRCLE
set serveroutput on
declare
radius float;
area float;
begin
dbms_output.put_line('********AREA OF CIRCLE*********');
radius:=&radius;
area:=3.14*radius*radius;
dbms_output.put_line('The area of circle is '||area);
end;
/
Q16. CALCULATE THE AREA OF A CIRCLE FOR A VALUE OF RADIUS VARYING FROM
3 TO 7. STORE THE RADIUS AND THE CORRESPONDING VALUES OF CALCULATED
AREA IN A TABLE AREAS.
create table areas ( r number(2), area number (14,2));
declare
r number(5);
area number(14,2);
pi constant number (4,2):=3.14;
begin
r:=3;
while r<=7
loop
area:=pi*power(r,2);
insert into areas values(r,area );
r:=r+1;
end loop;
end;
select * from areas;