PolarDB:Subtype

Syntax

PL/SQL allows users to customize subtypes by placing specific constraints on base types.

To define a subtype, use the following syntax:

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

Examples

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

sub_var and base_var both represents the VARCHAR type but with limited length. The base type can be any scalar or user-defined PL/SQL data type specifier such as CHAR or RECORD.

• Example 1:

  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;

  Sample result:

  NOTICE:  rec = (1,a)
  DO

• Example 2:

  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;

  Sample result:

  NOTICE:  sub_var = a
  DO

Unconstrained subtypes

An unconstrained subtype has the same set of values as its base type, so it is only another name for the base type. Unconstrained subtypes of the same base type are interchangeable with each other and with the base type. No data type conversion occurs. To define an unconstrained subtype, use the following syntax:

SUBTYPE subtype_name IS base_type;

Constrained subtypes

A constrained subtype has only a subset of the values of its base type. If the base type allows you to specify size, precision and scale, or a range of values, you can specify them for its subtypes. To define a constrained subtype, use the following syntax:

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

Examples

You can enforce a NOT NULL constraint on a variable that uses the subtype. You can also enforce a RANGE constraint on a subtype whose base type is PLS_INTEGER.

A constrained subtype can be implicitly converted to its base type, but the base type can be implicitly converted to the constrained subtype only if the value does not violate a constraint of the subtype.

To assign a variable of one constrained subtype to a variable of another constrained subtype, both the requirements for implicit conversion and the constraints of the target subtype must be met.

In the following examples, the constraints of the target subtypes are not met.

• Precision constraint

  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;

  Sample result:

  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

  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;

  Sample result:

  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;

  Sample result:

  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

  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;

  Sample result:

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

The type of input parameter and return value of local functions

A subtype, like other local types, can be used as the type of input parameter and return value of local functions.

Examples

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;

Sample result:

NOTICE:  outer-inner
DO

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;

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