Variables and Types

Information is transmitted between a PL/SQL program and the database through variables. Every variable has a specific type associated with it. That type can be :

• One of the types used by SQL for database columns
• A generic type used in PL/SQL such as NUMBER
• Declared to be the same as the type of some database column

The most commonly used generic type is NUMBER. Variables of type NUMBER can hold either an integer or a real number. The most commonly used character string type is VARCHAR(n), where n is the maximum length of the string in bytes. This length is required, and there is no default. For example, we might declare:

DECLARE
price NUMBER;
myBeer VARCHAR(20);

Note that PL/SQL allows BOOLEAN variables, even though Oracle does not support BOOLEAN as a type for database columns.
Types in PL/SQL can be tricky. In many cases, a PL/SQL variable will be used to manipulate data stored in a existing relation. In this case, it is essential that the variable have the same type as the relation column. If there is any type mismatch, variable assignments and comparisons may not work the way you expect. To be safe, instead of hard coding the type of a variable, you should use the %TYPE operator. For example:

DECLARE
myBeer Beers.name%TYPE;

gives PL/SQL variable myBeer whatever type was declared for the name column in relation Beers.
A variable may also have a type that is a record with several fields. The simplest way to declare such a variable is to use %ROWTYPE on a relation name. The result is a record type in which the fields have the same names and types as the attributes of the relation. For instance:

DECLARE
beerTuple Beers%ROWTYPE;

makes variable beerTuple be a record with fields name and manufacture, assuming that the relation has the schema Beers(name, manufacture).
The initial value of any variable, regardless of its type, is NULL. We can assign values to variables, using the ":=" operator. The assignment can occur either immediately after the type of the variable is declared, or anywhere in the executable portion of the program. An example:

DECLARE
a NUMBER := 3;
BEGIN
a := a + 1;
END;
.

run;

This program has no effect when run, because there are no changes to the database.