User-Defined PL/SQL Subtypes v12
Advanced Server supports user-defined PL/SQL subtypes and (subtype) aliases. A subtype is a data type with an optional set of constraints that restrict the values that can be stored in a column of that type. The rules that apply to the type on which the subtype is based are still enforced, but you can use additional constraints to place limits on the precision or scale of values stored in the type.
You can define a subtype in the declaration of a PL function, procedure, anonymous block or package. The syntax is:
Where constraint
is:
Where:
subtype_name
subtype_name
specifies the name of the subtype.
type_name
type_name
specifies the name of the original type on which the subtype is based. type_name
may be:
- The name of any of the type supported by Advanced Server.
- The name of any composite type.
- A column anchored by a
%TYPE
operator. - The name of another subtype.
Include the constraint
clause to define restrictions for types that support precision or scale.
precision
precision
specifies the total number of digits permitted in a value of the subtype.
scale
scale
specifies the number of fractional digits permitted in a value of the subtype.
length
length
specifies the total length permitted in a value of CHARACTER, VARCHAR
, or TEXT
base types.
Include the NOT NULL
clause to specify that NULL
values may not be stored in a column of the specified subtype.
Note that a subtype that is based on a column will inherit the column size constraints, but the subtype will not inherit NOT NULL
or CHECK
constraints.
Unconstrained Subtypes
To create an unconstrained subtype, use the SUBTYPE
command to specify the new subtype name and the name of the type on which the subtype is based. For example, the following command creates a subtype named address
that has all of the attributes of the type, CHAR:
You can also create a subtype (constrained or unconstrained) that is a subtype of another subtype:
This command creates a subtype named cust_address
that shares all of the attributes of the address
subtype. Include the NOT NULL
clause to specify that a value of the cust_address
may not be NULL
.
Constrained Subtypes
Include a length
value when creating a subtype that is based on a character type to define the maximum length of the subtype. For example:
This example creates a subtype named acct_name
that is based on a VARCHAR
data type, but is limited to 15 characters in length.
Include values for precision
(to specify the maximum number of digits in a value of the subtype) and optionally, scale
(to specify the number of digits to the right of the decimal point) when constraining a numeric base type. For example:
This example creates a subtype named acct_balance
that shares all of the attributes of a NUMBER
type, but that may not exceed 3 digits to the left of the decimal point and 2 digits to the right of the decimal.
An argument declaration (in a function or procedure header) is a formal argument. The value passed to a function or procedure is an actual argument. When invoking a function or procedure, the caller provides (0 or more) actual arguments. Each actual argument is assigned to a formal argument that holds the value within the body of the function or procedure.
If a formal argument is declared as a constrained subtype:
- Advanced Server does not enforce subtype constraints when assigning an actual argument to a formal argument when invoking a function.
- Advanced Server enforces subtype constraints when assigning an actual argument to a formal argument when invoking a procedure.
Using the %TYPE Operator
You can use %TYPE
notation to declare a subtype anchored to a column. For example:
This command creates a subtype named emp_type
whose base type matches the type of the empno
column in the emp
table. A subtype that is based on a column will share the column size constraints; NOT NULL
and CHECK
constraints are not inherited.
Subtype Conversion
Unconstrained subtypes are aliases for the type on which they are based. Any variable of type subtype (unconstrained) is interchangeable with a variable of the base type without conversion, and vice versa.
A variable of a constrained subtype may be interchanged with a variable of the base type without conversion, but a variable of the base type may only be interchanged with a constrained subtype if it complies with the constraints of the subtype. A variable of a constrained subtype may be implicitly converted to another subtype if it is based on the same subtype, and the constraint values are within the values of the subtype to which it is being converted.