PL/SQL Language Elements, 11 of 52

Constants and Variables

You can declare constants and variables in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage space for a value, specify its datatype, and name the storage location so that you can reference it. Declarations can also assign an initial value and impose the NOT NULL constraint. For more information, see "Declarations".

Syntax

Text description of variable_declaration.gif follows

Text description of the illustration variable_declaration.gif

Text description of constant_declaration.gif follows

Text description of the illustration constant_declaration.gif

Keyword and Parameter Description

collection_name

Identifies a collection (index-by table, nested table, or varray) previously declared within the current scope.

collection_type_name

Identifies a user-defined collection type that was defined using the datatype specifier TABLE or VARRAY.

CONSTANT

Denotes the declaration of a constant. You must initialize a constant in its declaration. Once initialized, the value of a constant cannot be changed.

constant_name

Identifies a program constant. For naming conventions, see "Identifiers".

cursor_name

Identifies an explicit cursor previously declared within the current scope.

cursor_variable_name

Identifies a PL/SQL cursor variable previously declared within the current scope.

db_table_name

A database table or view that must be accessible when the declaration is elaborated.

db_table_name.column_name

A database table and column that must be accessible when the declaration is elaborated.

expression

An arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the declaration is elaborated, the value of expression is assigned to the constant or variable. The value and the constant or variable must have compatible datatypes.

NOT NULL

This constraint prevents the assigning of nulls to a variable or constant. At run time, trying to assign a null to a variable defined as NOT NULL raises the predefined exception VALUE_ERROR. The constraint NOT NULL must be followed by an initialization clause.

object_name

Identifies an instance of an object type previously declared within the current scope.

record_name

Identifies a user-defined or %ROWTYPE record previously declared within the current scope.

record_name.field_name

Identifies a field in a user-defined or %ROWTYPE record previously declared within the current scope.

record_type_name

Identifies a user-defined record type that is defined using the datatype specifier RECORD.

ref_cursor_type_name

Identifies a user-defined cursor variable type is was defined using the datatype specifier REF CURSOR.

%ROWTYPE

This attribute provides a record type that represents a row in a database table or a row fetched from a previously declared cursor. Fields in the record and corresponding columns in the row have the same names and datatypes.

scalar_datatype_name

Identifies a predefined scalar datatype such as BOOLEAN, NUMBER, or VARCHAR2. Includes any qualifiers for size, precision, or character versus byte semantics.

%TYPE

This attribute provides the datatype of a previously declared collection, cursor variable, field, object, record, database column, or variable.

variable_name

Identifies a program variable.

Usage Notes

Constants and variables are initialized every time a block or subprogram is entered. By default, variables are initialized to NULL. So, unless you expressly initialize a variable, its value is undefined.

Whether public or private, constants and variables declared in a package spec are initialized only once for each session.

An initialization clause is required when declaring NOT NULL variables and when declaring constants. If you use %ROWTYPE to declare a variable, initialization is not allowed.

Examples

Several examples of variable and constant declarations follow:

credit_limit CONSTANT NUMBER := 5000;
invalid      BOOLEAN := FALSE;
acct_id      INTEGER(4) NOT NULL DEFAULT 9999;
pi           CONSTANT REAL := 3.14159;
postal_code  VARCHAR2(20);
last_name    VARCHAR2(20 CHAR);
my_ename     emp.ename%TYPE;