Review of 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;

/

 

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

 

PL/SQL Block Structure

 

  PL/SQL  is a block-structured language. Each of the basic programming units you write to build your application is (or should be) a logical unit of work. The  PL/SQL   block allows you to reflect that logical structure in the physical design of your programs.

 

 The block determines both the scope of identifiers (the area of code in which a reference to the identifier can be resolved) and the way in which exceptions are handled and propagated. A block may also contain nested sub-blocks of code, each with its own scope.

 

 There is a common block structure to all the different types of modules. The block is broken up into four different sections, as follows:

 

Header

 

 Relevant for named blocks only, the header determines the way that the named block or program must be called. The header includes the name, parameter list, and  RETURN  clause (for a function only).

 

Declaration section

 

 The part of the block that declares variables, cursors, and sub-blocks that are referenced in the execution and exception sections. The declaration section is optional, but if you have one, it must come before the execution and exception sections.

 

Execution section

 

 The part of the  PL/SQL  block containing the executable statements, the code that is executed by the  PL/SQL   run-time engine. The execution section contains the  IF-THEN-ELSEs ,  LOOPs ,  assignments , and calls to other  PL/SQL  blocks. Every block must have at least one executable statement in the execution section.

 

Exception section

 

 The section that handles exceptions to normal processing (warnings and error conditions). This final section is optional. If it is included, control is transferred to this section when an error is encountered. This section then either handles the error or passes control to the block that called the current block.

 

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

 

Sequence of Section Construction:

 

 The ordering of the sections in a block corresponds to the way you would write your programs and the way they are executed.

 

Step 1

 

 Define the type of block ( procedure ,  function ,  anonymous ) and how it is called ( header ).

 

Step 2

 

 Declare any variables used in that block ( declaration  section).

 

Step 3

 

 Use those local variables and other  PL/SQL  objects to perform the required actions (execution section).

 

Step 4

 

 Handle any problems that arise during the execution of the block (exception section).

 

 The declaration and exception sections are optional in any  PL/SQL  block, but you must always have at least one executable statement in a block.

 

PL/SQL Block Structure Example:

 

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

 

BEGIN 

 NULL; 

END;

/

 

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 ON  is  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  SQL*Plus  on a screen using  SQL*Plus :

 

SET SERVEROUTPUT ON SIZE 1000000

BEGIN 
 DBMS_OUTPUT.PUT_LINE('Hello PL/SQL'); 
END;

/

 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.
 

 

Fig 1: SQL*Plus Login

 

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

  

Fig 2: PL/SQL Hello World

 

 

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 .