Expressions¶

The types of expressions in PL/CSQL are summarized by the following syntax:

<expression> ::=
      <literal>                                 # literal
    | <identifier>                              # identifier
    | SQL%ROWCOUNT                              # result size of Static SQL
    | <cursor_expression> <cursor_attribute>    # cursor attribute
    | <expression> <binary_op> <expression>     # binary operation
    | <unary_op> <expression>                   # unary operation
    | ( <expression> )                          # parentheses
    | <identifier>.<identifier>                 # record field reference
    | <identifier> <function_argument>          # function call
    | <case_expression>                         # CASE expression
    | SQLCODE                                   # exception code
    | SQLERRM                                   # exception message
    | <expression> IS [ NOT ] NULL              # IS NULL expression
    | <expression> [ NOT ] BETWEEN <expression> AND <expression>        # BETWEEN expression
    | <expression> [ NOT ] IN ( <expression> [ , <expression>, ... ] )  # IN expression
    | <expression> [ NOT ] LIKE <expression> [ ESCAPE <expression> ]    # LIKE expression

<literal> ::=
      DATE <quoted_string>
    | TIME <quoted_string>
    | DATETIME <quoted_string>
    | TIMESTAMP <quoted_string>
    | <numeric>
    | <quoted_string>
    | NULL
    | TRUE
    | FALSE

<numeric> ::= UNSIGNED_INTEGER | FLOATING_POINT_NUM

<cursor_attribute> ::= { %ISOPEN | %FOUND | %NOTFOUND | %ROWCOUNT }

<binary_op> ::=
      AND | XOR | OR
    | = | <=> | != | <> | <= | >= | < | >
    | * | / | + | -
    | >> | << | & | ^ | '|'
    | ||

<unary_op> ::= + | - | NOT | ~

<case_expression> ::=
      CASE <expression> <case_expression_when_part>... [ ELSE <expression> ] END
    | CASE <case_expression_when_part>... [ ELSE <expression> ] END

<case_expression_when_part> ::= WHEN <expression> THEN <expression>

Literals¶

Literals include date/time, numeric, string, NULL, TRUE, and FALSE values. Except that bit strings and collections are not supported, literal-related rules are the same as described in SQL Literals.

Identifiers¶

In PL/CSQL statements other than Static/Dynamic SQL, the following kinds of identifiers can be used:

Variables, constants, cursors, exceptions, and local procedures/functions declared in the declaration section
Parameters of procedures/functions
Implicitly declared iterators of FOR loops of type integer or record

Using an identifier without explicit or implicit declaration results in a compile-time error.

Result Count for Static SQL¶

SQL%ROWCOUNT is an expression that represents the result count immediately after executing a Static SQL statement.

For SELECT statements not associated with cursors, the INTO clause must be used, and the result must contain exactly one row. Therefore, when the SELECT executes successfully, SQL%ROWCOUNT will be 1. If the result has 0 or more than 1 row and causes a runtime error, the value of SQL%ROWCOUNT is undefined.
For INSERT, UPDATE, DELETE, MERGE, REPLACE, and TRUNCATE statements, it returns the number of affected rows.
For COMMIT and ROLLBACK, it returns 0.

Cursor Attributes¶

By appending %ISOPEN, %FOUND, %NOTFOUND, or %ROWCOUNT to a cursor_expression that evaluates to a cursor or SYS_REFCURSOR variable, you can check one of four cursor attributes.

%ISOPEN: Whether the cursor is open (BOOLEAN)
%FOUND: NULL before the first FETCH. Otherwise, whether the last FETCH returned 1 row (BOOLEAN). Raises INVALID_CURSOR Exception if the cursor is not open.
%NOTFOUND: NULL before the first FETCH. Otherwise, whether the last FETCH returned 0 rows (BOOLEAN). Raises INVALID_CURSOR Exception if the cursor is not open.
%ROWCOUNT: 0 before the first FETCH. Otherwise, the number of rows fetched so far (BIGINT). Raises INVALID_CURSOR Exception if the cursor is not open.

In the example below, the internal function iterate_cursor() iterates over records using cursor attributes and returns the total row count. If the passed cursor is not open (%ISOPEN is False), the function returns -1. Whether there are more records is determined using the %NOTFOUND attribute after FETCH. The %ROWCOUNT attribute increments with each FETCH and reflects the total number of rows fetched after the loop ends.

CREATE OR REPLACE PROCEDURE cursor_attributes AS
    ...

    FUNCTION iterate_cursor(rc SYS_REFCURSOR) RETURN INT
    AS
        v VARCHAR;
    BEGIN
        IF rc%ISOPEN THEN
            LOOP
                FETCH rc INTO v;
                EXIT WHEN rc%NOTFOUND;

                -- do something with v
                ...

            END LOOP;

            RETURN rc%ROWCOUNT;     -- number of records
        ELSE
            RETURN -1;              -- error
        END IF;
    END;
begin
    ...

end;

