Block Declaration

The basic program unit in PL/SQL is the block. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords partition the block into a declarative part, an executable part, and an exception-handling part. Only the executable part is required. You can nest a block within another block wherever you can place an executable statement. For more information, see "Understanding PL/SQL Block Structure" and "Scope and Visibility of PL/SQL Identifiers".


plsql block ::=

Description of plsql_block.gif follows
Description of the illustration plsql_block.gif

type definition ::=

Description of type_definition.gif follows
Description of the illustration type_definition.gif

subtype definition ::=

Description of subtype_definition.gif follows
Description of the illustration subtype_definition.gif

item definition ::=

Description of item_declaration.gif follows
Description of the illustration item_declaration.gif

sql_statement ::=

Description of sql_statement.gif follows
Description of the illustration sql_statement.gif

statement ::=

Description of statement.gif follows
Description of the illustration statement.gif

Keyword and Parameter Description


Any scalar or user-defined PL/SQL datatype specifier such as CHAR, DATE, or RECORD.


Signals the start of the executable part of a PL/SQL block, which contains executable statements. A PL/SQL block must contain at least one executable statement (even just the NULL statement). See "Understanding PL/SQL Block Structure".


Declares a collection (index-by table, nested table, or varray). For the syntax of collection_declaration, see "Collection Definition".


Declares a constant. For the syntax of constant_declaration, see "Constant and Variable Declaration".


Applies only to datatypes that can be constrained such as CHAR and NUMBER. For character datatypes, this specifies a maximum size in bytes. For numeric datatypes, this specifies a maximum precision and scale.


Declares an explicit cursor. For the syntax of cursor_declaration, see "Cursor Declaration".


Declares a cursor variable. For the syntax of cursor_variable_declaration, see "Cursor Variables".


Signals the start of the declarative part of a PL/SQL block, which contains local declarations. Items declared locally exist only within the current block and all its sub-blocks and are not visible to enclosing blocks. The declarative part of a PL/SQL block is optional. It is terminated implicitly by the keyword BEGIN, which introduces the executable part of the block. For more information, see "Declarations".

PL/SQL does not allow forward references. You must declare an item before referencing it in any other statements. Also, you must declare subprograms at the end of a declarative section after all other program items.


Signals the end of a PL/SQL block. It must be the last keyword in a block. Remember, END does not signal the end of a transaction. Just as a block can span multiple transactions, a transaction can span multiple blocks. See "Understanding PL/SQL Block Structure".


Signals the start of the exception-handling part of a PL/SQL block. When an exception is raised, normal execution of the block stops and control transfers to the appropriate exception handler. After the exception handler completes, execution proceeds with the statement following the block. See "Understanding PL/SQL Block Structure".

If there is no exception handler for the raised exception in the current block, control passes to the enclosing block. This process repeats until an exception handler is found or there are no more enclosing blocks. If PL/SQL can find no exception handler for the exception, execution stops and an unhandled exception error is returned to the host environment. For more information on exceptions, see Chapter 10.


Declares an exception. For the syntax of exception_declaration, see "Exception Definition".


Associates an exception with a sequence of statements, which is executed when that exception is raised. For the syntax of exception_handler, see "Exception Definition".


Declares a function. For the syntax of function_declaration, see "Function Declaration".


An undeclared identifier that optionally labels a PL/SQL block or statement. If used, label_name must be enclosed by double angle brackets and must appear at the beginning of the block or statement which it labels. Optionally, when used to label a block, the label_name can also appear at the end of the block without the angle brackets. Multiple labels are allowed for a block or statement, but they must be unique for each block or statement.

A global identifier declared in an enclosing block can be redeclared in a sub-block, in which case the local declaration prevails and the sub-block cannot reference the global identifier unless you use a block label to qualify the reference. See Example 2-19, "PL/SQL Block Using Multiple and Duplicate Labels".


Declares an instance of an object type. For the syntax of object_declaration, see "Object Type Declaration".


Declares a procedure. For the syntax of procedure_declaration, see "Procedure Declaration".


Declares a user-defined record. For the syntax of record_declaration, see "Record Definition".


An executable (not declarative) statement that. A sequence of statements can include procedural statements such as RAISE, SQL statements such as UPDATE, and PL/SQL blocks. PL/SQL statements are free format. That is, they can continue from line to line if you do not split keywords, delimiters, or literals across lines. A semicolon (;) serves as the statement terminator.


A user-defined subtype that was defined using any scalar or user-defined PL/SQL datatype specifier such as CHAR, DATE, or RECORD.


Declares a variable. For the syntax of variable_declaration, see "Constant and Variable Declaration".

PL/SQL supports a subset of SQL statements that includes data manipulation, cursor control, and transaction control statements but excludes data definition and data control statements such as ALTER, CREATE, GRANT, and REVOKE.


For examples, see the following:

Example 1-4, "Assigning Values to Variables as Parameters of a Subprogram"
Example 2-19, "PL/SQL Block Using Multiple and Duplicate Labels"
Example 13-1, "Declaring and Assigning Values to Variables"

Related Topics

"Constant and Variable Declaration"
"Exception Definition"
"Function Declaration"
"Procedure Declaration"