Define user-defined PL/SQL subtypes - PolarDB

PolarDB for PostgreSQL(Compatible with Oracle) 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 stored in a column of that type. The base type rules still apply, but you can add constraints to limit the precision, scale, or length of stored values.

Subtypes let you:

Add semantic clarity: Give meaningful names to data items to signal their intended use in code.
Restrict value ranges: Catch out-of-range values at assignment time.

Define subtypes in the declaration section of a PL function, procedure, anonymous block, or package.

Syntax

SUBTYPE subtype_name IS type_name[(constraint)] [NOT NULL]

where constraint is:

{precision [, scale]} | length

Parameters

subtype_name

The name of the new subtype.

type_name

The name of the base type. Accepted values:

Any type supported by PolarDB for PostgreSQL(Compatible with Oracle)
A composite type
A column reference using %TYPE
Another subtype

precision

The total number of digits permitted in a value of the subtype. Applies to numeric base types.

scale

The number of fractional digits permitted. Applies to numeric base types. Optional when precision is specified.

length

The total character length permitted. Applies to CHARACTER, VARCHAR, and TEXT base types.

NOT NULL

Prevents NULL values from being stored in the subtype.

A subtype based on a column inherits column size constraints, but does not inherit NOT NULL or CHECK constraints.

Unconstrained subtypes

An unconstrained subtype is an alias for its base type. Variables of the subtype and the base type are interchangeable without conversion.

SUBTYPE address IS CHAR;

This creates address with all attributes of CHAR.

Create a subtype from another subtype by passing the existing subtype as the base type:

SUBTYPE cust_address IS address NOT NULL;

This creates cust_address with all attributes of address, and adds a NOT NULL constraint.

Constrained subtypes

Use a constrained subtype to limit what values a variable can hold.

Character types — specify length to set the maximum number of characters:

SUBTYPE acct_name IS VARCHAR (15);

acct_name is based on VARCHAR and holds at most 15 characters.

Numeric types — specify precision and optionally scale:

SUBTYPE acct_balance IS NUMBER (5, 2);

acct_balance is based on NUMBER and holds values with at most 3 digits before and 2 digits after the decimal point.

Constraint enforcement in functions and procedures

When a formal argument is declared as a constrained subtype, PolarDB for PostgreSQL(Compatible with Oracle):

Enforces subtype constraints when assigning an actual argument to a formal argument during procedure invocation.
Does not enforce subtype constraints when assigning an actual argument to a formal argument during function invocation.

Use the %TYPE operator

Anchor a subtype to a specific column to keep the subtype's base type in sync with the column definition:

SUBTYPE emp_type IS emp.empno%TYPE

emp_type inherits the type of the empno column in the emp table, including its size constraints. NOT NULL and CHECK constraints on the column are not inherited.

Subtype conversion

Unconstrained subtypes are aliases for their base type. A variable of an unconstrained subtype is interchangeable with a base type variable in both directions, without conversion.

Constrained subtypes follow these rules:

A constrained subtype variable can always be assigned to a base type variable without conversion.
A base type variable can only be assigned to a constrained subtype variable if the value satisfies the subtype's constraints.
A constrained subtype variable can be implicitly converted to another subtype if both subtypes share the same base type and the value fits within the target subtype's constraints.