DB2 Version 10.1 for Linux, UNIX, and Windows

SUBTYPE definitions (PL/SQL)

A subtype is a definition of a type based on a built-in type.

Subtypes provide a layer of abstraction between variables and parameters and the data types that they use. This layer allows you to concentrate any changes to the data types in one location. You can add constraints to subtypes so that they cannot be nullable or limited to a specific range of values.

Subtypes can be defined in:

CREATE PACKAGE statement (PL/SQL)
CREATE PACKAGE BODY statement (PL/SQL)
CREATE PROCEDURE (PL/SQL)
CREATE FUNCTION (PL/SQL)
CREATE TRIGGER (PL/SQL)
Anonymous block (PL/SQL)

Syntax


>>-SUBTYPE--type-name--IS--built-in-type------------------------>

>--+-----------------------------------+--+----------+---------><
   '-RANGE--start-value--..--end-value-'  '-NOT NULL-'

Description

SUBTYPE type-name: Specifies an identifier for the subtype. You cannot specify BOOLEAN as the built-in type.
built-in-type: Specifies the built-in data type that the subtype is based on.
RANGE start-value .. end-value: Optionally defines a range of values within the domain of the subtype that is valid for the subtype.
NOT NULL: Optionally defines that the subtype is not nullable.

Example

The following example shows a package that defines a subtype for small integers:

CREATE OR REPLACE PACKAGE math
IS
SUBTYPE tinyint IS INTEGER RANGE -256 .. 255 NOT NULL
END;