Declaring every airport code as CHAR(3) and every percentage as a number between 0 and 100 repeats the same details throughout the code, and nothing stops a percentage of 120. A subtype gives a type a name, with optional constraints, so variables say what they hold and PL/SQL enforces the limits.
Code for This Guide
The main examples are in the examples/plsql-basics folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
subtype name is base_type [(size)] [range low .. high] [not null];
A subtype can add a size, a NOT NULL constraint, or, for PLS_INTEGER and related types, a RANGE. It can also be based on a column with %TYPE.
A Code and a Percentage
t_airport is a three-character code, and t_percent a PLS_INTEGER limited to 0 to 100.
Example:
declare subtype t_airport is char(3); subtype t_percent is pls_integer range 0 .. 100; -- RANGE: for PLS_INTEGER subtypes v_origin t_airport := 'DXB'; v_load t_percent; begin v_load := 87; dbms_output.put_line(v_origin || ' load factor ' || v_load || '%'); v_load := 120; end; /
Output:
DXB load factor 87% declare * ERROR at line 1: ORA-06502: PL/SQL: value or conversion error ORA-06512: at line 9
87 is accepted; 120 raises ORA-06502, value or conversion error, because it is outside the range.
Predefined Subtypes and %TYPE
PL/SQL has predefined subtypes such as NATURAL (0 or more), POSITIVE (1 or more), POSITIVEN (positive and not null), and SIGNTYPE (-1, 0, or 1). A subtype can also follow a column's type with %TYPE.
Example:
-- predefined subtypes: NATURAL is 0 or more, POSITIVEN 1 or more and NOT NULL;
-- a subtype can also be based on a column's type with %TYPE
declare
subtype t_fare is tickets.fare%type;
v_fare t_fare := 513.01;
v_count natural := 0;
begin
dbms_output.put_line('fare ' || v_fare || ', count ' || v_count);
v_count := -1;
end;
/Output:
fare 513.01, count 0 declare * ERROR at line 1: ORA-06502: PL/SQL: value or conversion error ORA-06512: at line 7
The fare variable takes the type of TICKETS.FARE, so it follows any change to the column. Assigning -1 to a NATURAL fails with ORA-06502.
Things to Know
- Declare subtypes in a package specification to share them across programs.
- A NOT NULL subtype needs an initial value in every declaration.
- RANGE constraints are checked on every assignment, which costs a little time in tight loops.
Related Guides
- How to Create Domains in Oracle, the SQL counterpart for columns
Conclusion
Subtypes name a type with optional size, range, and NOT NULL constraints, so PL/SQL variables document what they hold and reject values that do not fit. Use predefined subtypes such as NATURAL and POSITIVE, base subtypes on columns with %TYPE, and share them in package specifications.