If the cursor_expression evaluates to NULL, an INVALID_CURSOR Exception is raised.

Binary Ops, Unary Ops, Parentheses¶

PL/CSQL uses the following precedence for operators:

Operator	Description
+, -, ~	Unary sign, bitwise NOT
*, /, DIV, MOD	Multiplication, division, integer division, modulus
+, -	Addition, subtraction
\|\|	String concatenation
<<, >>	Bitwise shift
&	Bitwise AND
^	Bitwise XOR
\|	Bitwise OR
IS NULL	NULL test
LIKE	String pattern test
BETWEEN	Range test
IN	Inclusion test
=, <=>, <, >, <=, >=, <>, !=	Comparison
NOT	Logical NOT
AND	Logical AND
XOR	Logical XOR
OR	Logical OR

In Non-static/dynamic SQL statements, the % operator cannot be used for modulo, so use MOD instead.
In Non-static/dynamic SQL statements, logical operators &&, ||, and ! cannot be used, so use AND, OR, and NOT instead.
In Non-static/dynamic SQL statements, string comparisons follow UTF8 encoding regardless of DB settings, using lexicographical order of Unicode values. In Static/Dynamic SQL statements, encoding and comparison rules follow the database and table settings.

Parentheses can be used to explicitly specify evaluation precedence.

Record Field Reference¶

PL/CSQL supports record variables in the following two cases:

Implicitly declared record variables used for iterating over SELECT results in a FOR loop
Record variables declared using %ROWTYPE

To reference a field in a record variable, append a dot and the field name to the record variable.

CREATE PROCEDURE athlete_history(p_name VARCHAR)
AS
    CURSOR my_cursor IS
    SELECT host_year, score
    FROM history
    WHERE athlete = p_name;
BEGIN
    FOR r IN my_cursor LOOP     -- r: implicitly declared
        DBMS_OUTPUT.put_line('host_year: ' || r.host_year || ' score: ' || r.score);    -- r.<column-name>
    END LOOP;
END;

Function Call¶

In a function call expression, the number and types of arguments must match the function’s declaration. Arguments passed to OUT parameters of the function must be assignable variables or other OUT parameters, as their values will be changed by the function call.

CREATE OR REPLACE FUNCTION callee(o OUT INT) RETURN INT
AS
BEGIN
    ...
END;

CREATE OR REPLACE PROCEDURE proc(i INT, o OUT INT)
AS
    v INT;
    c CONSTANT INT := 0;
BEGIN
    ... callee(i) ...   -- Error: IN argument
    ... callee(o) ...   -- OK: OUT argument
    ... callee(v) ...   -- OK: variable
    ... callee(c) ...   -- Error: constant
END;

Callable functions may be stored functions, local functions, or built-in functions. Among these, built-in functions refer to the CUBRID built-in functions listed in Operators and Functions. However, the built-in function IF cannot be used because it conflicts with PL/CSQL syntax.

If an error occurs while calling another stored function or built-in function, an SQL_ERROR Exception is raised.

CASE Expression¶

A CASE expression sequentially evaluates conditions and returns the value associated with the first condition that is satisfied.

Like the CASE statement, CASE expressions come in two forms:

The first form includes an expression immediately after the CASE keyword. That expression is evaluated once, then compared against each WHEN clause’s expression. When a match is found, the corresponding THEN expression is evaluated and becomes the result of the CASE expression.
The second form does not have an expression after the CASE keyword. In this case, each WHEN clause must be a BOOLEAN expression. The first one that evaluates to TRUE determines which THEN expression is returned as the CASE result.

Both forms may include an optional ELSE clause, whose expression becomes the CASE value if no conditions match. If no conditions match and no ELSE is present, the CASE expression evaluates to NULL.

The following is an example of the first form:

CREATE OR REPLACE PROCEDURE print_even_odd(i INTEGER)
AS
    s VARCHAR(5);
BEGIN
    s := CASE i MOD 2
        WHEN 0 THEN 'Even'
        WHEN 1 THEN 'Odd'
        ELSE 'NULL'
    END;

    DBMS_OUTPUT.put_line(s);
END;

The following shows the second form with equivalent behavior:

CREATE OR REPLACE PROCEDURE print_even_odd(i INTEGER)
AS
    s VARCHAR(5);
BEGIN
    s := CASE
        WHEN i MOD 2 = 0 THEN 'Even'
        WHEN i MOD 2 = 1 THEN 'Odd'
        ELSE 'NULL'
    END;

    DBMS_OUTPUT.put_line(s);
END;

SQLCODE, SQLERRM¶

Inside an exception handling block, SQLCODE and SQLERRM represent the error code (INTEGER) and error message (STRING) of the current Exception. Outside an exception block, SQLCODE is 0 and SQLERRM is ‘no error’.

CREATE OR REPLACE PROCEDURE test_sql_code_errm
AS
BEGIN
    ...
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('code=' || SQLCODE);
        DBMS_OUTPUT.put_line('error message' || SQLERRM);
END;