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 categorized into the following types by complexity:

• Single constant or variable, for example, c

• Unary operator with a single operand, for example, -c

• Binary operator with two operands, for example, a + b

Objects such as variables, constants, literals, operators, function calls, placeholders, and other expressions can be used as operands. The data type of an operand decides the data type of the expression. Each time when you evaluate an expression, a single value of the data type will be produced. The data type of the result indicates the data type of the expression.

Concatenation operator

You can use the concatenation operator (||) to connect two strings.

The concatenation operator ignores the null operand.

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 Database

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 the operator precedence.

Do not embed any other characters between operators, including space characters.

The following table lists operators in descending order of precedence. For operators of the same precedence, the evaluation order is not specified.

You can use parentheses to control the operation sequence of operators in an expression. 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

Logical operators AND, OR, and NOT follow the three-state logic. AND and OR are binary operators, and NOT is a unary operator.

The PL operators of OceanBase Database include common operators, comparison operators, and logical operators.

The following table describes the logical operators.

Logical operators return values in the following logic:

• When and only when two operands are TRUE, AND returns TRUE.

• If any operand is TRUE, OR returns TRUE.

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

Short-circuit evaluation

PL uses short-circuit evaluation to evaluate a logical expression. In this way, PL can stop evaluating the expression immediately after the result is determined.

Short-circuit evaluation can avoid division by zero in OR expressions. When the value of a is 0 and the value of the left operand is TRUE, PL does not evaluate the right operand. If PL has evaluated two operands before it applies the OR operator, the right operand results in the division-by-zero error.

obclient> DELIMITER $$ -- The delimiter is redefined, because the expression contains a slash (/).
obclient> DECLARE
  a  INTEGER := 0;
  b  INTEGER := 20;
BEGIN
  IF (a = 0) OR ((b / a) < 5) THEN  -- If the evaluation stops after the first expression is executed, the division-by-zero error will not occur.
    DBMS_OUTPUT.PUT_LINE('The output is zero.');
  END IF;
END;
$$
Query OK, 0 rows affected

The output is zero.

Comparison operators

A comparison operator compares an expression with another expression. The evaluation result is always TRUE, FALSE, or NULL.

If the value of an expression is NULL, the evaluation result is NULL as well.

The following table lists comparison operators supported by PL.

IS [NOT] NULL operator

If the operand of the IS NULL operator is NULL, the BOOLEAN value TRUE is returned. Otherwise, FALSE is returned. The IS NOT NULL operator operates in the opposite way. The results of comparisons involving the NULL value are always NULL.

Relational operators

Relational operators include arithmetic comparisons, BOOLEAN comparisons, character comparisons, and date comparisons.

• Arithmetic comparisons

  A number indicates a larger quantity is greater. Real numbers are stored as approximate values. Therefore, we recommend that you make equality or inequality comparisons.

• BOOLEAN comparisons

  TRUE is greater than FALSE by definition. The results of comparisons with NULL are always NULL.

• Character comparisons

  By default, a character with a larger binary value is greater.

• Date comparisons

  A later date is greater than an earlier date.

LIKE operator

You can use the LIKE operator to compare the value of a character, string, or CLOB with a pattern. If the value match the pattern, TRUE is returned. Otherwise, FALSE is returned.

This pattern can include two wildcard characters: an underscore (_) and a percent sign (%). The underscore (_) matches one character. The percent sign (%) matches zero or multiple characters.

To search for an underscore (_) or a percent sign (%), define an escape character and place it before the underscore (_) or percent sign (%).

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 TRUE is returned. It does not match the pattern 'zhang%s_n', so FALSE is returned.

BETWEEN operator

You can use the BETWEEN operator to determine whether a value is within a specified range.

The value of the c BETWEEN a AND b expression is the same as that of the (c>= a)AND(c<= b) expression. The value of the x expression is evaluated 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

You can use the IN operator to determine whether a value belongs to a set. Only when x is equal to a set member, x IN(set) returns TRUE.

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 returns an expression whose BOOLEAN value is TRUE, FALSE, or NULL.

