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.

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.