The Oracle TREAT function allows you to change the declared type of the expression used in TREAT. This function comes in handy when you have a subtype that is more specific to your data and you want to convert the parent type to the more specific one. The optional REF keyword can only be used if the type you are referencing is a REF type.
The TREAT function works by doing a runtime check to confirm that an expression can be operated on as if it were of a different type in the hierarchy, normally a subtype of the declared type of the expression. In other words, the function attempts to treat a supertype instance as a subtype instance, for example, to treat a person as a student. If the person is a student, then the person is returned as a student, with the additional attributes and methods that a student may have. If the person is not a student, TREAT returns NULL in SQL.
The two main uses of TREAT are:
- To set a particular supertype value into a subtype. For example, to modify the type of an expression so that the expression can be assigned to a variable of a more specialized type in the hierarchy.
- To access general attributes or methods of a subtype of the declared type of a row or column.
This example retrieves the salary attribute of all people in the persons table, the value being NULL for instances of people that are not employees:
SELECT name, TREAT(VALUE(p) AS employee_t).salary salary
FROM persons p;