Expressions

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

Expressions are classified 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 the 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.

| IS NULL | Determines whether a value is NULL. | | AND | Logical AND. | | OR | Logical OR. | | NOT | Negation. For example, IS NOT NULL and NOT IN. |

The evaluation logic of logical operators is as follows:

• AND returns TRUE only when both operands are TRUE.

• OR returns TRUE if either operand is TRUE.

• NOT returns a value opposite to its operand, unless the operand is NULL. NOT NULL returns NULL, because NULL is an indeterminate value.

Short-circuit evaluation

When calculating a logical expression, PL uses short-circuit evaluation, which means that PL can stop evaluating the expression immediately after determining the result.

As shown in the following example, short-circuit evaluation can prevent 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 either expression is NULL, the 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 its operand is NULL; otherwise, it returns FALSE. The IS NOT NULL operator returns the opposite result. 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. Comparisons with NULL 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, and FALSE otherwise.

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 the 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 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 evaluates to the same result 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 tests for membership in a set. The expression 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, and variables.

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

You can use a BOOLEAN variable as a condition without comparing it to 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 text or an expression. At least one result must not 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 it returns 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 are specified after WHEN. A search CASE evaluates multiple boolean expressions and selects the first one that evaluates to 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 evaluates the first boolean_expression that returns TRUE and does not evaluate 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

In PL expressions, you can use all SQL functions 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

Static expressions

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

A static expression is defined as follows:

• If the expression is a NULL literal, then the expression is static.

• If the expression is a character, numeric, or Boolean literal, then the expression is static.

• If the expression is a reference to a static constant, then the expression is static.

• If the expression is a reference to a conditional compilation variable that starts with $$, then the expression is static.

• If the expression is an operator supported by static expressions, and all of its operands are static, and the operator does not raise an exception when evaluating these operands, then the expression is static.

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 types

• The bounds of a VARRAY

• The bounds of a range in a type declaration

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

PLS_INTEGER static expressions

PLS_INTEGER static expressions are:

• PLS_INTEGER literals

• 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, you must always reference static constants as package_name.constant_name.

If a PL unit's conditional compilation directive uses constant_name in a BOOLEAN expression, then the PL unit depends on the package_name package. If you change the package header, the dependent PL unit may become invalid and will 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 solely for controlling conditional compilation. This way, you avoid invalidating the package when you change the package header.