Expressions

An expression consists of one or more values, operators, and SQL evaluation functions. An expression always returns a single value.

Expressions are categorized into the following types based on their complexity:

• A single constant or variable (for example, c).

• A unary operator with a single operand (for example, -c).

• A binary operator with two operands (for example, a + b).

An operand can be a variable, constant, literal, operator, function call, placeholder, or another expression. The data type of an operand determines the data type of the expression. Each time an expression is evaluated, it returns a single value of that data type. The data type of the result is the same as the data type of the expression.

Concatenation operator

The concatenation operator (||) is used to concatenate two strings.

The concatenation operator ignores null operands.

Here is an example:

obclient> DECLARE
  a VARCHAR2(10) := 'Ocean';
  b VARCHAR2(10) := 'Base';
BEGIN
  DBMS_OUTPUT.PUT_LINE (a || NULL || NULL|| b);
END;
/
Query OK, 0 rows affected 

OceanBase

Operator precedence

An operation can be a unary operator with a single operand or a binary operator with two operands. Expressions are evaluated based on operator precedence. Do not embed any other characters (including spaces) in operators.

The following table lists the operators from the highest to the lowest precedence. Operators with the same precedence do not have a specified evaluation order.

| Operator | Name |

| ** | Power operator | | +, - | Identity and negation operators | | *, / | Multiplication and division operators | | +, -, || | Addition, subtraction, and concatenation operators | | =, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN | Comparison operators | | NOT | Negation operator | | AND | Logical AND | | OR | Logical OR | You can use parentheses to control the order of operations in an expression. Here is an example:

obclient> DECLARE
  x INTEGER := 1+2*3+2**2;
  y INTEGER := (1+2*3+2)**2;
  z INTEGER := ((1+2)*3+2)**2;
BEGIN
  DBMS_OUTPUT.PUT_LINE('x = ' || x);
  DBMS_OUTPUT.PUT_LINE('y = ' || y);
  DBMS_OUTPUT.PUT_LINE('z = ' || z);
END;
/
Query OK, 0 rows affected 

x = 11
y = 81
z = 121

Logical operators

The logical operators AND, OR, and NOT follow three-state logic. AND and OR are binary operators; NOT is a unary operator.

The PL operators of OceanBase Database include general operators, comparison operators, and logical operators. The following table describes the logical operators and their meanings.

| Operator | Meaning |

| IS NULL | Checks for a null value |

| AND | Logical AND | | OR | Logical OR | | NOT | Negation. For example, IS NOT NULL and NOT IN | The return values of logical operators are as follows:

• The AND operator returns TRUE only when both operands are TRUE.

• The OR operator returns TRUE if either operand is TRUE.

• The NOT operator returns the opposite value of its operand, unless the operand is NULL. NOT NULL returns NULL because NULL is an unknown value.

Short-circuit evaluation

When evaluating logical expressions, PL uses short-circuit evaluation. That is, PL can stop evaluating an expression immediately after determining the result.

For example, short-circuit evaluation prevents a division-by-zero error in an OR expression. If the value of a is 0, the left operand is TRUE, and PL does not evaluate the right operand. If PL evaluates both operands before applying the OR operator, the right operand will cause a division-by-zero error.

obclient> DELIMITER $$  --Since the expression contains a slash (/), redefine the delimiter.

obclient> DECLARE

a  INTEGER := 0;

b  INTEGER := 20;
BEGIN
IF (a = 0) OR ((b / a) < 5) THEN  --After the first expression is evaluated, the evaluation stops, and no division-by-zero error occurs.
  DBMS_OUTPUT.PUT_LINE('The output is zero.');
  END IF;
END;
  $$
    Query OK, 0 rows affected
  The output is zero.

Comparison operators

Comparison operators compare one expression with another. The result is always TRUE, FALSE, or NULL.

If one of the expressions is NULL, the result is also NULL. The following table lists the comparison operators supported by PL.

| = | Equal |

| <> , != , ~= , ^= | Not equal |

| < | Less than |

