PL/SQL Block Structure

Introducing PL/SQL block structure and anonymous block

PL/SQL program units organize the code into blocks. A block without name is known as anonymous block. The anonymous block is the simplest unit in PL/SQL. It is called anonymous block because it is not saved in the database. The anonymous blocks are only one-time use and useful in certain situations such as creating test units. The following illustrates anonymous block syntax:
PL/SQL code   
  1. [DECLARE]
  2.    Declaration statements;
  3. BEGIN
  4.    Execution statements;
  5.   [EXCEPTION]
  6.       EXCEPTION handling statements;
  7. END;
  8. /
Let’s examine the block structure in detail.
The anonymous block has three basic parts that are declaration, execution, and exception handling. Only execution part is required and the others are optional.
  • The declaration part allows you to define data types, structures, and variables. You can also declare a variable in the declaration part by giving it a name, a data type and a initial value. You can both define and declare variables in the declaration part.
  • The execution part is required in block structure and it must have at least one statement. The execution part is where you put the execution code or business logic. You can use both procedural and SQL code inside execution part.
  • The exception handling part is starting with the keyword EXCEPTION. The exception part is where you put the code to manage exceptions. You can either catch or handle exceptions in this part.
Note that the single forward slash (/) is a signal to tell SQL*Plus to execute the PL/SQL block.

PL/SQL block structure examples

Let’s take a look at the simplest block that does nothing.
PL/SQL code   
  1. BEGIN
  2.    NULL;
  3. END;
If you execute the above anonymous block in SQL*Plus you will it issues a message saying that “PL/SQL procedure successfully completed.”
Now if we want to output it on screen we execute the following block:
PL/SQL code   
  1. SET SERVEROUTPUT ON SIZE 1000000
  2. BEGIN
  3.    DMBS_OUTPUT.PUT_LINE('Hello PL/SQL');
  4. END;
  5. /
The first command does not belong to the anonymous block. It is telling SQL*Plus  to echo the database’s output to the screen after executing PL/SQL procedure.
In the above examples, you just uses the execution part to execute code. You will learn how to declare variables and handling exceptions in the next tutorials.

Exercise on anonymous block structure

Now it is your turn to create a block and execute it in SQL*Plus that print a greeting message “Hello Word” on screen.
First, you need to login to the SQL*Plus by the account by providing username and password as the figure 1 below.
SQL*Plus Login
Figure 1. SQL*Plus Login
Second, type the following code into the SQL*Plus and execute it as the figure 2 below:
PL/SQL Hello World
Figure 2.PL/SQL Hello World
Congratulation, you’ve finished the first PL/SQL program!