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.
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:
Here are some examples of variable declarations:
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:
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.
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;
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.