| > | Greater than | | <= | Less than or equal to | | >= | Greater than or equal to | | BETWEEN AND | Between two values | | IN | In a set | | IS <NOT> NULL | Check for NULL values | | LIKE | Pattern matching | IS [NOT] NULL operator The IS NULL operator returns the BOOLEAN value TRUE if its operand is NULL; otherwise, it returns FALSE. The IS NOT NULL operator has the opposite behavior. Comparisons involving NULL values always result in NULL. Relational operators Relational operators include arithmetic comparisons, BOOLEAN comparisons, character comparisons, and date comparisons.

• Arithmetic comparisons

A number is greater than another if it represents a larger quantity. Real numbers are stored as approximations, so it is recommended to compare them for equality or inequality.

• BOOLEAN comparisons

By definition, TRUE is greater than FALSE. Any comparison with NULL returns NULL.

• Character comparisons

By default, a character is greater than another if its binary value is larger.

• Date comparisons

A more recent date is greater than another date.

LIKE operator

The LIKE operator compares a character, string, or CLOB value with a pattern. It returns TRUE if the value matches the pattern, and FALSE otherwise.

The pattern can include two wildcard characters: the underscore (_), which matches exactly one character, and the percent sign (%), which matches zero or more characters.

To search for a literal percent sign or underscore, define an escape character and place it before the wildcard character.

Here is an example:

obclient> DECLARE
  PROCEDURE compare (
    name     VARCHAR2,
    customer VARCHAR2
  ) IS
  BEGIN
    IF name LIKE customer THEN
      DBMS_OUTPUT.PUT_LINE ('TRUE');
    ELSE
      DBMS_OUTPUT.PUT_LINE ('FALSE');
    END IF;
  END;
BEGIN
  compare('ZhangSan', 'Zhang%S_n');
  compare('ZhangSan', 'zhang%s_n');
END;
/
Query OK, 0 rows affected

TRUE
FALSE

In this example, ZhangSan matches the pattern 'Zhang%S_n', so the result is TRUE. It does not match the pattern 'zhang%s_n', so the result is FALSE.

BETWEEN operator

The BETWEEN operator tests whether a value falls within a specified range.

The expression c BETWEEN a AND b has the same value as the expression (c> = a)AND(c<= b). The value of x is computed only once.

Here is an example:

obclient> CREATE OR REPLACE PROCEDURE output_bool (
  bool_name   VARCHAR2,
  bool_value  BOOLEAN
) AUTHID DEFINER IS
BEGIN
  IF bool_value IS NULL THEN
    DBMS_OUTPUT.PUT_LINE (bool_name || ' = NULL');
  ELSIF bool_value = TRUE THEN
    DBMS_OUTPUT.PUT_LINE (bool_name || ' = TRUE');
  ELSE
    DBMS_OUTPUT.PUT_LINE (bool_name || ' = FALSE');
  END IF;
END;
/
obclient>BEGIN
  output_bool ('8 BETWEEN 7 AND 9', 8 BETWEEN 7 AND 9);
  output_bool ('8 BETWEEN 7 AND 8', 8 BETWEEN 7 AND 8);
  output_bool ('8 BETWEEN 5 AND 6', 8 BETWEEN 5 AND 6);
END;
/
Query OK, 0 rows affected 

8 BETWEEN 7 AND 9 = TRUE
8 BETWEEN 7 AND 8 = TRUE
8 BETWEEN 5 AND 6 = FALSE

IN operator

The IN operator tests for membership in a set. The expression x IN(set) returns TRUE only if x is equal to a member of set.

Here is an example:

DECLARE
  familyname VARCHAR2(10) := 'Zhang';
BEGIN
  output_bool (
    'familyname IN (''Zhao'', ''Qian'', ''Sun'', ''Li'')',
    familyname IN ('Zhao', 'Qian', 'Sun', 'Li')
  );
  output_bool (
    'familyname IN (''He'', ''Lv'', ''Shi'', ''Zhang'')',
    familyname IN ('He', 'Lv', 'Shi', 'Zhang')
  );
END;
/
Query OK, 0 rows affected 

familyname IN ('Zhao', 'Qian', 'Sun', 'Li') = FALSE
familyname IN ('He', 'Lv', 'Shi', 'Zhang') = TRUE

BOOLEAN expressions

A BOOLEAN expression is an expression that returns a BOOLEAN value of TRUE, FALSE, or NULL.

