PL/SQL - Variables
In this chapter, we will discuss Variables in Pl/SQL. A variable is nothing but a name
given to a storage area that our programs can manipulate. Each variable in PL/SQL
has a specific data type, which determines the size and the layout of the variable's
memory; the range of values that can be stored within that memory and the set of
operations that can be applied to the variable.
The name of a PL/SQL variable consists of a letter optionally followed by more
letters, numerals, dollar signs, underscores, and number signs and should not
exceed 30 characters. By default, variable names are not case-sensitive. You
cannot use a reserved PL/SQL keyword as a variable name.
PL/SQL programming language allows to define various types of variables, such as
date time data types, records, collections, etc. which we will cover in subsequent
chapters. For this chapter, let us study only basic variable types.
Variable Declaration in PL/SQL
PL/SQL variables must be declared in the declaration section or in a package as a
global variable. When you declare a variable, PL/SQL allocates memory for the
variable's value and the storage location is identified by the variable name.
The syntax for declaring a variable is −
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT
initial_value]
Where, variable_name is a valid identifier in PL/SQL, datatype must be a valid
PL/SQL data type or any user defined data type which we already have discussed
in the last chapter. Some valid variable declarations along with their definition are
shown below −
sales number(10, 2);
pi CONSTANT double precision := 3.1415;
name varchar2(25);
address varchar2(100);
When you provide a size, scale or precision limit with the data type, it is called
a constrained declaration. Constrained declarations require less memory than
unconstrained declarations. For example −
sales number(10, 2);
name varchar2(25);
address varchar2(100);
Initializing Variables in PL/SQL
Whenever you declare a variable, PL/SQL assigns it a default value of NULL. If you
want to initialize a variable with a value other than the NULL value, you can do so
during the declaration, using either of the following −
The DEFAULT keyword
The assignment operator
In this chapter, we will discuss Variables in Pl/SQL. A variable is nothing but a name
given to a storage area that our programs can manipulate. Each variable in PL/SQL
has a specific data type, which determines the size and the layout of the variable's
memory; the range of values that can be stored within that memory and the set of
operations that can be applied to the variable.
The name of a PL/SQL variable consists of a letter optionally followed by more
letters, numerals, dollar signs, underscores, and number signs and should not
exceed 30 characters. By default, variable names are not case-sensitive. You
cannot use a reserved PL/SQL keyword as a variable name.
PL/SQL programming language allows to define various types of variables, such as
date time data types, records, collections, etc. which we will cover in subsequent
chapters. For this chapter, let us study only basic variable types.
Variable Declaration in PL/SQL
PL/SQL variables must be declared in the declaration section or in a package as a
global variable. When you declare a variable, PL/SQL allocates memory for the
variable's value and the storage location is identified by the variable name.
The syntax for declaring a variable is −
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT
initial_value]
Where, variable_name is a valid identifier in PL/SQL, datatype must be a valid
PL/SQL data type or any user defined data type which we already have discussed
in the last chapter. Some valid variable declarations along with their definition are
shown below −
sales number(10, 2);
pi CONSTANT double precision := 3.1415;
name varchar2(25);
address varchar2(100);
When you provide a size, scale or precision limit with the data type, it is called
a constrained declaration. Constrained declarations require less memory than
unconstrained declarations. For example −
sales number(10, 2);
name varchar2(25);
address varchar2(100);
Initializing Variables in PL/SQL
Whenever you declare a variable, PL/SQL assigns it a default value of NULL. If you
want to initialize a variable with a value other than the NULL value, you can do so
during the declaration, using either of the following −
The DEFAULT keyword
The assignment operator
