Oracle TREAT() Function

Oracle TREAT() is a built-in function used to change the declared type of an expression.

Oracle TREAT() Syntax

Here is the syntax for the Oracle TREAT() function:

TREAT( expr AS [ REF ] [ schema. ]type | JSON )

Parameters

expr

Required. The expression being operated on.

[ REF ] [ schema. ]type | JSON

Required. The type to be declared as.

Return Value

The Oracle TREAT() function returns.

Note that you must have the EXECUTE object privilege on type to use this function.

  • In expr AS JSON, expr is a SQL data type containing JSON, such as CLOB.

  • In expr AS type, expr and type must be user-defined object types, not including top-level collections.

  • type must be some supertype or subtype of the declared type of expr. If the most specific type of expr is type (or a subtype of type), then TREAT returns expr. If the most specific type of expr is not type (or a subtype of type), then TREAT returns NULL.

  • If the declared type of expr is a REF type, only REF can be specified.

  • If the declared type of expr is a REF to the source type of expr, then type must be some subtype or supertype of the source type. If the most specific type of DEREF(expr) is type (or a subtype of type), then TREAT returns expr. If the most specific type of DEREF(expr) is not type (or a subtype of type), then TREAT returns NULL.

Oracle TREAT() Example

The following statement retrieves the salary attribute for all people in the persons table, with instances of people who are not employees having a value of null.

SELECT
    name,
    TREAT(VALUE(p) AS employee_t).salary salary
FROM persons p;

输出：

NAME                          SALARY
------------------------- ----------
Bob
Joe                           100000
Tim                             1000

Conclusion

Oracle TREAT() is a built-in function used to change the declared type of an expression.