Saturday, 26 November 2016

PL/SQL Subtypes

We are always working with data and their datatypes. In the tables we constrain the data by their datatype. For instance, we create a price column of type NUMBER(4,2) telling the database to store a maximum of four (4) digits of which we want to use two (2) as the decimal digits. This is how we, in real life, use our prices. In PL/SQL we can anchor our variables to this type using the <TABLE>.%TYPE syntax. But what if there is no column to anchor to? We can also use the NUMBER(6,2) syntax to constrain the values possible for a certain variable.

Consider this piece of code:

  l_var1 NUMBER(4,2);
  l_var2 NUMBER(4,2);
  l_var3 NUMBER(4,2);

What happens when we decide we want to store larger amounts or more decimal digits? We can easily change NUMBER(4,2) to NUMBER(6,3), but it’s likely that the variables are scattered all over the code we are managing. One solution is to create a table with a column holding the desired format so we can anchor to that column, but it seems like overkill to create a table just for the types we need. What happens if someone unaware of its use changes the datatype? This is where subtypes come in handy.


A subtype does not introduce a new type; rather, it places an optional constraint on its base type. Subtypes can be either constrained or unconstrained. Examples of unconstrained subtypes are: FLOAT for NUMBER and CHARACTER for CHAR. These are available mainly because of compatibility with other databases. Examples of built-in constrained subtypes are: SIGNTYPE (only values allowed are -1, 0 and 1) and POSITIVE which is essentially a BINARY_INTEGER with a restricted value range between 1 and 2147483647.

You can define your own subtypes in the declarative part of any PL/SQL block, subprogram, or package using the syntax:

SUBTYPE subtype_name IS base_type[(constraint)] [NOT NULL];

We can create subtypes for every special datatype we need. Of course we can use the unrestricted datatypes like NUMBER and then restrict it when we need it to be restricted, but chances are that you are going to forget about this at least once in your code, resulting in unpredictable behaviour. And how about the VARCHAR2 datatype. You always have to declare a variable with a constrained size. It is probably a lot easier to declare a subtype of a certain size once and then use that throughout your code.

You can simply create a package like this:

  -- Author  : Patrick Barel
  -- Purpose : Supply different subtypes

  SUBTYPE maxvarchar IS VARCHAR2(32767);
  SUBTYPE maxvarchardb IS VARCHAR2(4000);
END types;

…and then in your code simply refer to the types using something like this:

    l_text   types.maxvarchar;
    l_dbtext types.maxvarchardb;

Not only does this make your code more readable, self documenting if you will, it also helps when you want to use certain changes Oracle makes to the implementation of the database. Back in Oracle 7 the maximum varchar2 size for a database column was 2000. Nowadays it’s 4000. By using the subtypes we just need to change one line of code in our application and we use the new size throughout our application.


Consider this package:

PACKAGE subtype_test IS
  SUBTYPE money IS NUMBER(20, 2);
  FUNCTION add_number(amount_in IN NUMBER
                     ,add_in    IN NUMBER) RETURN NUMBER;
  FUNCTION add_money(amount_in IN money
                    ,add_in    IN money) RETURN money;
END subtype_test;

PACKAGE BODY subtype_test IS
  FUNCTION add_number(amount_in IN NUMBER
                     ,add_in    IN NUMBER) RETURN NUMBER IS
    dbms_output.put_line('AMOUNT_IN :'||amount_in);
    dbms_output.put_line('ADD_IN    :'||add_in);
    RETURN(amount_in + add_in);
  FUNCTION add_money(amount_in IN money
                    ,add_in    IN money) RETURN money IS
    dbms_output.put_line('AMOUNT_IN :'||amount_in);
    dbms_output.put_line('ADD_IN    :'||add_in);
    RETURN(amount_in + add_in);
END subtype_test;

These functions perform exactly the same tasks; the only difference is their input and return type. When I call function add_number, I can send input of any number and it will return a value with the maximum precision. When I call function add_money, I can send the same input parameters, but it will return a value with the precision specified in the subtype.

The output of this test script:

  l_test                                  NUMBER;
  l_test   := subtype_test.add_number( 10.125, 2.2542 );
  DBMS_OUTPUT.put_line( 'number : ' || TO_CHAR( l_test ));
  l_test   := subtype_test.add_money( 10.125, 2.2542 );
  DBMS_OUTPUT.put_line( 'money  : ' || TO_CHAR( l_test ));


number : 12.3792
money  : 12.38

The constraint of the subtype is not enforced on the parameters but the returned type is restricted even though the variable that receives this value is not.


If you take a look at the standard package you can see how Oracle uses subtypes extensively. For example all kinds of number subtypes:

  subtype FLOAT is NUMBER; -- NUMBER(126)
  subtype REAL is FLOAT; -- FLOAT(63)
  subtype INTEGER is NUMBER(38,0);
  subtype INT is INTEGER;
  subtype SMALLINT is NUMBER(38,0);
  subtype DECIMAL is NUMBER(38,0);
  subtype NUMERIC is DECIMAL;
  subtype DEC is DECIMAL;

As you can see there is one BASE type which in this case is NUMBER_BASE. FLOAT for instance is actually the same as NUMBER which is in nature a floating point. The subtype INTEGER is a restricted NUMBER because it doesn’t allow any decimal digits. As you can see, you can also create subtypes based on subtypes as long as they have been declared before you use them.

If you have a moment, check out the STANDARD package. Either view the code in the SYS.STANDARD package or cheeck out the files $ORA_HOME\RDBMS\ADMIN\stdspec.sql and $ORA_HOME\RDBMS\ADMIN\stdbody.sql. There is a lot you can learn, just from reading how Oracle solves things.