Our social:

Latest Post

All ABout PL/SQL Blocks To Perform Pl/SQL Blocks with Example of Square , Leap Year , Odd and Even ,Reverse The String , factorial and Q & A

Theory:

Explain PL/SQL block.
A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables.

PL/SQL


DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;

Structure of basic loop 
Basic loop structure encloses sequence of statements in between the LOOP and END LOOP statements. With each iteration, the sequence of statements is executed and then control resumes at the top of the loop.
LOOP   
 Sequence of statements;
END LOOP;
Here, sequence of statement(s) may be a single statement or a block of statements. An EXIT statement or an EXIT WHEN statement is required to break the loop.

Structure of for loop
A FOR LOOP is a repetition control structure that allows you to efficiently write a loop that needs to execute a specific number of times.
FOR counter IN initial_value ..final_value LOOP
sequence_of_statements;
END LOOP;

Structure of while loop
A WHILE LOOP statement in PL/SQL programming language repeatedly executes a target statement as long as a given condition is true.
WHILE condition LOOP
sequence_of_statements
END LOOP;

If-then statement
The IF statement associates a condition with a sequence of statements enclosed by the keywords THEN and END IF. If the condition is TRUE, the statements get executed, and if the condition is FALSE or NULL, then the IF statement does nothing.
IF condition THEN
  Sequence of statements;
END IF;

If-then-else statement
A sequence of IF-THEN statements can be followed by an optional sequence of ELSE statements, which execute when the condition is FALSE.
IF condition THEN
  Sequence of statements;
ELSE
  Sequence of statements;
END IF;

If-then-elseif statement
The IF-THEN-ELSIF statement allows you to choose between several alternatives. An IF-THEN statement can be followed by an optional ELSIF...ELSE statement. The ELSIF clause lets you add additional conditions.
IF(boolean_expression 1)THEN
  Sequence of statements;
ELSIF( boolean_expression 2) THEN
  Sequence of statements;
ELSIF( boolean_expression 3) THEN
  Sequence of statements;
ELSE
  Sequence of statements;
END IF;



  1. Display square of given no from 1 to 10 using loop, for and while.

SET SERVEROUTPUT ON
DECLARE
  i INTEGER :=1;
  j INTEGER;
BEGIN
dbms_output.put_line('Simple loop');
   LOOP
       j :=i*i;
dbms_output.put_line('Square of '||i||' is '||j);
       i :=i+1;
       EXIT when i>10;
   END LOOP;
END;







OUTPUT:

Simple Loop
Square of 1 is 1
Square of 2 is 4
Square of 3 is 9
Square of 4 is 16
Square of 5 is 25
Square of 6 is 36
Square of 7 is 49
Square of 8 is 64
Square of 9 is 81
Square of 10 is 100

SET SERVEROUTPUT ON
DECLARE
  i INTEGER;
  j INTEGER;
BEGIN
dbms_output.put_line('For loop');
   for i in 1..10
   LOOP
       j :=i*i;
dbms_output.put_line('Square of '||i||' is '||j);
   END LOOP;
END;

OUTPUT:

For  Loop
Square of 1 is 1
Square of 2 is 4
Square of 3 is 9
Square of 4 is 16
Square of 5 is 25
Square of 6 is 36
Square of 7 is 49
Square of 8 is 64
Square of 9 is 81
Square of 10 is 100

SET SERVEROUTPUT ON
DECLARE
  i INTEGER :=1;
  j INTEGER;
BEGIN
dbms_output.put_line('While loop');
   while i<=10
   LOOP
       j :=i*i;
dbms_output.put_line('Square of '||i||' is '||j);
       i :=i+1;
   END LOOP;
END;

OUTPUT:

While Loop
Square of 1 is 1
Square of 2 is 4
Square of 3 is 9
Square of 4 is 16
Square of 5 is 25
Square of 6 is 36
Square of 7 is 49
Square of 8 is 64
Square of 9 is 81
Square of 10 is 100



  1. Write a PL-SQL block for checking weather a given year is a Leap year or not.

SET SERVEROUTPUT ON
DECLARE
   year INTEGER;
BEGIN
dbms_output.put_line('Enter year:');
   year :='&year';
   if MOD(year,4)=0 then
dbms_output.put_line('Leap year');
   else
dbms_output.put_line('Not leap year');
   END if;
END;

OUTPUT:

Enter  year: 2008
old   5: year:='&year';
new   5: year:='2008';
enter year:                                                                     
2008is leap year                                                                

PL/SQL procedure successfully completed.







  1. Write a PL-SQL block to fine total no of odd and even (from 1 to 20)

SET SERVEROUTPUT ON
DECLARE
   i INTEGER;
   j INTEGER:=0;
   k INTEGER:=0;
BEGIN
   for i in 1..20
   LOOP
   If (I MOD 2=0) then
       j :=j+1;
   else
       k :=k+1;
   END if;
   END LOOP;
dbms_output.put_line('Even num: '||j);
dbms_output.put_line('Odd num: '||k);
END;

OUTPUT:

Even num:10
Odd num:10

PL/SQL procedure successfully completed.


  1. Write a PL-SQL block for reverse the string.

SET SERVEROUTPUT ON
DECLARE
str VARCHAR2(20);
revstr VARCHAR2(20);
len INTEGER;
   i INTEGER;
BEGIN
str :='&str';
len :=LENGTH(str);
   for i in reverse 1..len
   LOOP
revstr :=revstr|| substr(str,i,1);
   END LOOP;
dbms_output.put_line('Reverse of '||str||' is '||revstr);
END;

OUTPUT:

old   7: str:='&str';
new   7: str:='database';
                                                          
