PL/SQL Block

In a PL/SQL program, code is written in blocks. Each PL/SQL block has 3 sections, which are:

1. Declare section
2. Begin section
3. Exception section

Followed by END statement at the end.

PL/SQL Block

PL/SQL block creates the structured logical blocks of code that describes the process to be executed. Such a block consists of SQL statements and PL/SQL instructions that are then passed to the oracle engine for execution. PL/SQL block consists of the following four sections:

• DECLARE Section:

  PL/SQL code starts with a declaration section in which memory variables and other oracle objects like cursor, triggers etc can be declared and if required can be initialized as well. Once declared/initialised we can use them in SQL statements for data manipulation. As it is not necessary that we would require variables etc in every PL/SQL code, hence this section is an optional section.

• BEGIN Section:

  This section contains the SQL and PL/SQL statements that are required to be executed and contains the main logic. This section is responsible for handling the data retrieval and manipulation, may be working with branching, can use looping and conditional statements, etc.

• EXCEPTION Section:

  This section is optional. It is mainly used to handle the errors that may occur between BEGIN and EXCEPTION sections.

• END Section:

  This section is the indication of the end of the PL/SQL block.

Time for an Example!

Let's start with writing a basic PL/SQL block which will do nothing or simply print something in the console. This will help us understand the syntax.

BEGIN
   NULL;
END;

The above PL/SQL block has no DECLARE section, only BEGIN and END section. It will do nothing because the NULL statement does nothing.

We can

Display Output using PL/SQL Block

If you want to display output of your PL/SQL block's execution, you can do so by using the DBMS_OUTPUT.PUT_LINE statement.

Let's take a simple example:

BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
/

Hello World!

If you look closely in the PL/SQL block above, we have put an extra / after the END; statement, using that will execute the script automatically.

Anonymous PL/SQL Blocks

Generally PL/SQL blocks are a part of big programs and are either sub-programs, or functions, or procedures, or triggers etc, but they can be defined independently and can be executed as well.

When we define a PL/SQL block with no header, its known as Anonymous Block.

Let's take an example of a PL/SQL block which will add two numbers and show the result in output:

DECLARE 
    -- declare variable a and b  
    -- and these variables have integer datatype  
   a number;  
   b number;   
BEGIN 
   a:= 7;  
   b:= 77;  
   dbms_output.put_line('Sum of the number is: ' || a + b);  
END;  
/

84

If you do not understand how we did this, don't worry. This tutorial is just an introduction, we will learn about PL/SQL operators, datatypes, variables etc in the next few tutorials, so click on Next → and go to next tutorial.