PL/SQL User's Guide and Reference

Blocks

Description

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 PL/SQL 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 "Block Structure" and "Scope and Visibility" .

Syntax

plsql_block ::=

[<<label_name>>]
[DECLARE
   object_declaration [object_declaration] ...
   [subprogram_declaration [subprogram_declaration] ...]]
BEGIN
   seq_of_statements
[EXCEPTION 
   exception_handler [exception_handler] ...]
END [label_name];

object_declaration ::=

{  constant_declaration
 | cursor_declaration
 | cursor_variable_declaration
 | exception_declaration
 | plsql_table_declaration
 | record_declaration
 | variable_declaration}

subprogram_declaration ::=

{function_declaration | procedure_declaration}

Keyword and Parameter Description

label_name

This is an undeclared identifier that optionally labels a PL/SQL block. If used, label_name must be enclosed by double angle brackets and must appear at the beginning of the block. Optionally, label_name can also appear at the end of the block.

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. To reference the global identifier, you must use a block label to qualify the reference, as the following example shows:

<<outer>>
DECLARE
   x INTEGER;
BEGIN
   ...
   DECLARE
      x INTEGER;
   BEGIN
      ...
      IF x = outer.x THEN  -- refers to global x
         ...
      END IF;
   END;
END outer;

DECLARE

This keyword signals the start of the declarative part of a PL/SQL block, which contains local declarations. Objects 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.

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

constant_declaration

This construct declares a constant. For the syntax of constant_declaration, see "Constants and Variables"

cursor_declaration

This construct declares an explicit cursor. For the syntax of cursor_declaration, see "Cursors"

cursor_variable_ declaration

This construct declares a cursor variable. For the syntax of cursor_variable_declaration, see "Cursor Variables"

exception_declaration

This construct declares an exception. For the syntax of exception_declaration, see "Exceptions"

plsql_table_declaration

This construct declares a PL/SQL table. For the syntax of plsql_table_declaration, see "PL/SQL Tables"

record_declaration

This construct declares a user-defined record. For the syntax of record_declaration, see "Records"

variable_declaration

This construct declares a variable. For the syntax of variable_declaration, see "Constants and Variables"

function_declaration

This construct declares a function. For the syntax of function_declaration, see "Functions"

procedure_declaration

This construct declares a procedure. For the syntax of procedure_declaration, see "Procedures"

BEGIN

This keyword signals the start of the executable part of a PL/SQL block, which contains executable statements. The executable part of a PL/SQL block is required. That is, a block must contain at least one executable statement. The NULL statement meets this requirement.

seq_of_statements

This represents a sequence of executable (not declarative) statements, which can include SQL statements and PL/SQL blocks (sometimes called block statements). The syntax of seq_of_statements follows:

seq_of_statements ::=

statement [statement] ...

Statements are used to create algorithms. Besides SQL statements, PL/SQL has flow-of-control and error-handling statements. PL/SQL statements are free format. That is, they can continue from line to line, providing you do not split keywords, delimiters, or literals across lines. A semicolon (;) must terminate every PL/SQL statement. The syntax of statement follows:

statement ::=

[<<label_name>>] 
{  assignment_statement
 | exit_statement
 | goto_statement
 | if_statement
 | loop_statement
 | null_statement
 | plsql_block
 | raise_statement
 | return_statement
 | sql_statement}

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. The syntax of sql_statement follows:

sql_statement ::=

{  close_statement
 | commit_statement
 | delete_statement
 | fetch_statement
 | insert_statement
 | lock_table_statement
 | open_statement
 | open-for_statement
 | rollback_statement
 | savepoint_statement
 | select_statement
 | set_transaction_statement
 | update_statement}

EXCEPTION

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

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, see Chapter 6.

exception_handler

This construct associates an exception with a sequence of statements, which is executed when that exception is raised. For the syntax of exception_handler, see "Exceptions"

END

This keyword signals the end of a PL/SQL block. It must be the last keyword in a block. Neither the END IF in an IF statement nor the END LOOP in a LOOP statement can substitute for the keyword END.

END does not signal the end of a transaction. Just as a block can span multiple transactions, a transaction can span multiple blocks.

Example

The following PL/SQL block declares several variables and constants, then calculates a ratio using values selected from a database table:

-- available online in file EXAMP11
DECLARE
   numerator   NUMBER;
   denominator NUMBER;
   the_ratio   NUMBER;
   lower_limit CONSTANT NUMBER := 0.72;
   samp_num    CONSTANT NUMBER := 132;
BEGIN
   SELECT x, y INTO numerator, denominator FROM result_table
      WHERE sample_id = samp_num;
   the_ratio := numerator/denominator;
   IF the_ratio > lower_limit THEN
      INSERT INTO ratio VALUES (samp_num, the_ratio);
   ELSE
      INSERT INTO ratio VALUES (samp_num, -1);
   END IF;
   COMMIT;
EXCEPTION
   WHEN ZERO_DIVIDE THEN
      INSERT INTO ratio VALUES (samp_num, 0);
      COMMIT;
   WHEN OTHERS THEN
      ROLLBACK;
END;