Expression

An expression is an arbitrarily complex combination of operands (variables, constants, literals, operators, function calls, and placeholders) and operators. The simplest expression is a single variable.

The PL/SQL compiler determines the data type of an expression from the types of the operands and operators that comprise the expression. Every time the expression is evaluated, a single value of that type results.

Topics:

• Syntax

• Semantics

• Examples

• Related Topics

Syntax

expression ::=

Description of the illustration expression.gif

See:

• boolean_expression ::=

• character_expression ::=

• date_expression ::=

• numeric_expression ::=

• searched_case_expression ::=

• simple_case_expression ::=

boolean_expression ::=

Description of the illustration boolean_expression.gif

See:

• function_call ::=

• boolean_literal ::=

conditional_predicate ::=

Description of the illustration conditional_predicate.gif

other_boolean_form ::=

Description of the illustration other_boolean_form.gif

See:

• named_cursor ::=

• expression ::=

character_expression ::=

Description of the illustration character_expression.gif

See:

• function_call ::=

• character_literal ::=

• placeholder_expression ::=

date_expression ::=

Description of the illustration date_expression.gif

See:

• function_call ::=

• placeholder_expression ::=

numeric_expression ::=

Description of the illustration numeric_expression.gif

numeric_subexpression ::=

Description of the illustration numeric_subexpression.gif

See:

• named_cursor ::=

• placeholder_expression ::=

• function_call ::=

• numeric_literal ::=

function_call ::=

Description of the illustration function_call.gif

simple_case_expression ::=

Description of the illustration simple_case_expression.gif

searched_case_expression ::=

Description of the illustration searched_case_expression.gif

See boolean_expression ::=.

Semantics

boolean_expression

An expression whose value is TRUE, FALSE, or NULL. For more information, see "BOOLEAN Expressions".

Restriction on boolean_expression Because SQL has no data type equivalent to BOOLEAN, you cannot:

• Assign a BOOLEAN expression to a database table column

• Select or fetch the value of a database table column into a BOOLEAN variable

• Use a BOOLEAN expression in a SQL statement, built-in SQL function, or PL/SQL function invoked from a SQL statement

NOT, AND, OR

See "Logical Operators".

boolean_constant_name

The name of a constant of type BOOLEAN.

boolean_function_call

A call to a previously defined function that returns a BOOLEAN value. For more semantic information, see function_call.

boolean_variable_name

The name of a variable of type BOOLEAN.

conditional_predicate

INSERTING, UPDATING, DELETING

See "Conditional Predicates for Detecting the DML Operation that Fired a Trigger".

column_name

The name of a column of the table or view on which the trigger that contains conditional_predicate is defined.

other_boolean_form

collection_name

The name of a collection.

index

An index for the collection collection_name—a numeric expression whose value is of type PLS_INTEGER or a value that can be implicitly converted to PLS_INTEGER (see Table 3-10, "Possible Implicit PL/SQL Data Type Conversions").

EXISTS

A collection method (function) that returns TRUE if the indexth element of the collection exists and FALSE otherwise. For more information, see "EXISTS Method".

Restriction on EXISTS You cannot use EXISTS if collection_name identifies an associative array.

SQL

The implicit cursor associated with the most recently run SELECT or DML statement. For more information, see "Implicit Cursors".

%FOUND, %ISOPEN, %NOTFOUND

Cursor attributes described in "Implicit Cursor Attribute" and "Named Cursor Attribute".

relational_operator

See "Relational Operators".

IS [NOT] NULL

See "IS [NOT] NULL Operator".

LIKE pattern

See "LIKE Operator".

BETWEEN expression AND expression

See "BETWEEN Operator".

IN expression [, expression ]...

See "IN Operator".

character_expression

An expression that returns a character value.

character_constant_name

The name of a constant that stores a character value.

character_function_call

A call to a previously defined function that returns either a character value or a value that can be implicitly converted to a character value. For more semantic information, see function_call.

character_variable_name

