Oracle PL/SQL - PL SQL Data Type Unconstrained Subtypes

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

Introduction

An unconstrained subtype has the same set of values as its base type.

It is only alias 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 this syntax: 

SUBTYPE subtype_name IS base_type

An example of an unconstrained subtype, which PL/SQL predefines for compatibility with ANSI, is: 

SUBTYPE "DOUBLE PRECISION" IS FLOAT

In the following code the unconstrained subtypes MyNumberType and Counter show the intended uses of data items of their types.

Demo

SQL>
SQL>-- from   w  w  w .j  a v  a2  s. co m
SQL> DECLARE
  2    SUBTYPE MyNumberType IS NUMBER;
  3
  4    my_val   MyNumberType(6,2);
  5    your_val MyNumberType(8,2);
  6    his_val  MyNumberType(8,2);
  7    the_val  CONSTANT   MyNumberType(8,2) := 250000.00;
  8
  9    SUBTYPE MyNumberType2 IS NATURAL;
 10
 11    accounts     MyNumberType2 := 1;
 12    deposits     MyNumberType2 := 0;
 13    withdrawals  MyNumberType2 := 0;
 14    overdrafts   MyNumberType2 := 0;
 15
 16    PROCEDURE deposit (
 17      account  IN OUT MyNumberType,
 18      amount   IN     MyNumberType
 19    ) IS
 20    BEGIN
 21      account  := account + amount;
 22      deposits := deposits + 1;
 23    END;
 24
 25  BEGIN
 26    NULL;
 27  END;
 28  /

PL/SQL procedure successfully completed.

SQL>

Related Topics