Expressions

An expression is a combination 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 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, a single value of this data type is generated. The data type of the result is 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 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 the 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, and 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 logical operators return the following values:

• The AND operator returns TRUE only if 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.

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 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 the value of one expression is NULL, the comparison result is also NULL.

The following table lists the comparison operators supported by PL.

IS [NOT] NULL operator

The IS NULL operator returns the BOOLEAN value TRUE if the operand is NULL; otherwise, it returns FALSE. The IS NOT NULL operator works the opposite way. Comparisons involving NULL values always return 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 wildcard characters: the underscore (_) and the percent sign (%). The underscore matches exactly one character. 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 percent sign or underscore.

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 is used to test 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 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 is used to test membership in a set. The x IN(set) returns TRUE only if x is equal to a member of the 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 control statements and DML statements in the WHERE clause.

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 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 specified), 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 different BOOLEAN expressions are specified after WHEN. A search CASE evaluates multiple BOOLEAN expressions and selects the first one that returns TRUE.

Syntax for a search CASE expression:

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 other boolean_expression statements. If none of the boolean_expression statements return TRUE, it returns else_result (if specified). If else_result is not specified, 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:

• 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 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 uses a static expression-supported operator, provided that 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 string 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 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

• An expression in which x and y are PLS_INTEGER static expressions:

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

• An expression in which x and y are Boolean expressions:

  • NOT y
  • x AND y
  • x OR y
  • x > y
  • x >= y
  • x = y
  • x <= y
  • x <> y

• An expression in which 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, you must always reference static constants 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 changes, the dependent PL units 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 specifically for controlling conditional compilation to avoid invalidation of the package due to changes in the package header.