A pl/sql is a named pl/sql block

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:

<> DECLARE

BEGIN

  --program statements.

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

Labeled  Nested Block Syntax:

<> DECLARE

BEGIN

  --program statements.

   LABELED BLOCK Syntax:

            <> 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

<>DECLARE

  m NUMBER:=10;

BEGIN

m :=30;

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

  <> 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.

What is named block in PL SQL?

What is PL SQL block in Oracle?

What is PL SQL block structure?

What are the three PL SQL block types?