Reverse of database is esabatad                                                    

PL/SQL procedure successfully completed.


  1. Write a PL-SQL block for  factorial number.

SET SERVEROUTPUT ON
DECLARE
   fact INTEGER :=1;
num INTEGER;
BEGIN
num :='&num';
   for i in 1..num
   LOOP
       fact :=fact*i;
   END LOOP;
dbms_output.put_line('Factorial of '||num||' is '||fact);
END;

OUTPUT:

old  5: num:='&str';
new  5: num:='7';
Factorial of 7 is 5040                      
PL/SQL procedure successfully completed.


Questions:

  1. Difference between PL/SQL

PL/SQL
SQL
PL/SQL is a procedural language to create applications.
SQL is a data oriented language for selecting and manipulating sets of data.
PL/SQL is executes as a block of code.
SQL executes one statement at a time.
PL/SQL tell the database how to do things (procedural).
SQL tells the database what to do (declarative), not how to do it.
PL/SQL is used to code program blocks, triggers, functions, procedures and packages.
SQL is used to code queries, DML and DDL statements.
we cannot embed PL/SQL within a SQL statement.
We can embed SQL in a PL/SQL program

  1. Difference between varchar and varchar2.

The difference between VARCHAR and VARCHAR2 is that VARCHAR is ANSI standard but takes up space whereas VARCHAR2 is Oracle-only but makes more efficient use of space.
Varchar and Varchar2 both are of variable character.
Varchar can have maximum 2000 character while Varchar can contain maximum 4000 character.

In-order, Pre-order and Post-order Tree Traversal Explain : using C and c++ Programming

Unlike linear data structures (Array, Linked List, Queues, Stacks, etc) which have only one logical way to traverse them, trees can be traversed in different ways. Following are the generally used ways for traversing trees.



depth First Traversals:
(a) Inorder
(b) Preorder
(c) Postorder
Breadth First or Level Order Traversal
Inorder Traversal:
Algorithm Inorder(tree)
   1. Traverse the left subtree, i.e., call Inorder(left-subtree)
   2. Visit the root.
   3. Traverse the right subtree, i.e., call Inorder(right-subtree)
Uses of Inorder
In case of binary search trees (BST), Inorder traversal gives nodes in non-decreasing order. To get nodes of BST in non-increasing order, a variation of Inorder traversal where Inorder itraversal s reversed, can be used.
Example: Inorder traversal for the above given figure is 4 2 5 1 3.

Practice Inorder Traversal

Preorder Traversal:
Algorithm Preorder(tree)
   1. Visit the root.
   2. Traverse the left subtree, i.e., call Preorder(left-subtree)
   3. Traverse the right subtree, i.e., call Preorder(right-subtree) 
Uses of Preorder
Preorder traversal is used to create a copy of the tree. Preorder traversal is also used to get prefix expression on of an expression tree. Please see http://en.wikipedia.org/wiki/Polish_notation to know why prefix expressions are useful.
Example: Preorder traversal for the above given figure is 1 2 4 5 3.

Practice Preorder Traversal

Postorder Traversal:
Algorithm Postorder(tree)
   1. Traverse the left subtree, i.e., call Postorder(left-subtree)
   2. Traverse the right subtree, i.e., call Postorder(right-subtree)
   3. Visit the root.
Uses of Postorder
Postorder traversal is used to delete the tree. Please see the question for deletion of tree for details. Postorder traversal is also useful to get the postfix expression of an expression tree. Please seehttp://en.wikipedia.org/wiki/Reverse_Polish_notation to for the usage of postfix expression.



In-order traversal method:
1. Visit Left Sub-Tree
2. Process Current Node
3. Visit Right Sub-Tree

Pre-order traversal method:
1. Process Current Node
2. Visit Left Sub-Tree
3. Visit Right Sub-Tree

Post-order traversal method:
1. Visit Left Sub-Tree
2. Visit Right Sub-Tree
3. Process Current Node

C/C++ Implementation with full program:

#include <stdio.h>
#include <stdlib.h>

struct node
{
    int value;
    node* left;
    node* right;
};


struct node* root;

struct node* insert(struct node* r, int data);
void inOrder(struct node* r);
void preOrder(struct node* r);
void postOrder(struct node* r);

int main()
{
    root = NULL;
    int n, v;

    printf("How many data's do you want to insert ?\n");
    scanf("%d", &n);

    for(int i=0; i<n; i++){
        printf("Data %d: ", i+1);
        scanf("%d", &v);
        root = insert(root, v);
    }

    printf("Inorder Traversal: ");
    inOrder(root);
    printf("\n");

    printf("Preorder Traversal: ");
    preOrder(root);
    printf("\n");

    printf("Postorder Traversal: ");
    postOrder(root);
    printf("\n");


    return 0;
}

struct node* insert(struct node* r, int data)
{
    if(r==NULL)
    {
        r = (struct node*) malloc(sizeof(struct node));
        r->value = data;
        r->left = NULL;
        r->right = NULL;
    }
    else if(data < r->value){
        r->left = insert(r->left, data);
    }
    else {
        r->right = insert(r->right, data);
    }
    return r;

}

void inOrder(struct node* r)
{
    if(r!=NULL){
        inOrder(r->left);
        printf("%d ", r->value);
        inOrder(r->right);
    }
}

void preOrder(struct node* r)
{
    if(r!=NULL){
        printf("%d ", r->value);
        preOrder(r->left);
        preOrder(r->right);
    }
}

void postOrder(struct node* r)
{
    if(r!=NULL){
        postOrder(r->left);
        postOrder(r->right);
        printf("%d ", r->value);

    }
}