PL/SQL Basic Structure

PL/SQL is a block-structured language, which means that a program can be divided into logical blocks. A block consists of up to three sections, a declarative section, which is optional, a mandatory executable section and a further optional section that deals with exception handling.

The declarative section, which starts with the ‘Declare’ keyword, is where items are defined for use within the block, such as variables, constants, cursors and user defined exceptions. This section is optional because if the block doesn’t need any of these defined, then it isn’t necessary.

The executable section, which is mandatory, starts and ends with the ‘Begin’ and ‘End’ keywords. Here SQL and PL/SQL statements are placed that need to be run as part of the block.

The final section, which again is optional, is positioned just before the ‘End’ keyword of the executable section and deals with handling exceptions. If an error occurs in the executable section, then actions can be specified here to deal with them.

The example below shows the outline structure of a PL/SQL block.

DECLARE
   -- Variables, cursors and user defined functions.
BEGIN
   /* SQL statements
   PL/SQL statements */
EXCEPTION
   -- Actions to perform when errors occur.
END;

Notice that there is a semicolon after the ‘End’ keyword. This is required to terminate the statement. The lines of text in green are examples of code comments, which are useful to add in order to help explain the purpose of the statements at a later date. A single line comment can be preceded by ‘–‘, but if it is necessary for a comment to be over more than one line, then ‘/*’ would need to be placed at the start of the comment and ‘*/’ at the end.

A PL/SQL program can contain more than one block and indeed blocks can be nested inside one another. Items in the declarative section of a block can only be used in the block that they are defined.

The example PL/SQL block below just contains an executable section, which displays the message ‘Hello World’.

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

Once again, you will notice that there is a semicolon after the statement which outputs the message. This type of message output could be used for debugging purposes, to test the execution of a block.

Note that, in order to run the above example in SQL*Plus, a ‘/’ will need to be placed at the end. In SQL Developer use the ‘Run Script’ button or F5 short cut key. If the script runs successfully but ‘Hello World’ isn’t displayed on the screen then server output needs to be turned on. This only needs to be done once per session and not every time a script is run. Server output can be turned on as follows.

SET SERVEROUTPUT ON