DBMS PL/SQL Programs
Q1.sum 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;
/
Q2.greatest 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;
/
Q3.print 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.
Program: -
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;
/
Q7.program 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;
/
Q9.program 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;
/
Q10.Program 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;
/
Q11.program 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;
/
Q12.program 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;
/
Q14.program 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.program to find palindrome or not
DECLARE
len NUMBER;
str VARCHAR2(20) := '&Input_String';
chkstr VARCHAR2(20);
BEGIN
len := LENGTH(str);
FOR i IN REVERSE 1..len LOOP
chkstr := chkstr||SUBSTR(str,i,1);
END LOOP;
IF chkstr = str THEN
DBMS_OUTPUT.PUT_LINE(str||' Is A Palindrome!');
ELSE
DBMS_OUTPUT.PUT_LINE(str||' Is Not A Palindrome!');
END IF;
END;
Q16.program to generate prime numbers upto n numbers
DECLARE
Num number;
prime integer;
BEGIN
Num:=#
FOR i IN 1..num LOOP
prime :=1;
FOR j IN 2..i-1
LOOP
IF MOD(i,j)=0 THEN
prime:=0;
END IF;
EXIT WHEN prime=0;
END LOOP;
IF prime=1 THEN
DBMS_OUTPUT.PUT_LINE(i);
END IF;
END LOOP;
END;
/
No comments