PL/SQL Datatypes  «Prev 

Building a PL/SQL block

Building Blocks of PL/SQL Programs

PL/SQL is a block-structured language and a PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END, which break up the block into three sections:
  1. Declarative: statements that declare variables, constants, and other code elements, which can then be used within that block
  2. Executable: statements that are run when the block is executed
  3. Exception handling: a specially structured section you can use to catch, or trap, any exceptions that are raised when the executable section runs
Only the executable section is required. You do not have to declare anything in a block, and you do not have to trap exceptions raised in that block.
A block itself is an executable statement, so you can nest blocks within other blocks. Here are some examples:
The classic Hello World! block contains an executable section that calls the DBMS_OUTPUT.PUT_LINE procedure to display text on the screen:

BEGIN
  DBMS_OUTPUT.put_line ('Hello World!');
END;

Functions and procedures types of named blocks are discussed later in this article in more detail, as are packages. A package is a container for multiple functions and procedures and Oracle extends PL/SQL with many supplied or built-in packages.
This next example block declares a variable of type VARCHAR2 (string) with a maximum length of 100 bytes to hold the string Hello World!.
DBMS_OUTPUT.PUT_LINE then accepts the variable, rather than the literal string, for display:
DECLARE
  l_message  
  VARCHAR2 (100) := 'Hello World!';
BEGIN
  DBMS_OUTPUT.put_line (l_message);
END;
 

Note that I named the variable l_message. I generally use the l_ prefix for local variables variables defined within a block of code and the g_ prefix for global variables defined in a package.

This next example block adds an exception section that traps any exception (WHEN OTHERS) that might be raised and displays the error message, which is returned by the SQLERRM function (provided by Oracle).

DECLARE
  l_message  
  VARCHAR2 (100) := 'Hello World!';
BEGIN
  DBMS_OUTPUT.put_line (l_message);
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line (SQLERRM);
END;
  

The following example block demonstrates the PL/SQL ability to nest blocks within blocks as well as the use of the concatenation operator (||) to join together multiple strings.
DECLARE
  l_message  
  VARCHAR2 (100) := 'Hello';
BEGIN
  DECLARE
    l_message2     VARCHAR2 (100) := 
      l_message || ' World!'; 
  BEGIN
    DBMS_OUTPUT.put_line (l_message2);
  END;
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line 
   (DBMS_UTILITY.format_error_stack);
END;
  

DECLARE signals the start of the declarative section of a PL/SQL block. Variables, cursors, and exceptions are declared within this section.

BEGIN signals the start of the execution section of a PL/SQL block. A block must contain at least one executable statement.

Exception signals the start of the exception handling routines within a PL/SQL block. This section is optional.

END signals the end of the block.

A complete PL/SQL block.