Oracle PL/SQL for DBAs

Declaring Program Data

As I mentioned, before you can make a reference to a variable or a constant, 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, trigger, object type body, or package body. You can declare many types of data and data structures in PL/SQL, including variables, constants, TYPEs (such as a type of collection or a type of record), and exceptions. This section focuses on the declarations of variables and constants.

Declaring a variable

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:

    name datatype [NOT NULL] [default_assignment];

where name is the name of the variable or constant to be declared, and datatypeis the datatype or subtype that determines the type of data that can be assigned to the variable. You can include a NOT NULL clause , which means that if your code assigns a NULL to this variable, Oracle will raise an exception. The [default_assignment] clause allows you to initialize the variable with a value; this is optional for all declarations except those of constants.

The following examples illustrate declarations of variables of different ...