Oracle PL/SQL - PL SQL Data Type Constrained Subtypes

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. Unconstrained Subtypes

Introduction

A constrained subtype has only a subset of the values of its base type.

If the base type lets you specify size, precision and scale, or a range of values, then you can specify them for its subtypes.

The subtype definition syntax is: 

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

Otherwise, the only constraint that you can put on its subtypes is NOT NULL: 

SUBTYPE subtype_name IS base_type [ NOT NULL ]

The only base types for which you can specify a range of values are PLS_INTEGER and its subtypes both predefined and user-defined.

In the following code the constrained subtype Balance detects out-of-range values.

Demo

SQL>
SQL> DECLARE-- from  w  w  w . java 2  s. co  m
  2    SUBTYPE MyNumberType IS NUMBER(8,2);
  3
  4    my_val     MyNumberType;
  5    your_val   MyNumberType;
  6
  7  BEGIN
  8    my_val := 2000.00;
  9    your_val  := 1000000.00;
 10  END;
 11  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number
precision too large
ORA-06512: at line 9


SQL>

Related Topic