Syntax

The DECLARE statement specifies the storage space for a value of a specified data type and names the storage location for easy reference.

You must declare an object before you can reference 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 specified implicitly or explicitly.

You can specify NOT NULL implicitly or explicitly for a scalar variable. If you specify NOT NULL, you must assign an initial value to the variable (because the default initial value of a scalar variable is NULL).

PL/SQL 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 its declaration. For most data types, you can also specify an initial value.

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

Declare constants

Constants are used to store values that do not change.

The information about declaring variables also applies to declaring constants, but declaring a constant requires two additional elements: the CONSTANT keyword and an initial value (which is the constant's permanent value).

Initial values of variables and constants

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

If a variable or constant is declared in a block or subprogram, the initial value is assigned to it each time the block or subprogram is executed. If a variable or constant is declared in the SPECIFICATION of a package, the initial value is assigned to it for each session (regardless of whether the variable or constant is public or private).

To specify an initial value, use the assignment operator (:=) or the DEFAULT keyword, followed by an expression. The expression can include previously declared constants and previously initialized variables. If you do not specify an initial value for a variable, you must assign a value to it 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

Use %TYPE

You can use the %TYPE attribute to declare an object with the same data type as a previously declared variable or column (without knowing what the type is).

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

• The data type of the referenced database column is unknown.

• The variable type changes as the data type of the corresponding column changes. Syntax:

referencing_item referenced_item%TYPE;

The referenced item inherits the following from the referenced item:

• Data type and size.

• Constraints (unless the referenced item is a column).

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

The %TYPE attribute is especially useful for declaring variables to store database values. The syntax for declaring a variable with 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 variable lastname inherits the data type, size, and NOT NULL constraint of the variable firstname. Since lastname does not inherit the initial value of firstname, its declaration requires an initial value (which cannot exceed 30 characters).