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:=&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;

/