7.3.1 Declaring a Variable

When you declare a variable, PL/SQL allocates memory for the variable's value and names the storage location so that the value can be retrieved and changed. The declaration also specifies the datatype of the variable; this datatype is then used to validate values assigned to the variable.

The basic syntax for a declaration is:

name datatype [NOT NULL] [default assignment];

where name is the name of the variable or constant to be declared, and datatype is the datatype or subtype that determines the type of data that can be assigned to the variable. You can include a NOT NULL clause, which means that if your code assigns a NULL to this variable, Oracle will raise an exception. The [default assignment] clause allows you to initialize the variable with a value; this is optional for all declarations except those of constants.

The following examples illustrate declarations of variables of different datatypes:

DECLARE

-- Simple declaration of numeric variable

l_total_count NUMBER;

-- Declaration of number that rounds to nearest hundredth (cent):

l_dollar_amount NUMBER (10,2);

-- A single date value, assigned a default value of "right now"

-- and it can never be NULL

l_right_now DATE NOT NULL DEFAULT SYSDATE;

-- Using the assignment operator for the default value specification

l_favorite_flavor VARCHAR2(100) := `Anything with chocolate, actually';

-- Two-step declaration process for associative array.

-- First, the type of table:

TYPE list_of_books_t IS TABLE OF book%ROWTYPE INDEX BY BINARY_INTEGER;

-- And now the specific list to be manipulated in this block:

oreilly_oracle_books list_of_books_t;

The DEFAULT and assignment operator syntax for assigning a default value are equivalent and can be interchanged. So which should you use? I like to use the assignment operator (:=) to set default values for constants, and the DEFAULT syntax for variables. In the case of the constant, the assigned value is not really a default but an initial (and unchanging) value, so the DEFAULT syntax feels misleading to me.

7.3.2 Declaring Constants

There are just two differences between declaring a variable and declaring a constant: for a constant, you include the CONSTANT keyword, and you must supply a default value. So the syntax for the declaration of a constant is:

name CONSTANT datatype [NOT NULL] := | DEFAULT default_value;

The value of a constant is set upon declaration and may not change thereafter.

Here are some examples of declarations of constants:

DECLARE

-- The current year number; it's not going to change during my session.

l_curr_year CONSTANT PLS_INTEGER :=

TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY'));

-- Using the DEFAULT keyword

l_author CONSTANT VARCHAR2(100) DEFAULT 'Bill Pribyl';

-- Declare a complex datatype as a constant

-- this isn't just for scalars!

l_steven CONSTANT person_ot :=

person_ot ('HUMAN', 'Steven Feuerstein', 175, '09-23-1958');

Unless otherwise stated, the information provided in the rest of this chapter for variables also applies to constants.

An unnamed constant is a literal value, such as 2 or Bobby McGee. A literal does not have a name, although it does have an implied (undeclared) datatype.