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.
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
rev_num=rev_num*10+mod(num1,10); ---Reverse condition
num1=num1/10; ---Reverse condition
Dbms_Output.Put_Line('Reverse of Number'||num1||'is'|| rev_num);
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
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
temp_num := num1; ---assign num1 to temp_num
while (num1 > 0) ---check condition whether it is greather than 0
fact_num := fact_num * num1; ---factorial number logic
num1 := num1 - 1; ---factorial number logic
is ' || fact_num);
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.
var1:=tmp mod 10;
tot:= tot + (var1*var1*var1);
dbms_output.put_line(num||' is armstrong no');
dbms_output.put_line(num||' is not a armstrong no');
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.
num number := &n;
n1 number := 0;
n2 number := 1;
for i in 3..num
n3 := n1 + n2;
n1 := n2;
n2 := n3;
Example 5 : Write a program to print prime numbers between 1 to 100.
This is also a most common example asked in interviews.
Hope everyone likes this article on different PL/SQL Examples. Please comment in comment section if you like this article.