ORACLE subtype subtypes

To define sub-types

We can define our own subtypes in any PL/SQL block, subroutine, or package, with the following syntax:

Subtype Subtype_name is base_type[(constraint)] [not NULL];

Subtype_name is the name of the declared subtype, Base_type can be any scalar type or user-defined type, and constraints are used only to limit the precision and range of values of the base type, or the maximum length. Here are a few examples:

DECLARE
Subtype Birthdate is a DATE not NULL; --Based on DATE type

Subtype counter is NATURAL; --Based on NATURAL subtype

TYPE NameList is TABLE of VARCHAR2 (10);

Subtype Dutyroster is namelist; --Based on TABLE type

TYPE Timerec is RECORD (
Minutes INTEGER,
Hours INTEGER
);

Subtype Finishtime is Timerec; --Based on RECORD type

Subtype Id_num is emp.empno%type; --Based on column type

We can use%type or%rowtype to specify the base type. When%type provides a data type in a database field, the subtype inherits the size constraint (if any) of the field. However, subtypes do not inherit other constraints, such as not NULL.

2. Using sub-types

Once we have defined subtypes, we can declare variables, constants, and so on for that type. In the following example, we declare the counter type variable, and the name of the subtype represents the purpose for which the variable is used:

DECLARE
Subtype counter is NATURAL;

ROWS counter;

The following example shows how to constrain user-defined subtypes:

DECLARE
Subtype Accumulator is number;

Total Accumulator (7, 2);

Subtypes can also check that values are out of bounds to improve reliability. In the following example, we limit the range of type numeral to 9 to 9. If the program assigns a value outside this range to the numeral type variable, PL/SQL throws an exception.

DECLARE
Subtype numeral is number (1, 0);

X_axis numeral; --magnitude range is-9. 9
Y_axis numeral;
BEGIN
X_axis: = 10; --Raises Value_error
...
END;



This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/ZengMuAnSha/archive/2010/03/30/5431728.aspx

ORACLE subtype subtypes