The simplest BOOLEAN expression is a BOOLEAN literal, constant, or variable.

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

You can use BOOLEAN variables as conditions without comparing them with TRUE or FALSE.

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 the value of a single expression and compares the value 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. Generally, it is a variable. Each selector_value and each result can be literals or expressions. At least one result cannot be NULL.

A simple CASE expression returns the first result where selector_value matches selector. Remaining expressions are no longer evaluated. If no selector_value matches selector, the CASE expression returns else_result, if any. Otherwise, the expression 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

Searched CASE expressions

In a searched CASE statement, CASE is not followed by any expression that needs to be evaluated, but WHEN is followed by different BOOLEAN expressions. The searched CASE statement evaluates the values of several BOOLEAN expressions and selects the first branch whose result is TRUE.

The searched CASE expression has the following syntax:

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;

The searched CASE statement executes the first boolean_expression whose result is TRUE. Remaining boolean_expressions are not executed. If no boolean_expression whose result is TRUE, else_result, if any, is returned. If else_result does not exist, NULL is returned.

The following example shows a searched CASE expression that corresponds 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, all SQL functions can be used, except 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 referencing functions such as REF and VALUE

• XML functions such as APPENDCHILDXML and EXISTSNODE

• Conversion function BIN_TO_NUM

• JSON SQL operators such as JSON_ARRAYAGG, JSON_OBJAGG, JSON_TABLE, and JSON_TEXTCONTAINS

• SQL sorting operators and functions such as COLLATE, COLLATION, NLS_COLLATION_ID, and NLS_COLLATION_NAME

• Other functions such as 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 with a determinate value during compilation. Therefore, it does not include character comparisons, variables, or function calls. Only static expressions can be applied to conditional compilation directives.

An expression can be considered a static expression when it meets the following requirements:

• The literal value of the expression is NULL.

• The expression is a character, numeric, or BOOLEAN literal.

• The expression references a static constant.

• The expression references a conditional compilation variables that start with $$.

• The expression is an operator supported by a static expression, all operands in the expression are static, and no exception is caused when the operator evaluates the operands.

The following table lists the operators supported by static expressions.

Static expressions support the following functions:

• 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 to declare the following subtypes:

• Lengths of string types such as VARCHAR2, NCHAR, CHAR, NVARCHAR2, RAW, and ANSI

• Range and precision of the NUMBER type and subtypes, for example, FLOAT

• Precision of the interval type, such as year, month, and second

• Time and timestamp precision

• VARRAY bounds

• Range bounds in type declarations

Under any circumstances, the resulting type of a static expression must be the same as the subtype of the declared item and must be within the correct range of the context.

PLS_INTEGER static expressions

PLS_INTEGER static expressions include:

• PLS_INTEGER literals

• PLS_INTEGER static constants

• NULL

BOOLEAN static expressions

BOOLEAN static expressions include:

• BOOLEAN literals. The value can be TRUE, FALSE, or NULL.

• BOOLEAN static constants

• PLS_INTEGER static expressions represented by x and y in the following expressions:

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

• BOOLEAN expressions represented by x and y in the following expressions:

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

• Static expressions represented by x in the following expressions:

  • x IS NULL
  • x IS NOT NULL

VARCHAR2 static expressions

VARCHAR2 static expressions include:

• 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

A static constant is declared in a package by using the following syntax:

constant_name CONSTANT data_type := static_expression;

The type of static_expression must be the same as the value of data_type, which can be BOOLEAN or PLS_INTEGER.

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

If the conditional compile directive in a PL unit uses the constant_name constant in a BOOLEAN expression, the PL unit depends on the package_name package. If the program specification is modified, the dependent PL unit may become invalid and needs to be recompiled.

If you use a package that contains static constants to control conditional compilation in multiple PL units, we recommend that you create only the package specification and use it exclusively for conditional compilation control. This can prevent the program package from becoming invalid due to modifications to the package specification.