Basic PL/SQL Block Structure

A standard PL/SQL code segment is called a block. Unlike SQL, PL/SQL allows logical grouping of a set of statements that can be sent to the Oracle database as a single block.

Conceptually, a block can consist of three parts, or sections:

1. An optional declaration section, where you can declare variables, constants, and exceptions
2. A required section of executable statements
3. An optional section of exception handlers

The order of the blocks in the code segment is logical. Blocks begin with the declarative part, where you declare the constants, variables, records, cursors, or exceptions. Once these are declared, you can manipulate them to build your program logic within the executable section. Exceptions raised during execution can be dealt with in the exception handling section. A single exception handler is processed before the PL/SQL block is exited. Each block can contain nested blocks. You will learn about nested blocks in the next lesson.
Typically, a PL/SQL block must contain DECLARE, BEGIN, and END keywords. However, it may also include EXCEPTION keywords, where you can handle any exceptions for your block. Use the following series of images to see the order in which these sections appear within a block.

Oracle PL/SQL is a robust procedural language that allows developers to create sophisticated applications with complex business logic. It includes several reserved words that have special meanings and must be used properly to ensure that your PL/SQL code runs as expected. Among these reserved words are DECLARE, BEGIN, and EXCEPTION. Here are the guidelines for using these reserved words in a PL/SQL block:

1. DECLARE: This keyword is used to introduce a declarative section in a PL/SQL block. It precedes the BEGIN statement and is used to declare variables, constants, cursors, user-defined exceptions, and subprograms. Here are some important points to consider:
   
   • All declarations must precede any executable statements.
   • Variables and constants declared in the DECLARE section are initialized to their default values.
   • Declarations are local to the block in which they are declared and do not exist outside this block. In other words, a variable declared in one block is not accessible to another block unless it's a globally declared variable or in a package specification.
   • Declaration of a variable or constant must not conflict with the name of a schema object (like table or view) in the current scope.
2. BEGIN: The BEGIN keyword marks the beginning of the executable section of the PL/SQL block. This section contains the procedural statements that make up the logic of the program. Here are some guidelines related to the BEGIN section:
   
   • Every PL/SQL block must have a BEGIN section. However, the DECLARE section is optional.
   • Statements in the BEGIN section are executed in the order in which they appear.
   • The BEGIN keyword must be followed by at least one executable statement.
   • A PL/SQL block ends with the keyword END, optionally followed by a block label.
3. EXCEPTION: The EXCEPTION keyword is used to introduce the exception-handling part of a PL/SQL block. Here are the key guidelines for this part:
   
   • The EXCEPTION section follows the BEGIN section and contains code that is executed if an exception (error) occurs in the executable section.
   • Each exception handler starts with the keyword WHEN followed by the exception name, then the keyword THEN, and finally, the code to be executed when the exception is raised.
   • If an exception is raised and not handled by any exception handler in the current block or any enclosing blocks, PL/SQL stops the execution and raises the unhandled exception error to the host environment.
   • In a nested block, if an exception is raised that is not handled by the block, the exception propagates to the enclosing block. This continues until the exception is either handled or there are no more enclosing blocks.

The misuse or misunderstanding of these reserved words can lead to bugs or even catastrophic failures in your PL/SQL programs. Always ensure that they are used properly, following the guidelines outlined above, and your PL/SQL blocks will be well-structured, easy to understand, and reliable.

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 are discussed later in this module 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;
 

The basic unit of a PL/SQL source program is the block, which groups related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handlig part. Only the executable part is required. A block can have a label.

<< label >> (optional)
DECLARE -- Declarative part (optional)
-- Declarations of local types, variables, & subprograms
BEGIN -- Executable part (required)
-- Statements (which can use items declared in declarative part)
[EXCEPTION -- Exception-handling part (optional)
-- Exception handlers for exceptions (errors) raised in executable part]
END;

Declarations are local to the block and cease to exist when the block completes execution, helping to avoid cluttered namespaces for variables and subprograms. Blocks can be nested: Because a block is an executable statement, it can appear in another block wherever an executable statement is allowed. You can submit a block to an interactive tool such as 1) SQL*Plus or 2) Enterprise Manager or 3) embed it in an Oracle Precompiler or 4) OCI program. The interactive tool or program runs the block one time. The block is not stored in the database, and for that reason, it is called an anonymous block (even if it has a label). In the next lesson, you will learn about nested blocks.