PolarDB supports user-defined Procedural Language for SQL (PL/SQL) subtypes and (subtype) aliases.
A subtype is a data type that has an optional set of constraints. The constraints restrict the values that can be stored in a column of this type. If rules apply to a data type that has a subtype, the rules are still enforced. However, you can use additional constraints to limit the precision or the scale of values that the type stores.
SUBTYPE subtype_name IS type_name[(constraint)] [NOT NULL]
{precision [, scale]} | length
- subtype_name specifies the name of the subtype.
- type_name specifies the name of the original type on which the subtype is based.
Note You can set the type_name parameter to the following names:
- The name of a supported type.
- The name of a composite type.
- A column anchored by a %TYPE operator.
- The name of another subtype.
- precision specifies the total number of digits that are permitted in a value of the subtype.
- scale specifies the number of fractional digits that are permitted in a value of the subtype.
- length specifies the total length that is permitted in a value of CHARACTER, VARCHAR, or TEXT base types.
Unconstrained subtypes
SUBTYPE address IS CHAR;
SUBTYPE cust_address IS address NOT NULL;
The preceding command creates a cust_address subtype that shares all the attributes of the address subtype. You can include the NOT NULL clause to specify that a value of the cust_address subtype is not NULL.
Constrained subtypes
SUBTYPE acct_name IS VARCHAR (15);
The preceding example creates an acct_name subtype that is based on a VARCHAR data type. However, the subtype is limited to 15 characters in length.
SUBTYPE acct_balance IS NUMBER (5, 2);
This example creates an acct_balance subtype that shares all the attributes of a NUMBER type. The subtype cannot exceed three digits to the left of the decimal point and two digits to the right of the decimal.
A parameter declaration in a function header or a stored procedure header is a formal parameter. The value passed to a function or a stored procedure is an actual parameter. When a function or a stored procedure is called, the caller provides (zero or more) actual parameters. Each actual parameter is assigned to a formal parameter that constrains the body of the function or the value in the body of the stored procedure.
- PolarDB does not enforce subtype constraints when an actual parameter is assigned to a formal parameter in a function call.
- PolarDB enforces subtype constraints when an actual parameter is assigned to a formal parameter in a call of a stored procedure.
Use the %TYPE operator
SUBTYPE emp_type IS emp.empno%TYPE
This command creates an emp_type subtype. The base type of this subtype matches the type of the empno column in the emp table. A subtype that is based on a column shares the column size constraints. The NOT NULL and CHECK constraints are not inherited.
Subtype conversion
Unconstrained subtypes are aliases for the type on which they are based. A variable of a type or a subtype (unconstrained) is interchangeable with a variable of the base type without conversion, and vice versa.
A variable of a constrained subtype can be interchanged with a variable of the base type without conversion. However, if the constraints of the base type and the subtype are consistent, a variable of the base type can be interchanged with only a constrained subtype. A variable of a constrained subtype can be implicitly converted to another subtype. This happens if the variable and the subtype are based on the same subtype and the constraint values are in the values of the subtype to which the variable is converted.