PL/SQL User's Guide and Reference

User-Defined Subtypes

Each PL/SQL base type specifies a set of values and a set of operations applicable to objects of that type. Subtypes specify the same set of operations as their base type but only a subset of its values. Thus, a subtype does not introduce a new type; it merely places an optional constraint on its base type.

PL/SQL predefines several subtypes in package STANDARD. For example, PL/SQL predefines the subtype CHARACTER, as follows:

SUBTYPE CHARACTER IS CHAR;

The subtype CHARACTER specifies the same set of values as its base type CHAR. Thus, CHARACTER is an unconstrained subtype.

Subtypes can increase reliability, provide compatibility with ANSI/ISO and IBM types, and improve readability by indicating the intended use of constants and variables.

Defining Subtypes

You can define your own subtypes in the declarative part of any PL/SQL block, subprogram, or package using the syntax

SUBTYPE subtype_name IS base_type;

where subtype_name is a type specifier used in subsequent declarations and base_type stands for the following syntax:

{  cursor_name%ROWTYPE
 | cursor_variable_name%ROWTYPE
 | plsql_table_name%TYPE 
 | record_name%TYPE
 | scalar_type_name 
 | table_name%ROWTYPE 
 | table_name.column_name%TYPE 
 | variable_name%TYPE}

For example, all of the following subtype definitions are legal:

DECLARE
   SUBTYPE EmpDate IS DATE;           -- based on DATE type
   SUBTYPE Counter IS NATURAL;        -- based on NATURAL subtype
   TYPE NameTab IS TABLE OF VARCHAR2(10)
      INDEX BY BINARY_INTEGER;
   SUBTYPE EnameTab IS NameTab;       -- based on TABLE type
   TYPE TimeTyp IS RECORD (minute INTEGER, hour INTEGER);
   SUBTYPE Clock IS TimeTyp;          -- based on RECORD type
   SUBTYPE ID_Num IS emp.empno%TYPE;  -- based on column type
   CURSOR c1 IS SELECT * FROM dept;
   SUBTYPE Dept_Rec IS c1%ROWTYPE;    -- based on cursor rowtype

However, you cannot specify a constraint on the base type. For example, the following definitions are illegal:

DECLARE 
   SUBTYPE Accumulator IS NUMBER(7,2); -- illegal; must be NUMBER
   SUBTYPE Delimiter IS CHAR(1);       -- illegal; must be CHAR
   SUBTYPE Word IS VARCHAR2(15);       -- illegal

Although you cannot define constrained subtypes directly, you can use a simple workaround to define size-constrained subtypes indirectly. Simply declare a size-constrained variable, then use %TYPE to provide its datatype, as shown in the following example:

DECLARE 
   temp VARCHAR2(15);
   SUBTYPE Word IS temp%TYPE; -- maximum size of Word is 15

Likewise, if you define a subtype using %TYPE to provide the datatype of a database column, the subtype adopts the size constraint (if any) of the column. However, the subtype does not adopt other kinds of constraints such as NOT NULL.

Using Subtypes

Once you define a subtype, you can declare objects of that type. In the example below, you declare two variables of type Counter. Notice how the subtype name indicates the intended use of the variables.

DECLARE 
   SUBTYPE Counter IS NATURAL; 
   rows      Counter;
   employees Counter;

The following example shows that you can constrain a user-defined subtype when declaring variables of that type:

DECLARE 
   SUBTYPE Accumulator IS NUMBER; 
   total Accumulator(7,2);

Subtypes can increase reliability by detecting out-of-range values. In the example below, you restrict the subtype Scale to storing integers in the range -9 .. 9. If your program tries to store a number outside that range in a Scale variable, PL/SQL raises an exception.

DECLARE 
   temp NUMBER(1,0);
   SUBTYPE Scale IS temp%TYPE;
   x_axis Scale;  -- magnitude range is -9 .. 9
   y_axis Scale;
BEGIN
   x_axis := 10;  -- raises VALUE_ERROR

Type Compatibility

An unconstrained subtype is interchangeable with its base type. For example, given the following declarations, the value of amount can be assigned to total without conversion:

DECLARE 
   SUBTYPE Accumulator IS NUMBER; 
   amount NUMBER(7,2); 
   total  Accumulator; 
BEGIN
   ...
   total := amount;
   ...
END;

Different subtypes are interchangeable if they have the same base type. For instance, given the following declarations, the value of finished can be assigned to debugging:

DECLARE 
   SUBTYPE Sentinel IS BOOLEAN; 
   SUBTYPE Switch IS BOOLEAN; 
   finished  Sentinel; 
   debugging Switch; 
BEGIN
   ...
   debugging := finished;
   ...
END;

Different subtypes are also interchangeable if their base types are in the same datatype family. For example, given the following declarations, the value of verb can be assigned to sentence:

DECLARE 
   SUBTYPE Word IS CHAR; 
   SUBTYPE Text IS VARCHAR2; 
   verb     Word; 
   sentence Text; 
BEGIN
   ...
   sentence := verb;
   ...
END;