Block

The block, which groups related declarations and statements, is the basic unit of a PL/SQL source program. It has an optional declarative part, a required executable part, and an optional exception-handling part. Declarations are local to the block and cease to exist when the block completes execution.

A block can appear either at schema level (as a top-level block) or inside another block (as a nested block). A block can contain another block wherever it can contain an executable statement.

Syntax

plsql_block ::=

plsql_block
Description of the illustration plsql_block.gif

(body ::=)

declare_section ::=

declare_section
Description of the illustration declare_section.gif

(item_list_2 ::=)

item_list_1 ::=

item_1_list
Description of the illustration item_list_1.gif

(type_definition ::=, item_declaration ::=, function_declaration ::=, procedure_declaration ::=, pragma ::=)

item_list_2 ::=

item_2_list
Description of the illustration item_list_2.gif

(function_declaration ::=, function_definition ::=, procedure_declaration ::=, procedure_definition ::=, pragma ::=)

type_definition ::=

type_definition
Description of the illustration type_definition.gif

(record_type_definition ::=, ref_cursor_type_definition ::=, collection_type_definition ::=)

subtype_definition ::=

subtype_definition
Description of the illustration subtype_definition.gif

item_declaration ::=

item_declaration
Description of the illustration item_declaration.gif

collection_variable_dec ::=, constant_declaration ::=, cursor_declaration ::=, cursor_variable_declaration ::=, exception_declaration ::=, record_type_declaration ::=, variable_declaration ::=

pragma ::=

pragma
Description of the illustration pragma.gif

(autonomous_transaction_pragma ::=, exception_init_pragma ::=, inline_pragma ::=, restrict_references_pragma ::=, serially_resuable_pragma ::=)

body ::=

body
Description of the illustration basic_body.gif

(exception_handler ::=)

statement ::=

statement
Description of the illustration statement.gif

(plsql_block ::=, sql_statement ::=)

sql_statement ::=

sql_statement
Description of the illustration sql_statement.gif

Keyword and Parameter Descriptions

base_type

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

BEGIN

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

collection_variable_dec

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

constant_declaration

Declares a constant. For the syntax of constant_declaration, see Constant.

constraint

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

cursor_declaration

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

cursor_variable_declaration

Declares a cursor variable. For the syntax of cursor_variable_declaration, see Cursor Variable Declaration.

DECLARE

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.

END

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 PL/SQL Blocks.

EXCEPTION

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 PL/SQL Blocks.

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 about exceptions, see Chapter 11, "Handling PL/SQL Errors."

exception_declaration

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

exception_handler

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

function_declaration

Declares a function. See Function Declaration and Definition.

label_name

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-28, "Block with Multiple and Duplicate Labels".

name

Is the label name (without the delimiters << and >>).

object_declaration

Declares an instance of an object type. To create an object type, use the CREATE TYPE Statement.

object_ref_declaration

procedure_declaration

Declare a procedure. See Procedure Declaration and Definition.

record_declaration

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

statement

An executable (not declarative) statement. 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.

subtype_name

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

variable_declaration

Declares a variable. For the syntax of variable_declaration, see Constant.

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.

Examples

Related Topics