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.