Until the business requirements change

database into local program variables, and then write information from those variables back into the database.

Suppose that I have a company table with a column called NAME and a datatype of VARCHAR2(60). I can create a local variable to hold this data as follows:

Normalization of local variables

Another drawback to explicit declarations arises when you’re working with PL/SQL variables that store and manipulate calculated values not found in the database. Suppose that I hire some programmers to build an application to manage my company’s finances. I am very bottom-line-oriented, so many different programs make use of a total_revenue variable, declared as follows:

When you declare a variable, you can also specify the need for the variable to be NOT NULL. This NOT NULL declaration constraint is transferred to variables declared with the %TYPE attribute. If I include a NOT NULL in my declaration of a source variable (one that is referenced afterward in a %TYPE declaration), I must also make sure to specify a default value for the variables that use that source variable. Suppose that I declare max_available_date NOT NULL in the following example:

DECLARE

PLS_00218: a variable declared NOT NULL must have an initialization assignment.

If you use a NOT NULL variable in a %TYPE declaration, the new variable must have a default value provided. The same is not true, however, for variables declared with %TYPE where the source is a database column defined as NOT NULL. A NOT NULL constraint from a database table is not automatically transferred to a variable.

SUBTYPE POSITIVE IS BINARY_INTEGER RANGE 1 .. 2147483647;

A variable that is declared POSITIVE can store only integer values greater than zero.