| |
 |
|
PL/SQL Datatypes
Oracle Tips by
Burleson
|
Table 2.3 lists a number of datatypes that are usable inside PL/SQL
blocks, in addition to the default datatypes supported by the Oracle7
database.
|
Table 2.3 PL/SQL supported datatypes. |
|
|
|
Datatype |
Description |
|
binary_integer |
Variables of this type store signed integers,
ranging from -231 - 1 through 231 -1
(-2147483647..2147483647). Unlike number values, variables
of the binary_integer type (and its subtypes) can be used
in expressions without being converted (theoretically providing a
performance boost). |
|
natural |
A subtype of the binary_integer type
that holds only positive integers ranging from 0 through 231
- 1 (0..2147483647). |
|
positive |
A subtype of the binary_integer type
that holds only positive integers ranging from 1 through 231
- 1 (1..2147483647). |
|
dec, decimal, double precision, float,
integer, int, numeric, real, smallint |
Subtypes of the number type that have
the same constraints and range of values as the number
type. |
|
boolean |
This type contains the values TRUE and
FALSE. Variables of this type can also contain a NULL
value. Since this datatype isn’t implicitly supported by the
database engine, you cannot define a column using this type or
select output into a variable of this type. |
|
|
In addition to the datatypes listed in Table 2.3, PL/SQL allows
developers to define custom datatypes.
TIP: Datatype
Conversions
Like Oracle and SQL, PL/SQL
supports both implicit and explicit conversions of datatypes. The use
of explicit conversions is strongly suggested.
Records
PL/SQL allows developers to create a record containing one or more
columns. To use records, the record type must be specified before a
variable can be created based on the type; this is shown by Listing
2.14. Figure 2.8 shows the structure of a record.
Listing 2.14 A PL/SQL record declaration.
TYPE Student_rectype IS RECORD
(first_name varchar2 (12),
last_name varchar2 (15),
gpa number (3, 2));
Student_rec Student_rectype;
Individual columns within Student_rec are referenced as
Student_rec.first_name
Records are composite datatypes, constructed by the
developer to suit a particular set of needs. ( Scalar datatypes
are those datatypes that are automatically supported by the database
engine and PL/SQL, like varchar2, integer, and date
.) The values returned from cursors are often fetched into record
variables, which are then referenced by PL/SQL statements both as
whole records and as individual columns.
This is an
excerpt from the book "High Performance Oracle Database
Automation" by Jonathan Ingram and Donald K. Burleson, Series
Editor. |
