|PL/SQL User's Guide and Reference
Release 2 (9.2)
Part Number A96624-01
PL/SQL Language Elements, 4 of 52
The basic program unit in PL/SQL is the block. A PL/SQL block is defined by the keywords
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 "Block Structure" and "Scope and Visibility of PL/SQL Identifiers".
This is any scalar or user-defined PL/SQL datatype specifier such as
This keyword signals the start of the executable part of a PL/SQL block, which contains executable statements. The executable part of a block is required. That is, a PL/SQL block must contain at least one executable statement. The
NULL statement meets this requirement.
This declares a collection (index-by table, nested table, or varray). For the syntax of
collection_declaration, see "Collections".
This declares a constant. For the syntax of
constant_declaration, see "Constants and Variables".
This applies only to datatypes that can be constrained such as
NUMBER. For character datatypes, this specifies a maximum size in bytes. For numeric datatypes, this specifies a maximum precision and scale.
This declares an explicit cursor. For the syntax of
cursor_declaration, see "Cursors".
This declares a cursor variable. For the syntax of
cursor_variable_declaration, see "Cursor Variables".
This keyword 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.
PL/SQL does not allow forward references. So, you must declare an item 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 items.
This keyword signals the end of a PL/SQL block. It must be the last keyword in a block. Neither the
IF in an
IF statement nor the
LOOP in a
LOOP statement can substitute for the keyword
END does not signal the end of a transaction. Just as a block can span multiple transactions, a transaction can span multiple blocks.
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 7.
This declares an exception. For the syntax of
exception_declaration, see "Exceptions".
This associates an exception with a sequence of statements, which is executed when that exception is raised. For the syntax of
exception_handler, see "Exceptions".
This declares a function. For the syntax of
function_declaration, see "Functions".
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 (not enclosed by angle brackets) 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 unless you 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;
This declares an object (instance of an object type). For the syntax of
object_declaration, see "Object Types".
This declares a procedure. For the syntax of
procedure_declaration, see "Procedures".
This declares a user-defined record. For the syntax of
record_declaration, see "Records".
This is an executable (not declarative) statement that you use to create algorithms. A sequence of statements can include procedural statements such as
RAISE, SQL statements such as
UPDATE, and PL/SQL blocks (sometimes called "block statements").
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.
This identifies a user-defined subtype that was defined using any scalar or user-defined PL/SQL datatype specifier such as
This declares a variable. For the syntax of
variable_declaration, see "Constants and Variables".
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
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;