PL/SQL BLOCKS Includes Everything with Examples

PL/SQL BLOCKS Learn about PL/SQL Blocks, Structure and Syntax with examples including Nested Block and Anonymous Block in Oracle Data base.

PL/SQL block is a logical collection of procedural as well as non procedural statements. Here is what procedural / Non procedural statements are.

Procedural Statements – All  non SQL statements are procedural.
Non -Procedural statements – All SQL Statements are non procedural.

PL/SQL Blocks Structure Approach

• The basic unit in PL/SQL is a block.
• All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in the program.
• PL/SQL is Block Structured language divided into three logical blocks.
  1. Declarative part – this is optional
  2. Executable part – this is mandatory
  3. Exception handling – this is optional

PL/SQL Blocks Syntax

DECLARE
       -- Declarative part
      [Variable_declarations]
      [Cursor_declarations]
      [User_defined exceptions]
BEGIN

      --Execution part
      Program_code  --SQL and PL/SQL statements
EXCEPTION

     --Exception handling part
      Exception_handlers  – action to perform 
      when an exception occurs
END;

Brief about block structure parts

Declarative Part:

• used to define variables, user defined types, cursors..etc,. in the executable part for further manipulations.

Executable Part.

• All procedural statements are included between the BEGIN and END statement.
• It must have one executable statement.

Exception Handling Part:

• Error that Occur during execution of PL/SQL Block are deals with exception handling.

NOTES:

• BEGIN block and END;keyword are mandatory of any PL/SQL program
• DECLARE and Exception Blocks are optional.
• END; is not a block, it is a keyword that says end of PL/SQL program ‘;’ at the end.
• PL/SQL block structure follows divide-and-conquer approach to solve the problem step wise.

PL/SQL Blocks Types

• Anonymous Block
• Named Block

 

Anonymous Blocks

• For this blocks have no names are assigned to identify
• This blocks are created and executed at run time in the same session because they will not be stored in the database server as database objects.
• For this  type of blocks are compilation and execution happens in a single process.
• From this blocks we can call Named blocks like procedures, packages, functions etc., but we cannot call anonymous blocks because they are not stored in the database.
• It can have named or anonymous nested blocks.

 

Named Blocks

• For this blocks we can have a specific and unique name.
• This are stored as a database objects in the server.
• This can be used as long as server exist.
• Compilation and execution process happen separately while creating them as database objects.
• This blocks can be called from other blocks.
• This block structure is same as anonymous blocks except that ‘Declare’ Keyword is not used. Instead of ‘Declare’ Keyword , ‘Create’ Keyword is be used to insist the database as to store it as database object.
• This blocks contains Nested Blocks.
• Procedures and Functions are comes named blocks.

Example programs for Anonymous Blocks

A simple PL/SQL Block Example

Example:

DECLARE

BEGIN

    dbms_output.put_line('hello world');

END;

Output

hello world

PL SQL NESTED BLOCKS

• A block with in the BEGIN and END; block is called Nested blocks.

Nested PL/SQL Blocks Syntax:

DECLARE

BEGIN
   Program_code  --SQL and PL/SQL statements
   BEGIN
          Program_code  --SQL and PL/SQL statements
  END;
END;

Nested Block Example

DECLARE

BEGIN

       dbms_output.put_line('I am Going to School');

       BEGIN

       dbms_output.put_line('I am reading in a class');

       END;

END;

Output:

I am Going to School
I am reading in a class

 

Some simple example without declaration of blocks.

Example 1

BEGIN

    NULL;

END

Example 2

BEGIN

  RETURN;  //--Block with Return statement.

END

Some simple example with declaration of blocks.

Example 3

DECLARE

BEGIN

  NULL;

END;

MORE TOPICS ON PL-SQL BLOCKS

LABELED BLOCK

• PLACE A Lable before the DECLARE KEYWORD.
• Labelled blocks allows to access the variables that would not visible using anonymous blocks.

