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. Blocks can be nested.

An anonymous block is an executable statement.

Topics

• Syntax

• Semantics

• Examples

• Related Topics

Syntax

plsql_block ::=

Description of the illustration plsql_block.gif

See "body ::=".

declare_section ::=

Description of the illustration declare_section.gif

See "item_list_2 ::=".

item_list_1 ::=

Description of the illustration item_list_1.gif

See:

• "cursor_declaration ::="

• "function_declaration ::="

• "item_declaration ::="

• "procedure_declaration ::="

• "pragma ::="

• "type_definition ::="

item_list_2 ::=

Description of the illustration item_list_2.gif

See:

• "cursor_declaration ::="

• "cursor_definition ::="

• "function_declaration ::="

• "function_definition ::="

• "pragma ::="

• "procedure_declaration ::="

• "procedure_definition ::="

• "type_definition ::="

type_definition ::=

Description of the illustration type_definition.gif

See:

• "collection_type_definition ::="

• "record_type_definition ::="

• "ref_cursor_type_definition ::="

• "subtype_definition ::="

subtype_definition ::=

Description of the illustration subtype_definition.gif

constraint ::=

Description of the illustration constraint.gif

item_declaration ::=

Description of the illustration item_declaration.gif

See:

• "collection_variable_dec ::="

• "constant_declaration ::="

• "cursor_declaration ::="

• "cursor_variable_declaration ::="

• "exception_declaration ::="

• "record_variable_declaration ::="

• "variable_declaration ::="

pragma ::=

Description of the illustration pragma.gif

See:

• "autonomous_trans_pragma ::="

• "exception_init_pragma ::="

• "inline_pragma ::="

• "restrict_references_pragma ::="

• "serially_resuable_pragma ::="

body ::=

Description of the illustration body.gif

See:

• "exception_handler ::="

• "inline_pragma ::="

statement ::=

Description of the illustration statement.gif

See:

• "plsql_block ::="

• "procedure_call ::="

• "sql_statement ::="

procedure_call ::=

Description of the illustration procedure_call.gif

sql_statement ::=

Description of the illustration sql_statement.gif

Semantics

plsql_block

label

Undeclared identifier, unique for the block.

DECLARE

Starts the declarative part of the block.

declare_section

Contains local declarations, which exist only in the block and its sub-blocks and are not visible to enclosing blocks.

Restrictions on declare_section 

• A declare_section in create_package, create_package_body, or compound_trigger_block cannot include PRAGMA AUTONOMOUS_TRANSACTION.

• A declare_section in trigger_body or tps_body cannot declare variables of the data type LONG or LONG RAW.

subtype_definition

subtype

Name of the user-defined subtype that you are defining.

base_type

Base type of the subtype that you are defining. base_type must be scalar.

CHARACTER SET character_set

Specifies the character set for a subtype of a character data type.

Restriction on CHARACTER SET character_set Do not specify this clause if base_type is not a character data type.

NOT NULL

Imposes the NOT NULL constraint on data items declared with this subtype. For information about this constraint, see "NOT NULL Constraint".

constraint

Specifies a constraint for a subtype of a numeric data type.

Restriction on constraint Do not specify constraint if base_type is not a numeric data type.

precision

Specifies the precision for a constrained subtype of a numeric data type.

Restriction on precision Do not specify precision if base_type cannot specify precision.

scale

Specifies the scale for a constrained subtype of a numeric data type.

Restriction on scale Do not specify scale if base_type cannot specify scale.

RANGE low_value .. high_value

Specifies the range for a constrained subtype of a numeric data type. The low_value and high_value must be numeric literals.

Restriction on RANGE high_value .. low_value Specify this clause only if base_type is PLS_INTEGER or a subtype of PLS_INTEGER (either predefined or user-defined). (For a summary of the predefined subtypes of PLS_INTEGER, see Table 3-3. For information about user-defined subtypes with ranges, see "Constrained Subtypes".)

body

BEGIN

Starts the executable part of the block, which contains executable statements.

EXCEPTION

Starts the exception-handling part of the block. When PL/SQL raises an exception, normal execution of the block stops and control transfers to the appropriate exception_handler. After the exception handler completes, execution resumes with the statement following the block. For more information about exception-handling, see Chapter 11, "PL/SQL Error Handling."

exception_handler

See "Exception Handler".

END

Ends the block.

name

The name of the block to which END applies—a label, function name, procedure name, or package name.

statement

label

Undeclared identifier, unique for the statement.

assignment_statement

See "Assignment Statement".

basic_loop_statement

See "Basic LOOP Statement".

case_statement

See "CASE Statement".

close_statement

See "CLOSE Statement".

collection_method_call

Invocation of one of these collection methods, which are procedures:

• DELETE

• EXTEND

• TRIM

For syntax, see "Collection Method Invocation".

continue_statement

See "CONTINUE Statement".

cursor_for_loop_statement

See "Cursor FOR LOOP Statement".

execute_immediate_statement

See "EXECUTE IMMEDIATE Statement".

exit_statement

See "EXIT Statement".

fetch_statement

See "FETCH Statement".

for_loop_statement

See "FOR LOOP Statement".

forall_statement

See "FORALL Statement".

goto_statement

See "GOTO Statement".

if_statement

See "IF Statement".

null_statement

See "NULL Statement".

open_statement

See "OPEN Statement".

open_for_statement

See "OPEN FOR Statement".

pipe_row_statement

See "PIPE ROW Statement".

Restriction on pipe_row_statement This statement can appear only in the body of a pipelined table function; otherwise, PL/SQL raises an exception.

raise_statement

See "RAISE Statement".

return_statement

See "RETURN Statement".

select_into_statement

See "SELECT INTO Statement".

while_loop_statement

See "WHILE LOOP Statement".

procedure_call

procedure

Name of the procedure that you are invoking.

parameter [, parameter ]...

List of actual parameters for the procedure that you are invoking. The data type of each actual parameter must be compatible with the data type of the corresponding formal parameter. The mode of the formal parameter determines what the actual parameter can be:

If the procedure specifies a default value for a parameter, you can omit that parameter from the parameter list. If the procedure has no parameters, or specifies a default value for every parameter, you can either omit the parameter list or specify an empty parameter list.

sql_statement

commit_statement

SQL COMMIT statement. For syntax, see Oracle Database SQL Language Reference.

delete_statement

SQL DELETE statement. For syntax, see Oracle Database SQL Language Reference. See also "DELETE Statement Extension".

insert_statement

SQL INSERT statement. For syntax, see Oracle Database SQL Language Reference. See also "INSERT Statement Extension".

lock_table_statement

SQL LOCK TABLE statement. For syntax, see Oracle Database SQL Language Reference.

rollback_statement

SQL ROLLBACK statement. For syntax, see Oracle Database SQL Language Reference.

savepoint_statement

SQL SAVEPOINT statement. For syntax, see Oracle Database SQL Language Reference.

set_transaction_statement

SQL SET TRANSACTION statement. For syntax, see Oracle Database SQL Language Reference.

update_statement

SQL UPDATE statement. For syntax, see Oracle Database SQL Language Reference. See also "UPDATE Statement Extensions".

Examples

• Example 1-1, "PL/SQL Block Structure"

• Example 2-23, "Block with Multiple and Duplicate Labels"

• Example 4-29, "Incorrect Label Placement"

Related Topics

In this chapter:

• "Comment"

In other chapters:

• "Blocks"

• "Identifiers"

• "Pragmas"

• Chapter 3, "PL/SQL Data Types"

• "User-Defined PL/SQL Subtypes"