Oracle PL/SQL Programming, Third Edition

Programmer-Defined Subtypes

With the SUBTYPE statement, PL/SQL allows you to define your own subtypes or aliases of predefined datatypes, sometimes referred to as abstract datatypes . In PL/SQL, a subtype of a datatype is a variation that specifies the same set of rules as the original datatype, but that might allow only a subset of the datatype’s values.

There are two kinds of subtypes, constrained and unconstrained:

Constrained subtype

A subtype that restricts or constrains the values normally allowed by the datatype itself. POSITIVE is an example of a constrained subtype of BINARY_ INTEGER. The package STANDARD, which predefines the datatypes and the functions that are part of the standard PL/SQL language, declares the subtype POSITIVE as follows:

SUBTYPE POSITIVE IS BINARY_INTEGER RANGE 1 .. 2147483647;

A variable that is declared POSITIVE can store only integer values greater than zero.

Unconstrained subtype

A subtype that does not restrict the values of the original datatype in variables declared with the subtype. FLOAT is an example of an unconstrained subtype of NUMBER. Its definition in the STANDARD package is:

SUBTYPE FLOAT IS NUMBER;

In other words, an unconstrained subtype provides an alias or alternate name for the original datatype. Prior to Oracle8i, PL/SQL developers could define only unconstrained subtypes; now, constrained subtypes are allowed as well, as shown below:

CREATE OR REPLACE PACKAGE utility AS SUBTYPE big_string IS VARCHAR2(32767); SUBTYPE big_db_string ...