The simplest BOOLEAN expressions are BOOLEAN literals, constants, or variables.

Typically, BOOLEAN expressions are used as conditions in control statements and DML statements in the WHERE clause.

You can use a BOOLEAN variable as a condition without comparing it to TRUE or FALSE values.

Here is an example:

DECLARE

a BOOLEAN := FALSE;

BEGIN

-- The following three WHILE loops are equivalent

WHILE a = FALSE

LOOP
a := TRUE;
  END LOOP;
WHILE NOT (a = TRUE)
  LOOP

  a := TRUE;
    END LOOP;
      WHILE NOT a
    LOOP

  a := TRUE;
    END LOOP;
      END;
    /

## CASE expressions
  **Simple CASE expressions**
The simplest `CASE` statement evaluates a single expression and compares it with multiple potential values.

The syntax is as follows:

CASE selector
  WHEN selector_value_1 THEN result_1 
  WHEN selector_value_2 THEN result_2 ...
  WHEN selector_value_n THEN result_n 
  [ ELSE
        else_statements ] 
  END 
CASE;

selector is an expression (usually a variable). Each selector_value and each result can be a text or an expression. At least one result cannot be NULL.

A simple CASE expression returns the first result that matches selector_value with selector. The remaining expressions are not evaluated. If no selector_value matches selector, the CASE expression returns else_result (if it exists), otherwise it returns NULL. Here is an example:

obclient> DECLARE
    grade CHAR(1);
BEGIN
   grade := 'B';
    CASE grade
      WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('PASS');
      WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('SUSPEND');
      WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('FAIL');
      ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
      END CASE;
END;
 /
Query OK, 0 rows affected 

SUSPEND

Searched CASE expressions

In a searched CASE statement, there is no expression to evaluate after CASE, but instead, different boolean expressions follow WHEN. A searched CASE evaluates multiple boolean expressions and selects the first one that returns TRUE.

The syntax for a searched CASE expression is as follows:

CASE
    WHEN boolean_expression_1 THEN result_1 
    WHEN boolean_expression_2 THEN result_2 ...
    WHEN boolean_expression_n THEN result_n 
  [ ELSE
   else_result ] 
END CASE;

A searched CASE declaration executes the first boolean_expression that returns TRUE and does not execute any other boolean_expression. If none of the boolean_expression values are TRUE, it returns else_result (if it exists). If else_result does not exist, it returns NULL.

Here is an example of a searched CASE expression corresponding to a simple CASE expression:

obclient> DECLARE
    grade CHAR(1);
BEGIN
    grade := 'D';
      CASE
       WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('PASS');
       WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('SUSPEND');
       WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('FAIL');
       ELSE DBMS_OUTPUT.PUT_LINE('UNEXPECTED');
     END CASE;
END;
/
Query OK, 0 rows affected 

UNEXPECTED

SQL functions in PL expressions

In PL expressions, you can use all SQL functions except the following:

• Aggregate functions such as AVG and COUNT

• Analytic functions such as LAG and RATIO_TO_REPORT

• Data mining functions such as CLUSTER_ID and FEATURE_VALUE

• Encoding and decoding functions such as DECODE and DUMP

• Model functions such as ITERATION_NUMBER and PREVIOUS

• Object reference functions such as REF and VALUE

• XML functions such as APPENDCHILDXML and EXISTSNODE

• The BIN_TO_NUM conversion function

• The JSON SQL operators JSON_ARRAYAGG, JSON_OBJAGG, JSON_TABLE, and JSON_TEXTCONTAINS

• The SQL collation operators and functions COLLATE, COLLATION, NLS_COLLATION_ID, and NLS_COLLATION_NAME

• The other functions CUBE_TABLE, DATAOBJ_TO_PARTITION, LNNVL, NVL2, SYS_CONNECT_BY_PATH, SYS_TYPEID, and WIDTH_BUCKET

Static expressions

A static expression is an expression whose value can be determined at compile time. In other words, it does not contain character comparisons, variables, or function calls. Static expressions are the only type of expression that can appear in conditional compilation directives.

A static expression is defined as follows:

