Variables in PL/SQL: Declaration, Syntax

Variables  in PL/SQL

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:


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,


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,


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,


a NUMBER :=3;


a:= a + 1;