[Chapter 4] 4.6 Programmer-Defined Subtypes

4.6 Programmer-Defined Subtypes

With Version 2.1 of PL/SQL (available with Oracle Server Version 7.1), you can define your own unconstrained subtypes of predefined datatypes. In PL/SQL, a subtype of a datatype is a variation that specifies the same set of rules as the original datatype, but might allow only a subset of the datatype's values.

There are two kinds of subtypes: constrained and unconstrained. A constrained subtype is one 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 parts 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 only store integer values greater than zero. The following assignment raises the VALUE_ERROR exception:

DECLARE
   pentagon_cost_cutting POSITIVE;--The report to Congress
BEGIN
   pentagon_cost_cutting := -100000000;--The inside reality

An unconstrained subtype is 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.

In spite of the limitations of unconstrained subtypes, you should still use them wherever you can identify a logical subtype of data in your applications. Later, when you can implement constrained subtypes, you will only have to include a constraint in the SUBTYPE declaration, and all variables that are declared with this subtype will take on those constraints.

4.6.1 Declaring Subtypes

In order to make a subtype available, you first have to declare it in the declaration section of an anonymous PL/SQL block, procedure, function, or package. You've already seen the syntax for declaring a subtype used by PL/SQL in the STANDARD package. The general format of a subtype declaration is:

SUBTYPE <subtype_name> IS <base_type>;

where subtype_name is the name of the new subtype; this name is the identifier that will be referenced in declarations of variables of that type. The base_type, which specifies the datatype which forms the basis for the subtype, can be any of the categories shown in Table 4.3 .

Table 4.3: PL/SQL Subtypes
Subtype Category	Description
PL/SQL datatype	Predefined PL/SQL datatype, including predefined subtypes, such as POSITIVE.
Programmer-defined subtype	Previously created with a SUBTYPE declaration.
variable_name%TYPE	The subtype is inferred from the datatype of the variable.
table_name.column_name%TYPE	Determined from the datatype of the column in the table.
table_name%ROWTYPE	Contains the same structure as the table.
cursor_name%ROWTYPE	Contains the same structure as the "virtual table" created by the cursor.
PL/SQL table	Contains the same structure as the PL/SQL table previously declared with a TYPE statement.

4.6.2 Examples of Subtype Declarations

Here are some examples of subtype declarations:

A subtype based on a predefined datatype:
```
SUBTYPE hire_date_type IS DATE;
```

A subtype based on a predefined subtype:

SUBTYPE soc_sec_number_type IS POSITIVE;

Multiple subtypes based on a PL/SQL table:

TYPE room_tab IS TABLE OF NUMBER(3) INDEX BY BINARY INTEGER;
SUBTYPE hotel_room_tab_type IS room_tab;
SUBTYPE motel_room_tab_type IS room_tab;
SUBTYPE resort_room_tab_type IS room_tab;

A general subtype based on NUMBER and then additional subtypes defined on that original subtype:

SUBTYPE primary_key_number_type IS NUMBER;
SUBTYPE company_key_type IS primary_key_number;
SUBTYPE employee_key_type IS primary_key_number;

A subtype based on the datatype of a table's column:
```
SUBTYPE last_name_type IS employee.last_name%TYPE;
```
A subtype based on the datatype of a record's column:
```
SUBTYPE first_name_type IS emp_rec.first_name%TYPE;
```

The following subtype declarations are invalid because they seek to apply a constraint to the subtype:

SUBTYPE three_value_logic IS            -- Invalid constraint!
VARCHAR2 IN ('YES', 'NO', 'MAYBE');

SUBTYPE prefix_type IS CHAR(3);         -- Invalid constraint!

4.6.3 Emulating Constrained Subtypes

While you cannot directly constrain a subtype in PL/SQL at present you can, in effect, create a constrained subtype by anchoring a subtype to a constrained type declaration.

Suppose that the column big_line of table text_editor was set to VARCHAR2(200), as shown below:

CREATE TABLE text_editor
   (big_line VARCHAR2(200) NOT NULL, ...other columns...);

By applying this example, I can now create a subtype through a %TYPE reference to that column, as follows:

SUBTYPE paragraph_type IS text_editor.big_line%TYPE;

Like the original column declaration of big_line, this paragraph type is defined as VARCHAR2(200). If I use paragraph_type to declare character variables, then those variables will take on a maximum length of 200:

opening_paragraph paragraph_type;

You can also use %TYPE against a PL/SQL variable to constrain a datatype. Suppose I declare the following variables and a single subtype:

DECLARE
   /* A local PL/SQL string of length 10 */
   small_string VARCHAR2(10);

   /* Create a subtype based on that variable */
   SUBTYPE teensy IS small_string%TYPE;

   /* Declare two variables based on the subtype */
   weensy teensy;
   weensy_plus teensy(100);
BEGIN

The subtype is based on the small_string variable. As a result, the weensy variable, which is declared on the subtype, has a length of 10 bytes by default. So if I try to perform the following assignment, PL/SQL will raise the VALUE_ERROR exception:

weensy := 'morethantenchars';

When I declared the weensy_plus variable, on the other hand, I overrode the default subtype-based length of 10 with a maximum length of 100. As a result, this next assignment does not raise an exception:

weensy_plus := 'Lots of room for me to type now';

When you create a subtype based on an existing variable or database column, that subtype inherits the length (or precision and scale, in the case of a NUMBER datatype) from the original datatype. This constraint takes effect when you declare variables based on the subtype, but only as a default. You can always override that constraint. You will have to wait for a future version of PL/SQL, however, to actually enforce the constraint in a programmer-defined subtype.

Finally, an anchored subtype does not carry over the NOT NULL constraint to the variables it defines. Nor does it transfer a default value that was included in the original declaration of a variable or column specification.