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.
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 .
Here are some examples of subtype declarations:
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:
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.