Our social:

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.