How to set a specific range for a variable-CodePudding

we know that this is how to declare a variable:

varname number(precision,scale);

but how do we specify that this variable can only have specific values like from 1 to 13?

varname number(2,0) range 1..13 -- but this is wrong, or should integer be used instead?

CodePudding user response：

You can declare custom PL/SQL subtypes that are restricted in the range of values, like this:

declare
    subtype small_int is pls_integer range 1..100;
    x small_int;
begin
    x := 10;       -- SUCCEEDS
    x := 110;      -- FAILS
end;

The subtype must have a base type PLS_INTEGER or one of its subtypes (e.g. NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE, SIMPLE_INTEGER).

Note: this is only applicable to PL/SQL. For a database column, you would normally have the column based on a simple data type (e.g. NUMBER) and put a constraint on the column to ensure the values are within the allowed range.