PL/SQL Block Structure

Summary: in this tutorial, you will learn about the PL/SQL block structure and how to write and execute the first PL/SQL block in SQL*PLUS.

Introducing PL/SQL block structure and anonymous block

PL/SQL program units organize the code into blocks. A block without a name is known as an anonymous block. The anonymous block is the simplest unit in PL/SQL. It is called anonymous block because it is not saved in the Oracle database.

An anonymous block is an only one-time use and useful in certain situations such as creating test units. The following illustrates anonymous block syntax:

[DECLARE]
   Declaration statements;
BEGIN
   Execution statements;
  [EXCEPTION]
      Exception handling statements;
END;
/Code language: SQL (Structured Query Language) (sql)

Let’s examine the PL/SQL block structure in greater detail.

The anonymous block has three basic sections that are the declaration, execution, and exception handling. Only the execution section is mandatory and the others are optional.

The declaration section allows you to define data types, structures, and variables. You often declare variables in the declaration section by giving them names, data types, and initial values.
The execution section is required in a block structure and it must have at least one statement. The execution section is the place where you put the execution code or business logic code. You can use both procedural and SQL statements inside the execution section.
The exception handling section is starting with the EXCEPTIONkeyword. The exception section is the place that you put the code to handle exceptions. You can either catch or handle exceptions in the exception section.

Notice that the single forward slash (/) is a signal to instruct SQL*Plus to execute the PL/SQL block.

SQL*Plus is an Oracle database client tool that executes PL/SQL statements and outputs the query’s results. SQL*Plus provides administrators and programmers with command-line interface to work with Oracle database. SQL*Plus is commonly referred as SQLPLUS.

PL/SQL block structure example

Let’s take a look at the simplest PL/SQL block that does nothing.

BEGIN
   NULL;
END;Code language: SQL (Structured Query Language) (sql)

If you execute the above anonymous block in SQL*Plus you will see that it issues a message saying:

PL/SQL procedure successfully completed.

Because the NULL statement does nothing.

To display database’s output on the screen, you need to:

First, use the SET SERVEROUTPUT ON command to instruct SQL*Plus to echo database’s output after executing the PL/SQL block. The SET SERVEROUTPUT ONis SQL*Plus command, which is not related to PL/SQL.
Second, use the DBMS_OUTPUT.PUT_LINE procedure to output a string on the screen.

The following example displays a message Hello PL/SQL on a screen using SQL*Plus:

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello PL/SQL');
END;
/Code language: SQL (Structured Query Language) (sql)

In this example, you just use the execution part to execute code. You will learn how to declare variables and handle exceptions in the next tutorials.

Exercise on anonymous block structure

Now, it is your turn to create a PL/SQL block and execute it in SQL*Plus that display a greeting message “Hello World” on the screen.

First, you need to login to the Oracle database via SQL*Plus by providing a username and password as shown the following picture.

Second, type the following code into the SQL*Plus and execute it as the following picture:

Congratulation, you’ve finished the first PL/SQL program!

In this tutorial, you have learned how PL/SQL organizes the code using block structure, and how to create the first PL/SQL and execute it using SQL*PLUS.