Declaration

A declaration allocates storage space for a value of a specified data type and names the storage location for convenience of reference.

You must declare an object before referencing it. A declaration can appear 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 an object from being assigned a null value. The NOT NULL attribute of an object can be implicitly or explicitly specified.

When you implicitly or explicitly specify the NOT NULL constraint for a scalar variable in a declaration, you must assign an initial value to it. This is because the default initial value of a scalar variable is NULL.

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

Declare variables

You must specify the name and data type of a variable in a declaration. For most data types, you can also specify an initial value for the variable in the declaration.

The name of a variable must be a valid user-defined identifier. The data type can be any PL data type, which includes all SQL data types. The data type can be a scalar type or a composite type.

Declare constants

A constant is used to store an unchanged value.

The information about variable declarations also applies to constant declarations, but constant declarations have two additional requirements: the CONSTANT keyword and an initial value for the constant. The initial value is the constant's permanent value.

Initial values of variables and constants

In a variable declaration, the initial value is optional unless the NOT NULL constraint is specified. In a constant declaration, an initial value is required.

If a 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 a declaration is in the SPECIFICATION of a package, the initial value is assigned to the variable or constant in each session, regardless of whether the variable or constant is a public or private variable.

To specify an initial value, use the assignment operator (:=) or the DEFAULT keyword, followed by an expression. The expression can contain constants that are previously declared and variables that are previously initialized. If no initial value is specified for a variable, assign a value to it before using 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 using ％TYPE

You can use ％TYPE to declare an object that has the same data type as a previously declared variable or column, without knowing the data type.

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

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

• The variable type changes with the data type of the corresponding column.

The syntax is as follows:

referencing_item referenced_item%TYPE;

The referenced item inherits the following attributes of the item it references:

• Data type and size

• Constraints (excluding column constraints).

The referenced item does not inherit the initial value of the item it references. Therefore, if the referenced item specifies or inherits the NOT NULL constraint, you must specify an initial value for it.

％TYPE is particularly useful for declaring variables to store database values. The syntax for declaring a variable of the same 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. However, it does not inherit the initial value of firstname. Therefore, an initial value must be specified for lastname, which cannot exceed 30 characters.