PL/SQL Datatypes  «Prev  Next»

Lesson 2	PL/SQL block Guidelines
Objective	Describe the Basic Structure of a PL/SQL Block

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.

Block Structure

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.

Block Usage

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 SlideShow to see the order in which these sections appear within a block.

1. DECLARE signals the start of the declarative section of a PL/SQL block.
2. BEGIN signals the start of the execution section of a PL/SQL block.
3. Exception signals the start of the exception handling routines within a PL/SQL block.
4. END signals the end of the block.
5. A complete PL/SQL block.

Building PL/SQL Block

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.