Overview

An expression calculates data values and consists of one or more values, operators, and SQL functions.

An expression usually uses the data type of its components. For example, 2*2 is a simple expression that evaluates to 4, and the data type is NUMBER, which is consistent with the data type of the component.

The following is a complex expression that uses functions and operators. The expression adds the current date by seven days, removes the time portion, and converts the result to the CHAR data type.

TO_CHAR(TRUNC(SYSDATE+7))

The following are general scenarios for using expressions:

• In the select list of a SELECT statement.

• In a WHERE or HAVING clause.

• In a CONNECT BY, START WITH, or ORDER BY clause.

• In a VALUES clause of an INSERT statement.

• In a SET clause of an UPDATE statement.

In the following example, in the SET clause of the UPDATE statement, you can replace the Smith string with an expression:

SET last_name = 'Smith';

In the SET clause, use the INITCAP(last_name) expression to replace the Smith string:

SET last_name = INITCAP(last_name);

In OceanBase Database, not all expressions can be used directly in each SQL statement. For more information, see SQL statements.

OceanBase Database supports the following types of expressions:

• Simple expressions

• Compound expression

• Conditional expressions

• Column expressions

• CURSOR expressions

• Datetime expressions

• Function expressions

• Interval expressions

• Scalar subquery expressions

• Type constructor expressions

• Expression lists