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.

You can define a subtype in the declaration of a PL function, stored procedure, anonymous block, or package. The follow syntax is available:
SUBTYPE subtype_name IS type_name[(constraint)] [NOT NULL] 
where constraint is:
{precision [, scale]} | length
where:
  • 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.
You can include the constraint clause to define limits on types that support precision or scale.
  • 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.
You can include NOT NULL in a clause to specify that NULL values are not stored in a column of a specified subtype.
Note A subtype that is based on a column inherits the column size constraints, but the subtype does not inherit NOT NULL or CHECK constraints.

Unconstrained subtypes

To create an unconstrained subtype, run the SUBTYPE command to specify the new subtype name and the name of the original type on which the subtype is based. For example, the following command creates an address subtype that has all the attributes of the CHAR type:
SUBTYPE address IS CHAR;
You can also create a subtype (constrained or unconstrained) that is a subtype of another subtype:
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

When you create a subtype that is based on a character type, you can include a length value to define the maximum length of the created subtype. The following example is provided:
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.

You can include values for precision to specify the maximum number of digits in a value of the subtype. When you constrain a numeric base type, you can also include scale to specify the number of digits to the right of the decimal point. The following example is provided:
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.

If a formal parameter is declared as a constrained subtype:
  • 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

You can use the %TYPE notation to declare a subtype that is anchored to a column, as shown in the following example.
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.