Arithmetic operators are used to perform operations such as negation, addition, subtraction, multiplication, and division on one or two operands. Some arithmetic operators can also be used to calculate datetime values and interval values.
The operands of arithmetic operators must be resolved to numeric data types or any data type that can be directly converted to a numeric data type by the database.
The data type of the result of a unary arithmetic operator is the same as the data type of the operand. For a binary arithmetic operator, OceanBase Database first determines the operand with the highest numeric precedence in the expression and then converts the other operands to the data type of that operand.
The following table describes the arithmetic operators supported in the current version of OceanBase Database.
| Operator | Description |
|---|---|
| +, - | Unary operators used to indicate positive and negative values. |
| +, - | Binary operators used to indicate addition and subtraction. |
| *, / | Binary operators used to indicate multiplication and division. |
Notice
Double negation or subtracting a negative value cannot be represented by two consecutive hyphens (--) in an arithmetic expression. The hyphens (--) are used to specify comments in SQL statements. You can separate consecutive hyphens with spaces or parentheses. For more information about comments in SQL statements, see Comments.
Example 1: SQL query that uses the + and - operators as unary operators.
SELECT * FROM order_items WHERE quantity = -1 ORDER BY order_id, line_item_id, product_id;
SELECT * FROM employees WHERE -salary < 0 ORDER BY employee_id;
Example 2: SQL query that uses the + and - operators as binary operators.
SELECT hire_date FROM employees WHERE SYSDATE - hire_date > 365 ORDER BY hire_date;
Example 3: SQL query that uses the * and / operators as binary operators.
UPDATE employees SET salary = salary * 1.1;
UPDATE employees SET salary = salary / 2;