Overview

An expression is a combination of one or more values, operators, and SQL functions. It is used to compute a value.

An expression usually has the data type of its components. For example, 2*2 is a simple expression. Its result is 4, and its data type is NUMBER, which is consistent with the data types of its components.

The following is a complex expression that uses functions and operators. This expression adds 7 days to the current date, removes the time part, and then converts the result to the CHAR data type.

TO_CHAR(TRUNC(SYSDATE+7))

The following are typical scenarios in which expressions are used:

• In the column list of a SELECT statement.

• In the WHERE and HAVING clauses.

• In the CONNECT BY, START WITH, and ORDER BY clauses.

• In the VALUES clause of an INSERT statement.

• In the SET clause of an UPDATE statement.

For example, in the SET clause of the following UPDATE statement, you can use an expression to replace the string Smith:

SET last_name = 'Smith';

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

SET last_name = INITCAP(last_name);

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

OceanBase Database supports the following expression types:

• Simple expressions

• Compound expressions

• Conditional expressions

• Column expressions

• CURSOR expressions

• Date and time expressions

• Function expressions

• Interval expressions

• Scalar subquery expressions

• Type constructor expressions

• Expression lists