The name of a variable that stores a character value.

||

The concatenation operator, which appends one string operand to another. For more information, see "Concatenation Operator".

date_expression

An expression that returns a date value.

date_constant_name

The name of a constant that stores a date value.

date_function_call

A call to a previously defined function that returns either a date value or a value that can be implicitly converted to a date value. For more semantic information, see function_call.

date_literal

A literal whose value is either a date value or a value that can be implicitly converted to a date value.

date_variable_name

The name of a variable that stores a date value.

+, -

Addition and subtraction operators.

numeric_expression

An expression that returns a numeric value.

+, -, /, *, **

Addition, subtraction, division, multiplication, and exponentiation operators.

numeric_subexpression

SQL

The implicit cursor associated with the most recently run SELECT or DML statement. For more information, see "Implicit Cursors".

%ROWCOUNT

A cursor attribute described in "Implicit Cursor Attribute" and "Named Cursor Attribute".

%BULK_ROWCOUNT]

An attribute of the implicit cursor SQL for use with the FORALL statement. See SQL%BULK_ROWCOUNT.

numeric_constant_name

The name of a constant that stores a numeric value.

numeric_function_call

A call to a previously defined function that returns either a numeric value or a value that can be implicitly converted to a numeric value. For more semantic information, see function_call.

numeric_variable_name

The name of variable that stores a numeric value.

collection_name

The name of a collection.

COUNT, FIRST, LAST, LIMIT, NEXT, PRIOR

Collection methods described in "Collection Method Call".

exponent

An expression whose value is numeric.

function_call

function_name

The name of a previously defined function.

parameter [, parameter ]...

List of actual parameters for the function being called. The data type of each actual parameter must be compatible with the data type of the corresponding formal parameter (see Table 3-10, "Possible Implicit PL/SQL Data Type Conversions"). If the function specifies an initial value for a parameter, you can omit that parameter from the parameter list.

If the function has no parameters, or specifies an initial value for every parameter, you can either omit the parameter list or specify an empty parameter list.

simple_case_expression

selector

An expression of any PL/SQL type except BLOB, BFILE, or a user-defined type. The selector is evaluated once.

WHEN selector_value THEN result

The selector_values are evaluated sequentially. If a selector_value is the value of selector, then the result associated with that selector_value is returned. Subsequent selector_values are not evaluated.

A selector_value can be of any PL/SQL type except BLOB, BFILE, an ADT, a PL/SQL record, an associative array, a varray, or a nested table.

ELSE result

The result is returned if and only if no selector_value has the same value as selector.

If you omit the ELSE clause, the simple case expression returns NULL.

searched_case_expression

WHEN boolean_expression THEN result

The boolean_expressions are evaluated sequentially. If a boolean_expression has the value TRUE, then the result associated with that boolean_expression is returned. Subsequent boolean_expressions are not evaluated.

ELSE result

The result is returned if and only if no boolean_expression has the value TRUE.

If you omit the ELSE clause, the searched case expression returns NULL.

Examples

• Example 2-29, "Concatenation Operator"

• Example 2-30, "Concatenation Operator with NULL Operands"

• Example 2-31, "Controlling Evaluation Order with Parentheses"

• Example 2-32, "Expression with Nested Parentheses"

• Example 2-33, "Improving Readability with Parentheses"

• Example 2-34, "Operator Precedence"

• Example 2-43, "Relational Operators in Expressions"

• Example 2-44, "LIKE Operator in Expression"

• Example 2-46, "BETWEEN Operator in Expressions"

• Example 2-47, "IN Operator in Expressions"

• Example 2-50, "Simple CASE Expression"

• Example 2-52, "Searched CASE Expression"

Related Topics

In this chapter:

• "Collection Method Call"

• "Constant"

• "Literal"

• "Variable"

In other chapters:

• "Expressions"

• "Operator Precedence"

• "Predefined PL/SQL Scalar Data Types and Subtypes"

• "Implicit Conversion"