Blocks in PL/SQL

In PL/SQL, All statements are classified into units that is called Blocks. PL/SQL blocks can include variables, SQL statements, loops, constants, conditional statements and exception handling. Blocks can also build a function or a procedure or a package.

The Declaration section: Code block start with a declaration section, in which memory variables, constants, cursors and other oracle objects can be declared and if required initialized.

The Begin section: Consist of set of SQL and PL/SQL statements, which describe processes that have to be applied to table data. Actual data manipulation, retrieval, looping and branching constructs are specified in this section.

The Exception section: This section deals with handling errors that arise during execution data manipulation statements, which make up PL/SQL code block. Errors can arise due to syntax, logic and/or validation rule.

The End section: This marks the end of a PL/SQL block.

 Broadly, PL/SQL blocks are two types: Anonymous blocks and Named blocks are as follows:

 1. Anonymous blocks: In PL/SQL, That's blocks which is not have header are known as anonymous blocks. These blocks do not form the body of a function or triggers or procedure. Example: Here a code example of find greatest number with Anonymous blocks. 

DECLARE 
    -- declare variable a, b and c 
    -- and these three variables datatype are integer  
   a number; 
   b number; 
   c number; 
BEGIN 
   a:= 10; 
   b:= 100; 
    --find largest number
    --take it in c variable
   IF a > b THEN 
      c:= a; 
   ELSE 
      c:= b; 
   END IF;
   dbms_output.put_line(' Maximum number in 10 and 100: ' || c); 
END; 
/ 
-- Program End 

Output:

Maximum number in 10 and 100: 100

2. Named blocks: That's PL/SQL blocks which having header or labels are known as Named blocks. These blocks can either be subprograms like functions, procedures, packages or Triggers. Example: Here a code example of find greatest number with Named blocks means using function. 

DECLARE 

    -- declare variable a, b and c 
    -- and these three variables datatype are integer  
DECLARE 
   a number; 
   b number; 
   c number; 
   --Function return largest number of  
   -- two given number
FUNCTION findMax(x IN number, y IN number)  
RETURN number 
IS 
    z number; 
BEGIN 
   IF x > y THEN 
      z:= x; 
   ELSE 
      Z:= y; 
   END IF;  
   RETURN z; 
END; 
BEGIN 
   a:= 10; 
   b:= 100;  
   c := findMax(a, b); 
   dbms_output.put_line(' Maximum number in 10 and 100 is: ' || c); 
END; 
/ 
-- Program End 

Output:

Maximum number in 10 and 100: 100