• An expression that is a NULL literal is static.

  • An expression that is a character, numeric, or Boolean literal is static.
    • An expression that references a static constant is static.
    • An expression that references a conditional compilation variable that starts with $$ is static.
  • An expression that is a static expression supported operator, if all of its operands are static and the operator does not raise an exception when evaluating these operands, is static.

The following table lists the operators supported by static expressions. | Operator | Category |

| () | Expression separator |

| ** | Exponentiation |

| *, /,+, - | Arithmetic operators: multiplication, division, addition, or subtraction |

| =, !=, <, <=, >=, > IS [NOT] NULL | Comparison operators |

| NOT | Logical operators |

| [NOT] LIKE, [NOT] LIKE2, [NOT] LIKE4, [NOT] LIKEC | Pattern matching operators |

| XOR | Binary operators |

The following functions are supported by static expressions.

• ABS

• ACOS

• ASCII

• ASCIISTR

• ASIN

• ATAN

• ATAN2

• BITAND

• CEIL

• CHR

• COMPOSE

• CONVERT

• COS

• COSH

• DECOMPOSE

• EXP

• FLOOR

• HEXTORAW

• INSTR

• INSTRB

• INSTRC

• INSTR2

• INSTR4

• IS [NOT] INFINITE

• IS [NOT] NAN

• LENGTH

• LENGTH2

• LENGTH4

• LENGTHB

• LENGTHC

• LN

• LOG

• LOWER

• LPAD

• LTRIM

• MOD

• NVL

• POWER

• RAWTOHEX

• REM

• REMAINDER

• REPLACE

• ROUND

• RPAD

• RTRIM

• SIGN

• SIN

• SINH

• SQRT

• SUBSTR

• SUBSTR2

• SUBSTR4

• SUBSTRB

• SUBSTRC

• TAN

• TANH

• TO_BINARY_DOUBLE

• TO_BINARY_FLOAT

• TO_CHAR

• TO_NUMBER

• TRIM

• TRUNC

• UPPER

Static expressions can be used in the following subtype declarations:

• Length of string types (VARCHAR2, NCHAR, CHAR, NVARCHAR2, RAW, and ANSI)

• Range and precision of NUMBER types and subtypes (such as FLOAT)

• Precision of interval types (year, month, and second)

• Precision of time and timestamp types

• Bounds of VARRAY

• Range bounds in type declarations

In each case, the result type of the static expression must match the subtype of the declared item and must be within the correct scope of the context.

PLS_INTEGER static expressions

A PLS_INTEGER static expression is:

• A PLS_INTEGER literal

• A PLS_INTEGER static constant

• NULL

BOOLEAN static expressions

A BOOLEAN static expression is:

• A BOOLEAN literal (TRUE, FALSE, or NULL)

• A BOOLEAN static constant

• The following expressions, where x and y are PLS_INTEGER static expressions:

• x > y

• x < y

• x >= y

• x <= y

• x = y

• x <> y

• The following expressions, where x and y are Boolean expressions:

• NOT y

• x AND y

  • x OR y
  • x > y
  • x >= y
  • x = y
  • x <= y
  • x <> y

• The following expressions, where x is a static expression:

  • x IS NULL
  • x IS NOT NULL VARCHAR2 static expressions A VARCHAR2 static expression is:
  • A string literal of up to 32,767 bytes.
  • A null value.
  • TO_CHAR(x), where x is a PLS_INTEGER static expression.
  • TO_CHAR(x, f, n), where x is a PLS_INTEGER static expression and f and n are VARCHAR2 static expressions.

• x || y, where x and y are VARCHAR2 or PLS_INTEGER static expressions.

  Static constants Static constants are declared in a package using the following syntax:

constant_name CONSTANT data_type := static_expression;

In this syntax, the type of static_expression must match the data_type (BOOLEAN or PLS_INTEGER).

Even in the body of the package_name package, static constants must always be referenced as package_name.constant_name.

If a PL unit's conditional compilation directive uses constant_name in a BOOLEAN expression, the PL unit depends on the package_name package. If the package header is changed, the dependent PL unit may become invalid and require recompilation.

If you use a package with static constants to control conditional compilation in multiple PL units, we recommend that you create only the package header and use it exclusively for controlling conditional compilation to avoid invalidating the package due to changes to the package header.