PL/SQL Datatypes   «Prev 

Building PL/SQL 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:
    1. All declarations must precede any executable statements.
    2. Variables and constants declared in the DECLARE section are initialized to their default values.
    3. 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.
    4. 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:
    1. Every PL/SQL block must have a BEGIN section. However, the DECLARE section is optional.
    2. Statements in the BEGIN section are executed in the order in which they appear.
    3. The BEGIN keyword must be followed by at least one executable statement.
    4. 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:
    1. The EXCEPTION section follows the BEGIN section and contains code that is executed if an exception (error) occurs in the executable section.
    2. 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.
    3. 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.
    4. 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.

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 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.

PL/SQL Exceptions

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;