[Chapter 4] 4.4 Variable Declarations

4.4 Variable Declarations

Before you can make a reference to a variable, you must declare it. (The only exception to this rule is for the index variables of FOR loops.) All declarations must be made in the declaration section of your anonymous block, procedure, function, or package (see Chapter 15, Procedures and Functions , for more details on the structure of the declaration section).

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:

<variable_name> <datatype> [optional default assignment];

where <variable_name> is the name of the variable to be declared and <datatype> is the datatype or subtype which determines the type of data which can be assigned to the variable. The [optional default assignment] clause allows you to initialize the variable with a value, a topic covered in the next section.

4.4.1 Constrained Declarations

The datatype in a declaration can either be constrained or unconstrained. A datatype is constrained when you specify a number which constrains or restricts the magnitude of the value which can be assigned to that variable. A datatype is unconstrained when there are no such restrictions.

Consider the datatype NUMBER. It supports up to 38 digits of precision -- and uses up the memory needed for all those digits. If your variable does not require this much memory, you could declare a number with a constraint, such as the following:

itty_bitty_# NUMBER(1);

large_but_constrained_# NUMBER(20,5);

Constrained variables require less memory than unconstrained number declarations like this:

no_limits_here NUMBER;

4.4.2 Declaration Examples

Here are some examples of variable declarations:

Declaration of date variable:
```
hire_date DATE;
```
This variable can only have one of three values: TRUE, FALSE, NULL:
```
enough_data BOOLEAN;
```
This number rounds to the nearest hundredth (cent):
```
total_revenue NUMBER (15,2);
```
This variable-length string will fit in a VARCHAR2 database column:
```
long_paragraph VARCHAR2 (2000);
```

This constant date is unlikely to change:

next_tax_filing_date CONSTANT DATE := '15-APR-96';

4.4.3 Default Values

You can assign default values to a variable when it is declared. When declaring a constant, you must include a default value in order for the declaration to compile successfully. The default value is assigned to the variable with one of the following two formats:

<variable_name> <datatype> := <default_value>;
<variable_name> <datatype> DEFAULT <default_value>;

The <default_value> can be a literal, previously declared variable, or expression, as the following examples demonstrate:

Set variable to 3:
```
term_limit NUMBER DEFAULT  3;
```

Default value taken from Oracle Forms bind variable:

call_topic VARCHAR2 (100) DEFAULT :call.description;

Default value is the result of a function call:
```
national_debt FLOAT DEFAULT POWER (10,10);
```

Default value is the result of the expression:

order_overdue CONSTANT BOOLEAN :=
   ship_date > ADD_MONTHS (order_date, 3) OR
   priority_level (company_id) = 'HIGH';

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.

4.4.4 NOT NULL Clause

If you do assign a default value, you can also specify that the variable must be NOT NULL. For example, the following declaration initializes the company_name variable to PCS R US and makes sure that the name can never be set to NULL:

company_name VARCHAR2(60) NOT NULL DEFAULT 'PCS R US';

If your code includes a line like this:

company_name := NULL;

then PL/SQL will raise the VALUE_ERROR exception. You will, in addition, receive a compilation error with this next declaration:

company_name VARCHAR2(60) NOT NULL;

Why? Because your NOT NULL constraint conflicts instantly with the indeterminate or NULL value of the company_name variable when it is instantiated.