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.