Arithmetic operators are used to perform negation, addition, subtraction, multiplication, and division operations on one or two parameters. Some of these arithmetic operators can also be used to calculate datetime and interval values.
The parameters to arithmetic operators must resolve to numeric data types or to any data type that can be implicitly converted to a numeric data type by a database.
Unary arithmetic operators return the same data type as that of the parameter. For binary arithmetic operators, OceanBase Database first determines the parameter with the highest numeric precedence in the expression, and then converts other parameters to the data type of that parameter.
The following table lists the arithmetic operators that are supported by the current version of OceanBase Database.
| Operator | Description |
|---|---|
| + and - | When used to represent the positive and negative expressions, they are unary operators. |
| + and - | When used to represent the addition and subtraction operations, they are binary operators. |
| * and / | They are binary operators that are used for multiplication and division. |
Notice
Do not use two consecutive minus signs (--) in an arithmetic expression to indicate double negation or subtraction of a negative value, because the characters "--" are used to indicate a comment in SQL statements. You can separate consecutive minus signs with a space or parentheses. For more information about comments in SQL statements, see Comments.
Example 1: SQL query that uses the plus (+) and minus (-) signs to respectively represent positive and negative expressions
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 plus (+) and minus (-) signs respectively as the addition and subtraction operators
SELECT hire_date FROM employees WHERE SYSDATE - hire_date > 365 ORDER BY hire_date;
Example 3: SQL query that uses the asterisk (*) and slash signs (/) respectively as the multiplication and division operators.
UPDATE employees SET salary = salary * 1.1;
UPDATE employees SET salary = salary / 2;