Constants and Variables

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

Syntax

Description of variable_declaration.gif follows

Description of the illustration variable_declaration.gif

Description of constant_declaration.gif follows
Description of the illustration constant_declaration.gif

Keyword and Parameter Description

collection_name

A collection (associative array, nested table, or varray) previously declared within the current scope.

collection_type_name

A user-defined collection type 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

A program constant. For naming conventions, see "Identifiers".

cursor_name

An explicit cursor previously declared within the current scope.

cursor_variable_name

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

A 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

A constraint that prevents the program from assigning a null value to a variable or constant. Assigning 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

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

record_name

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

record_name.field_name

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

record_type_name

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

ref_cursor_type_name

A user-defined cursor variable type, defined using the datatype specifier REF CURSOR.

%ROWTYPE

Represents a record that can hold a row from a database table or a cursor. Fields in the record have the same names and datatypes as columns in the row.

scalar_datatype_name

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

%TYPE

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

variable_name

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.

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.

You can define constants of complex types that have no literal values or predefined constructors, by calling a function that returns a filled-in value. For example, you can make a constant associative array this way.

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;