Introduction to PL/SQL
data types
Each value in PL/SQL
such as a constant, variable and parameter has a data type that
determines the storage format, valid values, and allowed operations.
Ø PL/SQL has two kinds of data types: scalar and composite.
Ø The scalar types are types that store single values such as number, Boolean, character, and DateTime.
Ø The composite types are types that store multiple values, for example, record and collection.
> scalar data types
PL/SQL divides the
scalar data types into four families:
· Number
· Boolean
· Character
· Datetime
A scalar data type may
have subtypes. A subtype is a data type that is a subset of another data type,
which is its base type. A subtype further defines a base type by restricting
the value or size of the base data type.
Note that PL/SQL scalar
data types include SQL data types and their own data type such as Boolean.
Numeric
data types
Ø The numeric data types
represent real numbers, integers, and floating-point numbers.
Ø They are stored as NUMBER, IEEE floating-point storage types (BINARY_FLOAT and BINARY_DOUBLE), and PLS_INTEGER.
Ø The data
types NUMBER, BINARY_FLOAT, and BINARY_DOUBLE are SQL data
types.
Ø The PLS_INTEGER datatype
is specific to PL/SQL. It represents signed 32 bits integers that range
from -2,147,483,648 to 2,147,483,647.
Ø Because PLS_INTEGER datatype
uses hardware arithmetic, they are faster than NUMBER operations,
which use software arithmetic.
Ø In
addition, PLS_INTEGER values require less storage than NUMBER.
Hence, you should always use PLS_INTEGER values for all calculations
in its range to increase the efficiency of programs.
Ø The PLS_INTEGER datatype
has the following predefined subtypes:
|
PLS_INTEGER subtypes |
Description |
|
NATURAL |
Represents
nonnegative PLS_INTEGER values |
|
NATURALN |
Represents
nonnegative PLS_INTEGER values with NOT NULL constraint |
|
POSITIVE |
Represents
positive PLS_INTEGER values |
|
POSITIVEN |
Represents
positive PLS_INTEGER value with NOT NULL constraint |
|
SIGNTYPE |
Represents three
values -1, 0, or 1, which are useful for tri-state logic programming |
|
SIMPLE_INTEGER |
Represents PLS_INTEGER values
with NOT NULL constraint. |
Note
that PLS_INTEGER and BINARY_INTEGER data types are
identical.
Boolean data type
The BOOLEAN datatype
has three data values: TRUE, FALSE, and NULL. Boolean values are typically used
in control flow structures such as IF-THEN, CASE, and loop statements like LOOP, FOR LOOP, and WHILE LOOP.
SQL does not have
the BOOLEAN data type, therefore, you cannot:
· Assign
a BOOLEAN value to a table column.
· Select the value from a
table column into a BOOLEAN variable.
· Use
a BOOLEAN value in a SQL function.
· Use
a BOOLEAN expression in a SQL statement.
· Use
a BOOLEAN value in
the DBMS_OUTPUT.PUTLINE and DBMS_OUTPUT.PUT subprograms.
Character data types
The character data types
represent alphanumeric text. PL/SQL uses the SQL character data types such as CHAR, VARCHAR2, LONG, RAW, LONG
RAW, ROWID, and UROWID.
· CHAR(n) is a
fixed-length character type whose length is from 1 to 32,767 bytes.
· VARCHAR2(n) is
varying length character data from 1 to 32,767 bytes.
Ø Datetime data types
The DateTime data types
represent dates, timestamps with or without time zone, and intervals. PL/SQL
DateTime data types are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH
LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND.
Ø Data type synonyms
Data types have synonyms
for compatibility with non-Oracle data sources such as IBM Db2, and SQL Server.
And it is not a good practice to use data type synonyms unless you are accessing
a non-Oracle Database.
|
Data Type |
Synonyms |
|
NUMBER |
DEC, DECIMAL, DOUBLE
PRECISION, FLOAT, INTEGER, INT, NUMERIC, REAL, SMALLINT |
|
CHAR |
CHARACTER, STRING |
|
VARCHAR2 |
VARCHAR |