Labeled Block Syntax:

<<LABEL_NAME1>> DECLARE

BEGIN

  --program statements.

END  LABEL_NAME;--at the end using of LABEL_NAME is optional.

Labeled  Nested Block Syntax:

<<LABEL_NAME>> DECLARE

BEGIN

  --program statements.

   LABELED BLOCK Syntax:

            <<LABEL_NAME2>> DECLARE

            BEGIN

             --program statements.

            END  LABEL_NAME2;--at the end using of LABEL_NAME is optional.

END  LABEL_NAME1;--at the end using of LABEL_NAME is optional.

Example Program

<<BLOCK_A>>DECLARE

  m NUMBER:=10;

BEGIN

m :=30;

DBMS_OUTPUT.PUT_LINE('THE VALUE OF m ='||m);

  <<BLOCK_B>> DECLARE

     n NUMBER :=20;
     v_total NUMBER;
     m NUMBER :=85;

   BEGIN

    BLOCK_A.m:=90;
    n:=80;
    v_total:=BLOCK_A.m+n;

     DBMS_OUTPUT.PUT_LINE('THE sum of m,n is  ='||v_total);
     DBMS_OUTPUT.PUT_LINE('THE sum of m,n is  ='||m);

   END BLOCK_B;

DBMS_OUTPUT.PUT_LINE('THE VALUE OF m ='||m); 

END BLOCK_A;

OUTPUT

THE VALUE OF m =30
THE sum of m,n is  =170
THE sum of m,n is  =85
THE VALUE OF m =90

NOTES:

• use of Labelled blocks is that labelled blocks allow to access those variables that would not visible when using anonymous block.
• Use of label name at the end of the block is optional

NESTED BLOCKS AND  VARIABLE SCOPE

Prerequisite: 

Variables in PL SQL

Scope of a variable means lifetime of the variable that exists in the PL/SQL Block. Once PL/SQL Block complete its execution, the life time of the variable is lost and space is released.

NESTED BLOCKS Syntax 

DECLARE
  --VARIABLE_DECLARATION
BEGIN
    -- --EXECUTION PART
       DECLARE
             --VARIABLE DECLARATION
       BEGIN
            --EXECUTION PART
            --  EXCEPTIIONS
      END;
 --EXCEPTION PART

END;

PL/SQL Blocks can be nested when ever an executable is allowed.

A nested block becomes a statement.

An exception section contains nested blocks.

NOTES:

• with in the same scope all identifiers are unique.
• Variables names should not be same even though data types are differ.
• Lifetime of the variable exists until the completing the execution  of PL/SQL Block. If execution completes, the life of variable will lost.

Example of Nested Blocks and Variable Scope

EXAMPLE 1

DECLARE
  m NUMBER:=10;
BEGIN
DBMS_OUTPUT.PUT_LINE('THE VALUE OF m ='||m);
   DECLARE
     m NUMBER:=20;
   BEGIN
     DBMS_OUTPUT.PUT_LINE('THE VALUE OF m ='||m);
   END;
DBMS_OUTPUT.PUT_LINE('THE VALUE OF m ='||m);  
END;

OUTPUT:
THE VALUE OF m =10
THE VALUE OF m =20
THE VALUE OF m =10

EXAMPLE -2

DECLARE
  m NUMBER:=10;
BEGIN
m :=30;
DBMS_OUTPUT.PUT_LINE('THE VALUE OF m ='||m);
   DECLARE
     n NUMBER:=20;
     v_total NUMBER;
   BEGIN
    m:=90;
    n:=80;
    v_total:=m+n;
     DBMS_OUTPUT.PUT_LINE('THE sum of m,n is  ='||v_total);
   END;--variable n and v_total lost its scope afte the end of this block.
DBMS_OUTPUT.PUT_LINE('THE VALUE OF m ='||m);  
END;

Output:

THE VALUE OF m =30
THE sum of m,n is  =170
THE VALUE OF m =90