Block Structure of PL/SQL in DBMS

In this tutorial, we are going to learn about the block structure of PL/SQL in the DBMS (database management system).
Submitted by Anushree Goswami, on July 01, 2019

PL/SQL is Block Structured. What is Block? PL/SQL programs are built on the basic units. These basic units are the blocks. Blocks can be named as Functions and Procedures or Anonymous Blocks as well. There is a different section of blocks:

Header Section
Declaration Section
Executable Section
Exception Section

This is how Block Structure works in PL/SQL...

    HEADER
    Type and Name of block 
    DECLARE 
    Variables; Constants; Cursors; 
    BEGIN 
    PL/SQL and SQL Statements
    EXCEPTION 
    Exception handlers
    END;

The blocks in PL/SQL are defined by the keywords DECLARE, BEGIN, EXCEPTION and END which divides the block into three sections:

Declarative: Declarative statements are those statements that are used to declare variables, constants and other code elements, which can be further used in that block.
Executable: Executable statements are those statements that are run when the block is executed.
Exception Handling: Exception Handling is the special structured section that can be used to "catch" or trap any exceptions that are raised when the executable section runs.

To run this section, only the executable section is required. You don't have to declare anything in a block and you also don't have to catch an exception raised in that block.

It is an important point to be taken into count, a block is itself an executable statement, and so nested blocks can be applied in it.

For e.g. –

    DECLARE
    a number;
    text1 varchar2 (20);
    text2 varchar2 (20):= "HI";
    BEGIN
    ------------------------------
    END;

The important data types in PL/SQL are these:

NUMBER, INTEGER, CHAR, VARCHAR2, DATE etc.

To convert the string into the data: to_date('02-05-2007', 'dd-mm-yyyy')

E.g. 1- How to write a HELLO WORLD! Program in PL/SQL?

The HELLO WORLD! Program contains an executable section that invokes the procedure DBMS_OUTPUT.put_line to display the text on the screen.

Syntax:

BEGIN
DBMS_OUTPUT.put_line('HELLO WORLD!');
END;

E.g. 2- How to input E.g. 1 in a form of string in a PL/SQL program?

Here, block declares the variable of the type VARCHAR2 (string) whose maximum length is 100 bytes which can hold the string 'HELLO WORLD!' which is given as input by the user. DBMS_OUTPUT.put_line then accepts the variable and not the literal string in order to display it on the screen.

Syntax:

DECLARE
l_messageVARCHAR2 (100):='HELLO WORLD!';
BEGIN
DBMS_OUTPUT.put_line(l_message);
END;

E.g. 3- How to catch any exception if they might occur in E.g. 2?

Here, an exception section is added in order to catch the exception (WHEN OTHERS) that might be raised when an error is found in the program and displays an error message which is returned by the SQL (provided by Oracle).

Syntax:

DECLARE
l_messageVARCHAR2 (100):='HELLO WORLD!';
BEGIN
DBMS_OUTPUT.put_line(l_message);
EXCEPTIONWHENOTHERS
THEN
DBMS_OUTPUT.put_line(SQL);
END;

E.g. 4- How to nest the blocks as well as join together multiple strings in E.g. 3?

Here, In order to nest the blocks, two variable of the type VARCHAR2 (string) whose maximum length is 100 bytes are needed to be declared and a concatenation (||) operator is used to join together multiple strings.

Syntax:

DECLARE 
l_messageVARCHAR2 (100):= 'HELLO'; 
BEGIN 
DECLARE 
l_message2 VARCHAR2 (100):= l_message||' WORLD!';
BEGIN 
DBMS_OUTPUT.put_line(l_message2); 
END; 
EXCEPTION WHEN OTHERS 
THEN DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack); 
END;

There are various tools to execute PL/SQL code. The most basic and popular one is SQL*Plus. It is a command-line interface and is used to execute SQL statements as well as PL/SQL blocks.

When some developers are solely dependent on SQL*Plus there are many others who use different IDE (Integrated Development Environment) for the same. The most popular IDE's are: