Expressions

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

Expressions can be classified into the following types based on complexity:

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

• A unary operator with one 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 produces a single value of that data type. The result of the expression has the same data type as 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 operator 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 space characters) in an operator.

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

You can use parentheses to control the evaluation order of operators 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.

The following table describes the return values of logical operators.

• 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 calculating a logical expression, PL uses short-circuit evaluation. That is, PL can stop evaluating the expression immediately after it determines the result.

As shown in the following example, short-circuit evaluation prevents a division-by-zero error in an OR expression. When the value of a is zero, the value of 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 calculation 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 the value of one expression is NULL, the comparison result is also NULL.

The comparison operators supported by PL are as follows:

IS [NOT] NULL operator

If the operand of the IS NULL operator is NULL, it returns the BOOLEAN value TRUE; otherwise, it returns FALSE. The IS NOT NULL operator has the opposite effect. 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 will return 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, otherwise FALSE.

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

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

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 the example, ZhangSan matches the pattern 'Zhang%S_n', so it returns TRUE; it does not match the pattern 'zhang%s_n', so it returns 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 the expression x is calculated only once.

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 x IN(set) returns TRUE only if x is equal to a member of set.

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 the WHERE clauses of control statements and DML statements.

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

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 value and compares it with multiple potential values.

Syntax:

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 literal 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 present), otherwise NULL.

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

Search CASE expressions

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

The syntax for a search 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 search CASE statement executes the first boolean_expression that returns TRUE and does not execute the remaining boolean_expression statements. If none of the boolean_expression statements return TRUE, it returns else_result (if present). If else_result does not exist, it returns NULL.

An example of a search CASE expression corresponding to a simple CASE expression is as follows:

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

You can use all SQL functions in PL expressions except for the following functions:

• 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 following functions: CUBE_TABLE, DATAOBJ_TO_PARTITION, LNNVL, NVL2, SYS_CONNECT_BY_PATH, SYS_TYPEID, and WIDTH_BUCKET

  Note

  Some of the preceding functions are not supported in the current version of OceanBase Database.

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 expressions that can appear in conditional compilation directives.

The following are static expressions:

• An expression that is NULL text.

• An expression that is a character, numeric, or Boolean literal.

• An expression that references a static constant.

• An expression that references a conditional compilation variable that starts with $$.

• An expression that uses a static expression-supported operator, if all of its operands are static and the operator does not raise an exception when evaluating these operands.

The following table lists the operators supported by static expressions.

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:

• The length of a character type (VARCHAR2, NCHAR, CHAR, NVARCHAR2, RAW, and ANSI)

• The range and precision of the NUMBER type and its subtypes (such as FLOAT)

• The precision of interval types (year, month, and second)

• The precision of time and timestamp

• The bounds of a VARRAY

• The range bounds in a type declaration

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

PLS_INTEGER static expressions

PLS_INTEGER static expressions are:

• PLS_INTEGER text

• PLS_INTEGER static constants

• NULL

BOOLEAN static expressions

BOOLEAN static expressions are:

• BOOLEAN literals (TRUE, FALSE, or NULL)

• BOOLEAN static constants

• 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

VARCHAR2 static expressions are:

• String literals with a maximum size of 32,767 bytes.

• Null values.

• 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 conditional compilation directive in a PL unit 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 need to be recompiled.

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 invalidation due to changes in the package header.