PL/SQL Procedure and Function ex: 6 DBMS Lab

PL/SQL Procedure and Function

WRITE A PL/SQL PROGRAM TO FIND THE  SUM OF DIGITS IN A GIVEN NUMBER

SQL> ed amstrong.sql 

declare

a number;

t number;

arm number;

d number;

begin

a:=&a;

t:=a;

arm:=0;

while t>0

loop

d:=mod(t,10);

arm:=arm+power(d,3);

t:=trunc(t/10);

end loop;

if arm=a then

dbms_output.put_line('given no is an armstrong no'|| a);

else

dbms_output.put_line('given no is not an armstrong no');

end if;

end;

/ 

SQL> set serveroutput on; 

SQL> @ amstrong.sql;

Enter value for a: 154

old   7: a:=&a;

new   7: a:=154;

given no is not an armstrong no 

PL/SQL procedure successfully completed. 

SQL> /

Enter value for a: 153

old   7: a:=&a;

new   7: a:=153;

given no is an armstrong no153 

PL/SQL procedure successfully completed.

WRITE A PL/SQL PROGRAM TO DISPLAY THE NUMBER IN REVERSE ORDER

SQL> ed rev.sql 

declare

a number;

rev number;

d number;

begin

a:=&a;

rev:=0;

while a>0

loop

d:=mod(a,10);

rev:=(rev*10)+d;

a:=trunc(a/10);

end loop;

dbms_output.put_line('no is'|| rev);

end;

/ 

SQL> @ rev.sql;

Enter value for a: 123

old   6: a:=&a;

new   6: a:=123;

no is321 

PL/SQL procedure successfully completed.

WRITE A PL/SQL PROGRAM TO CHECK WHETHER THE GIVEN NUMBER IS PRIME OR NOT

SQL> ed prime.sql 

declare

a number;

c number:=0;

i number;

begin

a:=&a;

for i in 1..a

loop

if mod(a,i)=0 then

c:=c+1;

end if;

end loop;

if c=2 then

dbms_output.put_line(a ||'is a prime number');

else

dbms_output.put_line(a ||'is not a prime number');

end if;

end;

/
 

SQL> @ prime.sql

Enter value for a: 63

old   6: a:=&a;

new   6: a:=63;

63is not a prime number 

PL/SQL procedure successfully completed. 

SQL> /

Enter value for a: 67

old   6: a:=&a;

new   6: a:=67;

67is a prime number 

PL/SQL procedure successfully completed.

WRITE A PL/SQL PROGRAM TO FIND THE FACTORIAL OF A GIVEN NUMBER

SQL> ed fact.sql 

declare

n number;

f number:=1;

begin

n:=&n;

for i in 1..n

loop

f:=f*i;

end loop;

dbms_output.put_line('the factorial is'|| f);

end;

/ 

SQL> @ fact.sql

Enter value for n: 6

old   5: n:=&n;

new   5: n:=6;

the factorial is720 

PL/SQL procedure successfully completed.

WRITE A PL/SQL PROGRAM TO GENERATE FIBONACCI SERIES

SQL> ed fib.sql 

declare

a number;

b number;

c number;

n number;

i number;

begin

n:=&n;

a:=0;

b:=1;

dbms_output.put_line(a);

dbms_output.put_line(b);

for i in 1..n-2

loop

c:=a+b;

dbms_output.put_line(c);

a:=b;

b:=c;

end loop;

end;

/ 

SQL> @ fib.sql

Enter value for n: 10

old   8: n:=&n;

new   8: n:=10;

0

1

1

2

3

5

8

13

21

34 

PL/SQL procedure successfully completed.

WRITE A FUNCTION TO FIND FACTORIAL OF A NUMBER

SQL> ed factfn.sql 
create or replace function fact(n number)return number
as 
fac number:=1;
begin
for i in 1..n
loop
fac:=fac*i;
end loop;
return fac;
end;
/ 
SQL> @ factfn.sql 
Function created. 
SQL> select fact(6) from dual; 
   FACT(6)
----------
       720