PL/SQL Examples :

In my previous articles i have given the idea about the roles and responsibilities of PL SQL developer.In this article i will try to give you the basic PL/SQL examples which are important for interview purpose.I try to give the information on anonymous block in my previous article and this article will give you the different PL/SQL Examples of anonymous block which are useful for interview purpose. I will try to explain the logic for those PL/SQL Examples which is helpful.

Different PL/SQL Examples :

Example 1 : Write a program to print reverse of number.

User can use the in built function of Oracle to reverse number directly. But here we will try to resolve this using anonymous block.

Program :

Declare num1 number:=&num1;           ---Declaring the input number 
rev_num number:=0;                      ---Declaring Reverse number as 0   
Begin                                   ---Start of PL/SQL block 
while(num1>0)                            ---Check condition that number is greater than 0
 Loop 
rev_num=rev_num*10+mod(num1,10);                 ---Reverse condition 
num1=num1/10;                                    ---Reverse condition 
End loop; 
Dbms_Output.Put_Line('Reverse of Number'||num1||'is'|| rev_num); 
End;

Output :

IF user gives number as 786 as input

Output will be

Reverse of Number 786 is 687.

Example 2 : Write a program to calculate factorial of given number.

Lets try to solve this using the anonymous block of PL/SQL.This is most common PL/SQL Example in all the different kind of PL/SQL Examples.Let me first explain the logic of factorial number.

4! is 4*3*2*1 = 24

Program:

Declare   num1   number:= &num1;                       ---Declaring the input of number
   fact_num number:= 1;                         ---Initialise fact_num as 1   
temp_num number;                             ---This is for doing factorial number logic   
begin     temp_num := num1;                          ---assign num1 to temp_num     
  while (num1 > 0)                          ---check condition whether it is greather than 0
 loop   
  fact_num := fact_num * num1;              ---factorial number logic      
 num1   := num1 - 1;                       ---factorial number logic   
 end loop;    
 Dbms_Output.Put_line('factorial   is ' || fact_num); 
end;

Factorial is 6.

Example 3 : Write a program to check that number is Armstrong number or not.

Before starting the program let me first explain you about the Armstrong number.

An Armstrong number of three digits is an integer such that the sum of the cubes of its digits is equal to the number itself. For example, 371 is an Armstrong number since 3**3 + 7**3 + 1**3 = 371.

Program:

declare   
num number;  
 tot number:=0;   
var1 number;   
var2 number; 
begin   num:=#   
tmp:=num;   
while tmp>0   
loop   
  var1:=tmp mod 10;     
tot:= tot + (var1*var1*var1);     
tmp:=tmp/10;   
end loop;   
if(tot==num)
 then     
dbms_output.put_line(num||' is armstrong no'); 
  else   
  dbms_output.put_line(num||' is not a armstrong no');   
end if 
end;

If user gives the input as 371 then output will be,

371 is armstrong no.

Example 4 : Write a program to print the Fibonacci series.

This is most common program may ask in interview.

Program:

DECLARE  
   num number := &n;    
 n1 number := 0;   
  n2 number := 1;     
n3 number; BEGIN    
 dbms_output.put_line(n1);    
 dbms_output.put_line(n2);    
 for i in 3..num 
loop    
  n3 := n1 + n2;     
 dbms_output.put_line(n3);    
  n1 := n2;   
   n2 := n3; 
end loop; 
END;

Example 5 : Write a program to print prime numbers between 1 to 100.

This is also a most common example asked in interviews.

Program:

begin  
for i in 1..100 loop  
if i in (1,5,7) then  
dbms_output.put_line(' These are known prime numbers '||i); 
 end if;  if i not in (1,5,7) then  
if mod(i,3)=0 or mod(i,6)=0 or mod(i,9)=0
 then  null;   
elsif mod(i,2)=0 or mod(i,4)=0 or mod(i,8)=0 then  
null;  elsif mod(i,5)=0 or mod(i,10)=0 then  null;   
elsif mod(i,7)=0 then  null;  else 
 dbms_output.put_line(' Is this a prime number?? '||i);  
end if;  
end if;  
end loop;  
end;

Hope everyone likes this article on different PL/SQL Examples. Please comment in comment section if you like this article.