Declare

You can declare a variable or a constant to allocate storage space and name the storage location so that it can be referenced later.

You must declare an object before you can reference it. A declaration can be made in the declaration section of any block, subprogram, or package.

NOT NULL constraint

You can apply the NOT NULL constraint to a scalar variable or constant, or to a scalar component of a composite variable or constant.

The NOT NULL constraint prevents a null value from being assigned to the object. The NOT NULL attribute of an object can be implicitly or explicitly specified.

If you specify the NOT NULL constraint for a scalar variable during its declaration, you must initialize the variable (because the default initial value of a scalar variable is NULL).

PL considers any zero-length string to be a NULL value, including the return values of character functions and BOOLEAN expressions.

Declare a variable

When you declare a variable, you must specify the name and data type of the variable. You can also specify an initial value for the variable.

The name of the variable must be an effective user-defined identifier. The data type can be any PL data type. PL data types include all SQL data types. The data type can be a scalar type or a composite type.

Declare a constant

You can declare a constant to store an invariant value.

The information in the preceding topic also applies to constant declarations, but constant declarations have two additional requirements: the CONSTANT keyword and the initial value of the constant (which is also the permanent value of the constant).

Initial values of variables and constants

You can specify an initial value when you declare a variable or a constant. If you specify the NOT NULL constraint for a variable during its declaration, you must initialize the variable (because the default initial value of a scalar variable is NULL). You must specify an initial value when you declare a constant.

If the declaration is in a block or subprogram, the initial value is assigned to the variable or constant each time the block or subprogram is executed. If the declaration is in the SPECIFICATION section of a package, the initial value is assigned to the variable or constant in each session, whether the variable or constant is public or private.

You can use the assignment operator (:=) or the DEFAULT keyword followed by an expression to specify an initial value. The expression can contain the initial values of previously declared constants and the initial values or currently assigned values of previously initialized variables. If you declare a variable without specifying an initial value, assign a value to the variable before you use it.

Here is an example:

obclient> DECLARE
   pi     CONSTANT NUMBER := 3.14159;
   radius NUMBER NOT NULL := 1;
   area   NUMBER := (pi * radius**2);
BEGIN
   DBMS_OUTPUT.PUT_LINE('The area of the circle is: '||area);
END;
/

Query OK, 0 rows affected 

The area of the circle is: 3.14159

Declare a variable by using the %TYPE attribute

You can declare an object that has the same data type as a previously declared variable or column without needing to know the data type.

The advantages of using the %TYPE attribute are as follows:

• The data type of the referenced column can be unknown.

• The type of the variable is automatically adapted to the data type of the column.

The syntax is as follows:

referencing_item referenced_item%TYPE;

The item referenced by the item declared using the %TYPE attribute inherits the following attributes of the referenced item:

• Data type and size

• Constraints (excluding those of columns)

The item declared using the %TYPE attribute does not inherit the initial value of the referenced item. Therefore, if the item declared using the %TYPE attribute or the item referenced has the NOT NULL constraint, you must specify an initial value for the item declared using the %TYPE attribute.

The %TYPE attribute is particularly useful for declaring variables to store database values. The syntax for declaring a variable that has the same data type as a column is as follows:

variable_name table_name.column_name%TYPE;

Here is an example:

obclient> DECLARE
  firstname  VARCHAR(30) NOT NULL := 'San';
  lastname  firstname%TYPE := 'Zhang';
BEGIN
  DBMS_OUTPUT.PUT_LINE('firstname=' || firstname);
  DBMS_OUTPUT.PUT_LINE('lastname=' || lastname);
END;
/
Query OK, 0 rows affected 

firstname=San
lastname=Zhang

In this example, the lastname variable inherits the data type, size, and NOT NULL constraint of the firstname variable. The lastname variable does not inherit the initial value of the firstname variable. Therefore, an initial value must be specified for the lastname variable (no more than 30 characters).