Subtype-PolarDB(PolarDB)-阿里云帮助中心

PL/SQL lets you define subtypes to give base types additional semantic meaning, enforce constraints, or alias them under domain-specific names.

Subtypes are useful when you want to:

Give a type a meaningful name that reflects its intended use (for example, Balance instead of NUMBER)
Restrict a type to a specific range of values to catch out-of-range assignments at runtime
Share a constrained type definition across multiple variables or local functions without repeating the constraint

Syntax

SUBTYPE subtype_name IS base_type
    [precision [, scale ] | RANGE low_value .. high_value ] [ NOT NULL ];

The base type can be any scalar or user-defined PL/SQL data type specifier, including CHAR, VARCHAR, NUMBER, PLS_INTEGER, and RECORD-based types.

Unconstrained subtypes

An unconstrained subtype has the same set of values as its base type — it is an alias, not a restriction. Unconstrained subtypes of the same base type are interchangeable with each other and with the base type. No data type conversion occurs.

SUBTYPE subtype_name IS base_type;

The example below defines subtype_char as an alias for VARCHAR(10). Both sub_var and base_var hold VARCHAR(10) values and are interchangeable:

DECLARE
  SUBTYPE subtype_char IS VARCHAR(10);
  sub_var subtype_char;
  base_var VARCHAR(10);
BEGIN
  NULL;
END;

A subtype's base type can itself be a subtype. The following example chains two subtypes and assigns a value through the chain:

DECLARE
  SUBTYPE subtype_char IS VARCHAR;
  SUBTYPE sub_subtype_char IS subtype_char;
  sub_var sub_subtype_char;
BEGIN
  sub_var := 'a';
  RAISE NOTICE 'sub_var = %', sub_var;
END;

Output:

NOTICE:  sub_var = a
DO

The base type can also be a user-defined composite type. The example below creates a subtype based on a RECORD type and uses it as a variable:

DECLARE
  TYPE r_type IS RECORD(id INT, name VARCHAR(10));
  SUBTYPE subtype_record IS r_type;
  rec subtype_record;
BEGIN
  rec.id := 1;
  rec.name := 'a';
  RAISE NOTICE 'rec = %', rec;
END;

Output:

NOTICE:  rec = (1,a)
DO

Constrained subtypes

A constrained subtype restricts its base type to a subset of values. Use the following syntax:

SUBTYPE subtype_name IS base_type
    { precision [, scale ] | RANGE low_value .. high_value } [ NOT NULL ]

The available constraint options depend on the base type:

Constraint	Applicable base types	Description
`precision [, scale ]`	Numeric types such as `NUMBER`	Limits the number of digits and decimal places
`RANGE low_value .. high_value`	`PLS_INTEGER` only	Restricts the value to a specific integer range
`NOT NULL`	Any base type	Prevents `NULL` assignments

Constraint behavior

A constrained subtype can be implicitly converted to its base type. The base type can be implicitly converted to the constrained subtype only if the value does not violate the subtype's constraint. To assign a variable of one constrained subtype to a variable of another constrained subtype, both the implicit conversion requirements and the target subtype's constraints must be met.

The following examples show how each constraint type enforces its rules.

Precision constraint

subtype_number restricts NUMBER to precision 8, scale 2, meaning values must be less than 10^6 in absolute value:

DECLARE
  SUBTYPE subtype_number IS NUMBER(8,2);
  var1  subtype_number;
  var2   subtype_number;
BEGIN
  var1 := 100000.00; -- The assignment is successful.
  RAISE NOTICE 'var1 = %' , var1;
  var2 := 1000000.00; -- The assignment failed.
END;

Output:

NOTICE:  var1 = 100000
ERROR:  numeric field overflow
DETAIL:  A field with precision 8, scale 2 must round to an absolute value less than 10^6.
CONTEXT:  PL/SQL assignment "var2 := 1000000.00"

RANGE constraint

RANGE is only supported when the base type is PLS_INTEGER:

DECLARE
  SUBTYPE subtype_range IS PLS_INTEGER RANGE 0..9;
  var subtype_range := 4; -- The assignment is successful.
BEGIN
  RAISE NOTICE 'var = %', var;
  var := 10; -- The assignment failed.
END;

Output:

NOTICE:  var = 4
ERROR:  the assignment of variable "var" is out of range, since it's declared between 0 and 9
CONTEXT:  PL/SQL function inline_code_block line 6 at assignment

NOT NULL constraint

DECLARE
  SUBTYPE subtype_range IS INT NOT NULL;
  var subtype_range := 1;
BEGIN
  RAISE NOTICE 'var = %', var;
  var := NULL; -- The assignment failed.
END;

Output:

NOTICE:  var = 1
ERROR:  null value cannot be assigned to variable "var" declared NOT NULL
CONTEXT:  PL/SQL function inline_code_block line 5 at assignment

Implicit type conversion across subtypes

When assigning between two different constrained subtypes, both implicit conversion rules and the target's constraint must be satisfied. In this example, var1 (subtype of INT) is assigned to var2 (subtype of CHAR(3)). The first assignment succeeds because 100 fits in 3 characters; the second fails because 1000 does not:

DECLARE
  SUBTYPE subtype_int IS INT;
  SUBTYPE subtype_char IS CHAR(3);
  var1 subtype_int := 100;
  var2 subtype_char;
BEGIN
  var2 := var1; -- The assignment is successful.
  RAISE NOTICE 'var2 = %', var2;
  var1 := 1000;
  var2 := var1; -- The assignment failed.
END;

Output:

NOTICE:  var2 = 100
ERROR:  value too long for type character(3 char)
CONTEXT:  PL/SQL function inline_code_block line 10 at assignment

Subtypes as function parameters and return types

A subtype can be used as the type of a local function's parameter or return value. The constraints on the subtype are enforced at the call site.

DECLARE
  SUBTYPE sub_type IS VARCHAR(20);
  PROCEDURE outer_proc IS
    outer_var sub_type; -- Use the local type sub_type
    FUNCTION inner_func(name sub_type) RETURN sub_type IS
    BEGIN
      RETURN name || 'inner'; -- The return value is of the sub_type type
    END;
  BEGIN
    outer_var := inner_func('outer-'); -- Call the local function inner_func
    RAISE NOTICE '%', outer_var;
  END;
BEGIN
  outer_proc; -- Call the local procedure outer_proc first
END;

Output:

NOTICE:  outer-inner
DO

An OUT parameter initializes the input variable to NULL. If the parameter's subtype has a NOT NULL constraint, the initialization fails with an error.

DECLARE
  SUBTYPE sub_type IS PLS_INTEGER NOT NULL;
  a sub_type := 1;
  PROCEDURE proc_test(id OUT sub_type) IS
  BEGIN
    NULL;
  END;
BEGIN
  proc_test(a); -- An error occurs.
END;

Output:

ERROR:  null value cannot be assigned to variable "a" declared NOT NULL
CONTEXT:  PL/SQL function inline_code_block line 3 at CALL