PL/SQL User's Guide and Reference

Constants and Variables

Description

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

constant_declaration ::=

constant_name CONSTANT 
   {  record_name.field_name%TYPE
    | scalar_type_name
    | table_name.column_name%TYPE
    | variable_name%TYPE} [NOT NULL] {:= | DEFAULT} expression;

variable_declaration ::=

variable_name 
   {  cursor_name%ROWTYPE
    | cursor_variable_name%TYPE
    | plsql_table_name%TYPE
    | record_name%TYPE
    | record_name.field_name%TYPE
    | scalar_type_name
    | table_name%ROWTYPE
    | table_name.column_name%TYPE
    | variable_name%TYPE} [[NOT NULL] {:= | DEFAULT} expression];

Keyword and Parameter Description

constant_name

This identifies a program constant. For naming conventions, see "Identifiers"

CONSTANT

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

record_name.field_name

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

scalar_type_name

This identifies a predefined scalar datatype such as BOOLEAN, NUMBER, or VARCHAR2. For more information, see "Datatypes"

table_name.column_name

This identifies a database table and column that must be accessible when the declaration is elaborated.

variable_name

This identifies a program variable. For naming conventions, see "Identifiers"

cursor_name

This identifies an explicit cursor previously declared within the current scope.

cursor_variable_name

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

plsql_table_name

This identifies a PL/SQL table previously declared within the current scope.

record_name

This identifies a user-defined record previously declared within the current scope.

table_name

This identifies a database table (or view) that must be accessible when the declaration is elaborated.

%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.

%TYPE

This attribute provides the datatype of a previously declared field, record, PL/SQL table, database column, or variable.

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.

expression

This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of expression, see "Expressions"

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

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 specification are initialized only once per session.

An initialization clause is required when declaring NOT NULL variables and when declaring constants.

You cannot use the attribute %ROWTYPE to declare a constant. 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;
last_name    VARCHAR2(20);
my_ename     emp.ename%TYPE;