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:
1) One of the types used by SQL for database columns,
2) A generic type used in PL/SQL such as NUMBER,
3) 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 o 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, one might declare:
DECLARE
price NUMBER;
myBeer VARCHAR(20);
PL/SQL allows BOOLEAN variables, even though Oracle does not
support BOOLEAN as a type for database columns.
In many cases, a PL/SQL variable will be used to manipulate
data stored in an existing relation. In this case, it is essential that the
variable have the ki same type as the relation column. If there is any type
mismatch, variable assignments and comparisons may not work the way one
expects. To be safe, instead of hard coding the type of a variable, one should
use the %TYPE operator.
For example,
DECLARE
myBeer
Beers.name%TYPE;
This gives PL/SQL variable myBeer whatever type was declared
for the 1 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 I name. The result is a record type in which the fields have the
same names and types as the attributes of the relation.
For example,
DECLARE
beerTuple Beers%ROWTYPE;
makes. variable beerTuple be a record with fields name and
manufacture, assunung that the relation hasthe schema Beers name manufacture).
The initial value of any variable, regardless of its type,
is NULL. One can assign values to variables, using the ":=" operator.
The assignment can occur either immediately after the type of the variable is
declared or Ywhere in the executable portion of the program.
For example,
DECLARE
a NUMBER :=3;
BEGIN
a:= a + 1;
END;
