# 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;````
Output:
If user gives input as 3 then output is
`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:=&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;````
Output:
```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;````

## 6 Replies to “PL/SQL Examples for Interview | Important Examples of PL SQL”

1. Diptimayee Swain says:

can u please send some scenario based questions related to packages,procedures,functions,triggers..

1. Amit S says:

Sure Diptimayee…

I sent you interview questions.

2. arnab says:

Can you send some real scenario based programs which are expected in interview?

1. Amit S says:

Hi Arnab,

You can check out the Website in detail.. You will get so many real examples to study.

3. Jomol S says:

can u please send some scenario based questions related to packages,procedures,functions,triggers,views,performance tuning

1. Amit S says:

Sure Jomol!